查看: 3510|回复: 8

[性能调整] 优化过的SQL因返回值量不同速度差异千倍,收集Histogram是否有帮助

[复制链接]
认证徽章
论坛徽章:
71
2015年新春福章
日期:2015-03-06 11:57:312013年新春福章
日期:2013-02-25 14:51:24双黄蛋
日期:2013-01-06 13:31:18蜘蛛蛋
日期:2013-01-06 10:26:08茶鸡蛋
日期:2012-11-21 19:35:23ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07版主2段
日期:2012-05-15 15:24:11铁扇公主
日期:2012-02-21 15:02:402012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:51
发表于 2012-1-13 16:52 | 显示全部楼层 |阅读模式
SELECT   count(*)   类型的SQL (具体见后面)  ,   ORACLE 11.2.0.2 版本  

代入以下数值运行耗时:  

:B1='AMEUPDUN'  返回12笔数据, 32毫秒
:B1='HWITRCUS'  返回19万数据, 18秒
:B1='JTFTASK'   返回1万数据, 1.5秒
:B1='OKSKPRCS'  无返回数据, 30毫秒
:B1='UMXLHELP'  返回40笔数据,45毫秒
:B1='WFERROR’  返回2万数据,2.5秒

以下SQL cost或index使用,
------------------------------------------
SELECT   count(*)  
  FROM WF_ITEMS WI   
WHERE WI.ITEM_TYPE =:B1       AND WI.END_DATE <= SYSDATE  
       AND EXISTS
              (SELECT NULL   
                 FROM WF_ITEM_TYPES WIT
                WHERE WI.END_DATE + NVL (WIT.PERSISTENCE_DAYS, 0) <= SYSDATE
                      AND WI.ITEM_TYPE = WIT.NAME)
       AND NOT EXISTS
                  (    SELECT NULL
                         FROM WF_ITEMS WI2
                        WHERE WI2.END_DATE IS NULL
                   START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
                              AND WI2.ITEM_KEY = WI.ITEM_KEY
                   CONNECT BY PRIOR WI2.ITEM_TYPE = WI2.PARENT_ITEM_TYPE
                              AND PRIOR WI2.ITEM_KEY = WI2.PARENT_ITEM_KEY
                   UNION ALL
                       SELECT NULL
                         FROM WF_ITEMS WI2
                        WHERE WI2.END_DATE IS NULL
                   START WITH WI2.ITEM_TYPE = WI.ITEM_TYPE
                              AND WI2.ITEM_KEY = WI.ITEM_KEY
                   CONNECT BY PRIOR WI2.PARENT_ITEM_TYPE = WI2.ITEM_TYPE
                              AND PRIOR WI2.PARENT_ITEM_KEY = WI2.ITEM_KEY)


ITEM_TYPE  上未收集Histogram .   

因为文档上说,Oracle 在界定skew的时候还不是用重复值来界定的。oracle认为的倾斜列是指在列中最大值和最小值之间分布不均匀,即使它是唯一的(PK, 比如1--200,1000-3000,10000--12000, 也会收集柱状图, 如果是连续的就不会收集了)。

见如下数据分布,  最大值和最小值之间分布应该是非常不均匀吧 。
  SELECT   ITEM_TYPE  , Count(1)   FROM WF_ITEMS  Group  By  ITEM_TYPE  ;   

AMEUPDUN     12
CS_KB_W1      8
HWITRCUS     198779
IBUSRDTL       3748
JTAUMEMN     6
JTAUMPSW     9177
JTFEC            42
JTFTASK        26299
JTF_APPR      23
OKCAUKAP    2
OKSKPRCS      8479
SERVEREQ      138319
UMXLHELP      1398
WFERROR      253766
WFTESTS       3

论坛徽章:
19
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:292015年新春福章
日期:2015-03-04 14:53:16优秀写手
日期:2014-03-19 06:00:24马上有对象
日期:2014-02-18 16:44:082014年新春福章
日期:2014-02-18 16:44:08比亚迪
日期:2013-10-23 21:35:02ITPUB社区12周年站庆徽章
日期:2013-10-08 14:54:39茶鸡蛋
日期:2013-07-25 19:48:40灰彻蛋
日期:2013-05-24 09:42:412013年新春福章
日期:2013-02-25 14:51:24
发表于 2012-1-13 17:35 | 显示全部楼层

收集histogram试试。

另:执行的时间和返回的数据行数有明显的关系,是否可以这么说,不是sql问题,也不是统计信息问题,而是这个sql处理的行数多,耗时多是正常的。

使用道具 举报

回复
认证徽章
论坛徽章:
71
2015年新春福章
日期:2015-03-06 11:57:312013年新春福章
日期:2013-02-25 14:51:24双黄蛋
日期:2013-01-06 13:31:18蜘蛛蛋
日期:2013-01-06 10:26:08茶鸡蛋
日期:2012-11-21 19:35:23ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07版主2段
日期:2012-05-15 15:24:11铁扇公主
日期:2012-02-21 15:02:402012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:51
发表于 2012-1-13 18:16 | 显示全部楼层
ahdong2007 发表于 2012-1-13 17:35
收集histogram试试。

另:执行的时间和返回的数据行数有明显的关系,是否可以这么说,不是sql问题,也 ...

我的优化建议中也是这么写的, 返回值多, 几乎是没有更好的优化方式了 。

使用强制full table scan hint 也是一样的 。

使用道具 举报

回复
论坛徽章:
3
ITPUB十周年纪念徽章
日期:2011-11-01 16:24:042012新春纪念徽章
日期:2012-01-04 11:53:542013年新春福章
日期:2013-02-25 14:51:24
发表于 2012-1-13 21:42 | 显示全部楼层
oracle认为是否倾斜是根据oracle的col_usage$表结合sql 决定是否收集统计信息

使用道具 举报

回复
论坛徽章:
15
2011新春纪念徽章
日期:2011-02-18 11:42:47马上有房
日期:2014-02-18 16:42:022014年新春福章
日期:2014-02-18 16:42:02夏利
日期:2014-01-05 23:30:122013年新春福章
日期:2013-02-25 14:51:24咸鸭蛋
日期:2013-01-06 18:38:44复活蛋
日期:2012-12-27 22:39:382012新春纪念徽章
日期:2012-01-04 11:51:22ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15蛋疼蛋
日期:2011-08-03 23:04:21
发表于 2012-1-13 21:49 | 显示全部楼层
这个走索引的对应一下逻辑IO就很明显了 ):   其实跟数据表堆的分布也有很大关系

使用道具 举报

回复
论坛徽章:
17
2009日食纪念
日期:2009-07-22 09:30:00马上有车
日期:2014-12-25 09:26:21奥运会纪念徽章:马术
日期:2012-09-06 17:58:19奥运会纪念徽章:举重
日期:2012-08-17 11:49:28奥运会纪念徽章:花样游泳
日期:2012-08-11 17:43:33奥运会纪念徽章:帆船
日期:2012-07-19 19:26:52奥运会纪念徽章:现代五项
日期:2012-06-25 11:12:17奥运会纪念徽章:游泳
日期:2012-06-13 11:58:41灰彻蛋
日期:2012-06-12 15:30:12茶鸡蛋
日期:2012-06-11 16:16:43
发表于 2012-1-13 22:00 | 显示全部楼层
返回的数据太多是没办法的。

如果type很少,可以实验一下不使用绑定变量,也许会有效果

使用道具 举报

回复
论坛徽章:
4
2012新春纪念徽章
日期:2012-01-04 11:58:18ITPUB 11周年纪念徽章
日期:2012-10-09 18:16:002013年新春福章
日期:2013-02-25 14:51:24ITPUB社区OCM联盟徽章
日期:2015-08-31 17:12:33
发表于 2012-1-17 10:07 | 显示全部楼层
楼主你这个是绑定变量了,oracle会使用相同的执行计划来执行这个sql,好处是减少硬解析,但是有个坏处,就是你绑定的列上各个值得分布概率落差很大的时候,对某些值来说是最优计划的时候,对另外一些值就是最烂的执行计划了

使用道具 举报

回复
认证徽章
论坛徽章:
71
2015年新春福章
日期:2015-03-06 11:57:312013年新春福章
日期:2013-02-25 14:51:24双黄蛋
日期:2013-01-06 13:31:18蜘蛛蛋
日期:2013-01-06 10:26:08茶鸡蛋
日期:2012-11-21 19:35:23ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07版主2段
日期:2012-05-15 15:24:11铁扇公主
日期:2012-02-21 15:02:402012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:51
发表于 2012-1-17 11:22 | 显示全部楼层
novamc 发表于 2012-1-17 10:07
楼主你这个是绑定变量了,oracle会使用相同的执行计划来执行这个sql,好处是减少硬解析,但是有个坏处,就是你绑 ...

ORACLE 11.2.0.2 版本  

11R2 版本好像有自适应游标了 ,  回头看看是否发挥作用。  

使用道具 举报

回复
论坛徽章:
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
发表于 2012-1-17 11:39 | 显示全部楼层
列值不多,建议收集一下试试。

使用道具 举报

回复

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

本版积分规则 发表回复

PostgreSQL中国大会,参会票抢购!

由 PostgreSQL中文社区与ITPUB联合主办的第九届《PostgreSQL 中国技术大会》将在北京隆重召开。PostgreSQL 作为功能最强的的开源关系型数据库之一,得到了越来越多企业的推广和运用,也越来越受到广大技术爱好者的欢迎和重视。这将是 PostgreSQL 的又一次交流盛会。
----------------------------------------
时间:2019年11月29~11月30日

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