Heim > Datenbank > MySQL-Tutorial > Hauptteil

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

WBOY
Freigeben: 2016-06-07 15:50:32
Original
854 Leute haben es durchsucht

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

 

 

 

 

 

 

Verwandte Etiketten:
Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage