查看: 10881|回复: 24

一组统计SQL的优化的经典案例

[复制链接]
认证徽章
论坛徽章:
50
2014年世界杯参赛球队: 荷兰
日期:2014-07-11 07:56:59蛋疼蛋
日期:2012-03-06 07:22:542012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-01-04 11:53:29蛋疼蛋
日期:2011-11-11 15:47:00ITPUB十周年纪念徽章
日期:2011-11-01 16:23:26
发表于 2010-4-1 06:50 | 显示全部楼层 |阅读模式
一、现象
2010-3-31 网管项目组反映湖北全流程监控业务的SQL执行比较缓慢,无法满足频繁采样监控的时间要求,急需优化。
现场技术人员发来需要调整的问题SQL,具体见附件环节采数脚本.rar,其中EXCLE中需要进一步优化的语句见蓝色及浅蓝色处。
于是准备介入分析调查。(本案例主要讲述思想,所以这些大同小异的脚本就不贴上去了,文中举了一例SQL,可以参考)
二、分析
连上湖北环境后,查看所有标注需改进的SQL,居然有30多个,吓了一跳,看来工作量还不小。不过静下心来一看,发现这些SQL原来都有共同规律。
1、        几乎全部是COUNT(*)和SUM的统计查询
2、        涉及到的都是CRM和OSS的网管外系统的表,我们的SQL语句都是本地通过数据链连接到外系统中去的。
3、        绝大多数SQL是统计10分钟的数据(代码随处可见SysDate - b.exit_date) * 24 * 60 <= 10的写法说明了这点)

进一步分析这些SQL,发现写法集中在如下几张表中,并且记录数都在千万以上,其中ls65_crm2.cust_indent_tache_t记录过亿

         1、 HBNEW97OSS3.oss_xml_hist                  ---记录数21965662条
         2、hbnew97oss3.process_inst;                     ---记录数29357919条
         3、HBNEW97OSS3.Bpm_Process_Inst           ---记录数 24926324条
         4、ls65_crm2.cust_indent_t;                          ---记录数 32931916条           
         5、ls65_crm2.cust_indent_tache_t              ---记录数139427859条
         从生活常识便可得知,这些数据的海洋中,最近10分钟的数据应该是占总数据量非常少的!实验如下
select count(*) from HBNEW97OSS3.oss_xml_hist
where (sysdate - acc_date) * 24 * 60 <= 10;             --返回记录数134条
select count(*) from hbnew97oss3.process_inst
where sts_date>sysdate-10/24/60 ;                       --返回记录数203条
select count(*)  from HBNEW97OSS3.Bpm_Process_Inst
where (sysdate - start_date) * 24 * 60 < 10;             --返回记录数117条
select count(*) from ls65_crm2.cust_indent_tache_t b  
where (sysdate - b.exit_date) * 24 * 60 < 10 ;           --返回记录数1518条
          由此可以得知,根据这个exit_date的条件将返回很少的一部分记录,占全库很小的比例,属于选择性很高的列,非常适合建立索引。其他列都不能与之比拟,比如
select count(*) from hbnew97oss3.process_inst  
where sub_type = 'SBPMRA'返回了500多万行,而按10分钟的条件才返回前面的203行

      经查询发现涉及到用到日期范围的字段如ls65_crm2.cust_indent_tache_t 表的exit_date 或HBNEW97OSS3.oss_xml_hist表的acc_date 都没有建索引!这一系列共性SQL监控语句中,放着如此高选择性的列未建索引,非常可惜,如果能有效的利用到该列的索引,应该性能脚本上能得到十倍甚至百倍以上的提高!

     当然,在本案例中特别要注意的一点是,即便建立了这些日期列的索引,由于写法有问题,最近也会用不到,比如
where (sysdate - b.exit_date) * 24 * 60 < 10 ;   这个写法其实根本就是在exit_date列建了索引,索引也根本不会被用到,因为把表的列进行四则运算后,ORACLE还是无法智能到还原公式,把谓词转化出来。有兴趣的人可以看我证明的小实验,具体见附件转化写法与索引.sql


三、解决方案
   通过以上的分析我们知道,现在我们着手完成如下四步,即可调优成功,整体性能应该可以大幅度提升。
1、        所有SQL中涉及到确定时间范围为最近10分钟的SQL语句的时间列,必须要索引
2、        为了能有可能进行索引直接回答问题,建议上一步骤继续深入,把这些时间字段列标记为NOT NULL属性(我想这个时间字段列基本可以确认有这个属性吧,如果可以确认,请加上这个NOT NULL属性,对COUNT和SUM的聚合只有百利而无一害,因为增加了索引变身“瘦表”的可能)
3、        凡是涉及到类似到where (sysdate - b.exit_date) * 24 * 60 < 10的写法,请修改为b.exit_date >sysdate-10/24/60 ,如果不这么改写,索引建了也没用!
4、        连接两个远程表,比连接两个本地表要昂贵的多!所以建议先在远程环境做好连接后,在远程的机器上建立一个视图,我们本地再访问这个视图。(你可以想像要先把数据读过来,再连接),这是以前在ASKTOM上看来的,TOM大师强调过的论点。有兴趣的看查看如下
http://asktom.oracle.com/pls/asktom/f?p=100:11:0:::11_QUESTION_ID:456820211101#41195643727258  这里有TOM对数据链看法的全部。

现在综合举一个例子如下:

比如如下SQL
select a.partition_id_region 本地网编码,
       count(a.agreement_id) 十分钟内竣工环节处理量
  from ls65_crm2.cust_indent_t@A2SVC2       a,
       ls65_crm2.cust_indent_tache_t@A2SVC2 b
where a.indent_state = '100'
   and a.agreement_id = b.agreement_id
   and b.operate_tache = 6
   and b.deal_flag = 1
   and (sysdate - b.exit_date) * 24 * 60 < 10
group by a.partition_id_region
order by a.partition_id_region;

   该如何调优呢
1、        首先在CRM的机器上建索引

Create index idx_ exit_date on cust_indent_tache_t(exit_date);

2、由于大量的COUNT和SUM的聚合语句,建议为该列建NOT NULL属性,等于告诉ORACLE可以把索引当“瘦表”来用而不会出错!

alter table cust_indent_tache_t modify  exit_date not null;

3、去掉数据链写法@A2SVC2,在远程环境直接建视图
Create or replace view v_cust_ident as
select a.partition_id_region 本地网编码,
       count(a.agreement_id) 十分钟内竣工环节处理量
  from ls65_crm2.cust_indent_t         a,
       ls65_crm2.cust_indent_tache_t   b
where a.indent_state = '100'
   and a.agreement_id = b.agreement_id
   and b.operate_tache = 6
   and b.deal_flag = 1
  b.exit_date >sysdate-10/24/60
group by a.partition_id_region
order by a.partition_id_region;

4、我们网管监控系统直接用调用远程视图
如:
Select * from v_cust_ident@A2SVC2;
四、总结
1、        对选择性高的列要敏感(什么样的查询能从大数据量返回少量值,这个查询列非常适宜建索引)
2、        SQL的写法要注意列避免被运算,这个可作为SQL开发规范来规范。
3、        远程多表连接开销很大,尽量用视图封装,TOM大师强调过,也可考虑将来做为开发规范。

[ 本帖最后由 wabjtam123 于 2010-4-1 06:55 编辑 ]
认证徽章
论坛徽章:
50
2014年世界杯参赛球队: 荷兰
日期:2014-07-11 07:56:59蛋疼蛋
日期:2012-03-06 07:22:542012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-01-04 11:53:29蛋疼蛋
日期:2011-11-11 15:47:00ITPUB十周年纪念徽章
日期:2011-11-01 16:23:26
发表于 2010-4-1 06:51 | 显示全部楼层
本案例中,主要是高选择性的索引列未建,其次是注意SQL写法,否则即便建了索引也用不到,这个可以用一个简单的例子发出来,我构造的。
(此外关于数据链也希望能引起大家的注意)

drop table test;
create table test (id int , day date default  sysdate);

insert into test select rownum,sysdate-rownum from dual connect by rownum<=100000;
select * from test;
drop index idx_day;

--最近1万分钟以内的记录
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> connect ljb/ljb
已连接。
SQL> set autotrace on
SQL> select count(*) from test where  (sysdate - day) * 24 * 60 < 10000;

  COUNT(*)
----------
         6


执行计划
----------------------------------------------------------
Plan hash value: 1950795681

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     9 |    87  (21)| 00:00:02 |
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST |     3 |    27 |    87  (21)| 00:00:02 |
---------------------------------------------------------------------------

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

   2 - filter((SYSDATE@!-"DAY")*24*60<10000)

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          4  recursive calls
          0  db block gets
        365  consistent gets
          0  physical reads
          0  redo size
        408  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(*) from test where day>sysdate-10000/24/60;

  COUNT(*)
----------
         6


执行计划
----------------------------------------------------------
Plan hash value: 788368599

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |     9 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |         |     1 |     9 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_DAY |     6 |    54 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   2 - access("DAY">SYSDATE@!-6.94444444444444444444444444444444444444)

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         86  consistent gets
          0  physical reads
          0  redo size
        408  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

使用道具 举报

回复
认证徽章
论坛徽章:
50
2014年世界杯参赛球队: 荷兰
日期:2014-07-11 07:56:59蛋疼蛋
日期:2012-03-06 07:22:542012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-01-04 11:53:29蛋疼蛋
日期:2011-11-11 15:47:00ITPUB十周年纪念徽章
日期:2011-11-01 16:23:26
发表于 2010-4-1 06:57 | 显示全部楼层
最后再说明一点,在做本案例优化的时候,由于所给的用户权限极小,甚至连看执行计划的权限都没有。本想第二天向局方要一个权限大的用户来分析,不过事实上很多情况下,不用看执行计划根据经验也能猜出问题在哪,也能做一些优化!

使用道具 举报

回复
论坛徽章:
7
2009日食纪念
日期:2009-07-22 09:30:002010新春纪念徽章
日期:2010-01-04 08:33:082010年世界杯参赛球队:法国
日期:2010-02-21 15:05:292010新春纪念徽章
日期:2010-03-01 11:08:27ITPUB9周年纪念徽章
日期:2010-10-08 09:31:222011新春纪念徽章
日期:2011-02-18 11:42:49ITPUB十周年纪念徽章
日期:2011-11-01 16:24:04
发表于 2010-4-1 09:05 | 显示全部楼层
学习

使用道具 举报

回复
论坛徽章:
7
生肖徽章2007版:鸡
日期:2008-01-02 17:35:53生肖徽章2007版:鼠
日期:2008-01-02 17:35:53奥运会纪念徽章:篮球
日期:2008-05-28 09:41:002011新春纪念徽章
日期:2011-02-18 11:43:34ITPUB十周年纪念徽章
日期:2011-11-01 16:20:28奥运会纪念徽章:棒球
日期:2012-08-17 12:55:562013年新春福章
日期:2013-02-25 14:51:24
发表于 2010-4-1 09:10 | 显示全部楼层
3、        凡是涉及到类似到where (sysdate - b.exit_date) * 24 * 60 < 10的写法,请修改为b.exit_date >sysdate-10/24/60 ,如果不这么改写,索引建了也没用!


这条优化估计很多时候很难改变,一个系统上线后,去修改sql语句,不是那么简单的事情,需要很多环节。这都是开发时候的审核不过关导致的。

使用道具 举报

回复
论坛徽章:
16
数据库板块每日发贴之星
日期:2007-05-10 01:02:022012新春纪念徽章
日期:2012-01-04 11:53:29ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262011新春纪念徽章
日期:2011-02-18 11:43:352010广州亚运会纪念徽章:乒乓球
日期:2010-10-14 10:59:16ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25数据库板块每日发贴之星
日期:2010-05-09 01:01:01数据库板块每日发贴之星
日期:2010-03-14 01:01:09数据库板块每日发贴之星
日期:2007-11-13 01:04:45ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44
发表于 2010-4-1 19:29 | 显示全部楼层
奇怪,用视图封装一下就会快吗,这是为什么啊?
查远程的表,用的是本地的SGA及PGA吧

使用道具 举报

回复
论坛徽章:
86
2015中国数据库技术大会纪念徽章
日期:2015-04-24 16:04:24马上有车
日期:2014-02-19 11:55:14马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11优秀写手
日期:2013-12-18 09:29:11日产
日期:2013-10-17 08:44:39马自达
日期:2013-08-26 16:28:022013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-23 16:55:51马上有房
日期:2014-02-19 11:55:14
发表于 2010-4-1 20:25 | 显示全部楼层
好文章,总结的很好。

使用道具 举报

回复
求职 : 数据库管理员
认证徽章
论坛徽章:
32
生肖徽章2007版:狗
日期:2009-07-20 17:10:18茶鸡蛋
日期:2013-05-23 16:34:43茶鸡蛋
日期:2013-07-19 14:39:22ITPUB社区千里马徽章
日期:2013-08-22 09:58:03雪铁龙
日期:2013-08-23 13:04:11林肯
日期:2013-09-04 14:07:20兰博基尼
日期:2013-09-16 13:45:20马上有钱
日期:2014-05-14 14:07:392014年世界杯参赛球队: 波黑
日期:2014-07-03 13:24:39马上有对象
日期:2014-10-30 14:04:32
发表于 2010-4-1 21:04 | 显示全部楼层
好文章,学习了!

使用道具 举报

回复
论坛徽章:
0
发表于 2010-4-1 22:39 | 显示全部楼层
b.exit_date >sysdate-10/24/60
至少对于这个语句而言,exit_date的not null约束是不需要的.因为这里的条件注定了满足条件的exit_date都是非空的,都可以在索引中找到.
但exit_date的not null约束对于类似于select count(1) from ls65_crm2.cust_indent_tache_t;使用这个索引的快速全扫描却是必要的条件.

对于部分表是本地表,部分表是远程表的情形,可以考虑使用driving_site提示。但对于表都是远程表的情形,我感觉一定是在远程处发生连接,只是将最后的结果取到本地来的,而不会将远程的表数据都取到本地后再连接的.但感觉这种情况下,可能优化器统计信息都没有而只能使用默认值了,也就是说连接是发生在远程的,可执行计划却是在本地生成的,很可能不是最优的.而你说的这种使用远程的视图的情况,可能连接是发生在远程的,执行计划也是在远程目的地生成的,应该会更准确些.

使用道具 举报

回复
论坛徽章:
2
2010新春纪念徽章
日期:2010-03-01 11:04:572010年世界杯参赛球队:科特迪瓦
日期:2010-03-07 14:39:46
发表于 2010-4-1 22:52 | 显示全部楼层
lz建了索引查询是快了,但是建了索引以后如果有其他sql用到这几个表以后可能导致执行计划变了,后果也很严重。
上面列出来的几个是大表,以后有数据新增或者修改,伴随着索引的维护也需要成本。

最好是根据应用情况从根本上来优化

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


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

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表