| |
|
| | | | | SCRIPT | | | PUBLISHED | | | 2016-10-31 | | | 2016-12-7 | | | English简体中文日本語??? |
|
|
文档内容
适用于:
Oracle Database - Enterprise Edition - 版本 9.0.1.0 到 11.2.0.4 [发行版 9.0.1 到 11.2]
本文档所含信息适用于所有平台
用途
这个脚本将根据已存在的表和索引的统计信息来核实b-tree索引结构。这个脚本将计算如下的条目：
- 估计索引的理论大小。
- 索引布局
这个脚本会将收集的信息以历史记录的形式保存在INDEX_HIST表中。这对避免做定时索引重建很有帮助。用户可以自定义这个历史记录
请注意，这个脚本不使用‘analyze table ... validate structure’，而依赖内部功能SYS_OP_LBID，它从9i开始存在。
在评估这个脚本前，强烈推荐您首先查看
NOTE 989093.1 - Index Rebuild, the Need vs the Implications
要求
SQL, SQL*Plus, iSQL*Plus
配置
1.创建一个用户，它将拥有索引统计信息表
2.为这个用户赋予‘dba’权限，以及 select on dba_tablespaces 权限。
3.执行脚本的代码
如果脚本以SYS外的其他用户执行，在创建包体时，将遭遇ORA-942错误
即使赋予了正确的角色，除非显式授予如下SELECT权限，CREATE PACKAGE BODY还是会失败
grant select on dba_tablespaces to
;
grant select on dba_indexes to
;
grant select on dba_tables to
;
grant select on dba_ind_columns to
;
grant select on dba_tab_cols to
;
grant select on dba_objects to
;
grant select on v_$parameter to
;
说明
因为脚本依赖于最新的统计信息，请首先收集统计信息，使之能够看到schema中的变化。
SQL> exec dbms_stats.gather_schema_stats('SCOTT');
之后运行下一个procedure：
SQL> exec index_util.inspect_schema ('SCOTT');
警告
此示例代码只为教育目的，Oracle Support不提供技术支持。它已经过内部测试，然而我们无法确保它在任何环境中都能成功使用。请您在使用之前先在测试环境中运行。
SCRIPT
CREATE TABLE index_log (
owner VARCHAR2(30),
index_name VARCHAR2(30),
last_inspected DATE,
leaf_blocks NUMBER,
target_size NUMBER,
idx_layout CLOB);
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 NUMBER := 0.6;
vTargetUse CONSTANT POSITIVE := 90; -- equates to pctfree 10
vHistRet CONSTANT POSITIVE := 10; -- (#) records to keep in index_hist
procedure 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);
vIndxLyt CLOB;
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 a.block_size into vBlockSize from dba_tablespaces a,dba_indexes b where b.index_name=aIndexName and b.owner=aIndexOwner and a.tablespacE_name=b.tablespace_name;
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 ' || aTableOwner || '.' || 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;
vIndxLyt := '';
FOR i IN l_data.FIRST..l_data.LAST LOOP
vIndxLyt := vIndxLyt || 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), vIndxLyt);
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 = vIndxLyt
where owner = aIndexOwner and index_name = aIndexName;
END IF;
END IF;
END inspect_index;
END index_util;
/
示例输出
找到满足条件的索引：
SQL> select owner, index_name, last_inspected, leaf_blocks, target_size
from index_log
OWNER 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 - 1
第一列列出索引块中的行数，第二列列出具有这个索引条目数的块数。例如有3个块分别有238行，1个块有345行。
找出一个索引的变化过程:
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
dba_tablespaces
参考
NOTE:989093.1
- Index Rebuild, the Need vs the Implications
2016-06-19 01:32 by 潇湘隐者,
2207 阅读,
评论,
收藏,
编辑
ORACLE数据库中的索引到底要不要定期重建呢？ 如果不需要定期重建，那么理由是什么？ 如果需要定期重建，那么理由又是什么？另外，如果需要定期重建，那么满足那些条件的索引才需要重建呢？关于这个问题，网上也有很多争论，也一直让我有点困惑，因为总有点不得庐山真面目的感觉，直到上周看到了一些资料，遂整理于此，方便以后翻阅：
首先来看看网上关于索引需要重建的准则或标准：
一：分析(analyze)指定索引之后，查询index_stats的height字段的值，如果这个值>=4 ,最好重建（rebuild）这个索引。虽然这个规则不是总是正确，但如果这个值一直都是不变的，则这个索引也就不需重建。
二：在分析(analyze)指定索引之后，查询index_stats的del_lf_rows和lf_rows的值,如果(del_lf_rows/lf_rows)*100 > = 20，则这个索引也需要重建。
关于这个论据，我们找到可以考证的官方资料为Various Aspects of Fragmentation (文档 ID 186826.1) ，其中有这么一段
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
另外网上还有一些关于重建索引的理由或说法，大概有这么一下（有可能不全），如下所示：
1、Oracle的B树索引随着时间的推移变得不平衡(错误的认识)
2、索引碎片在不断增加
3、索引不断增加，删除的空间没有重复使用（错误的认识）
4、索引 clustering factor （集群因子）不同步，可以通过重建修复(错误的认识)
但是这个资料非常古老了，是Oracle 8i， Oracle 9i时代的资料，当然对于索引深度超过4级以及已删除的索引条目至少占有现有索引条目总数的20%需要重建索引的准则，这种说法显然也没有过时，但是关于PCT_USED低于66%的就建议重建索引，这种说法对现在主流版本有点不合时宜。关于这些，其实ORACLE官方文档有关于索引重建的必要性和影响的讨论， 官方文档为索引重建的必要性与影响 (文档 ID 1525787.1)
-------------------------------------------------------------------
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
