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

By

 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

Post a Comment

Popular posts from this blog

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

By
  Oracle DROP INDEX statement DROP INDEX [schema_name.]index_name; DECLARE index_count INTEGER; BEGIN SELECT COUNT(*) INTO index_count FROM USER_INDEXES WHERE INDEX_NAME = 'index_name' ; IF index_count > 0 THEN EXECUTE IMMEDIATE 'DROP INDEX index_name' ; END IF; END; / In case want to check whether index exists and then delete it. If try to drop a non-existing, you will get the following error: SQL Error : ORA -01418 : specified index does not exist In SQLplus, set these before running the commands set long 100000 set longchunksize 100000 select DBMS_METADATA.GET_DDL( 'INDEX' , 'index_name') from all_indexes where owner in ( USER , 'USER_OTHER_THAN_LOGGED_IN_USER' ); Use below script to fetch metadata other information. SELECT DBMS_METADATA,GET_GRANTED_DDL('SYSTEM_GRANT','SSO******') from dual;
Read more

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

By
Following message reported from cluvfy while performing pre-requisite for Clusterware Installation:  Verifying RPM Package Manager database ...INFORMATION PRVG-11250 : The check "RPM Package Manager database" was not performed because it needs 'root' user privileges. SOLUTION Execute runcluvfy manually with "-method root" option and enter root password when prompted : Example: $/runcluvfy.sh stage -pre crsinst -n <HOSTNAME1>,<HOSTNAME2> -method root Enter "ROOT" password: For reference metalink doc id :  2548970.1
Read more

ORA-00257:archiver error, connect internal only until freed

By
  Issue: Application log shows below error and users are unable to connect to the database. 0RA-00257:archiver error, connect internal only until freed (OR) 0RA-00257:archiver error, connect internal only until freed ORA-16020: less destinations available than specified by LOG_ARCHIVE_MIN_SUCCEED_DEST Fix: 1) Check if there are any errors for the archive destination(s). Make sure that the number of VALID archive destinations is greater than or equal to the value specified by LOG_ARCHIVE_MIN_SUCCEED_DEST initialization parameter. set pages 1000 col dest_name for a30 col destination for a60 SELECT dest_id, dest_name, binding, status, destination, error FROM v$archive_dest; SHOW PARAMETER log_archive_min_succeed_dest 2) If space is full in one or more of the archive destinations (or if destination is not available), take any of the following steps: 2.a) Manually move the archives to another location and delete them from archive destination.   (OR) 2.b) Change the archive des...
Read more

Verifying Daemon “Avahi-Daemon” Not Configured And Running …FAILED (PRVG-1360)

By
  Execute "/tmp/CVU_19.0.0.0.0_grid/runfixup.sh" as root user on nodes "shsebs7idb1" to perform the fix up operations manually Press ENTER key to continue after execution of "/tmp/CVU_19.0.0.0.0_grid/runfixup.sh" has completed on nodes "shsebs7idb1" Fix: Daemon "avahi-daemon" not configured and running   Node Name                             Status   ------------------------------------  ------------------------   shsebs7idb1                           failed ERROR: shsebs7idb1: PRVG-9023 : Manual fix up command "/tmp/CVU_19.0.0.0.0_grid/runfixup.sh" was not issued by root user on node "shsebs7idb1" Result: "Daemon "avahi-daemon" not configured and running" could not be fixed on nodes "shsebs7idb1" SOLUTION: To fix this error, stop and disable avahi-daemon service. 1. Check avahi-daemon status: [root@shsebs7idb1 ~]# ls -...
Read more

Linux OL7/RHEL7: PRVE-0421 : No entry exists in /etc/fstab for mounting /dev/shm

By
  This is due to below bugs: OL7 -  Unpublished Bug 21441387- 12.2/MAIN/OL7.1: CVU REPORTS /DEV/SHM NOT MOUNTED WHEN IT IS MOUNTED  21441387.8 RHEL 7 - Unpublished Bug 25907259 : RHEL7.2: CVU REPORTS /DEV/SHM NOT MOUNTED WHEN IT IS MOUNTED  25907259.8 As a work-around in OL7: In OL7, the /dev/shm is not mounted in /etc/fstab on a fresh system, it is done in: [root@testc140 u02]# ll /usr/lib/dracut/modules.d/99base/init.sh -rwxr-xr-x. 1 root root 11379 Mar 26 10:14 vi /usr/lib/dracut/modules.d/99base/init.sh and comment the below lines 62 if ! ismounted /dev/shm; then 63 mkdir -m 0755 /dev/shm 64 mount -t tmpfs -o mode=1777,nosuid,nodev,strictatime tmpfs /dev/shm >/dev/null 65 fi so, this validation is not really valid for OL7.    We can also safely ignore this message. For reference metalink id: -  Doc ID 2065603.1
Read more

SKIP DNS RESLOV.CONF CHECK DURING RAC CONFIGURATION

By
 shsebs7idb1: PRVG-2064 : There are no configured name servers in the file              '/etc/resolv.conf' on the nodes "shsebs7idb1" Solution: [root@shsebs7idb2 ~]# ls -ltr /etc/resolv.conf -rw-r--r--. 1 root root 78 Jul  3 11:02 /etc/resolv.conf [root@shsebs7idb2 ~]# mv /etc/resolv.conf /etc/resolv.conf.sid Rename resolv.conf to resolv.conf.sid for example and re run the installation again . It  worked for me
Read more

How to write to a CSV file using Oracle SQL*Plus

By
  SPOOL command  The  SPOOL  command is  unavailable  in the browser-based SQL*Plus version,  iSQL*Plus . To generate files while using iSQL*Plus, change the necessary preference settings to directly output to a file. This is accomplished using the  SPOOL  statement. While  SPOOL  is  active , SQL*PLus will store the output of any query to the specified file. Therefore, the next command to enter is  spool : spool filepath Skipping ahead slightly,  after  your query is inserted, you also need to halt  spool  so the file output is closed by using the  spool off  command: spool off Insert the query  The last step after the settings are modified and  spool  is running is to insert your query. For our simple example, we’re outputting all books from our  books  table. SELECT   title,   author FROM   authors; Don’t forget the semi...
Read more

How to troubleshoot Long Running Concurrent Request in EBS 12.2

By
 The following points need to answer before jumping into troubleshooting: Are there any recent code changes done in the concurrent program?           Check long_running_sid and verify the recent code change of last_ddl_timestamp after a             discussion with the developer. Was this running long in the last few run as well, or was this time only?            ==> Check the program history. How much time does it take to complete?           Once again program history can give us some idea. Are these jobs fetching higher data compared to the last run           ===> Confirm on data change . Query Plan change will indicate this issue. Does this job runs at any specific time or it can be run anytime           ===> The nature of the job can help to understand the job's nature. Does this job fetch data using DB Link...
Read more

CPU Patch Analysis

By
 Oracle CPU patch analysis Identifying the Latest Critical Patch Update for Oracle E-Business Suite Release 12.2 (Doc ID 2484000.1) Database Quick-Link If you only care about the database patches, this MOS note steps you through really easily. Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patch sets and Base Releases (Doc ID 2118136.2) https://www.oracle.com/security-alerts/ Critical Patch Updates Critical Patch Updates provide security patches for supported Oracle on-premises products. They are available to customers with valid support contracts. Starting in April 2022, Critical Patch Updates are released on the third Tuesday of January, April, July, and October (They were previously published on the Tuesday closest to the 17th day of January, April, July, and October). The next four dates are: 16 April 2024 16 July 2024 15 October 2024 21 January 2025 A pre-release announcement will be published on the Thursday preceding eac...
Read more

How to set up the Oracle Wallets in Oracle Database 19C

By
  The Oracle Wallet is a container or repository that stores authentication and credentials such as certificates, certificate requests, and private keys., By using this we can connect to database without providing the schema name & password, Password will be connected by using the TNS ALIES name & hence the schema are encrypted & stored in the oracle wallets. Let us configure the schema password by using the wallets. Create a directory to store the wallets. Create a directory to store the password $ mkdir -p /u01/app/wallets Create a wallet & provide the wallet password. $ mkstore -wrl /u01/app/wallets/ -create Enter password: ********* Enter password again: ******** $ ll total 8 -rw-------. 1 oracle oinstall 194 Nov 1 14:38 cwallet.sso -rw-------. 1 oracle oinstall 0 Nov 1 14:38 cwallet.sso.lck -rw-------. 1 oracle oinstall 149 Nov 1 14:38 ewallet.p12 -rw-------. 1 oracle oinstall 0 Nov 1 14:38 ewallet.p12.lck Check the status of listener $lsnrctl status listener_n...
Read more