| Reading explain plan [message #528378] |
Mon, 24 October 2011 10:11  |
 |
Kwisatz78
Messages: 24
Registered: October 2011
|
Junior Member |
|
|
Hi all
I am very wet behind the ears when it comes to Oracle, however I was able to identify a poorly performing query that seemed to be maxing out our CPU. I have been trying to understand the Explain Plan. The plan below is from our test system which has considerably less information in the tables than our PROD system.
I can see there are a bunch of table scans at the end which may indicate missing indexes, but I am unclear on whether this is actually a problem as the %CPU seems to be worse for the JOIN near the top of the plan.
Can anyone explain(no pun intended) to me how to read the plans and where our issue lies?
Many thanks
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1870M| 3018G| | 677M (1)|999:59:59 | | |
| 1 | SORT ORDER BY | | 1870M| 3018G| 3567G| 677M (1)|999:59:59 | | |
|* 2 | HASH JOIN RIGHT OUTER | | 1870M| 3018G| | 10031 (93)| 00:02:01 | | |
| 3 | VIEW | VW_CUSTDOCUMENTCATEGORY | 1397 | 526K| | 7 (15)| 00:00:01 | | |
| 4 | REMOTE | | | | | | | DB~ | R->S |
|* 5 | HASH JOIN RIGHT OUTER | | 22M| 28G| | 837 (15)| 00:00:11 | | |
| 6 | VIEW | VW_CUSTOMCLRNLIST | 1397 | 387K| | 7 (15)| 00:00:01 | | |
| 7 | REMOTE | | | | | | | DB~ | R->S |
|* 8 | HASH JOIN RIGHT OUTER | | 407K| 412M| | 718 (2)| 00:00:09 | | |
| 9 | VIEW | VW_CUSTDOCUMENTTYPE | 1397 | 526K| | 7 (15)| 00:00:01 | | |
| 10 | REMOTE | | | | | | | DB~ | R->S |
|* 11 | HASH JOIN RIGHT OUTER | | 20702 | 13M| | 709 (2)| 00:00:09 | | |
| 12 | VIEW | VW_CUSTDOCUMENTLEVEL | 100 | 38600 | | 3 (34)| 00:00:01 | | |
| 13 | REMOTE | | | | | | | DB~ | R->S |
|* 14 | HASH JOIN | | 1449 | 411K| | 706 (1)| 00:00:09 | | |
|* 15 | HASH JOIN | | 1446 | 355K| | 569 (2)| 00:00:07 | | |
|* 16 | HASH JOIN | | 1549 | 119K| | 310 (2)| 00:00:04 | | |
| 17 | VIEW | VW_SQ_1 | 22690 | 709K| | 155 (2)| 00:00:02 | | |
| 18 | HASH GROUP BY | | 22690 | 487K| | 155 (2)| 00:00:02 | | |
| 19 | TABLE ACCESS FULL| REVISIONS | 23233 | 499K| | 154 (1)| 00:00:02 | | |
|* 20 | TABLE ACCESS FULL | REVISIONS | 23231 | 1066K| | 154 (1)| 00:00:02 | | |
| 21 | TABLE ACCESS FULL | DOCMETA | 21691 | 3664K| | 258 (1)| 00:00:04 | | |
|* 22 | TABLE ACCESS FULL | DOCUMENTS | 21737 | 827K| | 137 (1)| 00:00:02 | | |
-------------------------------------------------------------------------------------------------------------------------------
* <code_tags> added by BlackSwan
[Updated on: Mon, 24 October 2011 10:17] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
