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

 找回密码
 注册
查看: 4105|回复: 32

一个诡异的不走索引的优化问题

[复制链接]
认证徽章
论坛徽章:
11
2012新春纪念徽章
日期:2012-01-04 11:56:442015年新春福章
日期:2015-03-06 11:58:39暖羊羊
日期:2015-03-04 14:53:002014年新春福章
日期:2014-02-18 16:44:08问答徽章
日期:2014-01-02 15:42:02优秀写手
日期:2013-12-18 09:29:11ITPUB社区12周年站庆徽章
日期:2013-08-20 11:30:112013年新春福章
日期:2013-02-25 14:51:24奥运纪念徽章
日期:2012-12-06 09:21:40复活蛋
日期:2013-01-25 13:54:43
发表于 2017-6-14 16:58 | 显示全部楼层 |阅读模式
本帖最后由 xteitxu 于 2017-6-14 18:26 编辑

请各位道友给问题个问题,改动SQL的建议就不要提了。因为SQL被固定死了。没法做改动。本应该走索引的但是没走,
统计信息与直方图都做了收集。还是走全表扫,逻辑读大,给个优化建议也可以,谢谢各位。附件里有10053的trace.
问题描述:
oracle 11.2
涉及sql:
select *
  from (select TT.*, rownum RR
          from (select T1.*, T2.*
                  from (select *
                          from wx329.Mail
                         where Mail.status <> :1
                           and Mail.expireDate <=
                               to_date(:2, 'yyyy-mm-dd hh24:mi:ss')) T1
                  left join wx329.Mail_SEC T2
                    on T1.id = T2.id_SEC) TT)
where RR >= :3
   and RR < :4

目前的执行计划:
SQL> select * from table
  2        (dbms_xplan.display_cursor('g8s2vy6x8p3tm',0,
  3         'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g8s2vy6x8p3tm, child number 0
-------------------------------------
select * from (select TT.*,rownum RR from ( select T1.*,T2.* from (
select * from wx329.Mail where Mail.status<>:1  and
Mail.expireDate<=to_date(:2 ,'yyyy-mm-dd hh24:mi:ss') ) T1 left join
wx329.Mail_SEC T2 on T1.id = T2.id_SEC) TT) where RR >= :3  and
RR < :4

Plan hash value: 2592343961

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | E-Rows |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |        |       |       |          |
|*  1 |  FILTER               |          |        |       |       |          |
|*  2 |   VIEW                |          |    128K|       |       |          |
|   3 |    COUNT              |          |        |       |       |          |
|*  4 |     HASH JOIN OUTER   |          |    128K|   706K|   706K| 1171K (0)|
|*  5 |      TABLE ACCESS FULL| MAIL     |    128K|       |       |          |
|   6 |      TABLE ACCESS FULL| MAIL_SEC |    314K|       |       |          |
------------------------------------------------------------------------------


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

   1 - filter(:3<:4)
   2 - filter(("RR"<:4 AND "RR">=:3))
   4 - access("MAIL"."ID"="T2"."ID_SEC")
   5 - filter(("MAIL"."STATUS"<>:1 AND
              "MAIL"."EXPIREDATE"<=TO_DATE(:2,'yyyy-mm-dd hh24:mi:ss')))

正常的执行计划应该是:

------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |       |       |     1 (100)|          |
|*  1 |  FILTER                         |              |       |       |            |          |
|*  2 |   VIEW                          |              |     1 |  5065 |     1   (0)| 00:00:01 |
|   3 |    COUNT                        |              |       |       |            |          |
|   4 |     NESTED LOOPS OUTER          |              |     1 |  5052 |     1   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| MAIL         |     1 |  3037 |     1   (0)| 00:00:01 |
|*  6 |       INDEX SKIP SCAN           | MAIL_ST_EX   |     1 |       |     1   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID| MAIL_SEC     |     1 |  2015 |     0   (0)|          |
|*  8 |       INDEX UNIQUE SCAN         | SYS_C0027221 |     1 |       |     0   (0)|          |



SQL> select count(*) from mail;

  COUNT(*)
----------
    321604

已用时间:  00: 00: 00.19
SQL> select count(*) from mail_sec;

  COUNT(*)
----------
    321602
   
INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------
MAIL_ST_EX                     STATUS
MAIL_ST_EX                     EXPIREDATE
                 
数据分布:
SQL> select status,count(*) from mail group by status;

    STATUS   COUNT(*)
---------- ----------
         1       5622
         2         56
         4      16147
         3     294127
         0       5605
         
直方图信息:

TABLE_NAME           COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
-------------------- -------------------- --------------- -------------- ----------
MAIL                 STATUS                          5576              0
MAIL                 STATUS                         11403              1
MAIL                 STATUS                         11457              2
MAIL                 STATUS                        305869              3
MAIL                 STATUS                        321965              4

ora11g10053.zip

33 KB, 下载次数: 3

tracefile

论坛徽章:
1
2017金鸡报晓
日期:2017-02-08 14:09:13
发表于 2017-6-14 17:06 | 显示全部楼层
用spm固定执行计划

使用道具 举报

回复
认证徽章
论坛徽章:
11
2012新春纪念徽章
日期:2012-01-04 11:56:442015年新春福章
日期:2015-03-06 11:58:39暖羊羊
日期:2015-03-04 14:53:002014年新春福章
日期:2014-02-18 16:44:08问答徽章
日期:2014-01-02 15:42:02优秀写手
日期:2013-12-18 09:29:11ITPUB社区12周年站庆徽章
日期:2013-08-20 11:30:112013年新春福章
日期:2013-02-25 14:51:24奥运纪念徽章
日期:2012-12-06 09:21:40复活蛋
日期:2013-01-25 13:54:43
发表于 2017-6-14 17:21 | 显示全部楼层
cuicuigo 发表于 2017-6-14 17:06
用spm固定执行计划

固定执行计划是我的最后解决方案,现在只是想找到什么原因导致的执行计划有问题。想分析下问题所在,也可以通过大家的分析自己学习一下这类问题的分析思路。

使用道具 举报

回复
认证徽章
论坛徽章:
11
2012新春纪念徽章
日期:2012-01-04 11:56:442015年新春福章
日期:2015-03-06 11:58:39暖羊羊
日期:2015-03-04 14:53:002014年新春福章
日期:2014-02-18 16:44:08问答徽章
日期:2014-01-02 15:42:02优秀写手
日期:2013-12-18 09:29:11ITPUB社区12周年站庆徽章
日期:2013-08-20 11:30:112013年新春福章
日期:2013-02-25 14:51:24奥运纪念徽章
日期:2012-12-06 09:21:40复活蛋
日期:2013-01-25 13:54:43
发表于 2017-6-14 17:23 | 显示全部楼层
不知道这种问题跟绑定变量窥探有没有关系。但是我把共享池中的执行计划purge掉,手动执行后,执行计划还是老样子。还是我的分析思路不对。

使用道具 举报

回复
论坛徽章:
12
2008新春纪念徽章
日期:2008-02-13 12:43:03目光如炬
日期:2017-03-19 22:00:00马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11优秀写手
日期:2014-07-05 06:00:132013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-09 18:06:20ITPUB十周年纪念徽章
日期:2011-11-01 16:21:152012新春纪念徽章
日期:2012-01-04 11:50:442011新春纪念徽章
日期:2011-02-18 11:43:32
发表于 2017-6-14 17:30 | 显示全部楼层
10053看看plan的计算

使用道具 举报

回复
认证徽章
论坛徽章:
11
2012新春纪念徽章
日期:2012-01-04 11:56:442015年新春福章
日期:2015-03-06 11:58:39暖羊羊
日期:2015-03-04 14:53:002014年新春福章
日期:2014-02-18 16:44:08问答徽章
日期:2014-01-02 15:42:02优秀写手
日期:2013-12-18 09:29:11ITPUB社区12周年站庆徽章
日期:2013-08-20 11:30:112013年新春福章
日期:2013-02-25 14:51:24奥运纪念徽章
日期:2012-12-06 09:21:40复活蛋
日期:2013-01-25 13:54:43
发表于 2017-6-14 17:36 | 显示全部楼层
binhu 发表于 2017-6-14 17:30
用10053看看plan的计算

是看下面这一段吗?
----- Plan Table -----

============
Plan Table
============
-----------------------------------------+-----------------------------------+
| Id  | Operation              | Name    | Rows  | Bytes | Cost  | Time      |
-----------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT       |         |       |       |   14K |           |
| 1   |  FILTER                |         |       |       |       |           |
| 2   |   VIEW                 |         |   12K |   61M |   14K |  00:03:57 |
| 3   |    COUNT               |         |       |       |       |           |
| 4   |     HASH JOIN OUTER    |         |   12K | 6397K |   14K |  00:03:57 |
| 5   |      TABLE ACCESS FULL | MAIL    |   12K | 3457K |  5519 |  00:01:07 |
| 6   |      TABLE ACCESS FULL | MAIL_SEC|  308K |   72M |  5232 |  00:01:03 |
-----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter(TO_NUMBER(:3)<TO_NUMBER(:4))
2 - filter(("RR"<TO_NUMBER(:4) AND "RR">=TO_NUMBER(:3)))
4 - access("MAIL"."ID"="T2"."ID_SEC")
5 - filter(("MAIL"."STATUS"<>:1 AND "MAIL"."EXPIREDATE"<=TO_DATE(:2,'yyyy-mm-dd hh24:mi:ss')))

使用道具 举报

回复
论坛徽章:
4
ITPUB十周年纪念徽章
日期:2011-11-01 16:23:26现代
日期:2013-09-13 12:03:19优秀写手
日期:2014-03-25 05:59:50秀才
日期:2017-09-18 17:04:46
发表于 2017-6-14 18:32 | 显示全部楼层
status <>  1  绝对走全表

使用道具 举报

回复
论坛徽章:
304
奥迪
日期:2013-07-29 13:45:59红旗
日期:2014-02-07 10:47:20路虎
日期:2014-02-13 10:34:03保时捷
日期:2014-02-14 09:46:462014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14
发表于 2017-6-14 21:01 | 显示全部楼层
这语句走全表扫描是正常的,走索引是偏门的情况, 由于语句是绑定变量的写法,所以走索引,走全表扫描都有可能,

使用道具 举报

回复
论坛徽章:
177
秀才
日期:2016-02-18 09:39:10摩羯座
日期:2016-01-20 16:48:10火眼金睛
日期:2016-01-31 22:00:00巨蟹座
日期:2016-01-30 22:10:33目光如炬
日期:2016-01-03 22:00:00秀才
日期:2015-12-21 09:53:46目光如炬
日期:2015-12-20 22:00:00秀才
日期:2015-12-25 15:31:10秀才
日期:2015-12-14 15:02:13秀才
日期:2016-01-21 13:37:04
发表于 2017-6-15 09:50 | 显示全部楼层
直方图信息和表统计信息重新收集吧?看结果和实际记录相差太多了。
最好把操作日志都贴出来。

使用道具 举报

回复
认证徽章
论坛徽章:
11
2012新春纪念徽章
日期:2012-01-04 11:56:442015年新春福章
日期:2015-03-06 11:58:39暖羊羊
日期:2015-03-04 14:53:002014年新春福章
日期:2014-02-18 16:44:08问答徽章
日期:2014-01-02 15:42:02优秀写手
日期:2013-12-18 09:29:11ITPUB社区12周年站庆徽章
日期:2013-08-20 11:30:112013年新春福章
日期:2013-02-25 14:51:24奥运纪念徽章
日期:2012-12-06 09:21:40复活蛋
日期:2013-01-25 13:54:43
发表于 2017-6-15 10:07 | 显示全部楼层
jieyancai 发表于 2017-6-15 09:50
直方图信息和表统计信息重新收集吧?看结果和实际记录相差太多了。
最好把操作日志都贴出来。

exec dbms_stats.gather_table_stats(ownname => 'WANGXIAN329',tabname => 'MAIL',estimate_percent => 100,method_opt=> 'for all indexed columns',cascade => true) ;   我是这样收集的直方图。是不是该用for all columns size auto??

使用道具 举报

回复

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

本版积分规则

SACC2017购票8.8折优惠进行时

2017中国系统架构师大会(SACC2017)将于10月19-21日在北京新云南皇冠假日酒店震撼来袭。今年,大会以“云智未来”为主题,云集国内外顶级专家,围绕云计算、人工智能、大数据、移动互联网、产业应用等热点领域展开技术探讨与交流。本届大会共设置2大主会场,18个技术专场;邀请来自互联网、金融、制造业、电商等多个领域,100余位技术专家及行业领袖来分享他们的经验;并将吸引4000+人次的系统运维、架构师及IT决策人士参会,为他们提供最具价值的交流平台。
----------------------------------------
优惠时间:2017年8月30日前

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