Home » RDBMS Server » Performance Tuning » Performance Tuning (Sun Solaris)
Performance Tuning [message #422793] Sat, 19 September 2009 04:55 Go to next message
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 1233 times)

Report message to a moderator

Re: Performance Tuning [message #422806 is a reply to message #422793] Sat, 19 September 2009 09:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
For me, xplan.txt appears wrapped & not very readable.
I see many Full Table Scans.
How do Plans from UAT & Prod compare?
Do all tables & indexes on Prod have current statistics?

http://www.orafaq.com/forum/t/84315/74940/
which have you tried & what were the results

Report message to a moderator

Re: Performance Tuning [message #422816 is a reply to message #422806] Sat, 19 September 2009 15:53 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Post TKPROF

Report message to a moderator

Re: Performance Tuning [message #422920 is a reply to message #422793] Mon, 21 September 2009 03:41 Go to previous message
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.

Report message to a moderator

Previous Topic: Question about parallel processing.
Next Topic: db block gets, consistent gets
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat May 18 07:13:59 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.