This discussion is archived
1 2 Previous Next 25 Replies Latest reply: Aug 25, 2010 2:21 PM by Jonathan Lewis Go to original post RSS
  • 15. Re: Index rebuild
    user10394804 Newbie
    Currently Being Moderated
    Explain plan - After
    ============

    Operation     Object Name     Rows     Bytes     Cost
                        
    SELECT STATEMENT Hint=ALL_ROWS          46          2003
    HASH UNIQUE          46     1 K     2003
    HASH JOIN          488     20 K     2002
    NESTED LOOPS          488     17 K     1970
    NESTED LOOPS          488     13 K     993
    TABLE ACCESS FULL     WMS_OP_OPERATION_INSTANCES     488     8 K     16
    TABLE ACCESS BY INDEX ROWID     MTL_MATERIAL_TRANSACTIONS_TEMP     1     12     2
    INDEX UNIQUE SCAN     MTL_MATERIAL_TRANS_TEMP_U1     1          1
    TABLE ACCESS BY INDEX ROWID     MTL_TXN_REQUEST_LINES     1     8     2
    INDEX UNIQUE SCAN     MTL_TXN_REQUEST_LINES_U1     1          1
    VIEW          4 K     22 K     31
    CONNECT BY WITH FILTERING                    
    TABLE ACCESS BY INDEX ROWID     WMS_LICENSE_PLATE_NUMBERS     1     17     2
    INDEX UNIQUE SCAN     WMS_LICENSE_PLATE_NUMBERS_U1     1          1
    NESTED LOOPS                    
    CONNECT BY PUMP                    
    TABLE ACCESS BY INDEX ROWID     WMS_LICENSE_PLATE_NUMBERS     4 K     30 K     31
    INDEX RANGE SCAN     WMS_LICENSE_PLATE_NUMBERS_N1     65          1


    Before
    ---------

    Operation     Object Name     Rows     Bytes     Cost
                        
    SELECT STATEMENT Hint=ALL_ROWS          47          239
    HASH UNIQUE          47     1 K     239
    HASH JOIN          451     18 K     238
    HASH JOIN          451     15 K     231
    TABLE ACCESS BY INDEX ROWID     WMS_OP_OPERATION_INSTANCES     451     7 K     36
    INDEX RANGE SCAN     WMS_OP_OPERATION_INS_N3     922          5
    NESTED LOOPS          1 K     27 K     195
    TABLE ACCESS BY INDEX ROWID     MTL_MATERIAL_TRANSACTIONS_TEMP     1 K     15 K     190
    INDEX FULL SCAN     MTL_MATERIAL_TRANS_TEMP_N14     1 K          28
    TABLE ACCESS BY INDEX ROWID     MTL_TXN_REQUEST_LINES     1     8     1
    INDEX UNIQUE SCAN     MTL_TXN_REQUEST_LINES_U1     1          1
    VIEW          6 K     30 K     6
    CONNECT BY WITH FILTERING                    
    TABLE ACCESS BY INDEX ROWID     WMS_LICENSE_PLATE_NUMBERS     1     17     1
    INDEX UNIQUE SCAN     WMS_LICENSE_PLATE_NUMBERS_U1     1          1
    NESTED LOOPS                    
    CONNECT BY PUMP                    
    TABLE ACCESS BY INDEX ROWID     WMS_LICENSE_PLATE_NUMBERS     6 K     42 K     6
    INDEX RANGE SCAN     WMS_LICENSE_PLATE_NUMBERS_N1     66          1


    Just cost of the query went up from 239 to 2003

    thanks
  • 16. Re: Index rebuild
    CharlesHooper Expert
    Currently Being Moderated
    user10394804 wrote:
    Explain plan - After
    (snip)
    Before
    (snip)
    Just cost of the query went up from 239 to 2003

    thanks
    When posting execution plans, please use a { code } tag (without spaces) before and after the execution plan - the plan is impossible, or very difficult to read without retained spaces. Additionally, the columns in the execution plan seems to be aligned with tab characters rather than space characters - so the alignment will not look correct in a forum post.

    I brought the execution plan into a spreadsheet program, which allowed me to easily compare the lines in the execution plan. As already mentioned by Jonathan, there were more changes than just the calculated cost of the execution plan. Here is a side-by-side comparison:
    After                                                                                           Before
    Operation                                  Object Name                        Rows Bytes  Cost  Operation                                    Object Name                       Rows Bytes  Cost
    SELECT STATEMENT Hint=ALL_ROWS                                                  46        2003  SELECT STATEMENT Hint=ALL_ROWS                                                   47         239
      HASH UNIQUE                                                                   46   1 K  2003    HASH UNIQUE                                                                    47   1 K   239
        HASH JOIN                                                                  488  20 K  2002      HASH JOIN                                                                   451  18 K   238
          NESTED LOOPS                                                             488  17 K  1970        HASH JOIN                                                                 451  15 K   231
            NESTED LOOPS                                                           488  13 K   993          TABLE ACCESS BY INDEX ROWID          WMS_OP_OPERATION_INSTANCES         451   7 K    36
              TABLE ACCESS FULL                WMS_OP_OPERATION_INSTANCES          488   8 K    16            INDEX RANGE SCAN                   WMS_OP_OPERATION_INS_N3            922           5
              TABLE ACCESS BY INDEX ROWID      MTL_MATERIAL_TRANSACTIONS_TEMP        1    12     2          NESTED LOOPS                                                            1 K  27 K   195
                INDEX UNIQUE SCAN              MTL_MATERIAL_TRANS_TEMP_U1            1           1            TABLE ACCESS BY INDEX ROWID        MTL_MATERIAL_TRANSACTIONS_TEMP     1 K  15 K   190
            TABLE ACCESS BY INDEX ROWID        MTL_TXN_REQUEST_LINES                 1     8     2              INDEX FULL SCAN                  MTL_MATERIAL_TRANS_TEMP_N14        1 K          28
              INDEX UNIQUE SCAN                MTL_TXN_REQUEST_LINES_U1              1           1            TABLE ACCESS BY INDEX ROWID        MTL_TXN_REQUEST_LINES                1     8     1
          VIEW                                                                     4 K  22 K    31              INDEX UNIQUE SCAN                MTL_TXN_REQUEST_LINES_U1             1           1
            CONNECT BY WITH FILTERING                                                                     VIEW                                                                      6 K  30 K     6
              TABLE ACCESS BY INDEX ROWID      WMS_LICENSE_PLATE_NUMBERS             1    17     2          CONNECT BY WITH FILTERING
                INDEX UNIQUE SCAN              WMS_LICENSE_PLATE_NUMBERS_U1          1           1            TABLE ACCESS BY INDEX ROWID        WMS_LICENSE_PLATE_NUMBERS            1    17     1
              NESTED LOOPS                                                                                      INDEX UNIQUE SCAN                WMS_LICENSE_PLATE_NUMBERS_U1         1           1
                CONNECT BY PUMP                                                                               NESTED LOOPS
                TABLE ACCESS BY INDEX ROWID    WMS_LICENSE_PLATE_NUMBERS           4 K  30 K    31              CONNECT BY PUMP
                  INDEX RANGE SCAN             WMS_LICENSE_PLATE_NUMBERS_N1         65           1              TABLE ACCESS BY INDEX ROWID      WMS_LICENSE_PLATE_NUMBERS          6 K  42 K     6
                                                                                                                  INDEX RANGE SCAN               WMS_LICENSE_PLATE_NUMBERS_N1        66           1
    Notice that the MTL_MATERIAL_TRANS_TEMP_N14 and WMS_OP_OPERATION_INS_N3 indexes are no longer used, the table join order changed slightly (note the NESTED LOOPS on the fifth line of the new plan when it was on the seventh line before), and one of the hash joins changed into a nested loop join.

    Keep in mind that the plan only shows estimates - do not rely just on the displayed costs to determine which execution plan is best. Additionally, if the SQL statement contained bind variables, there is an even greater chance that an incorrect execution plan is displayed for this SQL statement. See Jonathan's comment for retrieving the actual execution plan.

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 17. Re: Index rebuild
    user10394804 Newbie
    Currently Being Moderated
    Hi

    Thanks for the reply

    One more query...We used to update our table statictic everyday for estimate_percentage 30%

    After rebuild ,found that all query related to one table is facing perfromance issue,what might be the reason?

    When I ran Gather Table statistics for that table for estimate_percentage 50 ,performance improved...What might be the reason?

    Thanks in advance
  • 18. Re: Index rebuild
    sb92075 Guru
    Currently Being Moderated
    What might be the reason?
    Larger sample size results in more accurate statistics
  • 19. Re: Index rebuild
    user10394804 Newbie
    Currently Being Moderated
    Ok...but 30% was giving good result..why suddenly after rebuild 30% was not enough
  • 20. Re: Index rebuild
    sb92075 Guru
    Currently Being Moderated
    why suddenly after rebuild 30% was not enough
    The characteristics of the data changed.
    Data could now be more skewed.
  • 21. Re: Index rebuild
    jgarry Guru
    Currently Being Moderated
    If you have dbcontrol configured, see if it has a history of previous statistics for the table and indices. Even if you have the scary bug Randolf mentioned, there may still be previous statistics that you can restore, test, and lock if they work, lots easier than trying to deal directly with the history table. With all the silliness of dbcontrol, that's one thing I've found useful. Make users happy, then figure out real answer at leisure.
  • 22. Re: Index rebuild
    ca200197 Newbie
    Currently Being Moderated
    Unfortunately, index rebuilds do not save the prior statistics. The fact of the index rebuild is recorded but no statistics. I have an SR open for over two years on that.

    I just noticed that I hadn't read Randolf's post in its entirety before replying.

    Edited by: ca200197 on Aug 25, 2010 9:52 AM

    Edited by: ca200197 on Aug 25, 2010 9:54 AM
  • 23. Re: Index rebuild
    ca200197 Newbie
    Currently Being Moderated
    Anything less than 100% uses sampling. You could just get unlucky with the samples picked. Even repeating the gather stats with the same 30% could give you different statistics. BTW, are you gathering histograms ( method opt => 'for all columns size {auto | skewonly}' )? Sampled histograms are especially susceptible to changes due to different samples.
  • 24. Re: Index rebuild
    OraDBA02 Newbie
    Currently Being Moderated
    Hi Randolf,

    Thanks for this valuable information.

    May i also ask you, how to restore old index statistics for this dictionary view? I see that there is no DBMS_STATS.RESTORE_INDEX_STATS procedure. Can i also ask if there is similar procedure to restore table partition and index partition (and subpartition) statistics as well ?

    Thanks beforehand...
  • 25. Re: Index rebuild
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    OraDBA02 wrote:

    May i also ask you, how to restore old index statistics for this dictionary view? I see that there is no DBMS_STATS.RESTORE_INDEX_STATS procedure. Can i also ask if there is similar procedure to restore table partition and index partition (and subpartition) statistics as well ?
    If you check the dbms_stats package you'll find it has procedures to set_table_stats(), set_column_stats() and set_index_stats(). In cases where you can't use the routines for exporting stats to a stat table and subsequently importing them, you can use these procedures to write whatever values you like into the data dictionary.

    Regards
    Jonathan Lewis
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points