Performance Tuning [message #422793] |
Sat, 19 September 2009 04:55 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
kmkumar24
Messages: 69 Registered: January 2007 Location: Singapore
|
Member |
|
|
Hello,
Oracle gurus, I need to understand the Explain plan here which is given to me for investigation. Actual problem is, the querry was running very well in UAT env, but it is very slow in Prod env.
Pl help me to give your suggestions. Thanks
-
Attachment: xplan.txt
(Size: 7.43KB, Downloaded 1308 times)
|
|
|
|
|
Re: Performance Tuning [message #422920 is a reply to message #422793] |
Mon, 21 September 2009 03:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Here's a readable version of the OP's attachment:----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 459 | 407K| 157 | | |
| 1 | HASH GROUP BY | | 459 | 407K| 157 | | |
| 2 | HASH JOIN | | 459 | 407K| 156 | | |
| 3 | TABLE ACCESS FULL | NMY_DATE_DIM | 1826 | 25564 | 6 | | |
| 4 | HASH JOIN | | 459 | 401K| 149 | | |
| 5 | PARTITION RANGE ALL | | 96 | 1248 | 2 | 1 | 7 |
| 6 | TABLE ACCESS FULL | NMY_MAIN_INVOICE_FACT | 96 | 1248 | 2 | 1 | 7 |
| 7 | HASH JOIN | | 836 | 720K| 147 | | |
| 8 | TABLE ACCESS FULL | SH_BA_INVOICE_CONTACT | 87 | 3741 | 2 | | |
| 9 | HASH JOIN | | 60219 | 48M| 143 | | |
| 10 | TABLE ACCESS FULL | NMY_BILL_PERIOD_DIM | 72 | 1368 | 2 | | |
| 11 | HASH JOIN RIGHT OUTER | | 60219 | 47M| 140 | | |
| 12 | VIEW | | 5 | 725 | 5 | | |
| 13 | HASH JOIN | | 5 | 490 | 5 | | |
| 14 | TABLE ACCESS FULL | NMY_VALUE_TYPE_DIM | 1 | 34 | 2 | | |
| 15 | PARTITION RANGE ALL | | 14 | 896 | 2 | 1 | 7 |
| 16 | TABLE ACCESS FULL | NMY_SUB_VALUE_FACT | 14 | 896 | 2 | 1 | 7 |
| 17 | HASH JOIN | | 60219 | 38M| 135 | | |
| 18 | INDEX FAST FULL SCAN | NMY_SINVC_FACT_PK | 389 | 14004 | 2 | | |
| 19 | HASH JOIN | | 60219 | 36M| 131 | | |
| 20 | TABLE ACCESS FULL | NMY_DEST_ZONE_DIM | 217 | 3255 | 2 | | |
| 21 | HASH JOIN | | 60219 | 35M| 128 | | |
| 22 | TABLE ACCESS FULL | NMY_BILL_ACCT_DIM | 106 | 2120 | 2 | | |
| 23 | HASH JOIN | | 60219 | 34M| 125 | | |
| 24 | TABLE ACCESS FULL | NMY_BILL_PERIOD_DIM | 72 | 1368 | 2 | | |
| 25 | HASH JOIN | | 60219 | 33M| 122 | | |
| 26 | HASH JOIN | | 70 | 8750 | 27 | | |
| 27 | TABLE ACCESS FULL | NMY_ACTOR_DIM | 25 | 425 | 2 | | |
| 28 | HASH JOIN | | 70 | 7560 | 25 | | |
| 29 | FILTER | | | | | | |
| 30 | HASH JOIN RIGHT OUTER | | 70 | 6790 | 22 | | |
| 31 | VIEW | | 10 | 210 | 4 | | |
| 32 | HASH JOIN | | 10 | 290 | 4 | | |
| 33 | INDEX FULL SCAN | NMY_CNTR_ACL_PK | 10 | 100 | 1 | | |
| 34 | TABLE ACCESS FULL | NMY_ACTOR_DIM | 17 | 323 | 2 | | |
| 35 | HASH JOIN RIGHT OUTER | | 338 | 25688 | 18 | | |
| 36 | TABLE ACCESS FULL | NMY_COST_CENTER_DIM | 5 | 100 | 2 | | |
| 37 | HASH JOIN RIGHT OUTER | | 338 | 18928 | 16 | | |
| 38 | INDEX FULL SCAN | NMY_CCC_LNK_PK | 6 | 48 | 1 | | |
| 39 | HASH JOIN | | 338 | 16224 | 14 | | |
| 40 | INDEX FULL SCAN | NMY_CNTR_ACL_PK | 10 | 100 | 1 | | |
| 41 | MERGE JOIN CARTESIAN| | 304 | 11552 | 13 | | |
| 42 | HASH JOIN | | 38 | 722 | 4 | | |
| 43 | TABLE ACCESS FULL | NMY_USER_DIM | 13 | 143 | 2 | | |
| 44 | INDEX FULL SCAN | NMY_FSCP_LNK_PK | 38 | 304 | 1 | | |
| 45 | BUFFER SORT | | 8 | 152 | 12 | | |
| 46 | TABLE ACCESS FULL | NMY_ACTOR_DIM | 8 | 152 | 0 | | |
| 47 | TABLE ACCESS FULL | NMY_ACTOR_LNK | 15 | 165 | 2 | | |
| 48 | VIEW | | 6846 | 3075K| 94 | | |
| 49 | SORT UNIQUE | | 6846 | 755K| 94 | | |
| 50 | UNION-ALL | | | | | | |
| 51 | HASH GROUP BY | | 3423 | 377K| 47 | | |
| 52 | HASH JOIN | | 3423 | 377K| 44 | | |
| 53 | TABLE ACCESS FULL | NMY_UDR_TYPE_DIM | 31 | 713 | 2 | | |
| 54 | PARTITION RANGE ALL | | 15754 | 1384K| 42 | 1 | 7 |
| 55 | TABLE ACCESS FULL | NMY_BUDR_FACT | 15754 | 1384K| 42 | 1 | 7 |
| 56 | HASH GROUP BY | | 3423 | 377K| 47 | | |
| 57 | HASH JOIN | | 3423 | 377K| 44 | | |
| 58 | TABLE ACCESS FULL | NMY_UDR_TYPE_DIM | 31 | 713 | 2 | | |
| 59 | PARTITION RANGE ALL | | 15754 | 1384K| 42 | 1 | 7 |
| 60 | TABLE ACCESS FULL | NMY_BUDR_FACT | 15754 | 1384K| 42 | 1 | 7 |
----------------------------------------------------------------------------------------------------------------
I'll guess that you've got a datawarehouse environment, and that you've got a lot more data in Prod that you do in UAT.
|
|
|