实例说明sql优化的重要性

2009-12-14 18:51| 发布者: admin| 查看: 32| 评论: 0|原作者: 情殇

◆接到报告说，某省数据库CPU长时间负载很......

接到报告说，某省数据库CPU长时间负载很高，很多时候还经常是cpu idle值为各位数，甚至经常为<5 的各位数，于是赶紧登陆检查，发现主要的瓶颈是WAIT IO，初步判断是和业务中的SQL语句有关的问题造成的。
HP-UX db01 B.11.11 U 9000/800 02/14/06

09:40:17 %usr %sys %wio %idle
09:40:19 13 3 72 11
09:40:21 10 2 80 8
09:40:23 9 3 71 16
09:40:25 11 3 71 16
09:40:27 12 3 74 12

Average 11 3 74 13

依据STATSPACK和一些维护经验，作出了如下调整策略：
1, 一个业务历史大表，已经将近40G多，有史以来的历史数据全部在线。
修改策略，将改表重建为时间分区表，按月份存放，并建立相应的local index
注：
由于日常对于这个表的操作都是insert，只有在授理投诉的时候会这个表来查找历史数据，因此这个表的影响几乎从未在STATSAPCK中体现过。
影响：这个调整使得wio降低了25％左右

2, 根据statspack报告：
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
2,996,669 86 34,845.0 26.6 168.34 1363.19 1482568540
Module: JDBC Thin Client
select count(*) from operation_log where 0=0 and OPR_TIME>=to_da
te('2006-03-14','yyyy-mm-dd') and OPR_TIME<=to_date('2006-03-14'
,'yyyy-mm-dd')+1

可以看到这个语句的执行计划：
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
|* 2 | TABLE ACCESS FULL | OPERATION_LOG | | | |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OPERATION_LOG"."OPR_TIME"<=TO_DATE('2006-03-15
00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"OPERATION_LOG"."OPR_TIME">=TO_DATE('2006-03-14 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))
俨然一个权标扫描，还是没有RBO的!!

检查发现这个表 322M，2074560行数据，只有一个主键索引：
INDEX_NAME
------------------------
PK_OPERATION_LOG

于是给它添加基于OPR_TIME字段的索引。
SQL> select COLUMN_NAME from dba_ind_columns where table_name = 'OPERATION_LOG'
2 and index_name='IDX_OPERLOG_TIME';

COLUMN_NAME
-------------------------------------------------------
OPR_TIME

SQL>

修改后的执行计划：
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 37 |
| 1 | SORT AGGREGATE | | 1 | 7 | |
|* 2 | INDEX RANGE SCAN | IDX_OPERLOG_TIME | 13038 | 91266 | 37 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OPERATION_LOG"."OPR_TIME">=TO_DATE('2006-03-14 00:00:00',
'yyyy-mm-dd hh24:mi:ss') AND "OPERATION_LOG"."OPR_TIME"<=TO_DATE('2006-03-
15 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

现在执行一次这个语句的时间：
SQL> l
1 SELECT COUNT (*)
2 FROM OPERATION_LOG
3 WHERE 0 = 0
4 AND opr_time >= TO_DATE ('2006-03-14', 'yyyy-mm-dd')
5* AND opr_time <= TO_DATE ('2006-03-14', 'yyyy-mm-dd') + 1
SQL> /

COUNT(*)
----------
11617
Elapsed: 00:00:00.01
SQL>

3, 根据statspack报告，发现BOSSCHARGEINFO 表有1995188行，
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
916,933 30 30,564.4 26.7 102.74 654.58 2412605967
Module: XXXXXXX (TNS V1-V3)
select MID ,SERVID ,ACCESSMODEID ,SERVICEGRADEID ,STATUS ,FEEMON
TH ,to_char(chargedate,'YYYYMMDDHH24MISS') from BOSSCHARGEINFO
where (status=4 and feemonth=:b0)

问题的现象和处理方法通问题2一样，这里是添加基于status和feemonth的联合索引。
修改后，该语句效率提高了一倍多（请注意改语句基本上每分钟执行一次，因此，总体效率的提高更为显著）。

4,
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
427,455 20 21,372.8 12.5 50.18 215.02 1608726146
Module: XXXXXXX (TNS V1-V3)
SELECT MID,SERVID,ACCESSMODEID, SERVICEGRADEID,STATUS,FEEMONTH,
to_char(chargedate,'YYYYMMDDHH24MISS') FROM DELAYCHARG
EINFO WHERE status = 4 and feemonth =
'200603'

问题的现象和处理方法通问题3一样，这里是添加基于status和feemonth的联合索引。
修改后，该语句效率提高了一倍多（请注意改语句基本上每分钟执行一次，因此，总体效率的提高更为显著）。

调整后的状态：
HP-UX db01 B.11.11 U 9000/800 04/05/06

15:45:16 %usr %sys %wio %idle
15:45:18 25 3 30 42
15:45:20 21 4 35 40
15:45:22 24 5 24 47
15:45:24 23 5 27 46
15:45:26 23 3 31 43

Average 23 4 30 44

收藏分享邀请

		自动登录	找回密码
密码			注册

实例说明sql优化的重要性

最新评论

相关分类

下级分类