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 142 times)
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: 24912
Registered: January 2009
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


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: 611
Registered: July 2006
Senior Member
Post TKPROF
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.
Previous Topic: Question about parallel processing.
Next Topic: db block gets, consistent gets
Goto Forum:
  


Current Time: Thu Sep 29 00:32:10 CDT 2016

Total time taken to generate the page: 0.05391 seconds