1 2 Previous Next 25 Replies Latest reply: Aug 25, 2010 4:21 PM by Jonathan Lewis RSS

    Index rebuild

    user10394804
      Hi All,

      After rebuilding indexes of a table,one of my query performance degraded .

      I checked in another instance and found same result.

      Now...can someone help me how to revert it back to my my old statistic for that table?

      Thanks in advance
        • 1. Re: Index rebuild
          sb92075
          FLASHBACK DATABASE

          ALWAYS
          Post Operating System (OS) name & version for DB server system.
          Post results of
          SELECT * from v$version;
          • 2. Re: Index rebuild
            Philippe Florent
            The execution plan of your query probably changed but some points are missing. Rebuilding an index and collecting statistics on an index are not the same thing. Do you mean you collected statistics after the rebuild ? Is it a drop/recreate (in older Oracle versions statistics were not automatically collected at index creation and it can definitely change a plan) ? In fact can you post the commands you entered and maybe the query/execution plans ? Which Oracle version ?
            • 3. Re: Index rebuild
              user10394804
              DB version : 10.2.0.4
              OS : AIX

              there was 4 index on the table

              statement issued

              alter index <index_name> rebuild online;


              Thanks
              • 4. Re: Index rebuild
                Philippe Florent
                10.2 ? OK stats are automatically collected at create/rebuild then...
                Flashback the database just for that would be a bit too much imo (and probably "impossible" on a production system) If you have a recent export it could help you but in fact it's still impossible to be 100% sure the stats are the main cause. I saw strange things with bind peeking for example , you could believe a change of the stats was the cause but in fact what was important was the values of the bind variables in the first query fired after the change... Did you try to refresh stats on the other tables/indexes of the schema (at least on the index/tables involved in the query) ?
                Do you have the query and its execution plans (at least the new one !) ?

                Edited by: Phil Florent on Aug 21, 2010 8:57 PM
                • 5. Re: Index rebuild
                  Jonathan Lewis
                  user10394804 wrote:
                  Hi All,

                  After rebuilding indexes of a table,one of my query performance degraded .

                  I checked in another instance and found same result.

                  Now...can someone help me how to revert it back to my my old statistic for that table?
                  Two questions -
                  Do you have the execution plan for this query from before the rebuilds ?
                  Do you have statistics from the indexes from before the rebuilds ?

                  You may have one of two problems -
                  The plan is the same but the work needed to satisfy the plan has increased.
                  The plan has changed because the index stats are different.

                  Until you know why the query performance has degraded you cannot work out the best way of addressing the problem.

                  Most people rebuild indexes because that tends to make the index smaller - but it can make the index bigger. If this has happened in your case then the increased size of the index may cause an increase in physical I/O even if the plan has changed. Alternatively, rebuilding an index tends to change the position of "logically adjacent" blocks to make them "physically adjacent". For very large range scans this may help with certain classes of indexes. For other classes of index it's better to have "recently split" blocks physically adjacent even though such blocks are not necessarily "logically adjacent". Such things may explain why your performance could change even though the plan stays the same.

                  It is rather more likely, though, that rebuilding the indexes has changed their sizes (leaf_blocks), and since you are on 10.2.0.4 their statistics will have changed on the rebuild. It is eminently possible, therefore, that the plan has changed - possibly to take virtually the same path but using a different, much less appropriate, index to access this table, perhaps to change the path quite dramatically. In the former case you may be able to modify the statistcs using dbms_stats.set_index_stats() to restore something like the original stats; in either case you could simply try stacking the query with all the hints it takes to force the efficient path.

                  Regards
                  Jonathan Lewis
                  • 6. Re: Index rebuild
                    user10394804
                    Query and explain plan - after and before rebuild

                    After rebuild:
                    =============
                    SELECT DISTINCT 'Y' , WOOI.LAST_UPDATED_BY
                    FROM
                    MTL_TXN_REQUEST_LINES MTRL, MTL_MATERIAL_TRANSACTIONS_TEMP MMTT,
                    WMS_OP_OPERATION_INSTANCES WOOI, (SELECT WLPN.LPN_ID FROM
                    WMS_LICENSE_PLATE_NUMBERS WLPN START WITH WLPN.LPN_ID = :B1 CONNECT BY
                    PRIOR WLPN.LPN_ID = WLPN.PARENT_LPN_ID) WLPN WHERE MTRL.LPN_ID =
                    WLPN.LPN_ID AND MMTT.MOVE_ORDER_LINE_ID = MTRL.LINE_ID AND
                    WOOI.SOURCE_TASK_ID = MMTT.TRANSACTION_TEMP_ID AND WOOI.OPERATION_STATUS =
                    2 AND WOOI.OPERATION_TYPE_ID = 2 AND WOOI.LAST_UPDATED_BY <> :B2


                    call count cpu elapsed disk query current rows
                    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
                    Parse 1 0.00 0.00 0 0 0 0
                    Execute 1 0.00 0.01 0 0 0 0
                    Fetch 1 0.01 0.10 183 509 0 0
                    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
                    total 3 0.01 0.11 183 509 0 0

                    Misses in library cache during parse: 1
                    Misses in library cache during execute: 1
                    Optimizer mode: ALL_ROWS
                    Parsing user id: 44 (APPS) (recursive depth: 1)

                    Rows Execution Plan
                    ------- ---------------------------------------------------
                    0 SELECT STATEMENT MODE: ALL_ROWS
                    0 SORT (UNIQUE)
                    0 HASH JOIN
                    0 NESTED LOOPS
                    0 NESTED LOOPS
                    0 TABLE ACCESS MODE: ANALYZED (FULL) OF
                    'WMS_OP_OPERATION_INSTANCES' (TABLE)
                    0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
                    'MTL_MATERIAL_TRANSACTIONS_TEMP' (TABLE)
                    0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
                    'MTL_MATERIAL_TRANS_TEMP_U1' (INDEX (UNIQUE))
                    0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
                    'MTL_TXN_REQUEST_LINES' (TABLE)
                    0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
                    'MTL_TXN_REQUEST_LINES_U1' (INDEX (UNIQUE))
                    0 VIEW
                    0 CONNECT BY (WITH FILTERING)
                    0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
                    'WMS_LICENSE_PLATE_NUMBERS' (TABLE)
                    0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
                    'WMS_LICENSE_PLATE_NUMBERS_U1' (INDEX (UNIQUE))
                    0 NESTED LOOPS
                    0 CONNECT BY PUMP
                    0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
                    'WMS_LICENSE_PLATE_NUMBERS' (TABLE)
                    0 INDEX MODE: ANALYZED (RANGE SCAN) OF
                    'WMS_LICENSE_PLATE_NUMBERS_N1' (INDEX)

                    ********************************************************************************

                    Before Rebuild
                    ===============
                    SELECT DISTINCT 'Y' , WOOI.LAST_UPDATED_BY
                    FROM
                    MTL_TXN_REQUEST_LINES MTRL, MTL_MATERIAL_TRANSACTIONS_TEMP MMTT,
                    WMS_OP_OPERATION_INSTANCES WOOI, (SELECT WLPN.LPN_ID FROM
                    WMS_LICENSE_PLATE_NUMBERS WLPN START WITH WLPN.LPN_ID = :B1 CONNECT BY
                    PRIOR WLPN.LPN_ID = WLPN.PARENT_LPN_ID) WLPN WHERE MTRL.LPN_ID =
                    WLPN.LPN_ID AND MMTT.MOVE_ORDER_LINE_ID = MTRL.LINE_ID AND
                    WOOI.SOURCE_TASK_ID = MMTT.TRANSACTION_TEMP_ID AND WOOI.OPERATION_STATUS =
                    2 AND WOOI.OPERATION_TYPE_ID = 2 AND WOOI.LAST_UPDATED_BY <> :B2


                    call count cpu elapsed disk query current rows
                    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
                    Parse 1 0.00 0.00 0 0 0 0
                    Execute 1 0.04 0.05 0 0 0 0
                    Fetch 1 0.02 0.44 96 667 0 0
                    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
                    total 3 0.06 0.49 96 667 0 0

                    Misses in library cache during parse: 1
                    Misses in library cache during execute: 1
                    Optimizer mode: ALL_ROWS
                    Parsing user id: 44 (APPS) (recursive depth: 1)

                    Rows Execution Plan
                    ------- ---------------------------------------------------
                    0 SELECT STATEMENT MODE: ALL_ROWS
                    0 HASH (UNIQUE)
                    0 HASH JOIN
                    0 NESTED LOOPS
                    0 NESTED LOOPS
                    0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
                    'WMS_OP_OPERATION_INSTANCES' (TABLE)
                    0 INDEX MODE: ANALYZED (RANGE SCAN) OF
                    'WMS_OP_OPERATION_INS_N3' (INDEX)
                    0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
                    'MTL_MATERIAL_TRANSACTIONS_TEMP' (TABLE)
                    0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
                    'MTL_MATERIAL_TRANS_TEMP_U1' (INDEX (UNIQUE))
                    0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
                    'MTL_TXN_REQUEST_LINES' (TABLE)
                    0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
                    'MTL_TXN_REQUEST_LINES_U1' (INDEX (UNIQUE))
                    0 VIEW
                    0 CONNECT BY (WITH FILTERING)
                    0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
                    'WMS_LICENSE_PLATE_NUMBERS' (TABLE)
                    0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
                    'WMS_LICENSE_PLATE_NUMBERS_U1' (INDEX (UNIQUE))
                    0 NESTED LOOPS
                    0 CONNECT BY PUMP
                    0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
                    'WMS_LICENSE_PLATE_NUMBERS' (TABLE)
                    0 INDEX MODE: ANALYZED (RANGE SCAN) OF
                    'WMS_LICENSE_PLATE_NUMBERS_N1' (INDEX)
                    • 7. Re: Index rebuild
                      sb92075
                      Forgive me but posted SQL is not valid, AFAIK.

                      specifically
                      AND WOOI.LAST_UPDATED_BY :B2
                      above is missing some operator (=,<,>) to the left of ":B2"

                      Is COPY & PASTE broken for you?
                      • 8. Re: Index rebuild
                        user10394804
                        sorry ... its "<>"
                        • 9. Re: Index rebuild
                          user10394804
                          not equal to '<>'
                          • 10. Re: Index rebuild
                            Philippe Florent
                            OK the plan is different : you have a full on WMS_OP_OPERATION_INSTANCES / WOOI after the rebuild. On which column(s) WMS_OP_OPERATION_INS_N3 ? Which indexes were rebuilt ?
                            There is a bind variable and then I am still not 100% sure the stats are responsible for this change.
                            • 11. Re: Index rebuild
                              Jonathan Lewis
                              user10394804 wrote:
                              Query and explain plan - after and before rebuild
                              After rebuild:
                              =============
                              SELECT DISTINCT 'Y' , WOOI.LAST_UPDATED_BY 
                              FROM
                              MTL_TXN_REQUEST_LINES MTRL, MTL_MATERIAL_TRANSACTIONS_TEMP MMTT, 
                              WMS_OP_OPERATION_INSTANCES WOOI, (SELECT WLPN.LPN_ID FROM 
                              WMS_LICENSE_PLATE_NUMBERS WLPN START WITH WLPN.LPN_ID = :B1 CONNECT BY 
                              PRIOR WLPN.LPN_ID = WLPN.PARENT_LPN_ID) WLPN WHERE MTRL.LPN_ID = 
                              WLPN.LPN_ID AND MMTT.MOVE_ORDER_LINE_ID = MTRL.LINE_ID AND 
                              WOOI.SOURCE_TASK_ID = MMTT.TRANSACTION_TEMP_ID AND WOOI.OPERATION_STATUS = 
                              2 AND WOOI.OPERATION_TYPE_ID = 2 AND WOOI.LAST_UPDATED_BY <> :B2 
                              
                              
                              call     count       cpu    elapsed       disk      query    current        rows
                              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                              Parse        1      0.00       0.00          0          0          0           0
                              Execute      1      0.00       0.01          0          0          0           0
                              Fetch        1      0.01       0.10        183        509          0           0
                              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                              total        3      0.01       0.11        183        509          0           0
                              
                              Misses in library cache during parse: 1
                              Misses in library cache during execute: 1
                              Optimizer mode: ALL_ROWS
                              Parsing user id: 44  (APPS)   (recursive depth: 1)
                              
                              Rows     Execution Plan
                              -------  ---------------------------------------------------
                              0  SELECT STATEMENT   MODE: ALL_ROWS
                              0   SORT (UNIQUE)
                              0    HASH JOIN
                              0     NESTED LOOPS
                              0      NESTED LOOPS
                              0       TABLE ACCESS   MODE: ANALYZED (FULL) OF 
                              'WMS_OP_OPERATION_INSTANCES' (TABLE)
                              0       TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                              'MTL_MATERIAL_TRANSACTIONS_TEMP' (TABLE)
                              0        INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 
                              'MTL_MATERIAL_TRANS_TEMP_U1' (INDEX (UNIQUE))
                              0      TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                              'MTL_TXN_REQUEST_LINES' (TABLE)
                              0       INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 
                              'MTL_TXN_REQUEST_LINES_U1' (INDEX (UNIQUE))
                              0     VIEW
                              0      CONNECT BY (WITH FILTERING)
                              0       TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                              'WMS_LICENSE_PLATE_NUMBERS' (TABLE)
                              0        INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 
                              'WMS_LICENSE_PLATE_NUMBERS_U1' (INDEX (UNIQUE))
                              0       NESTED LOOPS
                              0        CONNECT BY PUMP
                              0        TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                              'WMS_LICENSE_PLATE_NUMBERS' (TABLE)
                              0         INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                              'WMS_LICENSE_PLATE_NUMBERS_N1' (INDEX)
                              
                              ********************************************************************************
                              
                              Before Rebuild
                              ===============
                              SELECT DISTINCT 'Y' , WOOI.LAST_UPDATED_BY 
                              FROM
                              MTL_TXN_REQUEST_LINES MTRL, MTL_MATERIAL_TRANSACTIONS_TEMP MMTT, 
                              WMS_OP_OPERATION_INSTANCES WOOI, (SELECT WLPN.LPN_ID FROM 
                              WMS_LICENSE_PLATE_NUMBERS WLPN START WITH WLPN.LPN_ID = :B1 CONNECT BY 
                              PRIOR WLPN.LPN_ID = WLPN.PARENT_LPN_ID) WLPN WHERE MTRL.LPN_ID = 
                              WLPN.LPN_ID AND MMTT.MOVE_ORDER_LINE_ID = MTRL.LINE_ID AND 
                              WOOI.SOURCE_TASK_ID = MMTT.TRANSACTION_TEMP_ID AND WOOI.OPERATION_STATUS = 
                              2 AND WOOI.OPERATION_TYPE_ID = 2 AND WOOI.LAST_UPDATED_BY <> :B2 
                              
                              
                              call     count       cpu    elapsed       disk      query    current        rows
                              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                              Parse        1      0.00       0.00          0          0          0           0
                              Execute      1      0.04       0.05          0          0          0           0
                              Fetch        1      0.02       0.44         96        667          0           0
                              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                              total        3      0.06       0.49         96        667          0           0
                              
                              Misses in library cache during parse: 1
                              Misses in library cache during execute: 1
                              Optimizer mode: ALL_ROWS
                              Parsing user id: 44  (APPS)   (recursive depth: 1)
                              
                              Rows     Execution Plan
                              -------  ---------------------------------------------------
                              0  SELECT STATEMENT   MODE: ALL_ROWS
                              0   HASH (UNIQUE)
                              0    HASH JOIN
                              0     NESTED LOOPS
                              0      NESTED LOOPS
                              0       TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                              'WMS_OP_OPERATION_INSTANCES' (TABLE)
                              0        INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                              'WMS_OP_OPERATION_INS_N3' (INDEX)
                              0       TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                              'MTL_MATERIAL_TRANSACTIONS_TEMP' (TABLE)
                              0        INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 
                              'MTL_MATERIAL_TRANS_TEMP_U1' (INDEX (UNIQUE))
                              0      TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                              'MTL_TXN_REQUEST_LINES' (TABLE)
                              0       INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 
                              'MTL_TXN_REQUEST_LINES_U1' (INDEX (UNIQUE))
                              0     VIEW
                              0      CONNECT BY (WITH FILTERING)
                              0       TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                              'WMS_LICENSE_PLATE_NUMBERS' (TABLE)
                              0        INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 
                              'WMS_LICENSE_PLATE_NUMBERS_U1' (INDEX (UNIQUE))
                              0       NESTED LOOPS
                              0        CONNECT BY PUMP
                              0        TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                              'WMS_LICENSE_PLATE_NUMBERS' (TABLE)
                              0         INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                              'WMS_LICENSE_PLATE_NUMBERS_N1' (INDEX)
                              I've added "code" tags (see below) to make your output more readable.
                              There is an oddity with your example - the AFTER is quicker than the BEFORE, contrary to your comments.

                              You'll notice that the main difference in the plans is that one plan uses a full tablescan and the other uses an index. Presumably that's one of the indexes you've rebuilt - and the one for which the statistics changed enough that the optimizer changed it's decision about using it. Do you have any before/after stats for that index.

                              One other problem, though. The plans you've got from your tkprof output are headed "Execution Plan", not "Rowsource Operation". This means they may be untrue. To cross-check you need to close down the session tidity (so that it dumps it's actual execution plans) or you need to use dbms_xplan.display_cursor() to pull the actual execution plans from memory.

                              Regards
                              Jonathan Lewis
                              • 12. Re: Index rebuild
                                Philippe Florent
                                Hi Jonathan,
                                I often forget this "Execution Plan" / "Rowsource Operation" difference and I have already made bad claims because of that. Always good to point it out !
                                Best regards
                                Phil
                                • 13. Re: Index rebuild
                                  Randolf Geist
                                  Jonathan Lewis wrote:
                                  Do you have any before/after stats for that index.
                                  I just wanted to point out that from version 10 on the automated history of statistics should allow to get information about the previous index statistics before the rebuild in SYS.WRI$_OPTSTAT_IND_HISTORY only to find out that there is bug 5519322 - "ALTER INDEX REBUILD does not backup old index statistics" that affects all releases up to and including 11.1.0.7 and is only fixed in 11.2.0.1. However one-off patches are available for some platforms / versions, including 10.2.0.4 AIX...

                                  So unfortunately that can't be used in this particular case here to check / restore the previous index statistics.

                                  Regards,
                                  Randolf

                                  Oracle related stuff blog:
                                  http://oracle-randolf.blogspot.com/

                                  Co-author of the "OakTable Expert Oracle Practices" book:
                                  http://www.apress.com/book/view/1430226684
                                  http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684
                                  • 14. Re: Index rebuild
                                    user10394804
                                    Hi

                                    Will exp,truncate and imp help ?

                                    Thanks
                                    1 2 Previous Next