查看: 5633|回复: 8

[性能调整] 一次谓词推入的优化案例

[复制链接]
论坛徽章:
5
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51数据库板块每日发贴之星
日期:2011-07-22 01:01:02蜘蛛蛋
日期:2011-08-24 14:10:13ITPUB十周年纪念徽章
日期:2011-11-01 16:24:042012新春纪念徽章
日期:2012-01-04 11:54:26
发表于 2011-7-21 22:07 | 显示全部楼层 |阅读模式
网友QQ发来如下信息,问我下面这个SQL是否能有性能提升的地方,他们监控到这个SQL磁盘读很高
SQL> set autotrace on
SQL> select * from( select GRDL_ID qyid,KHMC nsrmc,KHBM nsrsbh,KHBM six_nsrsbh,'' six_dssh,gjc,fzgs_dm,'2' khlx from khgl_grdlxx
  2      union all
  3        select dwkh_id qyid, khmc nsrmc,nvl(nsrsbh,dssh) nsrsbh,nvl(six_nsrsbh,six_dssh) six_nsrsbh,six_dssh,gjc,fzgs_dm,'0' khlx from KHGL_DWKH_COREINFO
  4      union all
  5        select DLS_BM qyid,DLS_MC nsrmc,DLS_BM nsrsbh,DLS_BM six_nsrsbh,'' six_dssh,gjc,fzgs_dm,'1' khlx from KHGL_DLSJBXX
  6     )
  7      where (six_nsrsbh = '706773' or six_dssh = '706773')  and rownum<11;

QYID                             NSRMC
-------------------------------- -------------------------------------------------------------------------------------
a4af925f2a224bc4a8ac42dc87bb5192 农一师塔里木广告信息公司
b59f82aa67ae4b88b331a4042e1ced43 保定市丽景园林绿化有限公司
6db2f2cc8d8446a2b80f190fffd1ff72 保定市绿景园林绿化有限公司


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

-------------------------------------------------------------------------------------------
| Id  | Operation            | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                    |    10 |  1320 |    18   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY       |                    |       |       |            |          |
|   2 |   VIEW               |                    |    32 |  4224 |    18   (0)| 00:00:01 |
|   3 |    UNION-ALL         |                    |       |       |            |          |
|*  4 |     TABLE ACCESS FULL| KHGL_GRDLXX        |    11 |   803 |     5   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| KHGL_DWKH_COREINFO |    11 |  1144 |     7   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL| KHGL_DLSJBXX       |    10 |   380 |     6   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<11)
   4 - filter(''='706773' OR "KHBM"='706773')
   5 - filter(NVL("SIX_NSRSBH","SIX_DSSH")='706773' OR "SIX_DSSH"='706773')
   6 - filter(''='706773' OR "DLS_BM"='706773')


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      27226  consistent gets
      26937  physical reads
          0  redo size
       1288  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed
根据SQL的执行计划和统计信息可以看出,这个SQL效率确非常低下,3个表都采用了全表扫描,这个必然导致物理读高。
仔细分析执行计划以及SQL语句,我真的无语了,这个SQL语句写得之烂让我情何以堪,于是我做如下改写
select *
  from (select GRDL_ID qyid,
               KHMC nsrmc,
               KHBM nsrsbh,
               KHBM six_nsrsbh,
               '' six_dssh,
               gjc,
               fzgs_dm,
               '2' khlx
          from khgl_grdlxx
         where KHBM = '706773'
        union all
        select dwkh_id qyid,
               khmc nsrmc,
               nvl(nsrsbh, dssh) nsrsbh,
               nvl(six_nsrsbh, six_dssh) six_nsrsbh,
               six_dssh,
               gjc,
               fzgs_dm,
               '0' khlx
          from KHGL_DWKH_COREINFO where (six_nsrsbh = '706773' or
                                  six_dssh = '706773')
        union all
        select DLS_BM qyid,
               DLS_MC nsrmc,
               DLS_BM nsrsbh,
               DLS_BM six_nsrsbh,
               '' six_dssh,
               gjc,
               fzgs_dm,
               '1' khlx
          from KHGL_DLSJBXX
         where DLS_BM = '706773')
where  rownum < 11;
改写之后,SQL执行计划如下:
执行计划
----------------------------------------------------------
Plan hash value: 1863808260

----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Rows  | Bytes | Cost (%CPU)| Time
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                        |     8 |  1056 |    12   (0)| 00:00:
|*  1 |  COUNT STOPKEY                      |                        |       |       |            |
|   2 |   VIEW                              |                        |     8 |  1056 |    12   (0)| 00:00:
|   3 |    UNION-ALL                        |                        |       |       |            |
|   4 |     TABLE ACCESS BY INDEX ROWID     | KHGL_GRDLXX            |     1 |    73 |     2   (0)| 00:00:
|*  5 |      INDEX UNIQUE SCAN              | IDX_GRDLXX_KHBM        |     1 |       |     1   (0)| 00:00:
|   6 |     TABLE ACCESS BY INDEX ROWID     | KHGL_DWKH_COREINFO     |     6 |   624 |     8   (0)| 00:00:
|   7 |      BITMAP CONVERSION TO ROWIDS    |                        |       |       |            |
|   8 |       BITMAP OR                     |                        |       |       |            |
|   9 |        BITMAP CONVERSION FROM ROWIDS|                        |       |       |            |
|* 10 |         INDEX RANGE SCAN            | IDX_DWKHCORE_SIX_DSSH  |       |       |     3   (0)| 00:00:
|  11 |        BITMAP CONVERSION FROM ROWIDS|                        |       |       |            |
|* 12 |         INDEX RANGE SCAN            | IDX_DWKHCORE_SIXNSRSBH |       |       |     3   (0)| 00:00:
|  13 |     TABLE ACCESS BY INDEX ROWID     | KHGL_DLSJBXX           |     1 |    38 |     2   (0)| 00:00:
|* 14 |      INDEX UNIQUE SCAN              | PK_KHGL_DLSJBXX        |     1 |       |     1   (0)| 00:00:
----------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<11)
   5 - access("KHBM"='706773')
  10 - access("SIX_DSSH"='706773')
  12 - access("SIX_NSRSBH"='706773')
  14 - access("DLS_BM"='706773')


统计信息
----------------------------------------------------------
         15  recursive calls
          0  db block gets
         17  consistent gets
          0  physical reads
          0  redo size
       1288  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed
为什么要这么改写呢? 因为不改写的执行计划里面有''='706773' 这个会限制使用索引
其实这里ORACLE用到的CBO转换技术有1个,叫做Pushing Predicate(谓词推入),但是在第一个SQL语句中,由于有这样的SELECT 条件'' six_dssh
导致谓词推入的时候过滤也成了''='706773' ,从这里也知道CBO还是不够智能,如果能智能一点,帮我们把''='706773' 这样的过滤省略了岂不是更好
论坛徽章:
18
紫蛋头
日期:2011-07-25 08:06:53咸鸭蛋
日期:2011-12-27 11:35:38鲜花蛋
日期:2012-01-11 11:08:36奥运会纪念徽章:射击
日期:2012-09-11 08:56:18奥运会纪念徽章:体操
日期:2012-10-25 09:07:51紫蛋头
日期:2012-12-10 13:46:51灰彻蛋
日期:2013-01-28 14:23:202013年新春福章
日期:2013-02-25 14:51:24
发表于 2011-7-22 08:21 | 显示全部楼层
優化前的語句中,似乎這樣的寫法,謂詞條件是推入不進去的哦

使用道具 举报

回复
论坛徽章:
5
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51数据库板块每日发贴之星
日期:2011-07-22 01:01:02蜘蛛蛋
日期:2011-08-24 14:10:13ITPUB十周年纪念徽章
日期:2011-11-01 16:24:042012新春纪念徽章
日期:2012-01-04 11:54:26
 楼主| 发表于 2011-7-22 10:29 | 显示全部楼层

回复 #2 jdw618 的帖子

很明显推入进去了的。你再好好看看执行计划

使用道具 举报

回复
招聘 : Java研发
认证徽章
论坛徽章:
71
马上加薪
日期:2014-02-19 11:55:14蜘蛛蛋
日期:2012-12-26 18:16:01茶鸡蛋
日期:2012-11-16 08:12:48ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07奥运会纪念徽章:网球
日期:2012-08-23 14:58:08奥运会纪念徽章:沙滩排球
日期:2012-07-19 17:28:14版主2段
日期:2012-07-07 02:21:02咸鸭蛋
日期:2012-03-23 18:17:482012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:51
发表于 2011-7-22 10:35 | 显示全部楼层
写SQL的人逻辑比较强悍。。。。

使用道具 举报

回复
论坛徽章:
23
生肖徽章:狗
日期:2006-09-07 10:14:43喜羊羊
日期:2015-03-13 17:41:592014年新春福章
日期:2014-04-10 09:43:05蛋疼蛋
日期:2012-05-23 22:26:07鲜花蛋
日期:2012-01-16 17:28:092012新春纪念徽章
日期:2012-01-04 11:51:22双黄蛋
日期:2011-12-21 09:05:53ITPUB十周年纪念徽章
日期:2011-11-01 16:21:152009新春纪念徽章
日期:2009-01-04 14:52:28生肖徽章2007版:猴
日期:2008-11-21 15:08:48
发表于 2011-7-22 10:41 | 显示全部楼层
我怎么感觉改写后的SQL结果集跟原始不太一样

使用道具 举报

回复
论坛徽章:
18
紫蛋头
日期:2011-07-25 08:06:53咸鸭蛋
日期:2011-12-27 11:35:38鲜花蛋
日期:2012-01-11 11:08:36奥运会纪念徽章:射击
日期:2012-09-11 08:56:18奥运会纪念徽章:体操
日期:2012-10-25 09:07:51紫蛋头
日期:2012-12-10 13:46:51灰彻蛋
日期:2013-01-28 14:23:202013年新春福章
日期:2013-02-25 14:51:24
发表于 2011-7-22 11:31 | 显示全部楼层
這個rownum要是真推進去了,真會影響查詢結果
不知道這sql的用意是要查那些rownum

使用道具 举报

回复
论坛徽章:
7
优秀写手
日期:2013-12-18 09:29:08问答徽章
日期:2014-02-07 23:48:392014年新春福章
日期:2014-02-18 16:50:09马上有车
日期:2014-02-18 16:50:09马上有钱
日期:2014-10-26 21:51:11暖羊羊
日期:2015-03-04 14:54:572015年新春福章
日期:2015-03-06 11:59:47
发表于 2014-10-9 20:40 | 显示全部楼层
取rownum不排序,得到的结果集有可能会变化吧

使用道具 举报

回复
论坛徽章:
9
ITPUB 11周年纪念徽章
日期:2012-10-31 14:48:002013年新春福章
日期:2013-02-25 14:51:24奥运纪念徽章
日期:2013-05-20 09:57:09大众
日期:2013-09-26 08:56:23三菱
日期:2013-11-09 10:48:19凯迪拉克
日期:2013-11-28 09:17:19红旗
日期:2013-12-16 12:38:48雪佛兰
日期:2013-12-17 09:11:49马上有车
日期:2014-03-30 11:41:45
发表于 2014-10-11 08:39 | 显示全部楼层
这个是推进了的,11g版本已经智能了不用改写的

使用道具 举报

回复
论坛徽章:
10
2014年世界杯参赛球队: 意大利
日期:2014-05-25 15:17:282015年新春福章
日期:2015-03-06 11:59:472015年新春福章
日期:2015-03-04 14:55:13马上加薪
日期:2014-12-09 16:09:35马上有房
日期:2014-11-10 17:37:26马上有房
日期:2014-08-20 21:59:35马上有对象
日期:2014-07-15 14:15:052014年世界杯参赛球队: 阿尔及利亚
日期:2014-07-10 10:37:592014年世界杯参赛球队: 智利
日期:2014-06-05 20:11:04慢羊羊
日期:2015-04-28 13:30:12
发表于 2014-10-11 09:12 | 显示全部楼层
sql稍微复杂一些有时候11g还是会犯蠢,之前在做性能测试时遇到过类似几个案例。

使用道具 举报

回复

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

本版积分规则 发表回复

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