一般观点认为 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,如果索引确实无法增加数据检索的速度,则清除之.
