-
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.
-
format your post properly with the code tags, so hard to read otherwise
-
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?
-
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"
-
may be the index you are referring to is crap for your query
-
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
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|