This script will verify the structure of a b-tree index based on the existing table and index statistics. The script calculates the following items:
- Estimate the size the index should be. Optimal packing can be specified
- The index layout This script keeps a history of the the data gathered in the INDEX_HIST table. This can be useful to prevent a pattern in index rebuilding. The history log can be user defined
Important to note is that this script does not use the 'analyze table ... validate structure' but it relies on the internal function SYS_OP_LBID which is available as of 9i
Configuring the Script
1. Create a user that will contain the index statistics tables2. Assign the 'dba' privilege to this user.
3. Execute the code located in the script section
Running the Script
SQL> exec index_util.inspect_schema ('SCOTT');Caution
This script is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it. Proofread this script before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.
Script
CREATE TABLE index_log ( owner VARCHAR2(30), index_name VARCHAR2(30), last_inspected DATE, leaf_blocks NUMBER, target_size NUMBER, idx_layout VARCHAR2(4000));
ALTER TABLE index_log ADD CONSTRAINT pk_index_log PRIMARY KEY (owner,index_name);
CREATE TABLE index_hist ( owner VARCHAR2(30), index_name VARCHAR2(30), inspected_date DATE, leaf_blocks NUMBER, target_size NUMBER, idx_layout VARCHAR2(4000));
ALTER TABLE index_hist ADD CONSTRAINT pk_index_hist PRIMARY KEY (owner,index_name,inspected_date);
--
-- Variables:
-- vMinBlks: Specifies the minimum number of leaf blocks for scanning the index
-- Indexes below this number will not be scanned/reported on
-- vScaleFactor: The scaling factor, defines the threshold of the estimated leaf block count
to be smaller than the supplied fraction of the current size.
-- vTargetUse : Supplied percentage utilisation. For example 90% equates to the default pctfree 10
-- vHistRet : Defines the number of records to keep in the INDEX_HIST table for each index entry
--
CREATE OR REPLACE PACKAGE index_util AUTHID CURRENT_USER IS
vMinBlks CONSTANT POSITIVE := 1000;
vScaleFactor CONSTANT POSITIVE := 0.6;
vTargetUse CONSTANT POSITIVE := 90; -- equates to pctfree 10
vHistRet CONSTANT POSITIVE := 10; -- (#) records to keep in index_histprocedure inspect_schema (aSchemaName IN VARCHAR2); procedure inspect_index (aIndexOwner IN VARCHAR2, aIndexName IN VARCHAR2, aTableOwner IN VARCHAR2, aTableName IN VARCHAR2, aLeafBlocks IN NUMBER); END index_util; /CREATE OR REPLACE PACKAGE BODY index_util IS
procedure inspect_schema (aSchemaName IN VARCHAR2) IS
begin
FOR r IN (select table_owner, table_name, owner index_owner, index_name, leaf_blocks
from dba_indexes
where owner = upper(aSchemaname)
and index_type in ('NORMAL','NORMAL/REV','FUNCTION-BASED NORMAL')
and partitioned = 'NO'
and temporary = 'N'
and dropped = 'NO'
and status = 'VALID'
and last_analyzed is not null
order by owner, table_name, index_name) LOOP
IF r.leaf_blocks > vMinBlks THEN
inspect_index (r.index_owner, r.index_name, r.table_owner, r.table_name, r.leaf_blocks);
END IF;
END LOOP;
commit;
end inspect_schema;
procedure inspect_index (aIndexOwner IN VARCHAR2, aIndexName IN VARCHAR2, aTableOwner IN VARCHAR2, aTableName IN VARCHAR2, aLeafBlocks IN NUMBER) IS
vLeafEstimate number;
vBlockSize number;
vOverhead number := 192; -- leaf block "lost" space in index_stats
vIdxObjID number;
vSqlStr VARCHAR2(4000);
vCnt number := 0;
TYPE IdxRec IS RECORD (rows_per_block number, cnt_blocks number);
TYPE IdxTab IS TABLE OF IdxRec;
l_data IdxTab;
begin
select value into vBlockSize from v$parameter where name='db_block_size';
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
into vLeafEstimate
from (select /*+ no_merge */ table_name, num_rows, decode(partitioned,'YES',10,6) rowid_length
from dba_tables
where table_name = aTableName
and owner = aTableOwner) tab,
(select /*+ no_merge */ index_name, index_type, num_rows, decode(uniqueness,'UNIQUE',0,1) uniq_ind
from dba_indexes
where table_owner = aTableOwner
and table_name = aTableName
and owner = aIndexOwner
and index_name = aIndexName) ind,
(select /*+ no_merge */ column_name
from dba_ind_columns
where table_owner = aTableOwner
and table_name = aTableName
and index_owner = aIndexOwner
and index_name = aIndexName) ic,
(select /*+ no_merge */ column_name, avg_col_len
from dba_tab_cols
where owner = aTableOwner
and table_name = aTableName) tc
where tc.column_name = ic.column_name
group by ind.num_rows, ind.uniq_ind, tab.rowid_length;
IF vLeafEstimate < vScaleFactor * aLeafBlocks THEN
select object_id into vIdxObjID
from dba_objects
where owner = aIndexOwner
and object_name = aIndexName;
vSqlStr := 'SELECT rows_per_block, count(*) blocks FROM (SELECT /*+ cursor_sharing_exact ' ||
'dynamic_sampling(0) no_monitoring no_expand index_ffs(' || aTableName ||
',' || aIndexName || ') noparallel_index(' || aTableName ||
',' || aIndexName || ') */ sys_op_lbid(' || vIdxObjID ||
', ''L'', ' || aTableName || '.rowid) block_id, ' ||
'COUNT(*) rows_per_block FROM ' || aIndexOwner||'.'||aTableName || ' GROUP BY sys_op_lbid(' ||
vIdxObjID || ', ''L'', ' || aTableName || '.rowid)) group by rows_per_block order by rows_per_block';
execute immediate vSqlStr BULK COLLECT INTO l_data;
vSqlStr := '';
/*
FOR i IN l_data.FIRST..l_data.LAST LOOP
vSqlStr := vSqlStr || l_data(i).rows_per_block || ' - ' || l_data(i).cnt_blocks || chr(10);
END LOOP;
*/
select count(*) into vCnt from index_log where owner = aIndexOwner and index_name = aIndexName;
IF vCnt = 0
THEN insert into index_log values (aIndexOwner, aIndexName, sysdate, aLeafBlocks, round(vLeafEstimate,2), vSqlStr);
ELSE vCnt := 0;
select count(*) into vCnt from index_hist where owner = aIndexOwner and index_name = aIndexName;
IF vCnt >= vHistRet THEN
delete from index_hist
where owner = aIndexOwner
and index_name = aIndexName
and inspected_date = (select MIN(inspected_date)
from index_hist
where owner = aIndexOwner
and index_name = aIndexName);
END IF;
insert into index_hist select * from index_log where owner = aIndexOwner and index_name = aIndexName;
update index_log
set last_inspected = sysdate,
leaf_blocks = aLeafBlocks,
target_size = round(vLeafEstimate,2),
idx_layout = vSqlStr
where owner = aIndexOwner and index_name = aIndexName;
END IF;
END IF;
END inspect_index;
END index_util;
/
Script Output
To find out the indexes that meet the criteria:To verify the Index layout the following query can be used:SQL> select owner, index_name, last_inspected, leaf_blocks, target_size from index_logOWNER INDEX_NAME LAST_INSP LEAF_BLOCKS TARGET_SIZE ------------------------------ ------------------------------ --------- ----------- ----------- SYS I_ARGUMENT1 17-JUN-10 432 303 SYS I_ARGUMENT2 17-JUN-10 282 186 SYS I_COL1 17-JUN-10 288 182 SYS I_DEPENDENCY1 17-JUN-10 109 103 SYS I_DEPENDENCY2 17-JUN-10 136 95 SYS I_H_OBJ#_COL# 17-JUN-10 258 104 SYS WRH$_SQL_PLAN_PK 17-JUN-10 118 59 SYS WRI$_ADV_PARAMETERS_PK 17-JUN-10 210 121 SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 17-JUN-10 2268 1313 SYS I_WRI$_OPTSTAT_H_ST 17-JUN-10 1025 963 SYS I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST 17-JUN-10 338 191
SQL> select idx_layout from index_log where owner='SCOTT' and index_name='T_IDX'; IDX_LAYOUT ------------ 104 - 1 204 - 1 213 - 1 219 - 1 221 - 2 222 - 1 223 - 2 224 - 1 225 - 1 230 - 1 231 - 3 235 - 3 236 - 1 238 - 3 239 - 2 241 - 1 242 - 2 243 - 1 245 - 3 247 - 1 249 - 1 250 - 1 252 - 3 255 - 1 257 - 2 263 - 2 264 - 1 267 - 1 268 - 1 276 - 1 283 - 1 296 - 1 345 - 1The first column lists the number of rows in the index block and the second column lists the number of blocks that have this number of index entries, for example there are 3 blocks with 238 rows, and 1 block with 345 rows.
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'; INSPECTED_DATE LEAF_BLOCKS TARGET_SIZE -------------------- ----------- ----------- 10-MAR-2010 10:04:04 432 303 10-APR-2010 10:04:03 435 430 10-MAY-2010 10:04:02 431 301
Niciun comentariu:
Trimiteți un comentariu