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

Thread: TKPROF Help

  1. #1
    Join Date
    Feb 2004
    Posts
    35

    TKPROF Help

    Hello,
    Any suggestions on improving the following tkprof performance?
    Even though there are indexes in OBJ#(7910), it's still using FTS.

    TKPROF Output:
    Code:
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.10       0.10          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch      337     96.94      94.65          0     272401          0        5029
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total      339     97.04      94.76          0     272401          0        5029
    
    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 76  
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
       5029  SORT UNIQUE 
      30174   NESTED LOOPS  
       5029    NESTED LOOPS  
       5029     NESTED LOOPS  
       5029      TABLE ACCESS BY INDEX ROWID OBJ#(7912) 
       5029       BITMAP CONVERSION TO ROWIDS 
          1        BITMAP AND  
          1         BITMAP CONVERSION FROM ROWIDS 
       5032          INDEX RANGE SCAN OBJ#(8055) (object id 8055)
          2         BITMAP OR  
          2          BITMAP CONVERSION FROM ROWIDS 
     124354           INDEX RANGE SCAN OBJ#(21720) (object id 21720)
          1          BITMAP CONVERSION FROM ROWIDS 
          2           INDEX RANGE SCAN OBJ#(21720) (object id 21720)
       5029      TABLE ACCESS BY INDEX ROWID OBJ#(8347) 
       5029       INDEX UNIQUE SCAN OBJ#(8354) (object id 8354)
       5029     TABLE ACCESS BY INDEX ROWID OBJ#(7904) 
       5029      INDEX UNIQUE SCAN OBJ#(8041) (object id 8041)
      30174    TABLE ACCESS FULL OBJ#(7910) 
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                     337        0.00          0.00
      SQL*Net more data to client                   671        0.00          0.03
      SQL*Net message from client                   337       75.10        280.09
    ********************************************************************************
    Last edited by marist89; 12-16-2004 at 09:46 PM.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    format your post properly with the code tags, so hard to read otherwise

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Crystal ball broken -- what's the query, what's the explain plan you get when you execute it, and why do you think the FTS is bad?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    why do you think the FTS is bad?
    Atleast i can see here from row source operation the FTS on OBJ#(7910) is suerly bad in fact worst as its using a Nested Loop Operation and this obj being the 2nd obj in the join.. which means it has to do a FTS on that obj as many times as the inner loop has fetched the recs to outer loop..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    may be the index you are referring to is crap for your query

  6. #6
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    high cpu for fetch hints to i/o congestion
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by abhaysk
    Atleast i can see here from row source operation the FTS on OBJ#(7910) is suerly bad in fact worst as its using a Nested Loop Operation and this obj being the 2nd obj in the join.. which means it has to do a FTS on that obj as many times as the inner loop has fetched the recs to outer loop..
    So? How do you know that oracle can't read that OBJ#(7910) in a single (or at least in very few) I/O operations and that the number of rows it contains is fairly small?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    So? How do you know that oracle can't read that OBJ#(7910) in a single (or at least in very few) I/O operations and that the number of rows it contains is fairly small?
    Wat so ever might be the reason.. but nessted loops and the second obj for scan being FTS is obviously bad and will only increase PIO/LIO.. ( PIO incase if the table is not very small )..

    Instead hash is most optimal and faster.. ( this was what i wanted to convey ).. But may be the joining columns is not one to one and so it might be using nested loops.. if that was the case oracle should have choosed it to scan first and then join other with index look up..

    well u can only tell that confidently when u see the SQL.. it was just a speculation..

    Abhay..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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