A truly staggering amount of time, effort, and resources are expended regularly in a lot of shops rebuilding indexes ... needlessly.
I decided to put a stop to this in an application I was building and coded a procedure that applies math, not myth, to the problem.
The limits here, 10% and 75% were chosen at random for this demo. You should adjust these to be realistic for your application based on testing.
The code also, on the advise of Richard Foote, coalesces rather than rebuilds due to the lower cost and lack of locking.
If you have partitioned tables you can modify the code to look at the partition or subpartition level. |
CREATE TABLE idx_maint_hist (
schema_name VARCHAR2(30),
index_name VARCHAR2(30),
coalesce_or_rbld VARCHAR2(1) DEFAULT 'C', -- by default coalesce rather than rebuild
ignore_flag VARCHAR2(1) DEFAULT 'N', -- by default do not ignore an index
run_date DATE,
clust_factor NUMBER,
tab_blks NUMBER(38),
blks_bef_rbld NUMBER(38),
blks_aft_rbld NUMBER(38),
blks_prv_rbld NUMBER(38),
max_rows_per_lb NUMBER(10),
leaf_blks_lt75_pct NUMBER(10),
rebuilt VARCHAR2(1));
ALTER TABLE idx_maint_hist
ADD CONSTRAINT cc_idx_maint_hist_coal_or_rbld
CHECK (coalesce_or_rbld IN ('C','R'));
ALTER TABLE idx_maint_hist
ADD CONSTRAINT cc_idx_maint_hist_ignore_flag
CHECK (ignore_flag IN ('N','Y'));
CREATE OR REPLACE PACKAGE index_util AUTHID CURRENT_USER IS
/*******************************************************
This code is protected under applicable copyright law and may be
freely used and distributed as long as in so doing it remains free. Any
modifications to this code do not constitute a waiver of this restriction.
Copyright (c) 2009 Daniel A. Morgan
Made Available Pursuant To GNU General Public License 2'
*******************************************************/
PROCEDURE rebuild_indexes;
END index_util;
/
CREATE OR REPLACE PACKAGE BODY index_util IS
/*******************************************************
This code is protected under applicable copyright law and may be
freely used and distributed as long as in so doing it remains free. Any
modifications to this code do not constitute a waiver of this restriction.
Copyright (c) 2009 Daniel A. Morgan
Made Available Pursuant To GNU General Public License 2'
*******************************************************/
---------------------------------------------------
-- private function: returns 0 (zero) if the coalesce produced errors
FUNCTION rebuildNPIndexes(
pIndexName IN VARCHAR2,
pType IN VARCHAR2 DEFAULT 'C',
pDegree IN POSITIVE DEFAULT 1)
RETURN NATURAL IS
vOutVal NATURAL;
vRetVal NATURAL := 0; -- blocks after coalesce
eRebldError EXCEPTION;
BEGIN
IF pType = 'C' THEN
EXECUTE IMMEDIATE 'ALTER INDEX ' || pIndexName || ' COALESCE';
ELSE
dbms_index_utl.build_indexes(list => pIndexName, just_unusable => FALSE,
max_slaves => pDegree, num_errors => vOutVal);
END IF;
-- if no errors
IF vOutVal = 0 THEN
SELECT ui.leaf_blocks
INTO vRetVal
FROM user_indexes ui
WHERE ui.index_name = pIndexName;
ELSE
RAISE eRebldError;
END IF;
RETURN vRetVal;
EXCEPTION
WHEN others THEN
RETURN vRetVal;
END rebuildNPIndexes;
---------------------------------------------------
PROCEDURE rebuild_indexes IS
vMinBlkCnt CONSTANT NUMBER := 2; -- minimum blocks in table to consider
vBatchSize CONSTANT POSITIVE := 2000; -- bulk collect limit clause
vDegree CONSTANT POSITIVE := 2; -- degree of parallelism for index rebuild
vDivLine CONSTANT NUMBER := 0.75; -- pct above which blocks are considered full
vActLine CONSTANT NUMBER := 0.10; -- pct above which it is ok to coalesce or rebuild
vMinBytes CONSTANT NUMBER := 0; -- usage bytes must exceed this number or ignore
vMinIOCost CONSTANT NUMBER := 0; -- i/o cost must exceed this number or ignore
vIdxObjID user_objects.object_id%TYPE;
vIdxTable user_indexes.table_name%TYPE;
vSqlStr VARCHAR2(512);
TYPE idxAarray IS TABLE OF idx_maint_hist%ROWTYPE;
l_data idxAarray;
CURSOR icur IS
WITH imh AS (SELECT *
FROM idx_maint_hist imh1
WHERE imh1.schema_name(+) = USER
AND imh1.ignore_flag = 'N'
AND imh1.run_date = (
SELECT MAX(imh2.run_date)
FROM idx_maint_hist imh2
WHERE imh2.schema_name = USER)),
vsp AS (SELECT v.object_name, SUM(bytes), sum(io_cost)
FROM v$sql_plan v
WHERE object_owner = USER
AND object_type LIKE '%INDEX%'
GROUP BY v.object_name
HAVING SUM(bytes) > vMinBytes
AND SUM(io_cost) > vMinIOCost)
SELECT user SCHEMA_NAME, ui.index_name,
imh.coalesce_or_rbld, imh.ignore_flag, imh.run_date,
CAST(NULL AS NUMBER) clust_factor,
CAST(NULL AS NUMBER(38)) tab_blks,
CAST(NULL AS NUMBER(38)) blks_bef_rbld,
CAST(NULL AS NUMBER(38)) blks_aft_rbld,
imh.blks_aft_rbld blks_prv_rbld, -- blocks after the last become previous
CAST(NULL AS NUMBER(38)) max_rows_per_lb,
CAST(NULL AS NUMBER(10)) leaf_blks_lt75_pct,
'N' rebuilt
FROM imh, vsp, user_indexes ui
WHERE ui.index_name = vsp.object_name -- if the index is not used ignore it
AND ui.index_name = imh.index_name(+)
AND ui.leaf_blocks >= vMinBlkCnt; -- ignore indexes with too few leaf blocks
BEGIN
dbms_output.put_line('Copyright (c) 2009-2010 Daniel A. Morgan');
dbms_output.put_line('Made Available Pursuant To GNU General Public License 2');
dbms_application_info.set_module('index_util','index_rebuild');
OPEN icur;
LOOP
FETCH icur BULK COLLECT INTO l_data LIMIT vBatchSize;
FOR i IN 1 .. l_data.COUNT LOOP
-- gather the number of table blocks
SELECT ut.blocks
INTO l_data(i).tab_blks
FROM user_tables ut
WHERE ut.table_name IN (
SELECT ui.table_name
FROM user_indexes ui
WHERE ui.index_name = l_data(i).index_name);
-- gather index blocks and clustering factor stats
SELECT ui.clustering_factor, ui.leaf_blocks, table_name
INTO l_data(i).clust_factor, l_data(i).blks_bef_rbld, vIdxTable
FROM user_indexes ui
WHERE ui.index_name = l_data(i).index_name;
-- gathering leaf block stats requires the object_id
SELECT MIN(uo.object_id)
INTO vIdxObjID
FROM user_objects uo
WHERE uo.object_name = l_data(i).index_name;
-- get the maximum number of rows per leaf block
BEGIN
vSqlStr :=
'SELECT MAX(rows_per_block) maxblocks FROM (SELECT /*+ cursor_sharing_exact ' ||
'dynamic_sampling(0) no_monitoring no_expand index_ffs(' || vIdxTable ||
',' || l_data(i).index_name || ') noparallel_index(' || vIdxTable ||
',' || l_data(i).index_name || ') */ sys_op_lbid(' || vIdxObjID ||
', ''L'', ' || vIdxTable || '.rowid) block_id, ' ||
'COUNT(*) rows_per_block FROM ' || vIdxTable || ' GROUP BY sys_op_lbid(' ||
vIdxObjID || ', ''L'', ' || vIdxTable || '.rowid))';
execute immediate vSqlStr INTO l_data(i).max_rows_per_lb;
EXCEPTION
WHEN OTHERS THEN
l_data(i).max_rows_per_lb := NULL;
END;
-- get the number of blocks below the tunable vDivLine threshhold
BEGIN
vSqlStr :=
'SELECT COUNT(rows_per_block) FROM (SELECT /*+ cursor_sharing_exact ' |
'dynamic_sampling(0) no_monitoring no_expand index_ffs(' || vIdxTable ||
',' || l_data(i).index_name || ') noparallel_index(' || vIdxTable ||
',' || l_data(i).index_name || ') */ sys_op_lbid(' || vIdxObjID ||
', ''L'', ' || vIdxTable || '.rowid) block_id, ' ||
'COUNT(*) rows_per_block FROM ' || vIdxTable || ' GROUP BY sys_op_lbid(' ||
vIdxObjID || ', ''L'', ' || vIdxTable || '.rowid))' ||
' WHERE rows_per_block < ' || l_data(i).max_rows_per_lb * vDivLine;
execute immediate vSqlStr INTO l_data(i).leaf_blks_lt75_pct;
EXCEPTION
WHEN OTHERS THEN
l_data(i).leaf_blks_lt75_pct := NULL;
END;
-- if the number of unfilled blocks is greater than vMinBlkCnt,
-- and the number of leaf blocks that are less than vActLine full
-- exceeds 25% of the index leaf blocks, and a rebuild is
-- allowed then rebuild the index
IF (l_data(i).leaf_blks_lt75_pct > vMinBlkCnt)
AND (l_data(i).leaf_blks_lt75_pct >= (l_data(i).blks_bef_rbld * vActLine)) THEN
-- the return value is the number of blocks following rebuild
-- uncomment next 2 lines after testing. Once indexes are rebuilt
-- it is not easy to make them bad again without flashback database
IF l_data(i).coalesce_or_rbld IS NULL THEN
l_data(i).coalesce_or_rbld := 'C';
END IF;
IF l_data(i).ignore_flag IS NULL THEN
l_data(i).ignore_flag := 'N';
END IF;
-- l_data(i).blks_aft_rbld := rebuildNPIndexes(l_data(i).schema_name || '.' ||
-- l_data(i).index_name, l_data(i).coalesce_or_rbld, vDegree);
l_data(i).rebuilt := 'Y';
ELSE
l_data(i).blks_aft_rbld := l_data(i).blks_bef_rbld;
END IF;
END LOOP;
FORALL j IN 1..l_data.COUNT
INSERT INTO idx_maint_hist VALUES l_data(j);
EXIT WHEN l_data.COUNT < vBatchSize;
END LOOP;
COMMIT;
CLOSE icur;
dbms_application_info.set_module(NULL, NULL);
EXCEPTION
WHEN OTHERS THEN
dbms_application_info.set_module(NULL, NULL);
RAISE;
END rebuild_indexes;
---------------------------------------------------
END index_util;
/
TRUNCATE TABLE idx_maint_hist;
exec index_util.rebuild_indexes;
SELECT index_name, run_date, clust_factor, tab_blks, blks_bef_rbld, rebuilt
FROM idx_maint_hist
WHERE schema_name = USER
ORDER BY 1;
SELECT index_name, blks_bef_rbld, blks_aft_rbld, blks_prv_rbld, max_rows_per_lb, leaf_blks_lt75_pct
FROM idx_maint_hist
WHERE schema_name = USER
ORDER BY 1; |
