一般观点认为 oracle 数据库使用的索引不会超过设计时创建索引总数的 25%, 或者不以它们被期望的使用方式使用 . 在实际应用中 , 调优速度较慢的查询时 , 经常发现执行的 sql 调用了垃圾索引 , 而不是我们设计时建立的索引 . 所以我们有必要通过监控数据库索
一般观点认为 oracle 数据库使用的索引不会超过设计时创建索引总数的 25%, 或者不以它们被期望的使用方式使用 . 在实际应用中 , 调优速度较慢的查询时 , 经常发现执行的 sql 调用了垃圾索引 , 而不是我们设计时建立的索引 . 所以我们有必要通过监控数据库索引的使用 , 释放那些未被使用的索引 , 从而节省维护索引的开销 , 优化性能 .
为了查看目前系统中索引是否有效 , 我从 2008.09.19 号开始设置了索引监控 , 到目前共跟踪了 4 天的运行数据 . 下面我根据得到的索引监控信息 , 分几个角度解析 bi 系统的后台数据库索引的有效性 , 及维护无效索引的内存 ,io 和时间花销 .
1, 索引有效性统计
首先创建一个用来存储索引在监控时间段内是否被使用的临时表 ods.jax_t2.,
CREATE TABLE ods.jax_t2(
owner VARCHAR2 ( 100 ),
index_name VARCHAR2 ( 100 ),
table_name VARCHAR2 ( 100 ),
MONITORING VARCHAR2 ( 10 ),
used VARCHAR2 ( 10 )
) TABLESPACE odsd;
然后分别使用各不同账户登陆 , 并执行下面语句 , 将用户的信息统一写入 ods.jax_t2 中 .
INSERT INTO ods.jax_t2(owner,index_name,table_name, monitoring ,used)
SELECT USER ,index_name,table_name, MONITORING ,used FROM V$OBJECT_USAGE;
COMMIT ;
最后通过查询表 ods.jax_t2 可以得到索引有效使用率 .
SELECT owner, COUNT (INDEX_NAME),
NVL( SUM (DECODE(USED, 'YES' , 1 , 0 )), 0 ) 有效索引数目,
ROUND( 100 * NVL( SUM (DECODE(USED, 'YES' , 1 , 0 )), 0 ) /
COUNT (INDEX_NAME),
2 ) 索引有效率
FROM ods.jax_t2
GROUP BY owner
ORDER BY 索引有效率;
Owner
索引总数
有效索引数
有效索引率率 (%)
DC
130
0
0
OD
31
0
0
PRICE
6
0
0
DP22
70
11
15.71
WAREHOUSE
91
19
20.88
TODS
224
48
21.43
FBI
89
26
29.21
ODS
355
105
29.58
DP23
70
28
40
DW
50
23
46
RPT
13
6
46.15
CTL
32
20
62.5
合计
1161
286
24.63
2, 索引占用空间信息统计
数据字典 dba_segments 中存储有各数据库对象的空间分配情况 . 我们连立 dba_segments 和 ods.jax_t2 可以查询得到各用户总的空间分配和有效索引 , 无效索引所占用的空间大小 . 从统计信息中我们看到 , 在总共的 61G 索引中 , 只有 11G 左右的索引被有效利用 . 其他的索引空间在监控期间未被使用 , 这就是说 , 这 50G 的索引只有维护开销 , 而没能起到我们所设想的增加查询速度的功能 .
SELECT DS.OWNER, SEGMENT_TYPE, ROUND( SUM (BYTES) / 1024 / 1024 ),
round( SUM (decode(jt.used, 'YES' ,ds.bytes, 0 ))/ 1024 / 1024 ) 有效索引,
round( SUM (decode(jt.used, 'NO' ,ds.bytes, 0 ))/ 1024 / 1024 ) 无效索引
FROM DBA_SEGMENTS DS,ods.jax_t2 jt
WHERE ds.owner = jt.owner AND ds.segment_name = jt.Index_Name
AND DS.OWNER NOT IN ( 'SYS' , 'SYSTEM' , 'OUTLN' , 'WMSYS' )
AND DS.SEGMENT_TYPE = 'INDEX'
GROUP BY DS.OWNER, DS.SEGMENT_TYPE
ORDER BY 无效索引
Owner
对象类型
索引总空间 (M)
有效索引空间 (M)
无效索引空间 (M)
DP23
INDEX
5
2
3
DP22
INDEX
4
1
4
OD
INDEX
7
0
7
RPT
INDEX
10
1
9
CTL
INDEX
34
22
13
FBI
INDEX
199
2
197
PRICE
INDEX
200
0
200
TODS
INDEX
1504
270
1235
DC
INDEX
2188
0
2188
DW
INDEX
5212
2325
2887
ODS
INDEX
22240
8703
13537
WAREHOUSE
INDEX
29750
4
29745
总计
Index
61353
11330
50023
3, 部分索引维护的空间和时间花销
在这里 , 我选择了数据抽取过程中两个相对执行时间教程的表 CR_CUSTOMER_EXPIATION_A as CCEA 和 CR_ORDER_ROLE as COR 表进行一下分析 .
CCEA
COR
记录占用空间
28 (M)
2112 (M)
索引占用空间
40 (M)
5072 (M)
日维护记录行数 删除 / 插入
550138/550952
258593/279324
无效索引数 / 索引总数
1/1
2/4
删除所需时间
50.20 (S)
172 (S)
插入所需时间
16.25 (S)
39.22 (S)
去掉无效索引后删除时间
19.88 (S)
23.77 (S)
去掉无效索引后插入所需时间
2.78 (S)
13.75 (S)
根据上面的比较结果我们看到 , 目前系统中索引占用的总数据大小高达 60G 以上 , 但实际有效的索引占用空间只有 10G 左右 , 绝大多数的索引只是增加了我们的维护时间和空间开销 , 而无法为系统的性能提供支持 , 测试数据显示 , 在删除无效索引之后 , 系统的维护速度得到大幅度提高 . 所以我建议 :
1, 对一些检索比较频繁的表 , 找出系统中引用该表的查询语句 , 查看其执行计划 , 检索是否使用正确索引 ;
2, 如果已经使用正确索引 , 则考虑通过重建索引等手段查看是否能提高查询速度 ;
3, 如果索引确实无法增加数据检索的速度 , 则清除之 .
查看更多关于从实例看oracle的索引监控与无效索引维护的详细内容...