Index Rebuild
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.
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
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.
Note: To re-allocate an index, to another tablespace for example a rebuild is required.
- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.
4. Fragmentation within B*Tree Segments
----------------------------------------
B*Trees get fragmented due to repeated DML operations.
4.1. If the index is mostly insert and the keys are inserted in ascending order
towards the end of multiple sub-trees, over time the space utilization
goes down: the index should be reorganized.
If the insert activity is not expected to be high but random, it is
recommended to set PCTFREE parameter so that subsequent inserts does not
cause any potential utilization problems.
4.2. On deletes, if a leaf block becomes empty, it is put on the list of free
blocks. It is not removed from the tree at this point for performance
reasons as this block might have a key inserted into it soon.
4.3. Deleting or updating keys (updates will be treated effectively like a
delete / insert as far as the index is concerned) can cause fragmentation
within an index. A rebuild or coalesce may be called for, to avoid
performance issues, when del_lf_rows / lf_rows ratio becomes significant.
The "deleted" space within an index may get reused slowly (or never
again) if inserts are increasing and deletes are random. Rebuilding the
index may not reduce the height if there are no deleted rows in the index
to reclaim. At that point, maybe look at using a larger block size or
partitioning, but even that will not guarantee fewer logical and physical
IOs to lookup a key.
Along with the REBUILD clause of the ALTER INDEX, Oracle8i introduces the
COALESCE clause as another way to address fragmentation issues.
In the following cases, it may be worthwhile to rebuild the index:
--> the percentage of the space used is bad - lower than 66%: PCT_USED
--> deleted leaf blocks represent more than 20% of total leaf blocks: DEL_LF_ROWS
--> the height of the tree is bigger than 3: HEIGHT or BLEVEL
statistics after statistics collection is performed:
DBA_TABLES, DBA_TAB_PARTITIONS.
The following views are helpful in determining tree utilization :
DBA_INDEXES, DBA_IND_PARTITIONS, INDEX_STATS.
Please use the below script to determine the indexes that need to be rebuild.
set serveroutput on size 100000
DECLARE
vOwner dba_indexes.owner%TYPE; /* Index Owner */
vIdxName dba_indexes.index_name%TYPE; /* Index Name */
vAnalyze VARCHAR2(100); /* String of Analyze Stmt */
vCursor NUMBER; /* DBMS_SQL cursor */
vNumRows INTEGER; /* DBMS_SQL return rows */
vHeight index_stats.height%TYPE; /* Height of index tree */
vLfRows index_stats.lf_rows%TYPE; /* Index Leaf Rows */
vDLfRows index_stats.del_lf_rows%TYPE; /* Deleted Leaf Rows */
vDLfPerc NUMBER; /* Del lf Percentage */
vMaxHeight NUMBER; /* Max tree height */
vMaxDel NUMBER; /* Max del lf percentage */
CURSOR cGetIdx IS SELECT owner,index_name
FROM dba_indexes WHERE OWNER NOT LIKE 'SYS%';
BEGIN
/* Define maximums. This section can be customized. */
vMaxHeight := 3;
vMaxDel := 20;
/* For every index, validate structure */
OPEN cGetIdx;
LOOP
FETCH cGetIdx INTO vOwner,vIdxName;
EXIT WHEN cGetIdx%NOTFOUND;
/* Open DBMS_SQL cursor */
vCursor := DBMS_SQL.OPEN_CURSOR;
/* Set up dynamic string to validate structure */
vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE';
DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);
vNumRows := DBMS_SQL.EXECUTE(vCursor);
/* Close DBMS_SQL cursor */
DBMS_SQL.CLOSE_CURSOR(vCursor);
/* Does index need rebuilding? */
/* If so, then generate command */
SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows FROM INDEX_STATS;
IF vDLfRows = 0 THEN /* handle case where div by zero */
vDLfPerc := 0;
ELSE
vDLfPerc := (vDLfRows / vLfRows) * 100;
END IF;
IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN
DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' || vOwner || '.' || vIdxName || ' REBUILD;');
END IF;
END LOOP;
CLOSE cGetIdx;
END;
/
or
set serveroutput on size 100000
DECLARE
vOwner dba_indexes.owner%TYPE; /* Index Owner */
vIdxName dba_indexes.index_name%TYPE; /* Index Name */
vAnalyze VARCHAR2(100); /* String of Analyze Stmt */
vCursor NUMBER; /* DBMS_SQL cursor */
vNumRows INTEGER; /* DBMS_SQL return rows */
vHeight index_stats.height%TYPE; /* Height of index tree */
vLfRows index_stats.lf_rows%TYPE; /* Index Leaf Rows */
vDLfRows index_stats.del_lf_rows%TYPE; /* Deleted Leaf Rows */
vDLfPerc NUMBER; /* Del lf Percentage */
vMaxHeight NUMBER; /* Max tree height */
vMaxDel NUMBER; /* Max del lf percentage */
CURSOR cGetIdx IS SELECT owner,index_name
FROM dba_indexes WHERE OWNER not in ('SYS','OUTLN','SYSTEM','TSMSYS','DBSNMP','WMSYS','EXFSYS','XDB','OLAPSYS','MDSYS','CAGAUDIT','SYSMAN','DMSYS','CTXSYS','ORDSYS');
BEGIN
/* Define maximums. This section can be customized. */
vMaxHeight := 3;
vMaxDel := 20;
/* For every index, validate structure */
OPEN cGetIdx;
LOOP
FETCH cGetIdx INTO vOwner,vIdxName;
EXIT WHEN cGetIdx%NOTFOUND;
/* Open DBMS_SQL cursor */
vCursor := DBMS_SQL.OPEN_CURSOR;
/* Set up dynamic string to validate structure */
vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE';
DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);
vNumRows := DBMS_SQL.EXECUTE(vCursor);
/* Close DBMS_SQL cursor */
DBMS_SQL.CLOSE_CURSOR(vCursor);
/* Does index need rebuilding? */
/* If so, then generate command */
SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows FROM INDEX_STATS;
IF vDLfRows = 0 THEN /* handle case where div by zero */
vDLfPerc := 0;
ELSE
vDLfPerc := (vDLfRows / vLfRows) * 100;
END IF;
IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN
DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' || vOwner || '.' || vIdxName || ' REBUILD;');
END IF;
END LOOP;
CLOSE cGetIdx;
END;
/
Caution
This sample code is provided for educational purposes only, and is not supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Script
Example script (customize to suit) :
begin
for c1 in (
select i.owner, i.index_name, s.tablespace_name,
i.initial_extent, i.next_extent, i.min_extents,
i.max_extents,
i.pct_increase, s.bytes
from sys.dba_segments s, sys.dba_indexes i
where s.segment_type = 'INDEX'
and i.index_name = s.segment_name
and i.owner='&owner'
and i.owner not in
('SYSTEM','SYS','DBVISION','MONITOR','TUNING') )
loop
if c1.tablespace_name not like '%INDX%' then -- Make sure we use the INDEX tablespace
c1.tablespace_name := 'INDX';
end if;
if c1.pct_increase not between 0 and 100 then -- Set PCTINCREASE to0
c1.pct_increase := 0;
end if;
if c1.pct_increase is null then -- Set PCTINCREASE to0
c1.pct_increase := 0;
end if;
if c1.bytes/1024/1024 > 200 then -- Largest extent willbe 200K
c1.bytes := 200*1024*1024;
end if;
if c1.max_extents < 121 then -- Set MAXEXTENTS=121
c1.max_extents := 121;
end if;
dbms_output.put_line('ALTER INDEX '||c1.owner||'.'||c1.index_name||
' REBUILD TABLESPACE '||'&tablespace_name'||' STORAGE ('||
' INITIAL '||to_char(c1.bytes)||
' NEXT '||to_char(c1.bytes)||
' PCTINCREASE '||c1.pct_increase||' MAXEXTENTS '||c1.max_extents||');');
end loop;
dbms_output.put_line('ALTER TABLESPACE &tablespace_name COALESCE;');
end;
/
Rebuilding the partition of an Index with parallel option will not change the overall degree of index.
I dont see any command to change the degree back to 1 either after rebuilding partitioned index with degree>2.(So it may not be required actually)
However you can change the degree of index to >2 before rebuilding the index partition and change it back to 1 after index partition is rebuilt. But I dont think it will make a difference.
ALTER INDEX A PARALLEL (DEGREE 4);
ALTER INDEX A REBUILD PARTITION A_1 ONLINE PARALLEL (DEGREE 4); OR ALTER INDEX A REBUILD PARTITION A_1 ONLINE PARALLEL ;
ALTER INDEX A PARALLEL (DEGREE 1
set lines 132
set pages 9999
SET FEEDBACK OFF
SET ECHO OFF
SET HEADING ON
SET VERIFY ON
SET TERMOUT OFF
SET TRIMSPOOL ON
select m.table_name, m.timestamp, sum(m.inserts), sum(m.updates), sum(m.deletes), t.num_rows, t.last_analyzed from sys.dba_tab_modifications m, dba_tables t
where m.table_owner = t.owner and m.table_name = t.table_name and m.table_owner not in ('SYS','OUTLN','SYSTEM','TSMSYS','DBSNMP','WMSYS','EXFSYS','XDB','OLAPSYS','MDSYS','CAGAUDIT','SYSMAN','DMSYS','CTXSYS','ORDSYS') and rownum<=100
group by m.table_name, m.timestamp, t.num_rows, t.last_analyzed order by 3,4,5
select owner,segment_name,segment_type,bytes/1024/1024/1024 from dba_segments where segment_name in ('PF_PAY_PLAN_CRED_S_03','PF_PAY_PLAN_CRED_S_05','PF_PAY_PLAN_CRED_S_04','PK_PAY_PLAN_CRED_S','PF_PAY_PLAN_CRED_S_01','PF_PAY_PLAN_CRED_S_02');
SELECT t.table_name, i.index_name,i.blevel,i.status,t.last_analyzed FROM dba_tables t, dba_indexes i WHERE t.table_name = i.table_name AND t.table_name ='PAYMENT_PLAN_CREDIT_SHARE';
elect owner,segment_name,segment_type,bytes/1024/1024/1024 from dba_segments where segment_name in ('PF_PAY_PLAN_CRED_S_03','PF_PAY_PLAN_CRED_S_05','PF_PAY_PLAN_CRED_S_04','PK_PAY_PLAN_CRED_S','PF_PAY_PLAN_CRED_S_01','PF_PAY_PLAN_CRED_S_02');
select object_name,owner,LAST_DDL_TIME from dba_objects where object_name='QT_CRDR_DELETION';
Note==The Clustering Factor records the number of data blocks that will be accessed when scanning an index.
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.
SELECT INDEX_NAME, CLUSTERING_FACTOR FROM DBA_INDEXES WHERE INDEX_NAME IN ('EMP_NAME_IX','EMP_EMP_ID_PK'
select a.owner, a.table_name, a.num_rows, b.bytes/1048576 MB, a.last_analyzed from dba_tables a, dba_segments b
where a.owner=b.owner and a.table_name=b.segment_name and a.table_name='&table_name' order by b.bytes desc;
select object_name,object_type,owner,select LAST_DDL_TIME from dba_objects where owner='DMS_USER' AND object_name IN('PRCL_ITEM_PRCL_FK_I','CUST_REQ_CUSTORD_FK_I','CUSTORD_PK','PRCL_ITEM_PK','PRCL_ITEM_PCDR_DATE_I','PRCL_PK','CUST_REQIT_CUST_REQ_FK_I','PRCL_ITEM_PP_FK_I',
'PRCL_ITEM_WMS_STAT_I','WMSORD_PK','P_LOCN_DC_LOCN_FK_I','PLS_PROD_I','WSF_PROD_I','CUST_REQIT_PP_FK_I') order by 3;
select table_name, index_name , degree
from dba_indexes where owner='DMS_USER' AND index_name IN('PRCL_ITEM_PRCL_FK_I','CUST_REQ_CUSTORD_FK_I','CUSTORD_PK','PRCL_ITEM_PK','PRCL_ITEM_PCDR_DATE_I','PRCL_PK','CUST_REQIT_CUST_REQ_FK_I','PRCL_ITEM_PP_FK_I',
'PRCL_ITEM_WMS_STAT_I','WMSORD_PK','P_LOCN_DC_LOCN_FK_I','PLS_PROD_I','WSF_PROD_I','CUST_REQIT_PP_FK_I') order by 3;
SELECT t.table_name, i.index_name,i.blevel,i.status,t.last_analyzed FROM dba_tables t, dba_indexes i WHERE t.table_name = i.table_name AND t.table_name ='QT_CRDR_DELETION';
SQL> set long 1000
SQL> set pagesize 0
SQL> select DBMS_METADATA.GET_DDL('TABLE','QT_CRDR_DELETION','AQ_ADMIN') from DUAL;
No comments:
Post a Comment