实例说明sql优化的重要性-数据库

     接到报告说，某省数据库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