How to know t's Time to Rebuild Indexes in Oracle

 The below script can give a fair idea of the current state of the index by using the ANALYZE INDEX VALIDATE STRUCTURE command. 

Here's some sample output from the INDEX_STATS Table:

           

SQL> select owner,index_name,table_name,blevel from dba_indexes where BLEVEL>3

 - Analyze indexes and find out 

ratio of (DEL_LF_ROWS/LF_ROWS*100) is > 20

  SQL> ANALYZE INDEX IDX_GAM_ACCT VALIDATE STRUCTURE;

  Statement processed.

   

  SQL> SELECT name, height,lf_rows,lf_blks,del_lf_rows FROM INDEX_STATS;

   

  NAME          HEIGHT      LF_ROWS    LF_BLKS    DEL_LF_ROW

  ------------- ----------- ---------- ---------- ----------

  APPSOL_DL_ACCT        2          1          3          6 

   

  1 row selected.

 

There are two rules of thumb to help determine if the index needs to be rebuilt:


If the index has height greater than four, rebuild the index.

The deleted leaf rows should be less than 20%.


SQL> select index_name,status,owner,table_name from dba_indexes where status='UNUSABLE';


SQL> select partition_name,status from dba_ind_partitions where index_name like '%&i%';


Rebuilding an Index


Alter Index Rebuild command can be used to rebuild indexes. 

It rebuilds a spatial index or a specified partition of a partitioned index.


ALTER INDEX REBUILD command has a few forms:


ALTER INDEX [schema.]index REBUILD  

     [PARAMETERS ('rebuild_params [physical_storage_params]' ) ] 

     [{ NOPARALLEL | PARALLEL [ integer ] }] ;

OR


ALTER INDEX [schema.]index REBUILD ONLINE 

     [PARAMETERS ('rebuild_params [physical_storage_params]' ) ] 

     [{ NOPARALLEL | PARALLEL [ integer ] }] ;


OR


ALTER INDEX [schema.]index REBUILD PARTITION partition  

     [PARAMETERS ('rebuild_params [physical_storage_params]' ) ];

 

 

Handling Unusable Indexes


Rebuild

To re-create an existing index or one of its partitions or subpartitions.

If the index is marked unusable, then a successful rebuild will mark it usable.


Select 'ALTER INDEX '|| index_name ||' rebuild partition ' || PARTITION_NAME ||' online ;' from dba_IND_PARTITIONS where INDEX_OWNER='HR';

ALTER INDEX INDEX_NAME REBUILD ONLINE PARALLEL 4;


The legacy method is based on SQL command:

ANALYZE INDEX ... VALIDATE structure;

Which has a bad idea to set an exclusive lock on the base table and so forbid any DML. As this method was quite intrusive so we should avoid it.


SQL> ANALYZE INDEX <owner>.<INDEX name> VALIDATE structure;
 
INDEX <owner>.<INDEX name> analyzed.

SQL> SET lines 200
SQL> col name FOR a30
SQL> SELECT name, height, ROUND(del_lf_rows*100/lf_rows,4) AS percentage FROM index_stats;
 
NAME                               HEIGHT PERCENTAGE
------------------------------ ---------- ----------
<INDEX name>                            4      .0008

New method
==========
SQL> SET LONG 1000
SQL> SELECT dbms_metadata.get_ddl('INDEX', '<index name>', '<owner>') AS ddl FROM dual;
DDL
--------------------------------------------------------------------------------
 
  CREATE INDEX <owner>.<INDEX name> ON <owner>.<TABLE name>
  ("SO_SUB_ITEM__ID", "SO_PENDING_CAUSE__CODE")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE <TABLESPACE name>

Then explain create index statement and display related explain plan:

SQL> SET lines 150
SQL> EXPLAIN PLAN FOR
  2  CREATE INDEX <owner>.<INDEX name>
  3  ON <owner>.<TABLE name> (<COLUMN 1>, <COLUMN 2>)
  4  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  5  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  6  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  7  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  8  TABLESPACE <TABLESPACE name>;
 
Explained.
 
SQL> SELECT * FROM TABLE(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
PLAN hash VALUE: 1096024652
 
---------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                     | ROWS  | Bytes | COST (%CPU)| TIME     |
---------------------------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |                          |    74M|  1419M|   156K  (1)| 00:00:07 |
|   1 |  INDEX BUILD NON UNIQUE| <INDEX name>             |       |       |            |          |
|   2 |   SORT CREATE INDEX    |                          |    74M|  1419M|            |          |
|   3 |    TABLE ACCESS FULL   | <TABLE name>             |    74M|  1419M| 85097   (1)| 00:00:04 |
---------------------------------------------------------------------------------------------------
 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - estimated INDEX SIZE: 2617M bytes
 
14 ROWS selected.


create below script taking owner and index name as parameters (create_index_cost.sql):

SET linesize 200 pages 1000
SET serveroutput ON SIZE 999999
SET verify off
SET feedback off
DECLARE
  used_bytes NUMBER;
  alloc_bytes NUMBER;
  FUNCTION format_size(value1 IN NUMBER)
  RETURN VARCHAR2 AS
  BEGIN
    CASE
      WHEN (value1>1024*1024*1024) THEN RETURN LTRIM(TO_CHAR(value1/(1024*1024*1024),'999,999.999') || 'GB');
      WHEN (value1>1024*1024) THEN RETURN LTRIM(TO_CHAR(value1/(1024*1024),'999,999.999') || 'MB');
      WHEN (value1>1024) THEN RETURN LTRIM(TO_CHAR(value1/(1024),'999,999.999') || 'KB');
      ELSE RETURN LTRIM(TO_CHAR(value1,'999,999.999') || 'B');
    END CASE;
  END format_size;
BEGIN
  dbms_output.put_line('----------------------------------------------------------------');
  dbms_output.put_line('----------------- DBMS_SPACE.CREATE_INDEX_COST -----------------');
  dbms_output.put_line('----------------------------------------------------------------');
  dbms_space.create_index_cost(dbms_metadata.get_ddl('INDEX', UPPER('&2.'), UPPER('&1.')), used_bytes, alloc_bytes);
  dbms_output.put_line('Used: ' || format_size(used_bytes));
  dbms_output.put_line('Allocated: ' || format_size(alloc_bytes));
END;
/
SET feedback ON

It gives:

SQL> @create_index_cost <owner> <INDEX name>
----------------------------------------------------------------
----------------- DBMS_SPACE.CREATE_INDEX_COST -----------------
----------------------------------------------------------------

Taking only the size estimate part of MOS note 989186.1 and modifying it to take only two parameters which would be index owner and index name it could become something like (inspect_index.sql):

SET linesize 200 pages 1000
SET serveroutput ON SIZE 999999
SET verify off
SET feedback off
DECLARE
  vtargetuse   CONSTANT POSITIVE := 90;  -- equates to pctfree 10  
  vleafestimate NUMBER;  
  vblocksize    NUMBER;
  voverhead     NUMBER := 192; -- leaf block "lost" space in index_stats 
  vpartitioned dba_indexes.partitioned%TYPE;
  vtable_owner dba_indexes.table_owner%TYPE;
  vtable_name dba_indexes.table_name%TYPE;
  vleaf_blocks dba_indexes.leaf_blocks%TYPE;
  vtablespace_name dba_tablespaces.tablespace_name%TYPE;
  vpartition_name dba_ind_partitions.partition_name%TYPE;
  cursor01 SYS_REFCURSOR;
  CURSOR cursor02 (vblocksize NUMBER, vtable_owner VARCHAR2, vtable_name VARCHAR2, vleaf_blocks NUMBER, vtablespace_name VARCHAR2, vpartition_name VARCHAR2) IS
  SELECT ROUND(100 / vtargetuse * -- assumed packing efficiency
               (ind.num_rows * (tab.rowid_length + ind.uniq_ind + 4) + SUM((tc.avg_col_len) * (tab.num_rows) ))  -- column data bytes  
               / (vblocksize - voverhead)) index_leaf_estimate
  FROM (SELECT  /*+ no_merge */ table_name, num_rows, DECODE(partitioned,'YES',10,6) rowid_length  
       FROM dba_tables
       WHERE table_name  = vtable_name  
       AND   owner       = vtable_owner) tab,  
      (SELECT  /*+ no_merge */ index_name, index_type, num_rows, DECODE(uniqueness,'UNIQUE',0,1) uniq_ind  
       FROM dba_indexes  
       WHERE table_owner = vtable_owner  
       AND table_name    = vtable_name  
       AND owner         = UPPER('&1.')  
       AND index_name    = UPPER('&2.')) ind,  
      (SELECT  /*+ no_merge */ column_name  
       FROM dba_ind_columns  
       WHERE table_owner = vtable_owner  
       AND table_name    = vtable_name 
       AND index_owner   = UPPER('&1.')   
       AND index_name    = UPPER('&2.')) ic,  
      (SELECT  /*+ no_merge */ column_name, avg_col_len  
       FROM dba_tab_cols  
       WHERE owner     = vtable_owner  
       AND table_name  = vtable_name) tc  
  WHERE tc.column_name = ic.column_name  
  GROUP BY ind.num_rows, ind.uniq_ind, tab.rowid_length;
  CURSOR cursor03 (vblocksize NUMBER, vtable_owner VARCHAR2, vtable_name VARCHAR2, vleaf_blocks NUMBER, vtablespace_name VARCHAR2, vpartition_name VARCHAR2) IS
  SELECT ROUND(100 / vtargetuse * -- assumed packing efficiency
               (ind.num_rows * (tab.rowid_length + ind.uniq_ind + 4) + SUM((tc.avg_col_len) * (tab.num_rows) ))  -- column data bytes  
               / (vblocksize - voverhead)) index_leaf_estimate
  FROM (SELECT  /*+ no_merge */ a.table_name, b.num_rows, DECODE(a.partitioned,'YES',10,6) rowid_length  
       FROM dba_tables a, dba_tab_partitions b
       WHERE b.partition_name = vpartition_name
       AND   a.table_name = b.table_name
       AND        a.owner = b.table_owner
       AND  a.table_name  = vtable_name  
       AND  a.owner       = vtable_owner) tab,  
      (SELECT  /*+ no_merge */ a.index_name, a.index_type, b.num_rows, DECODE(a.uniqueness,'UNIQUE',0,1) uniq_ind  
       FROM dba_indexes a, dba_ind_partitions b
       WHERE b.partition_name = vpartition_name
       AND a.table_owner = vtable_owner  
       AND a.table_name    = vtable_name  
       AND a.index_name    = b.index_name
       AND a.owner         = b.index_owner
       AND a.owner         = UPPER('&1.')  
       AND a.index_name    = UPPER('&2.')) ind,  
      (SELECT  /*+ no_merge */ column_name  
       FROM dba_ind_columns  
       WHERE table_owner = vtable_owner  
       AND table_name    = vtable_name 
       AND index_owner   = UPPER('&1.')   
       AND index_name    = UPPER('&2.')) ic,  
      (SELECT  /*+ no_merge */ column_name, avg_col_len  
       FROM dba_tab_cols  
       WHERE owner     = vtable_owner  
       AND table_name  = vtable_name) tc  
  WHERE tc.column_name = ic.column_name  
  GROUP BY ind.num_rows, ind.uniq_ind, tab.rowid_length;
  vsql VARCHAR2(5000);
  FUNCTION format_size(value1 IN NUMBER)
  RETURN VARCHAR2 AS
  BEGIN
    CASE
      WHEN (value1>1024*1024*1024) THEN RETURN LTRIM(TO_CHAR(value1/(1024*1024*1024),'999,999.999') || 'GB');
      WHEN (value1>1024*1024) THEN RETURN LTRIM(TO_CHAR(value1/(1024*1024),'999,999.999') || 'MB');
      WHEN (value1>1024) THEN RETURN LTRIM(TO_CHAR(value1/(1024),'999,999.999') || 'KB');
      ELSE RETURN LTRIM(TO_CHAR(value1,'999,999.999') || 'B');
    END CASE;
  END format_size;
BEGIN
  SELECT partitioned
  INTO vpartitioned
  FROM dba_indexes
  WHERE owner = UPPER('&1.')
  AND index_name = UPPER('&2.');
 
  IF vpartitioned = 'YES' THEN
    dbms_output.put_line('This is a partitioned index.');
    vsql:='select a.block_size, c.owner, c.table_name, b.leaf_blocks, a.tablespace_name, b.partition_name
          from dba_tablespaces a, dba_ind_partitions b, dba_part_indexes c
          where b.index_name = upper(''' || '&2.' || ''')
          and b.index_owner = upper(''' || '&1.' || ''')
          and a.tablespace_name = b.tablespace_name
          and b.index_name = c.index_name
          and b.index_owner = c.owner';
  ELSE
    dbms_output.put_line('This is a non-partitioned index.');
    vsql:='select a.block_size, b.table_owner, b.table_name, b.leaf_blocks, a.tablespace_name, ''N/A''
          from dba_tablespaces a, dba_indexes b
          where b.index_name = upper(''' || '&2.' || ''')
          and b.owner = upper(''' || '&1.' || ''')
          and a.tablespace_name = b.tablespace_name';
  END IF;
 
  OPEN cursor01 FOR vsql;
  LOOP
    FETCH cursor01 INTO vblocksize, vtable_owner, vtable_name, vleaf_blocks, vtablespace_name, vpartition_name;
    EXIT WHEN cursor01%notfound;
    IF vpartitioned = 'NO' THEN
      OPEN cursor02(vblocksize, vtable_owner, vtable_name, vleaf_blocks, vtablespace_name, vpartition_name);
      LOOP
        FETCH cursor02 INTO vleafestimate;
        EXIT WHEN cursor02%notfound;
        dbms_output.put_line('For index ' || UPPER('&1.') || '.' || UPPER('&2.') || ', source table is ' || vtable_owner || '.' || vtable_name);
        dbms_output.put_line('Index in tablespace ' || vtablespace_name);
        dbms_output.put_line('Current leaf blocks: ' || vleaf_blocks);
        dbms_output.put_line('Current size: ' || format_size(vleaf_blocks * vblocksize));
        dbms_output.put_line('Estimated leaf blocks: ' || ROUND(vleafestimate,2));
        dbms_output.put_line('Estimated size: ' || format_size(vleafestimate * vblocksize));
      END LOOP;
      CLOSE cursor02;
    ELSE
      OPEN cursor03(vblocksize, vtable_owner, vtable_name, vleaf_blocks, vtablespace_name, vpartition_name);
      LOOP
        FETCH cursor03 INTO vleafestimate;
        EXIT WHEN cursor03%notfound;
        dbms_output.put_line('For index ' || UPPER('&1.') || '.' || UPPER('&2.') || ', source table is ' || vtable_owner || '.' || vtable_name);
        dbms_output.put_line('Index partition ' || vpartition_name || ' in tablespace ' || vtablespace_name);
        dbms_output.put_line('Current leaf blocks: ' || vleaf_blocks);
        dbms_output.put_line('Current size: ' || format_size(vleaf_blocks * vblocksize));
        dbms_output.put_line('Estimated leaf blocks: ' || ROUND(vleafestimate,2));
        dbms_output.put_line('Estimated size: ' || format_size(vleafestimate * vblocksize));
      END LOOP;
      CLOSE cursor03;
    END IF;
  END LOOP;
  CLOSE cursor01;
END;
/
SET feedback ON

On my test index it gives:

SQL> @inspect_index <owner> <INDEX name>
FOR INDEX <owner>.<INDEX name>, source TABLE IS <owner>.<TABLE name>
CURRENT leaf blocks: 375382
CURRENT SIZE: 2.864GB
Estimated leaf blocks: 335395
Estimated SIZE: 2.559GB

Which is a third estimation of the size the index would take on disk… 


Candidates list for rebuild


script  is taking index owner and index name as parameters (index_saving.sql):

SET linesize 200 pages 1000
SET serveroutput ON SIZE 999999
SET verify off
SET feedback off
DECLARE
  unformatted_blocks NUMBER;
  unformatted_bytes NUMBER;
  fs1_blocks NUMBER;
  fs1_bytes NUMBER;
  fs2_blocks NUMBER;
  fs2_bytes NUMBER;
  fs3_blocks NUMBER;
  fs3_bytes NUMBER;
  fs4_blocks NUMBER;
  fs4_bytes NUMBER;
  full_blocks NUMBER;
  full_bytes NUMBER;
  dbms_space_bytes NUMBER;
  bytes_dba_segments NUMBER;
  used_bytes NUMBER;
  alloc_bytes NUMBER;
  vpartitioned dba_indexes.partitioned%TYPE;
  CURSOR cursor01 IS SELECT NVL(partition_name,'N/A') AS partition_name, bytes AS bytes_dba_segments
  FROM dba_segments WHERE owner=UPPER('&1.')  AND segment_name=UPPER('&2.');
  item01 cursor01%ROWTYPE;
  FUNCTION format_size(value1 IN NUMBER)
  RETURN VARCHAR2 AS
  BEGIN
    CASE
      WHEN (value1>1024*1024*1024) THEN RETURN LTRIM(TO_CHAR(value1/(1024*1024*1024),'999,999.999') || 'GB');
      WHEN (value1>1024*1024) THEN RETURN LTRIM(TO_CHAR(value1/(1024*1024),'999,999.999') || 'MB');
      WHEN (value1>1024) THEN RETURN LTRIM(TO_CHAR(value1/(1024),'999,999.999') || 'KB');
      ELSE RETURN LTRIM(TO_CHAR(value1,'999,999.999') || 'B');
    END CASE;
  END format_size;
BEGIN
  SELECT partitioned
  INTO vpartitioned
  FROM dba_indexes
  WHERE owner = UPPER('&1.')
  AND index_name = UPPER('&2.');
 
  dbms_output.put_line('----------------------------------------------------------------');
  dbms_output.put_line('Analyzing index &1..&2.');
 
  OPEN cursor01;
  dbms_space_bytes:=0;
  bytes_dba_segments:=0;
  LOOP
    FETCH cursor01 INTO item01;
    EXIT WHEN cursor01%notfound;
    dbms_output.put_line('----------------------------------------------------------------');
    dbms_output.put_line('-------------------- DBMS_SPACE.SPACE_USAGE --------------------');
    dbms_output.put_line('----------------------------------------------------------------');
    IF vpartitioned='NO' THEN
      dbms_space.space_usage(UPPER('&1.'), UPPER('&2.'), 'INDEX', unformatted_blocks, unformatted_bytes, fs1_blocks, fs1_bytes, fs2_blocks,
                             fs2_bytes, fs3_blocks, fs3_bytes, fs4_blocks, fs4_bytes, full_blocks, full_bytes);
    ELSE
      dbms_output.put_line('Index partition: ' || item01.partition_name);
      dbms_space.space_usage(UPPER('&1.'), UPPER('&2.'), 'INDEX PARTITION', unformatted_blocks, unformatted_bytes, fs1_blocks, fs1_bytes, fs2_blocks,
                             fs2_bytes, fs3_blocks, fs3_bytes, fs4_blocks, fs4_bytes, full_blocks, full_bytes, item01.partition_name);
    END IF;
    dbms_output.put_line('Total number of blocks unformatted :' || unformatted_blocks);
    --dbms_output.put_line('Total number of bytes unformatted: ' || unformatted_bytes);
    dbms_output.put_line('Number of blocks having at least 0 to 25% free space: ' || fs1_blocks);
    --dbms_output.put_line('Number of bytes having at least 0 to 25% free space: ' || fs1_bytes);
    dbms_output.put_line('Number of blocks having at least 25 to 50% free space: ' || fs2_blocks);
    --dbms_output.put_line('Number of bytes having at least 25 to 50% free space: ' || fs2_bytes);
    dbms_output.put_line('Number of blocks having at least 50 to 75% free space: ' || fs3_blocks);
    --dbms_output.put_line('Number of bytes having at least 50 to 75% free space: ' || fs3_bytes);
    dbms_output.put_line('Number of blocks having at least 75 to 100% free space: ' || fs4_blocks);
    --dbms_output.put_line('Number of bytes having at least 75 to 100% free space: ' || fs4_bytes);
    dbms_output.put_line('The number of blocks full in the segment: ' || full_blocks);
    --dbms_output.put_line('Total number of bytes full in the segment: ' || format_size(full_bytes));
    dbms_space_bytes:=dbms_space_bytes + unformatted_bytes + fs1_bytes + fs2_bytes + fs3_bytes + fs4_bytes + full_bytes;
    dbms_output.put_line('----------------------------------------------------------------');
    dbms_output.put_line('------------------------- DBA_SEGMENTS -------------------------');
    dbms_output.put_line('----------------------------------------------------------------');
    --select bytes into bytes_dba_segments from dba_segments where owner=upper('&1.') and segment_name=upper('&2.');
    dbms_output.put_line('Size of the segment: ' || format_size(item01.bytes_dba_segments));
    bytes_dba_segments:=bytes_dba_segments + item01.bytes_dba_segments;
  END LOOP;
  CLOSE cursor01;
 
  dbms_output.put_line('----------------------------------------------------------------');
  dbms_output.put_line('----------------- DBMS_SPACE.CREATE_INDEX_COST -----------------');
  dbms_output.put_line('----------------------------------------------------------------');
  dbms_space.create_index_cost(dbms_metadata.get_ddl('INDEX', UPPER('&2.'), UPPER('&1.')), used_bytes, alloc_bytes);
  dbms_output.put_line('Used: ' || format_size(used_bytes));
  dbms_output.put_line('Allocated: ' || format_size(alloc_bytes));
  dbms_output.put_line('----------------------------------------------------------------');
  dbms_output.put_line('---------------------------- Results ---------------------------'); 
  dbms_output.put_line('----------------------------------------------------------------');
  dbms_output.put_line('Potential percentage gain (DBMS_SPACE): ' || ROUND(100 * (dbms_space_bytes - alloc_bytes) / dbms_space_bytes) || '%');
  dbms_output.put_line('Potential percentage gain (DBA_SEGMENTS): ' || ROUND(100 * (bytes_dba_segments - alloc_bytes) / bytes_dba_segments) || '%');
END;
/
SET feedback ON

It gives for me:

SQL> @index_saving <owner> <INDEX name>
----------------------------------------------------------------
Analyzing INDEX <owner>.<INDEX name>
----------------------------------------------------------------
-------------------- DBMS_SPACE.SPACE_USAGE --------------------
----------------------------------------------------------------
Total NUMBER OF blocks unformatted :1022
NUMBER OF blocks HAVING AT LEAST 0 TO 25% free SPACE: 0
NUMBER OF blocks HAVING AT LEAST 25 TO 50% free SPACE: 35
NUMBER OF blocks HAVING AT LEAST 50 TO 75% free SPACE: 0
NUMBER OF blocks HAVING AT LEAST 75 TO 100% free SPACE: 0
The NUMBER OF blocks full IN the SEGMENT: 365448
----------------------------------------------------------------
------------------------- DBA_SEGMENTS -------------------------
----------------------------------------------------------------
SIZE OF the SEGMENT: 2.803GB
----------------------------------------------------------------
----------------- DBMS_SPACE.CREATE_INDEX_COST -----------------
----------------------------------------------------------------
Used: 1.386GB
Allocated: 2.438GB
----------------------------------------------------------------
---------------------------- Results ---------------------------
----------------------------------------------------------------
Potential percentage gain (DBMS_SPACE): 13%
Potential percentage gain (DBA_SEGMENTS): 13%

A simple query like this gives a good first analysis of what could be potential candidates for shrink/rebuild:

SELECT owner,index_name,index_saving_function(owner,index_name) AS percentage_gain
FROM dba_indexes
WHERE owner='<your_owner>'
AND last_analyzed IS NOT NULL
AND partitioned='NO'
ORDER BY 3 DESC;

On all the queries shared around the next idea is to order by and group by this number of keys per leaf block and see how many blocks you have to access to get them. The queries using an aggregate function to sum row by row this block required to be read are the best to make an analysis (sys_op_lbid.sql):

SET linesize 200 pages 1000
SET serveroutput ON SIZE 999999
SET verify off
SET feedback off
DECLARE
  vsql VARCHAR2(1000);
  v_id NUMBER;
  vtable_owner dba_indexes.table_owner%TYPE;
  vtable_name dba_indexes.table_owner%TYPE;
  col01 VARCHAR2(50);
  col02 VARCHAR2(50);
  col03 VARCHAR2(50);
  col04 VARCHAR2(50);
  col05 VARCHAR2(50);
  col06 VARCHAR2(50);
  col07 VARCHAR2(50);
  col08 VARCHAR2(50);
  col09 VARCHAR2(50);
  col10 VARCHAR2(50);
  TYPE IdxRec IS RECORD (keys_per_leaf NUMBER, blocks NUMBER, cumulative_blocks NUMBER);
  TYPE IdxTab IS TABLE OF IdxRec;
  l_data IdxTab;
BEGIN
  SELECT object_id
  INTO v_id
  FROM dba_objects
  WHERE owner = UPPER('&1.')
  AND object_name = UPPER('&2.')
  AND object_type = 'INDEX';
 
  SELECT table_owner, table_name
  INTO vtable_owner, vtable_name
  FROM dba_indexes
  WHERE owner = UPPER('&1.')
  AND index_name = UPPER('&2.');
 
  SELECT
    NVL(MAX(DECODE(column_position, 1,column_name)),'null'),
    NVL(MAX(DECODE(column_position, 2,column_name)),'null'),
    NVL(MAX(DECODE(column_position, 3,column_name)),'null'),
    NVL(MAX(DECODE(column_position, 4,column_name)),'null'),
    NVL(MAX(DECODE(column_position, 5,column_name)),'null'),
    NVL(MAX(DECODE(column_position, 6,column_name)),'null'),
    NVL(MAX(DECODE(column_position, 7,column_name)),'null'),
    NVL(MAX(DECODE(column_position, 8,column_name)),'null'),
    NVL(MAX(DECODE(column_position, 9,column_name)),'null'),
    NVL(MAX(DECODE(column_position, 10,column_name)),'null')
  INTO col01, col02, col03, col04, col05, col06, col07, col08, col09, col10
  FROM dba_ind_columns
  WHERE table_owner = vtable_owner
  AND table_name  = vtable_name
  AND index_name  = UPPER('&2.')
  ORDER BY column_position;
 
  vsql:='SELECT keys_per_leaf, blocks, SUM(blocks) OVER(ORDER BY keys_per_leaf) cumulative_blocks FROM (SELECT ' ||
        'keys_per_leaf,COUNT(*) blocks FROM (SELECT /*+ ' ||
        'cursor_sharing_exact ' ||
        'dynamic_sampling(0) ' ||
        'no_monitoring ' ||
        'no_expand ' ||
        'index_ffs(' || vtable_name || ',' || '&2.' || ') ' ||
        'noparallel_index(' || vtable_name || ',' || '&2.' || ') */ ' ||
        'sys_op_lbid(' || v_id || ',''L'',t1.rowid) AS block_id,' ||
        'COUNT(*) AS keys_per_leaf ' ||
        'FROM &1..' || vtable_name ||' t1 ' ||
        'WHERE ' || col01 || ' IS NOT NULL ' ||
        'OR ' || col02 || ' IS NOT NULL ' ||
        'OR ' || col03 || ' IS NOT NULL ' ||
        'OR ' || col04 || ' IS NOT NULL ' ||
        'OR ' || col05 || ' IS NOT NULL ' ||
        'OR ' || col06 || ' IS NOT NULL ' ||
        'OR ' || col07 || ' IS NOT NULL ' ||
        'OR ' || col08 || ' IS NOT NULL ' ||
        'OR ' || col09 || ' IS NOT NULL ' ||
        'OR ' || col10 || ' IS NOT NULL ' ||
        'GROUP BY sys_op_lbid('||v_id||',''L'',t1.rowid)) ' ||
        'GROUP BY keys_per_leaf) ' ||
        'ORDER BY keys_per_leaf';
  --dbms_output.put_line(vsql);
  EXECUTE IMMEDIATE vsql BULK COLLECT INTO l_data;
 
  dbms_output.put_line('KEYS_PER_LEAF     BLOCKS CUMULATIVE_BLOCKS');
  dbms_output.put_line('------------- ---------- -----------------');
   FOR i IN l_data.FIRST..l_data.LAST LOOP
     dbms_output.put_line(LPAD(l_data(i).keys_per_leaf,13) || ' ' || LPAD(l_data(i).blocks,10) || ' ' || LPAD(l_data(i).cumulative_blocks,17));
   END LOOP;
END;
/
SET feedback ON

If you specify COMPACT, then Oracle Database only defragments the segment space and compacts the table rows for subsequent release. The database does not readjust the high water mark and does not release the space immediately. You must issue another ALTER TABLE … SHRINK SPACE statement later to complete the operation. This clause is useful if you want to accomplish the shrink operation in two shorter steps rather than one longer step.

Even if they explain COMPACT option only for tables I have feeling that it behaves almost the same for indexes (I have been able to perform multiple tests as my test database got refreshed from live one that remained unchanged):>/p>

SQL> ALTER INDEX <owner>.<INDEX name> shrink SPACE compact;
 
INDEX <owner>.<INDEX name> altered.
 
SQL> @index_saving <owner> <INDEX name>
----------------------------------------------------------------
Analyzing INDEX <owner>.<INDEX name>
----------------------------------------------------------------
-------------------- DBMS_SPACE.SPACE_USAGE --------------------
----------------------------------------------------------------
Total NUMBER OF blocks unformatted :1022
NUMBER OF blocks HAVING AT LEAST 0 TO 25% free SPACE: 0
NUMBER OF blocks HAVING AT LEAST 25 TO 50% free SPACE: 3
NUMBER OF blocks HAVING AT LEAST 50 TO 75% free SPACE: 0
NUMBER OF blocks HAVING AT LEAST 75 TO 100% free SPACE: 13865
The NUMBER OF blocks full IN the SEGMENT: 351615
----------------------------------------------------------------
------------------------- DBA_SEGMENTS -------------------------
----------------------------------------------------------------
SIZE OF the SEGMENT: 2.803GB
----------------------------------------------------------------
----------------- DBMS_SPACE.CREATE_INDEX_COST -----------------
----------------------------------------------------------------
Used: 1.386GB
Allocated: 2.438GB
----------------------------------------------------------------
---------------------------- Results ---------------------------
----------------------------------------------------------------
Potential percentage gain (DBMS_SPACE): 9%
Potential percentage gain (DBA_SEGMENTS): 13%
 
SQL> @inspect_index <owner> <INDEX name>
FOR INDEX <owner>.<INDEX name>, source TABLE IS <owner>.<TABLE name>
CURRENT leaf blocks: 375382
CURRENT SIZE: 2.864GB
Estimated leaf blocks: 335395
Estimated SIZE: 2.559GB
 
SQL> @sys_op_lbid <owner> <INDEX name>
KEYS_PER_LEAF     BLOCKS CUMULATIVE_BLOCKS
------------- ---------- -----------------
      

Does not provide a very good result the index remained almost unchanged ! Without COMPACT keywords (figures slightly different as index evolve on live database:):

SQL> ALTER INDEX <owner>.<INDEX name> shrink SPACE;
 
INDEX <owner>.<INDEX name> altered.
 
SQL> @index_saving <owner> <INDEX name>
----------------------------------------------------------------
Analyzing INDEX <owner>.<INDEX name>
----------------------------------------------------------------
-------------------- DBMS_SPACE.SPACE_USAGE --------------------
----------------------------------------------------------------
Total NUMBER OF blocks unformatted :0
NUMBER OF blocks HAVING AT LEAST 0 TO 25% free SPACE: 0
NUMBER OF blocks HAVING AT LEAST 25 TO 50% free SPACE: 3
NUMBER OF blocks HAVING AT LEAST 50 TO 75% free SPACE: 0
NUMBER OF blocks HAVING AT LEAST 75 TO 100% free SPACE: 0
The NUMBER OF blocks full IN the SEGMENT: 368842
----------------------------------------------------------------
------------------------- DBA_SEGMENTS -------------------------
----------------------------------------------------------------
SIZE OF the SEGMENT: 2.821GB
----------------------------------------------------------------
----------------- DBMS_SPACE.CREATE_INDEX_COST -----------------
----------------------------------------------------------------
Used: 1.526GB
Allocated: 2.688GB
----------------------------------------------------------------
---------------------------- Results ---------------------------
----------------------------------------------------------------
Potential percentage gain (DBMS_SPACE): 4%
Potential percentage gain (DBA_SEGMENTS): 5%

A bit better without COMPACT keyword, we see that blocks have been de-fragmented but not released and index is still not in its optimal form. Which could be satisfactory versus the load you want to put on your database. let’s try rebuilding it which is a bit more resource consuming:

SQL> ALTER INDEX <owner>.<INDEX name> rebuild ONLINE;
 
INDEX <owner>.<INDEX name> altered.
 
SQL> @index_saving <owner> <INDEX name>
----------------------------------------------------------------
Analyzing INDEX <owner>.<INDEX name>
----------------------------------------------------------------
-------------------- DBMS_SPACE.SPACE_USAGE --------------------
----------------------------------------------------------------
Total NUMBER OF blocks unformatted :0
NUMBER OF blocks HAVING AT LEAST 0 TO 25% free SPACE: 0
NUMBER OF blocks HAVING AT LEAST 25 TO 50% free SPACE: 1
NUMBER OF blocks HAVING AT LEAST 50 TO 75% free SPACE: 0
NUMBER OF blocks HAVING AT LEAST 75 TO 100% free SPACE: 0
The NUMBER OF blocks full IN the SEGMENT: 325098
----------------------------------------------------------------
------------------------- DBA_SEGMENTS -------------------------
----------------------------------------------------------------
SIZE OF the SEGMENT: 2.507GB
----------------------------------------------------------------
----------------- DBMS_SPACE.CREATE_INDEX_COST -----------------
----------------------------------------------------------------
Used: 1.386GB
Allocated: 2.438GB
----------------------------------------------------------------
---------------------------- Results ---------------------------
----------------------------------------------------------------
Potential percentage gain (DBMS_SPACE): 2%
Potential percentage gain (DBA_SEGMENTS): 3%
 
SQL> @inspect_index <owner> <INDEX name>
FOR INDEX <owner>.<INDEX name>, source TABLE IS <owner>.<TABLE name>
CURRENT leaf blocks: 323860
CURRENT SIZE: 2.471GB
Estimated leaf blocks: 330825
Estimated SIZE: 2.524GB
 
SQL> @sys_op_lbid <owner> <INDEX name>

There have been many discussions about whether rebuilding indexes is useful or not. Generally speaking, the need to rebuild b-tree indexes is very rare, basically because a b-tree index is largely self-managed or self-balanced.

The most common justifications given for rebuilding an index are:
- index becomes fragmented
- index grows and grows - deleted space is not re-used
- index clustering factor becomes out of sync

In fact most indexes remain both balanced and fragmentation-free because free leaf entries will be reused. Inserts/Updates and Deletes result in free slots being scattered around the index blocks, but these will typically be refilled.
The clustering factor reflects how sorted the table data is with respect to the given index key.  Rebuilding an index never has an influence on the clustering factor but instead requires a table re-organization.

Secondly the impact of rebuilding the index can be quite significant, please read the following comments thoroughly:

1. Most scripts around depend on the index_stats dynamic table. This is populated by the command:

analyze index ... validate structure;


While this is a valid method to inspect the index, it grabs an exclusive table lock while analyzing the index.  Especially for large indexes, this can be very dramatic, as DML operations on the table are not permitted during that time.  While it can be run online without the locking considerations, it may consume additional time. 


2. Redo activity may increase and general performance might be impacted as a direct result of rebuilding an index.

Insert/update/delete causes the index to evolve over time as the index splits and grows. When the index is rebuild, it will become more tightly packed; however as DML operations continue on the table, the index splits have to be redone again until the index reaches its equilibrium. As a result, the redo activity increases and the index splits are now more likely to impact performance directly as we consume more I/O, CPU, etc to serve the index restructuring. After a certain period of time the index may again experience 'issues' and may be re-flagged for a rebuild, causing the vicious cycle to continue. Therefore, it is often better to leave the index in its natural equilibrium and/or at least prevent indexes from being rebuilt on a regular basis.


3. An index coalesce is often preferred instead of an index rebuild. It has the following advantages:

- does not require approximately 2 times the disk storage
- always online
- does not restructure the index, but combines index leaf blocks as much as possible, avoiding system overhead as explained in point 2.
To re-allocate an index, to another tablespace for example a rebuild is required.



Due to the reasons listed above, it is strongly advised not to rebuild indexes on a regular basis but instead use proper diagnostics.

Please see the following note which lists a script that can be used to analyze the index structure. It does not use the 'analyze index validate structure' command but is based on the current table and index statistics to estimate the index size.


To find out the indexes that meet the criteria:

SQL> select owner, index_name, last_inspected, leaf_blocks, target_size 

  from index_log

To verify the Index layout the following query can be used:

SQL> select idx_layout

    from index_log 

    where owner='SCOTT'
 

            and index_name='T_IDX'; 


To find out the evolution over time for a specific index :


SQL> select to_char(inspected_date,'DD-MON-YYYY HH24:MI:SS') inspected_date, 

leaf_blocks, target_size 

from index_hist 

where index_name='T_IDX';


Commnad to cerate index on upper of cloumn.

sql> create index idx_sre_e_mail_upper on fsynrelatie (upper(sre_e_mail)) tablespace idx;


 

Comments

Popular posts from this blog

How to fix Oracle SQL Developer connection issue "Got minus one from a read call"

How to drop index and before dropping it how to get the DDL.

How to troubleshoot Long Running Concurrent Request in EBS 12.2

How to autopopulate user_guid in fnd_user table ( EBS Blank Page)

Opatch is getting faild for GI patch with error code 2 while doing prereq

Few Important steps of Oracle Database Clone

How to Check AD and TXK code levels in your EBS environment

The Server is not able to service this request: [Socket:000445]Connection rejected, filter blocked Socket, weblogic.security.net.FilterException

CPU Patch Analysis

PRVG-11250 : The check "RPM Package Manager database" was not performed because