关闭

实例说明sql优化的重要性

138人阅读 评论(0) 收藏 举报
 

HTML Tags and JavaScript tutorial



实例说明sql优化的重要性





我看原文以后的感想:1.不要使数据表数据过大2.建立适当的查询索引.
原文地址:
http://blog.csdn.net/lunar2000/archive/2006/04/06/652263.aspx
原文如下:
接到报告说,某省数据库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
 


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:152382次
    • 积分:4043
    • 等级:
    • 排名:第7944名
    • 原创:242篇
    • 转载:0篇
    • 译文:0篇
    • 评论:16条
    文章存档
    最新评论