-
Recursive Statements consumes lot of time?
Dear Tom,
One particular operation on our databases takes 39 seconds and SqlTrace\TkProf
tells us that non recursive statements takes 1.15 seconds whereas recursive
statements issued by oracle consumes 37.15 seconds.
Code:
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 107 0.09 0.10 0 3 0 0
Execute 206 1.03 1.05 2 130 268 180
Fetch 206 0.03 0.02 0 1714 0 283
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 519 1.15 1.17 2 1847 268 463
Misses in library cache during parse: 43
Misses in library cache during execute: 37
-------------------------------------------------------
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 22265 1.39 1.17 0 0 0 0
Execute 34614 23.48 24.42 4 70946 20210 12667
Fetch 23961 12.28 11.78 11 1098480 0 16335
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 80840 37.15 37.38 15 1169426 20210 29002
Misses in library cache during parse: 42
Misses in library cache during execute: 37
How should we do to improve on this? Could you please guide us.
Thanks & Regards,
Shailesh
Last edited by marist89; 09-15-2005 at 11:55 AM.
Reason: formatting
-
Well, I'm not Tom (that's an understatement), but what are your wait events?
Jeff Hunter
-
You ought to be able to pin down which of the recursive statements is consuming the majority of the time, based on the trace file. You know how to track dependencies between non-recursive and recursive statements in the trace file? And what was the operation?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|