ORACLE性能优化有时就这么简单_index

最新推荐文章于 2021-01-09 09:00:13 发布

crygaj7140

最新推荐文章于 2021-01-09 09:00:13 发布

阅读量123

点赞数

性能优化有时就这么简单

一、概述

最近有一个系统（aix6.1+oracle10.2.0.5 RAC）CPU每五分钟就达到百分之百，这个系统是监控系统，针对所有运行系统每五分钟取数，所以之前也没太在意，其实在年前就有过这种情况，只是把执行频繁的语句发送给项目组，有针对系统运行环境调整了一下SGA等大小，只是不告警了（cpu使用超过百分之九十就告警），也就没管。可这几天有出现该问题，还是继续将执行频繁的sql发送给项目组，由于所有受监控的系统都会每五分钟向该系统发数，也就理所当然的认为系统就这个特点，也就没理会。可是，这两天，告警短信也每五分钟就发送一次，随之的恢复短信，自己看着不烦，领导看着也烦啊，那就看看吧（虽然对sql语句不感冒吧）

二、处理过程

通过提取一段时间内的AWR，发现下面这条语句执行次数最频繁，当然也是消耗cpu资源最多的。

SELECT V1400, H, L, A, to_char(HTIME, 'yyyy-mm-dd hh24:mi:ss'), to_char(LTIME, 'yyyy-mm-dd hh24:mi:ss') FROM XXXDBA.ORACLETABLEAVAILABLE WHERE RESOURCEID = :1 AND TIME = to_date(:2, 'yyyy-mm-dd')

第一眼看去有绑定变量，而且截取几个时间段的AWR发现每次执行的语句列名也不完全相同，就想，让项目组的人去看吧。闲来无事，就查看了一下该表的信息

SQL> desc XXXDBA.ORACLETABLEAVAILABLE

Name Null? Type

---------------------------------------

RESOURCEID NOT NULL VARCHAR2(128)

TIME NOT NULL DATE

V0000 NUMBER

V0005 NUMBER

V0010 NUMBER

V0015 NUMBER

V0020 NUMBER

V0025 NUMBER

V0030 NUMBER

V0035 NUMBER

V0040 NUMBER

………………………………..

V2355 NUMBER

H NUMBER

L NUMBER

A NUMBER

HTIME DATE

LTIME DATE

第一反应是感觉这个表会不会定时或者根据条件更新列啊，先不管了，看一下执行计划再说，发现执行一次竟然那么长时间，而且走的全表扫描

SQL> explain plan for SELECT V0800,H,L,A,to_char(HTIME,'yyyy-mm-dd hh24:mi:ss'),to_char(LTIME,'yyyy-mm-dd hh24:mi:ss') FROM XXXDBA.ORACLETABLEAVAILABLE WHERE RESOURCEID = :1 AND TIME = to_date(:2,'yyyy-mm-dd');

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1457290298

-----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 65 | 18311 (1)| 00:03:40 |

|* 1 | TABLE ACCESS FULL| ORACLETABLEAVAILABLE | 1 | 65 | 18311 (1)| 00:03:40 |

-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("RESOURCEID"=:1 AND "TIME"=TO_DATE(:2,'yyyy-mm-dd'))

13 rows selected.

查看该表行数

SQL> select count(*) from XXXDBA.ORACLETABLEAVAILABLE;

COUNT(*)

----------

326796

查看是否有相关索引，竟然没有索引，按理说该表不算太大，但执行次数太多，加个索引会快些吧

SQL> select owner,index_name,index_type,table_name from dba_indexes where table_name='ORACLETABLEAVAILABLE';

no rows selected

通过询问项目组人员，得知，该表收集受监听系统时间点数据，更做相应更新操作，列名不变，查询语句中条件语句列RESOURCEID是唯一的，建议项目组添加索引，索引信息如下

SQL> select owner,index_name,index_type,table_name from dba_indexes where table_name='ORACLETABLEAVAILABLE';

OWNER INDEX_NAME INDEX_TYPE TABLE_NAME

------------------------------ ------------------------------ --------------------------- ------------------------------

XXXDBA P_ORACLETABLEAVAILABLE NORMAL ORACLETABLEAVAILABLE

SQL> select dbms_metadata.get_ddl('INDEX','P_ORACLETABLEAVAILABLE','XXXDBA') from dual;

DBMS_METADATA.GET_DDL('INDEX','P_ORACLETABLEAVAILABLE','XXXDBA')

--------------------------------------------------------------------------------------------------------------------

CREATE UNIQUE INDEX "XXXDBA"."P_ORACLETABLEAVAILABLE" ON "XXXDBA"."ORACLETABLEAVAILABLE" ("RESOURCEID", "TIME")

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "PERFORMANCE"

再次查看执行计划，这速度，杠杠的

SQL> explain plan for SELECT V0900,H,L,A,to_char(HTIME,'yyyy-mm-dd hh24:mi:ss'),to_char(LTIME,'yyyy-mm-dd hh24:mi:ss')

2 FROM XXXDBA.ORACLETABLEAVAILABLE

3 WHERE RESOURCEID = :1 AND TIME = to_date(:2,'yyyy-mm-dd')

4 ;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 1279632247

-----------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 65 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| ORACLETABLEAVAILABLE | 1 | 65 | 3 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | P_ORACLETABLEAVAILABLE | 1 | | 2 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("RESOURCEID"=:1 AND "TIME"=TO_DATE(:2,'yyyy-mm-dd'))

14 rows selected.

查看 CPU 信息 , 这简直是直线下降，在上午 10 点左右添加的索引

等待会话数，更直观

截取添加索引前后相同时间段AWR报告，进行对比，明显改观不少

Snapshot Set

Begin Snap Id

Begin Snap Time

End Snap Id

End Snap Time

Elapsed Time (min)

DB Time (min)

Avg Active Users

1st

33847

11-Mar-14 14:00:24

33848

11-Mar-14 14:31:40

31.27

227.75

7.28

2nd

33868

12-Mar-14 10:33:33

33869

12-Mar-14 11:02:13

28.67

14.79

0.47

1st Per Sec

2nd Per Sec

%Diff

1st Per Txn

2nd Per Txn

%Diff

Redo size:

90,352.30

91,860.86

1.67

1,104.51

1,148.70

4.00

Logical reads:

308,580.08

19,107.48

-93.81

3,772.25

238.94

-93.67

1^st

2nd

Event

Waits

Time(s)

Percent Total DB Time

Wait Class

Event

Waits

Time(s)

Percent Total DB Time

Wait Class

CPU time

5,342.3

39.09

CPU time

651.4

73.39

*gc buffer busy

4,559,903

3,796.7

27.78

Cluster

log file sync

121,763

151.2

17.03

Commit

*gc cr multi block request

1,007,965

209.3

1.53

Cluster

gc current block 2-way

139,251

62.9

7.08

Cluster

gc current block 2-way

358,579

187.7

1.37

Cluster

*log file parallel write

112,638

51.5

5.80

System I/O

log file sync

131,750

140.6

1.03

Commit

*gc cr block 2-way

45,035

23.5

2.65

Cluster

-log file parallel write

115,952

46.0

.34

System I/O

-gc cr multi block request

88,143

22.9

2.58

Cluster

-gc cr block 2-way

51,321

27.9

.20

Cluster

-gc buffer busy

1,290

11.4

1.28

Cluster

三、总结

在目前环境中呢，作为一个运维管理DBA，经常的习惯是保证数据库不出问题，做好备份，数据库别挂了就行。也习惯于将性能问题归于SQL语句编写的不规范，只是将SQL语句发送与项目组，让其优化，可惜太多时候是肉包子打狗有去无回，他们用心的少，只要我这访问慢就找你，系统一告警领导也找你，我们就说SQL语句的问题，来来回回、绕来绕去。倒不如静下心来看看你认为有问题的SQL语句，并标明哪哪有问题，交给项目组人员修改，利人利己。大多的数据库性能问题都跟SQL有关，但却也五花八门，其实当你认真的投入进去，并收到的效果时其乐无穷，何况简单的语句，效果却很明显的呢？每天进步一点就好。