1 2 Previous Next 25 Replies Latest reply: Aug 25, 2010 4:21 PM by Jonathan Lewis Go to original post RSS
      • 15. Re: Index rebuild
        user10394804
        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
          Charles Hooper
          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
            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
              What might be the reason?
              Larger sample size results in more accurate statistics
              • 19. Re: Index rebuild
                user10394804
                Ok...but 30% was giving good result..why suddenly after rebuild 30% was not enough
                • 20. Re: Index rebuild
                  sb92075
                  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
                    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
                      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
                        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
                          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
                            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