Rem Filename : index_degrad.sql Rem Rem Parameters : &1 = [owner(pattern).]table(pattern) Rem The default value for owner is the currently connected user. Rem Rem Author : Rem Rem Modification history Rem -------------------- Rem 15-Nov-2002 V1.0 - Initial creation. Rem 24-May-2006 V1.1 - Added exception handlers for : "ORA-00054: resource busy and acquire with NOWAIT specified" Rem - Improved the documentation. Rem - Added report header. Rem Rem ============================================================================================================================== Rem Rem Description : This script lists some basic index degradation information. Rem The output can be used to decide which indexes/partitions are candidates for a rebuild. Rem Rem - The indexes on SYS owned tables are omitted. Rem - Partitioned indexes are supported. Rem - The client info in v$session is updated with the progress of report. Rem Rem The following values can be used in the decision making for the rebuild operation Rem Rem 1. B-tree % used : By default indexes are created with a B-tree fill degree of 90%. DML operations on the indexed Rem table will cause this value to drop slowly. In a few cases the value might rise as well. Rem You should consider rebuilding any index for which this value drops below 70%. Rem A drop of this value has a direct negative impact on the performance of index scan operations. Rem It is normal that small indexes show poor values for this figure, this isn't an indication of Rem potential index performance degradation. These low values usually increase upon adding more Rem rows to the table. Rem Rem 2. B-tree hight : If this value rises while the number of allocated leaf rows remains at a similar level, you Rem should consider rebuilding the index. Rem A drop of this value has a negative impact on the performance of index lookup operations. Rem Rem 3. Deleted rows degradation : In versions upto 8.0 this was type of degradation was rather problematic. Rem From 8.1 onwards, Oracle has greatly improved in this aspect. Significant Rem drops in this values also show up as a drop in the B-tree % used figure. Rem Therefore usually this value can be ignored during rebuild decision making. Rem Sample output Rem ------------- Rem MXXXXXXX @index_degrad est%.%his% Rem Rem Reporting indexes degradation for all tables matching "EST%.%HIS%", Rem -> 12 matching indexes/partitions found. Rem -> Total size = 61.632 Kbytes. Rem Rem Be patient this might take a while. Rem Rem Rem ------------------------------------------------------------------------------------------------------------------------ Rem Index degradation report for all tables owned by and name matching pattern <%HIS%> Rem Rem of database PDB1 on server deeisep1 Rem ------------------------------------------------------------------------------------------------------------------------ Rem Current date and time : Wednesday 24-MAY-2006 14:22:13 Rem ------------------------------------------------------------------------------------------------------------------------ Rem Rem Table owner : EST_CORE Rem Index B-tree B-tree B-tree Allocated B-tree degradation Rem Table Index Size(Kb) Size(Kb) % used Height Leaf rows Deleted rows (% del) Rem ------------------------------- ---------------------- -------- -------- ------ ------ ------------ -------------------- Rem CO_LRS_SEQ_STATUS_HISTS XIF1CO_LRS_SEQ_STATUS_H 64 39 45 2 1.116 0 ( 0%) Rem CO_LRS_SEQ_STATUS_HISTS XPKCO_LRS_SEQ_STATUS_HI 64 54 55 2 1.116 0 ( 0%) Rem CO_ORDER_HISTORY_TYPES XPKCO_ORDER_HISTORY_TYP 64 8 2 1 10 0 ( 0%) Rem CO_PO_HISTORY_ENTRIES XIF1CO_PO_HISTORY_ENTRI 2.048 1.367 59 2 51.068 81 ( 0%) Rem CO_PO_HISTORY_ENTRIES XIF2CO_PO_HISTORY_ENTRI 2.048 1.399 58 2 51.034 47 ( 0%) Rem CO_PO_HISTORY_ENTRIES XIF3CO_PO_HISTORY_ENTRI 2.048 1.652 46 2 51.023 36 ( 0%) Rem CO_PO_HISTORY_ENTRIES XPKCO_PO_HISTORY_ENTRIE 2.048 1.538 59 2 51.076 89 ( 0%) Rem CO_VEH_HISTORY_ENTRIES XAK1CO_VEH_HISTORY_ENTR 18.432 17.535 19 3 143.102 1.553 ( 1%) Rem CO_VEH_HISTORY_ENTRIES XIF1CO_VEH_HISTORY_ENTR 9.216 8.820 31 3 169.751 28.202 ( 17%) Rem CO_VEH_HISTORY_ENTRIES XIF3CO_VEH_HISTORY_ENTR 12.288 11.900 19 3 142.109 560 ( 0%) Rem CO_VEH_HISTORY_ENTRIES XPKCO_VEH_HISTORY_ENTRI 11.264 10.078 31 3 171.190 29.641 ( 17%) Rem Rem Table owner : EST_ZSA Rem Index B-tree B-tree B-tree Allocated B-tree degradation Rem Table Index Size(Kb) Size(Kb) % used Height Leaf rows Deleted rows (% del) Rem ------------------------------- ---------------------- -------- -------- ------ ------ ------------ -------------------- Rem ZS_LRS_SEQ_COUNTERS_HISTS XPKZS_LRS_SEQ_COUNTERS_ 2.048 1.130 60 2 19.622 0 ( 0%) Rem Rem Total elapsed time to analyze index degradation : 0:00:02 Rem Rem ============================================================================================================================== SET PAGESIZE 0 SET VERIFY OFF SET SERVEROUTPUT ON SIZE 500000 FORMAT WORD_WRAPPED SET FEEDBACK OFF SET TRIMSPOOL ON SET LINESIZE 3500 ALTER SESSION set nls_numeric_characters = ',.'; -- Disect the input argument, and get the owner filter and table filter into two seperate -- substitution variables. The owner filter defaults to the current user. DECLARE b_tree_height number; index_size number; btree_space number; btree_pct_used number; deleted_rows number; index_rows number; degradation number; count_indexes number := 0; sum_index_size number := 0; counter number := 0; datetime_begin date; elapsed_time_total number; first_index boolean := true; prev_owner dba_indexes.table_owner%type := '###'; prev_index dba_indexes.index_name%type := '###'; E_resource_busy EXCEPTION; PRAGMA EXCEPTION_INIT (E_resource_busy, -54); BEGIN datetime_begin := sysdate; dbms_application_info.set_module( module_name => 'INDEX_DEGRAD', action_name => 'initializing' ); dbms_output.put_line('Index Index B-tree B-tree B-tree Allocated B-tree degradation'); dbms_output.put_line('Name Size(Mb) Size(Mb) % used Height Leaf rows Deleted rows (% del)' ); dbms_output.put_line('----------------------------------------------------------- -------- -------- ------ ------ --------- --------------------' ); prev_owner := ''; first_index := false; FOR r_index in (SELECT name, partition_name, height b_tree_height, del_lf_rows deleted_rows, lf_rows index_rows, del_lf_rows/(lf_rows+0.00001) degradation, blocks*db.block_size index_size, round(btree_space/1024/1024) btree_space, nvl(pct_used,0) btree_pct_used FROM index_stats, (SELECT value/1024/1024 block_size FROM v$parameter WHERE name = 'db_block_size') db) LOOP dbms_output.put_line(rpad(r_index.name || '.' || r_index.partition_name, 60 ) || to_char(r_index.index_size,'999G999') || ' ' || to_char(r_index.btree_space,'999G999') || ' ' || to_char(r_index.btree_pct_used,'99999') || ' ' || to_char(r_index.b_tree_height,'9G999') || ' ' || to_char(r_index.index_rows,'999G999G999') || ' ' || to_char(r_index.deleted_rows,'999G999G999') || ' (' || lpad(ltrim(to_char(round(r_index.degradation*100),'990')),3) || '%)' ); END LOOP; dbms_application_info.set_module( module_name => 'SQL*Plus', action_name => '' ); dbms_application_info.set_client_info( client_info => '' ); END; / SET FEEDBACK ON SET PAGESIZE 9999