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<deeisep1:PDB> @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 <EST%> 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/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   IN    ('NORMAL','BITMAP','IOT - TOP')
     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')) || ' Mbytes.' );
    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/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   IN    ('NORMAL','BITMAP','IOT - TOP')
     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     IN    ('NORMAL','BITMAP','IOT - TOP')
                      AND i.table_name     like '&InputTableFilter'
                      AND i.owner          =     s.owner
                      AND i.table_owner    like '&InputOwnerFilter'
                      AND i.table_owner   !=    'SYS'
                      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(Mb) Size(Mb) % 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/1024), nvl(pct_used,0)
          INTO b_tree_height, deleted_rows, index_rows, degradation, index_size, 
               btree_space, btree_pct_used
          FROM index_stats,
               (SELECT value/1024/1024 block_size FROM v$parameter WHERE name = 'db_block_size') db;  

        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/1024), nvl(pct_used,0)
          INTO b_tree_height, deleted_rows, index_rows, degradation, index_size, 
               btree_space, btree_pct_used
          FROM index_stats,
               (SELECT value/1024/1024 block_size FROM v$parameter WHERE name = 'db_block_size') db;  

        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
