DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Recursive Statements consumes lot of time?

  1. #1
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80

    Question 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

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Well, I'm not Tom (that's an understatement), but what are your wait events?
    Jeff Hunter

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width