从实例看oracle的索引监控与无效索引维护

最新推荐文章于 2024-02-04 11:04:44 发布

纯粹打酱油

最新推荐文章于 2024-02-04 11:04:44 发布

阅读量249

点赞数

一般观点认为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)