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. DEFINE InputOwnerFilter = ' ' DEFINE InputTableFilter = ' ' COLUMN table_owner NOPRINT NEW_VALUE InputOwnerFilter COLUMN table_name NOPRINT NEW_VALUE InputTableFilter SELECT decode(instr('&&1','.'), 0, user, /*Default to current user.*/ upper(substr('&&1',1,instr('&&1','.')-1))) table_owner, decode(instr('&&1','.'), 0, upper('&&1'), /*Only the table name was passed in.*/ upper(substr('&&1',instr('&&1','.')+1))) table_name FROM dual; CLEAR COLUMNS DECLARE count_indexes number := 0; sum_index_size number := 0; BEGIN SELECT count(*) count_indexes, sum(bytes)/1024 sum_index_size INTO count_indexes, sum_index_size FROM dba_indexes i, dba_segments s WHERE i.index_name = s.segment_name AND i.table_name like '&InputTableFilter' AND i.owner = s.owner AND i.table_owner like '&InputOwnerFilter' AND i.index_type = 'NORMAL' AND i.table_owner != 'SYS'; IF count_indexes > 0 THEN dbms_output.put_line( 'Reporting indexes degradation for all tables matching "&InputOwnerFilter..&InputTableFilter", ' ); dbms_output.put_line( '-> ' || to_char(count_indexes) || ' matching indexes/partitions found.' ); dbms_output.put_line( '-> Total size = ' || ltrim(to_char(sum_index_size,'999G999G999')) || ' Kbytes.' ); dbms_output.put_line( chr(10) || 'Be patient this might take a while.' ); ELSE dbms_output.put_line( chr(10) || 'No indexes found on the table matching the given pattern "&InputOwnerFilter..&InputTableFilter".' ); END IF; END; / PROMPT @@header_title 120 DC 'Index degradation report for all tables owned by <&InputOwnerFilter> and name matching pattern <&InputTableFilter>' 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' ); SELECT count(*) count_indexes, sum(bytes)/1024 sum_index_size INTO count_indexes, sum_index_size FROM dba_indexes i, dba_segments s WHERE i.index_name = s.segment_name AND i.table_name like '&InputTableFilter' AND i.owner = s.owner AND i.table_owner like '&InputOwnerFilter' AND i.index_type = 'NORMAL' AND i.table_owner != 'SYS'; FOR r_index IN ( SELECT i.table_name table_name, i.table_owner table_owner, i.owner index_owner, i.index_name, i.partitioned, s.partition_name, s.bytes/1024 index_size, p.partition_position FROM dba_indexes i, dba_segments s, dba_ind_partitions p WHERE i.index_name = s.segment_name AND i.index_type = 'NORMAL' AND i.table_name like '&InputTableFilter' AND i.owner = s.owner AND i.table_owner = '&InputOwnerFilter' AND i.table_owner != 'SYS' AND s.owner = '&InputOwnerFilter' AND s.owner = p.index_owner (+) AND s.segment_name = p.index_name (+) AND s.partition_name = p.partition_name (+) ORDER BY table_owner, table_name, i.index_name, partition_position ) LOOP counter := counter + 1; dbms_application_info.set_action( action_name => 'analyzing' ); dbms_application_info.set_client_info( client_info => 'progress : ' || to_char(counter) || '/' || to_char(count_indexes) ); -- For each new owner encountered (re-display) the heading. IF prev_owner <> r_index.table_owner THEN dbms_output.put_line( chr(10) || 'Table owner : ' || r_index.table_owner || chr(10) || lpad('Index B-tree B-tree B-tree Allocated B-tree degradation', 120) ); dbms_output.put_line( 'Table Index Size(Kb) Size(Kb) % used Height Leaf rows Deleted rows (% del)' ); dbms_output.put_line( '------------------------------- ---------------------- -------- -------- ------ ------ ------------ --------------------' ); prev_owner := r_index.table_owner; first_index := false; END IF; IF r_index.partitioned = 'NO' THEN BEGIN --execute immediate 'validate index ' || r_index.index_owner || '.' || r_index.index_name; SELECT height, del_lf_rows, lf_rows, del_lf_rows/(lf_rows+0.00001), blocks*db.block_size, round(btree_space/1024), nvl(pct_used,0) INTO b_tree_height, deleted_rows, index_rows, degradation, index_size, btree_space, btree_pct_used FROM acc_admin.t_informe_indice, (SELECT value/1024 block_size FROM v$parameter WHERE name = 'db_block_size') db WHERE OWNER = r_index.index_owner AND INDEX_NAME = r_index.index_name; dbms_output.put_line( rpad( r_index.table_name, 32 ) || rpad( r_index.index_name, 23 ) || to_char(index_size,'999G999') || ' ' || to_char(btree_space,'999G999') || ' ' || to_char(btree_pct_used,'99999') || ' ' || to_char(b_tree_height,'9G999') || ' ' || to_char(index_rows,'999G999G999') || ' ' || to_char(deleted_rows,'999G999G999') || ' (' || lpad(ltrim(to_char(round(degradation*100),'990')),3) || '%)' ); EXCEPTION when E_resource_busy then dbms_output.put_line( rpad( r_index.table_name, 32 ) || rpad( r_index.index_name, 23 ) || '===== ORA-00054: resource busy and acquire with NOWAIT specified =====' ); when others then null; END; ELSE IF prev_owner <> r_index.table_owner OR prev_index <> r_index.index_name THEN dbms_output.put_line( rpad( r_index.table_name, 32 ) || r_index.index_name ); END IF; BEGIN --execute immediate 'validate index ' || r_index.index_owner || '.' || r_index.index_name -- || ' partition (' || r_index.partition_name || ')'; SELECT height, del_lf_rows, lf_rows, del_lf_rows/(lf_rows+0.00001), blocks*db.block_size, round(btree_space/1024), nvl(pct_used,0) INTO b_tree_height, deleted_rows, index_rows, degradation, index_size, btree_space, btree_pct_used FROM acc_admin.t_informe_indice, (SELECT value/1024 block_size FROM v$parameter WHERE name = 'db_block_size') db WHERE OWNER = r_index.index_owner AND INDEX_NAME = r_index.index_name AND PARTITION_NAME = r_index.partition_name; dbms_output.put_line( lpad('partition',15,chr(160)) || rpad(to_char(r_index.partition_position,'999'),17) || '. ' || rpad( r_index.partition_name, 21 ) || to_char(index_size,'999G999') || ' ' || to_char(btree_space,'999G999') || ' ' || to_char(btree_pct_used,'99999') || ' ' || to_char(b_tree_height,'9G999') || ' ' || to_char(index_rows,'999G999G999') || ' ' || to_char(deleted_rows,'999G999G999') || ' (' || lpad(ltrim(to_char(round(degradation*100),'990')),3) || '%)' ); EXCEPTION when E_resource_busy then dbms_output.put_line( lpad('partition',15,chr(160)) || rpad(to_char(r_index.partition_position,'999'),17) || '===== ORA-00054: resource busy and acquire with NOWAIT specified =====' ); when others then null; END; END IF; prev_index := r_index.index_name; END LOOP; elapsed_time_total := round((sysdate - datetime_begin)*24*3600); dbms_output.put_line(chr(10)||'Total elapsed time to analyze index degradation : ' || ltrim(to_char(floor(elapsed_time_total/3600),'99')) ||':'|| ltrim(to_char(floor(mod(elapsed_time_total,3600)/60),'09')) ||':'|| ltrim(to_char(mod(mod(elapsed_time_total,3600),60),'09')) || chr(10) ); 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