How to non intrusively find index rebuild or shrink candidates ?

Preamble

How to find index rebuild contenders ? I usually get this question around four times a year ! The always working answer is: when you have deleted many rows from source table. Obviously if you delete many rows from source table the under line index will have its leaf blocks getting empty and so will benefit from a rebuild. Well to be honest benefit from a rebuild if you do not insert back those rows in source table or if you insert new rows with a different key (related to the index). Okay but how do we know how much leaf empty blocks have been created and how much space would we gain by rebuilding the index ?

The legacy method is based on SQL command:

ANALYZE INDEX ... VALIDATE structure;

Which has the bad idea to set an exclusive lock on base table and so forbid any DML. As this method was quite intrusive it has rarely been used on production databases… Despite this you still have plenty of references still suggesting this method that, in my opinion, you must avoid !

Looking a bit of the subject the newest and non intrusive methods are now based on the Oracle estimation of the index size versus the size it currently has. Some more advanced methods are also now displaying the index distribution that could give you an insight of the quality of the index and if you should consider to rebuild it or not.

Legacy situation

Start by analyzing validate structure the index, again this is intrusive command that is forbidding any DML on source table:

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

Then you have access to a table called INDEX_STATS. The interesting columns are HEIGHT for index height (number of blocks required to go from the root block to a leaf block), LF_ROWS for the number of leaf rows (values in the index) and DEL_LF_ROWS for the number of deleted leaf rows in the index. The seen everywhere formula is to rebuild index when its height is greater than 3 or percentage of leaf rows deleted is greater than 20%. So here is the query:

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      .0006

But again this is clearly a method to avoid nowadays…

Newest methods to estimate indexes size

Current methods are all coming from the feature of well known EXPLAIN PLAN command for DDL. Explaining the DDL of a create index command will feedback the estimated sire of the index. Let’s apply it to my existing index but you can also use it for an index you have not yet created. Get the DDL of your index using DBMS_METADATA.GET_DDL function:

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.

And what do we see at the end of the explain plan: estimated index size: 2617M bytes. Oracle is telling us the size the index would take on disk !

Since Oracle 10gR1 since has been wrapped in a procedure of DBMS_SPACE, so you got this all in one using DBMS_SPACE.CREATE_INDEX_COST procedure.

I have 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 -----------------
----------------------------------------------------------------
Used: 1.386GB
Allocated: 2.438GB

From official documentation:

  • used_bytes: The number of bytes representing the actual index data
  • alloc_bytes: Size of the index when created in the tablespace

So as we can see not an exactly byte to byte equivalence, 2617MB for EXPLAIN PLAN command and 2.438GB (2496MB) for DBMS_SPACE.CREATE_INDEX_COST procedure. But the procedure is far simpler to use !

In My Oracle Support (MOS) note Script to investigate a b-tree index structure (Doc ID 989186.1) Oracle claim they use undocumented SYS_OD_LBID instead of ANALYZE INDEX … VALIDATE STRUCTURE command. But looking deeper into their script, SYS_OP_LBID usage is for something completely and in fact they do not use it to list indexes that might benefit from rebuild. We will see SYS_OP_LBID function in a later chapter of this blog post.

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… But not more explanation of the formula is given by Oracle so difficult to take it as is…

Index rebuild candidates list

Once we have the estimated size (whatever the method) of the index we can compare it with its actual size and see how much we might gain. To compute the current size of an existing index (of course) we have two methods:

  • DBMS_SPACE.SPACE_USAGE procedure
  • DBA_SEGMENTS view

Of course using DBA_SEGMENTS you are not only taking the real blocks used under the High Water Mark (HWM) but as you can see below it does not make a huge difference for my test index. The script I have written 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%

Let say we chose the DBMS_SPACE method, I have then tried to club this in a function for non-partitioned indexes to be able to analyze multiple indexes of a schema at same time. To handle security problem I have granted to my DBA account:

SQL> GRANT EXECUTE ON dbms_space TO yjaquier;
 
GRANT succeeded.
 
SQL> GRANT EXECUTE ON dbms_metadata TO yjaquier;
 
GRANT succeeded.
 
SQL> GRANT ANALYZE ANY TO yjaquier;
 
GRANT succeeded.

And DBMS_METADATA as they say in official documentation:

If you want to write a PL/SQL program that fetches metadata for objects in a different schema (based on the invoker’s possession of SELECT_CATALOG_ROLE), you must make the program invokers-rights.

So used the AUTHID CURRENT_USER as invoker’s rights clause:

CREATE OR REPLACE FUNCTION index_saving_function(index_owner IN VARCHAR2, index_name VARCHAR2)
RETURN NUMBER
AUTHID current_user
AS
  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;
  used_bytes NUMBER;
  alloc_bytes NUMBER;
BEGIN
  dbms_space.space_usage(UPPER(index_owner), UPPER(index_name), '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);
  dbms_space_bytes:=unformatted_bytes+fs1_bytes+fs2_bytes+fs3_bytes+fs4_bytes+full_bytes;
  dbms_space.create_index_cost(dbms_metadata.get_ddl('INDEX', UPPER(index_name), UPPER(index_owner)), used_bytes, alloc_bytes);
  IF (dbms_space_bytes <> 0) THEN
    RETURN (100 * (dbms_space_bytes - alloc_bytes) / dbms_space_bytes);
  ELSE
    RETURN 0;
  END IF;
END;
/

Finally 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;

To go further

The SYS_OP_LBID internal Oracle function that has been first (I think) shared by Jonathan Lewis and that you can find on plenty of blog post as well as MOS note Script to investigate a b-tree index structure (Doc ID 989186.1) return the leaf block id where is store the source table key that is given by source table rowid parameter of the SYS_OP_LBID function. If you group by the result per leaf block id you get the number of source table keys per leaf block id.

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 much 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

Remark:
The SYS_OP_LBID function does not work on index partition (ORA-01760: illegal argument for function) so I had to supply the object_id of the index object itself (object_type=’INDEX’). It also means that executing this script on (big) partitioned tables will take a bit of time and resources…

Then a nice trick is to copy and paste the result in Excel and do a chart on this figures. Doing this you will better see any sudden jump in number of blocks required to read key lea blocks. In a well balanced index the progression should be as much linear as possible:

index_rebuild01
index_rebuild01

I initially thought that any sudden jump in the number of block required to be read to get the keys is an indication of a index that would benefit from rebuild. But I was wrong (see below why after the index has been rebuilt) ! In this chart what you have to try to identify if the number of cumulative blocks increasing rapidly while the number of keys read is moving slowly. In my chart it start well has number of key read is increasing while number of blocks is flat. But then after number of blocks is constantly increasing while the number of keys read is moving slowly. Said differently the list should be more condensed. The issue is here…

If we are back to the raw figures we see the jump here below:

KEYS_PER_LEAF     BLOCKS CUMULATIVE_BLOCKS       
------------- ---------- -----------------       
.
.
          117        196               202
          118        289               491
          119        347               838
          120        205              1043
          121        502              1545
          122        690              2235
          123        851              3086
          124       9629             12715
          125      11104             23819
          126       5773             29592
          127       1991             31583
          128       1148             32731
          129        956             33687
          130        982             34669
          131       1036             35705
          132       1946             37651
          133       4435             42086
          134       6254             48340
          135       2265             50605
          136         26             50631
          137         27             50658
          138         30             50688
          139         21             50709
          140         72             50781
          141         57             50838
          142         95             50933
          143        211             51144
          144        483             51627
          145        408             52035
.
.
          228        823            140172
          229        795            140967
          230       1111            142078
          231     215514            357592
          232       3212            360804
.
.

Rebuild or shrink ?

One of the drawback of rebuilding an index is that you need double index space on disk, this is also a little bit longer than coalescing it… If you run an enterprise edition of the Oracle database then the ONLINE keyword keep you safe from DML locking.

For an index or index-organized table, specifying ALTER [INDEX | TABLE] … SHRINK SPACE COMPACT is equivalent to specifying ALTER [INDEX | TABLE ] … COALESCE.

Few people have tried to compare REBUIL and SHRINK and draw some conclusions (see references), but to be honest it looks difficult to give precise rules on what to do. If your index is not too much fragmented SHRINK should give good result, if not then you have to go for REBUILD. It also depends on which overhead you would like to put on your database. I have tried both on my test index:

I have firstly tried using SHRINK SPACE COMPACT with very low result and found in Oracle official documentation:

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
------------- ---------- -----------------
.
.
          119         10                21
          120          8                29
          121         18                47
          122         36                83
          123         34               117
          124        522               639
          125        537              1176
          126        253              1429
          127         90              1519
.
.
          230       1238             91459
          231     267907            359366
          232       1562            360928
.
.

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>
KEYS_PER_LEAF     BLOCKS CUMULATIVE_BLOCKS
------------- ---------- -----------------
           56          1                 1
          217      10086             10087
          218        146             10233
          219        120             10353
          220         70             10423
          221         64             10487
          222         65             10552
          223         76             10628
          224      29037             39665
          225       1566             41231
          226       1268             42499
          227       1077             43576
          228        861             44437
          229        928             45365
          230       1245             46610
          231     277246            323856
          248          1            323857
          341          3            323860

Graphically it gives:

index_rebuild02
index_rebuild02

Much better ! I still have a big jump is number of blocks required to be read when number of keys is increasing but I suppose it comes from a key that has high frequency in my source table. Which demonstrate that it is not abnormal to have such big jump in queries with SYS_OP_LBID internal function.

The process you could apply is to try to shrink your index by default. If you are unhappy with the result try to afford a rebuild. The good thing now is that checking the index is not locking anything and you can launch it multiple time even on a production database…

References

About Post Author

Share the knowledge!
This entry was posted in Oracle and tagged , . Bookmark the permalink.

3 thoughts on “How to non intrusively find index rebuild or shrink candidates ?

    • Hi,

      Nice catch ! I have tried to update the scripts to handle partitioned indexes. I anyways noticed that it is not providing accurate results on BITMAP indexes. By the way if you check Oracle support note 989186.1 you will see that Oracle exclude partitioned, temporary and index type not in ‘NORMAL’,’NORMAL/REV’,’FUNCTION-BASED NORMAL’. There might be a reason…

      Give a try and let me know…

      Yannick.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>