实例说明sql优化的重要性

原创 2007年09月22日 18:16:00
 

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
 


相关文章推荐

实例说明optimize table在优化mysql时重要性——数据库优化

今天在看CU的时候,发现有人问有关optimize来表优化的问题,当年因为这个问题,困扰我很长一段时间,今天有空我把这个问题,用实际数据来展示出来,让大家可以亲眼来看看,optimize table的...

论好的编程习惯与机制对减少人为错误的重要性——由拼接SQL语句小bug想到

一点点小教训,简要说下。 很多企业有复杂的生产流程与测试流程,多到让人烦的不行,其实这也是企业抵御风险,避免人为错误的机制。其实,这种思维很重要,个人开发写程序其实也很需要机制来抵御风险。 最...

SQL中变量赋初始值的重要性

SQL中变量赋初始值的重要性

SQL SERVER——索引的重要性

转载请标明出处: http://blog.csdn.net/z_cloud_for_sql/article/details/55211345?ref=myread前面很多篇不管CPU、内存、磁盘、...

SQL语句实例说明

我是在MySQL数据库中做的测试,不同的数据库有一定的差别。   先来一些MySQL 显示表字段及注释等信息命令 SHOW DATABASES                          ...

mysql sql优化实例1(force index使用)

通过实例说明sql优化的步骤,简单介绍了force index优化sql的过程

【Oracle】--11s到4s的SQL性能优化实例

性能优化很重要,更重要的是有思路,有方法去进行优化!优化是一个非常让人兴奋的事情!...

站内优化的重要性

长尾关键词没有认真设置的话会丢掉原有的排名。威海服务网与朋友聊天时谈到以前排名稳定的长尾流量掉了很多,而且长尾关键词浮动变化很大,因为长尾可以带来很高的转化率,所以是很重要的,那为什么会变的不稳定了呢...

[观点]程序员优化代码的重要性

我正好看到了下面的一段代码: public void Execute()           {               ArrayList empIds = PayrollDatabase.G...

一张图说明不写备注的重要性

“音乐是全世界同行的语言” 写了几年代码,这才是我见过的第二份备注极少,却很容易阅读,而且扩展健壮的代码的 第一份是08年的时候一个澳洲老写的给财务用的代码,vba写的命名极长,一个简单功能代码很...
  • avi9111
  • avi9111
  • 2017年04月01日 18:46
  • 289
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:实例说明sql优化的重要性
举报原因:
原因补充:

(最多只允许输入30个字)