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

Post a Comment

Popular posts from this blog

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

Image
 During connection from SQL Developer to EBS database got below error message: Status : Failure - Test failed: IO Error: Got minus one from a read call Following solution can be done : 1. Go to directory $ORACLE_HOME / network / admin 2. Modify sqlnet.ora file with following parameter : tcp.validnode_checking = no 3. If you don 't want to disable this, you can put the machine names as follows: tcp.invited_nodes=(machine1, machine2) 3. Bounce the listener. Due to security enhancements it is not good to put tcp.validnode_checking = no or comment out will through error when we start listener like below: Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=shsebs7idb1.appsolworld.com)(PORT=1522))) TNS-00584: Valid node checking configuration error TNS-12560: TNS:protocol adapter error Better plan is to add localhost or IP address of client (Local Computer) in the sqlnet.ora file That will solve the problem NAMES.DIRECTORY_PATH=(TNSNAMES, ONAMES, HOSTNAME) SQLNET.EXPI
Read more

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

  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

How to troubleshoot Long Running Concurrent Request in EBS 12.2

 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          ====> Check whether the DB link is involved or not. The below-given approach would help to conclude the gray areas. Check the load of the server using t
Read more

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

  Issue:  EBS Local Login url is working fine. When accessing sso url, users are getting error as below appended to the url. And login page will be blank. One of the reason could be : OAM Authentication is fine but during authorization process, OAM is unable to map guid from oid to user_guid in fnd_user. This can be due to user_guid being null in fnd_user table. As per the process, when user logins for the first time, user_guid need to be populated automatically (from oid) Query guid from oid and update fnd_user table with that. ldapsearch -h oidhost.appsolworld.com -p 3060 -D "cn=orcladmin" -w orcladmpwd -b "cn=Users,dc=dc,dc=online,dc=org" -s sub "(uid=$1)" orclsamaccountname krbprincipalname mail orcluserprincipalname orclguid Pass userid to this command which gives guid along with other details. Update fnd_user table with above guid : update fnd_user  set user_guid=' ' where user_name like ' '; Solution:  As per the process,  when user
Read more

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

OPatch failed with error code 2  When checked the patch conflict for a patch, got below error. ]$opatch prereq CheckConflictAgainstOHWithDetail -ph ./ Oracle Interim Patch Installer version 12.2.0.1.29 Copyright (c) 2020, Oracle Corporation.  All rights reserved. PREREQ session Oracle Home       : /u01/oracle/product/19.0.0.0 Central Inventory : /u01/oracle/oraInventory    from           : /u01/oracle/product/19.0.0.0/oraInst.loc OPatch version    : 12.2.0.1.29 OUI version       : 12.2.0.1.4 Log file location : /oragrid/orahome/product/19.3.0/cfgtoollogs/opatchauto/core/opatch/opatch2023-12-22_09-37-15AM_1.log Invoking prereq "checkconflictagainstohwithdetail" Prereq "checkConflictAgainstOHWithDetail" is not executed. The details are: Unable to create Patch Object. Exception occured : /orastage/OCT23_GRID_CPU/35642822/33575402/etc/config/actions.xml with Version field of the component "delete" in actions file cannot be <null> or empty. Please check p
Read more

Few Important steps of Oracle Database Clone

 Make Sure oraInst.loc location is correct and accessble. [demantra@dsiddem ~]$ cat /etc/oraInst.loc inventory_loc=/orastage/oraInventory inst_group=dba [demantra@dsiddem ~]$ cd /orastage/oraInventory [demantra@dsiddem oraInventory]$ ls -ltr total 16 drwxrwx---. 5 demantra dba 4096 Jul 22 19:37 backup drwxrwx---. 2 demantra dba 4096 Jul 22 19:37 ContentsXML drwxrwx---. 2 demantra dba 4096 Jul 22 19:37 locks drwxrwx---. 3 demantra dba 4096 Jul 23 10:08 logs Remove Oracle Home from Inventory [demantra@dsiddem oraInventory]$ cd $ORACLE_HOME/oui/bin [demantra@dsiddem bin]$ ./runInstaller -detachHome ORACLE_HOME=$ORACLE_HOME Starting Oracle Universal Installer... Checking swap space: must be greater than 500 MB.   Actual 3039 MB    Passed The inventory pointer is located at /etc/oraInst.loc Run Clone.pl [demantra@dsiddem bin]$ echo $ORACLE_HOME /dnbsid/demantra/oracle/19.0.0 [demantra@dsiddem bin]$ echo $ORACLE_BASE /dnbsid/demantra [demantra@dsiddem bin]$ E01=ORACLE_HOME=$ORACLE_HOME [dema
Read more

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

Below query can be used in finding out current AD and TXK code levels.  [ap******@shsd*** 22989949]$ sqlplus apps SQL*Plus: Release 10.1.0.5.0 - Production on Fri Sep 30 01:00:25 2022 Copyright (c) 1982, 2005, Oracle.  All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production SQL> col ABBREVIATION for a10 set lines 1000 col NAME for a50 col CODELEVEL for a20 SELECT ABBREVIATION,NAME,codelevel FROM AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad');SQL> SQL> SQL> SQL> ABBREVIATI NAME                                               CODELEVEL ---------- -------------------------------------------------- -------------------- ad         Oracle Applications DBA                            C.13 txk        Oracle Applications Technology Stack               C.13 Check fwk in r12.2 select abbreviation ,codelevel from ad_trackable_entities where abbreviation in( 'fwk') order by abbreviation; SELECT codelevel
Read more

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

Image
  Connection Filters allow one to deny access to UNWANTED Connections at the network level. Connection Filters can be used to protect server resources on individual servers, server clusters, or an entire internal network or Intranet. Connection filters are particularly useful when using the Administration port. Depending on the network firewall configuration, it may be possible to use a connection filter to further restrict administration access. A typical use might be to restrict access to the Administration port to only the servers and machines in the domain. WebLogic Server provides a default connection filter called  weblogic.security.net.ConnectionFilterImpl . This default connection filter accepts all incoming connections except the ones defined in the Administration Console as a Connection filter rule. Custom connection filters may be written  by implementing the classes in the weblogic.security.net package. This post will demonstrate the steps to configure Weblogic Connection F
Read more

CPU Patch Analysis

 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 each Cr
Read more

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

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