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

WBOY
发布: 2016-06-07 15:50:32
原创
853 人浏览过

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

 

 

 

 

 

 

来源:php.cn
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板