ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 22527|回复: 27

[SQL] 请教关于SQL执行计划的瓶颈

[复制链接]
认证徽章
论坛徽章:
7
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:522011新春纪念徽章
日期:2011-02-18 11:43:332013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2013-12-18 09:29:092014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02秀才
日期:2017-03-20 13:42:20
发表于 2017-2-18 10:30 | 显示全部楼层 |阅读模式
本帖最后由 samt007 于 2017-2-18 12:31 编辑
  1. -------------------------------------------------------
  2. ----------------第(1)个SQL的执行计划报告---------------
  3. -------------------------------------------------------
  4. XYG SQL Monitoring Report

  5. SQL Text
  6. ------------------------------
  7. SELECT /*+ SAMT006 monitor gather_plan_statistics */ORGANIZATION_ID
  8.         ,SEGMENT1
  9.         ,DESCRIPTION
  10.         ,PRIMARY_UOM_CODE
  11.         ,CREATION_DATE
  12.         ,ENABLED_FLAG
  13.         ,INVENTORY_ITEM_STATUS_CODE
  14.         ,INVENTORY_ITEM_ID
  15.     FROM XYG_ALI_SYSTEM_ITEMS_VL
  16.    WHERE (ORGANIZATION_ID = :R1 AND INVENTORY_ITEM_STATUS_CODE || '' = 'Active')
  17.      AND (SEGMENT1 LIKE :R2)
  18.      AND (DESCRIPTION LIKE :R3)
  19.      AND (ENABLED_FLAG = :R4)
  20. ORDER BY DESCRIPTION

  21. Global Information
  22. ------------------------------
  23. Status              :  DONE (ALL ROWS)
  24. KEY                 :  794568953261
  25. Instance ID         :  1
  26. Session             :  8843
  27. SQL ID              :  6fpyqv0fbyydz
  28. SQL Child Addr      :  070000066E970BE0-(0)
  29. SQL Execution ID    :  16777216
  30. Execution Started   :  2017/02/18 10:15:57
  31. First Refresh Time  :  2017/02/18 10:15:57
  32. Last Refresh Time   :  2017/02/18 10:16:08
  33. Duration            :  11s
  34. Module/Action       :  
  35. Service             :  PROD
  36. Program             :  Toad.exe

  37. Binds*
  38. ===============================================
  39. | Name | Position | Type         | Value      |
  40. ===============================================
  41. | :R1  |        1 | NUMBER       | 106        |
  42. | :R2  |        2 | VARCHAR2(32) | 103%       |
  43. | :R3  |        3 | VARCHAR2(32) | %遥控开关% |
  44. | :R4  |        4 | VARCHAR2(32) | Y          |
  45. ===============================================
复制代码
执行计划如下:
汇总1.jpg
执行计划.jpg


非常奇怪的是,扫描1条记录需要11秒?还是我误解了什么?

再来一个用标准的SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('6fpyqv0fbyydz',0,'ALL IOSTATS LAST'));看到的结果:
执行计划疑问.jpg





论坛徽章:
25
双子座
日期:2015-07-16 14:18:24妮可·罗宾
日期:2016-06-26 22:18:42乌索普
日期:2016-11-17 17:21:49蒙奇·D·路飞
日期:2016-12-31 10:56:17白羊座
日期:2016-05-12 14:53:36双子座
日期:2016-03-21 18:25:51水瓶座
日期:2016-03-09 15:54:47水瓶座
日期:2016-01-26 10:44:56双鱼座
日期:2015-08-20 10:30:26蒙奇·D·路飞
日期:2017-03-20 10:23:01
发表于 2017-2-18 10:38 | 显示全部楼层
猜测优化器预估这个表返回的行数不是1,建议分析表,在看看计划是否合理。

使用道具 举报

回复
认证徽章
论坛徽章:
7
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:522011新春纪念徽章
日期:2011-02-18 11:43:332013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2013-12-18 09:29:092014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02秀才
日期:2017-03-20 13:42:20
发表于 2017-2-18 12:27 | 显示全部楼层
alibull 发表于 2017-2-18 10:38
猜测优化器预估这个表返回的行数不是1,建议分析表,在看看计划是否合理。

这个1行是实际返回的行数啊。执行计划也是正常的。

使用道具 举报

回复
认证徽章
论坛徽章:
40
2014年世界杯参赛球队: 瑞士
日期:2014-07-11 13:13:56慢羊羊
日期:2015-05-05 22:00:25喜羊羊
日期:2015-05-18 16:24:25ITPUB季度 技术新星
日期:2016-07-07 09:28:30慢羊羊
日期:2015-06-12 13:08:22暖羊羊
日期:2015-07-02 16:06:20暖羊羊
日期:2015-07-06 16:28:55摩羯座
日期:2015-10-29 21:07:02狮子座
日期:2015-07-29 17:14:43摩羯座
日期:2015-09-02 13:58:47
发表于 2017-2-18 12:37 | 显示全部楼层
samt007 发表于 2017-2-18 12:27
这个1行是实际返回的行数啊。执行计划也是正常的。

用10046跟踪一下。级别选择12.

使用道具 举报

回复
认证徽章
论坛徽章:
7
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:522011新春纪念徽章
日期:2011-02-18 11:43:332013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2013-12-18 09:29:092014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02秀才
日期:2017-03-20 13:42:20
发表于 2017-2-18 13:18 | 显示全部楼层
bfc99 发表于 2017-2-18 12:37
用10046跟踪一下。级别选择12.

好的,差点忘记这个办法了。先试试!谢谢!

使用道具 举报

回复
认证徽章
论坛徽章:
7
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:522011新春纪念徽章
日期:2011-02-18 11:43:332013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2013-12-18 09:29:092014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02秀才
日期:2017-03-20 13:42:20
发表于 2017-2-18 14:07 | 显示全部楼层
bfc99 发表于 2017-2-18 12:37
用10046跟踪一下。级别选择12.

分析之后,tkprof格式化之后的结果:
基本的等待事件是db file sequential read。不知道这个情况是否正常?
SELECT /*+ SAMT009 monitor gather_plan_statistics */ORGANIZATION_ID
        ,SEGMENT1
        ,DESCRIPTION
        ,PRIMARY_UOM_CODE
        ,CREATION_DATE
        ,ENABLED_FLAG
        ,INVENTORY_ITEM_STATUS_CODE
        ,INVENTORY_ITEM_ID
    FROM XYG_ALI_SYSTEM_ITEMS_VL
   WHERE (ORGANIZATION_ID = :R1 AND INVENTORY_ITEM_STATUS_CODE || '' = 'Active')
     AND (SEGMENT1 LIKE :R2)
     AND (DESCRIPTION LIKE :R3)
     AND (ENABLED_FLAG = :R4)
ORDER BY DESCRIPTION

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.51       7.62       6710       6716          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.51       7.62       6710       6716          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  NESTED LOOPS  (cr=6716 pr=6710 pw=0 time=7623688 us)
         1          1          1   NESTED LOOPS  (cr=6714 pr=6710 pw=0 time=7620520 us cost=7 size=94 card=1)
         1          1          1    TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_TL (cr=6711 pr=6709 pw=0 time=7619869 us cost=5 size=48 card=1)
         1          1          1     INDEX RANGE SCAN MTL_SYSTEM_ITEMS_TL_N1 (cr=6710 pr=6708 pw=0 time=62684 us cost=4 size=0 card=1)(object id 121044)
         1          1          1    INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1 (cr=3 pr=1 pw=0 time=642 us cost=1 size=0 card=1)(object id 353365)
         0          0          0   TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B (cr=2 pr=0 pw=0 time=3164 us cost=2 size=46 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.10          0.10
  Disk file operations I/O                      188        0.00          0.00
  db file sequential read                      6710        0.09          6.98
  gc current block 2-way                          1        0.00          0.00
********************************************************************************

使用道具 举报

回复
认证徽章
论坛徽章:
40
2014年世界杯参赛球队: 瑞士
日期:2014-07-11 13:13:56慢羊羊
日期:2015-05-05 22:00:25喜羊羊
日期:2015-05-18 16:24:25ITPUB季度 技术新星
日期:2016-07-07 09:28:30慢羊羊
日期:2015-06-12 13:08:22暖羊羊
日期:2015-07-02 16:06:20暖羊羊
日期:2015-07-06 16:28:55摩羯座
日期:2015-10-29 21:07:02狮子座
日期:2015-07-29 17:14:43摩羯座
日期:2015-09-02 13:58:47
发表于 2017-2-18 21:38 | 显示全部楼层
本帖最后由 bfc99 于 2017-2-18 21:43 编辑
samt007 发表于 2017-2-18 14:07
分析之后,tkprof格式化之后的结果:
基本的等待事件是db file sequential read。不知道这个情况是否正 ...

从目前的信息来看,在回表的那一步,耗费了近7秒,占用了整个SQL执行时间的绝大部分。
假设当前的SQL返回的那行结果的ROWID是xxxxx,则跟踪以下SQL的执行情况(继续用10046跟踪)。:
SELECT *
    FROM MTL_SYSTEM_ITEMS_TL  --注意,不是XYG_ALI_SYSTEM_ITEMS_VL,XYG_ALI_SYSTEM_ITEMS_VL似乎是个视图
   WHERE ROWID=‘XXXXX’;

如果这样查询也很慢的话(和原来回表那步的执行时间接近),怀疑表所在的存储可能存在瓶颈。

使用道具 举报

回复
认证徽章
论坛徽章:
7
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:522011新春纪念徽章
日期:2011-02-18 11:43:332013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2013-12-18 09:29:092014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02秀才
日期:2017-03-20 13:42:20
发表于 2017-2-18 22:39 | 显示全部楼层
bfc99 发表于 2017-2-18 21:38
从目前的信息来看,在回表的那一步,耗费了近7秒,占用了整个SQL执行时间的绝大部分。
假设当前的SQL返 ...

好的,我再试试,非常感谢!

使用道具 举报

回复
求职 : 数据库开发
论坛徽章:
4
2014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09优秀写手
日期:2014-04-23 06:00:19红宝石
日期:2014-06-03 13:13:19
发表于 2017-2-21 11:29 | 显示全部楼层
虽然只返回一行,但是没办法从索引快速得到,所以才会有这么多回表,带来大量的物理读,才会造成这么慢;
如果索引缓存后能快一些,但仍然不优;
一个办法是建组合索引,看ORGANIZATION_ID与segment1组合后能否有帮助,这取决于数据的分布;
另一个思路是从需求端入手,不搞DESCRIPTION的模糊,或者只右边模糊;
需求仔细分析一下,这个SQL肯定是可以写得更合理的;

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
477
本田
日期:2014-01-05 16:51:44技术图书徽章
日期:2014-04-21 10:26:402014年世界杯参赛球队: 伊朗
日期:2014-05-23 10:41:312014年世界杯参赛球队: 比利时
日期:2014-06-17 12:09:43itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29
发表于 2017-2-21 17:08 | 显示全部楼层
有 Disk file operations I/O ,这是用的什么存储?OS又是啥?

使用道具 举报

回复

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

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP证:060528号 北京市公安局海淀分局网监中心备案编号:1101082001 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表