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.


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
