查看: 4920|回复: 8

【大话IT】 表上的索引是不是太多太乱了,导致优化器没走正确的执行计划

[复制链接]
论坛徽章:
25
双子座
日期:2015-07-16 14:18:24蒙奇·D·路飞
日期:2016-12-31 10:56:17乌索普
日期:2016-11-17 17:21:49妮可·罗宾
日期:2016-06-26 22:18:42白羊座
日期: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
发表于 2015-3-2 19:51 | 显示全部楼层 |阅读模式

  1. 本来在列dp_datetime_date上有单列索引和组合索引,但是没走索引。是不是索引太多太乱了。



  2. 连接到:
  3. Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
  4. With the Partitioning, OLAP, Data Mining and Real Application Testing options

  5. SQL> set linesize 10000
  6. SQL> set pagesize 20000
  7. SQL> alter session set  statistics_level=all;

  8. 会话已更改。

  9. SQL> select count(1) count
  10.   2    from Kh_khdd d, kh_khdd_extend ex, wd_zlsz wd
  11.   3   where d.ddbh = ex.ddbh
  12.   4     and d.ct_hyid = wd.wdbh
  13.   5     and (d.dp_deptid = 'XC' or d.ds_deptid = 'XC')
  14.   6     and d.dp_datetime_date >= to_date('2015-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
  15.   7     and d.dp_datetime_date <= to_date('2015-02-28 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
  16.   8     and d.pnr_hcglgj = '1'
  17.   9     and ex.wdzc_code like '%8410A%'
  18. 10     and d.zkfx = '1'
  19. 11     /

  20.      COUNT
  21. ----------
  22.         91

  23. SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));

  24. PLAN_TABLE_OUTPUT
  25. -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  26. -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  27. SQL_ID  a99n0fkm7vtyq, child number 0
  28. -------------------------------------
  29. select count(1) count   from Kh_khdd d, kh_khdd_extend ex, wd_zlsz wd  where d.ddbh = ex.ddbh    and d.ct_hyid = wd.wdbh    and (d.dp_deptid
  30. = 'XC' or d.ds_deptid = 'XC')    and d.dp_datetime_date >= to_date('2015-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')    and d.dp_datetime_date
  31. <= to_date('2015-02-28 23:59:59', 'yyyy-mm-dd hh24:mi:ss')    and d.pnr_hcglgj = '1'    and ex.wdzc_code like '%8410A%'    and d.zkfx = '1'

  32. Plan hash value: 1000426998

  33. ------------------------------------------------------------------------------------------------------------------------------------------------------
  34. | Id  | Operation                      | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
  35. ------------------------------------------------------------------------------------------------------------------------------------------------------
  36. |   1 |  SORT AGGREGATE                |                  |      1 |      1 |    68 |            |       |         1 |00:21:05.33 |    1911K|    745K|
  37. |   2 |   NESTED LOOPS                 |                  |      1 |     61 |  4148 |  7731   (1)| 00:01:33 |     91 |00:21:05.33 |    1911K|    745K|
  38. |   3 |    NESTED LOOPS                |                  |      1 |     61 |  2928 |  7609   (1)| 00:01:32 |  15835 |00:20:31.09 |    1862K|    742K|
  39. |   4 |     INDEX FULL SCAN            | IDX_WD_ZLSZ_WDBH |      1 |     20 |   140 |     1   (0)| 00:00:01 |     20 |00:00:00.01 |       1 |      0 |
  40. |*  5 |     TABLE ACCESS BY INDEX ROWID| KH_KHDD          |     20 |      3 |   123 |   380   (0)| 00:00:05 |  15835 |00:20:31.05 |    1862K|    742K|
  41. |*  6 |      INDEX RANGE SCAN          | IDX_CT_HYID      |     20 |    613 |       |     3   (0)| 00:00:01 |   2692K|00:00:48.54 |   12602 |   4967 |
  42. |*  7 |    TABLE ACCESS BY INDEX ROWID | KH_KHDD_EXTEND   |  15835 |      1 |    20 |     2   (0)| 00:00:01 |     91 |00:00:34.17 |   48932 |   2851 |
  43. |*  8 |     INDEX UNIQUE SCAN          | SYS_C008073      |  15835 |      1 |       |     1   (0)| 00:00:01 |  15835 |00:00:12.14 |   31693 |   1001 |
  44. ------------------------------------------------------------------------------------------------------------------------------------------------------

  45. Query Block Name / Object Alias (identified by operation id):
  46. -------------------------------------------------------------

  47.    1 - SEL$1
  48.    4 - SEL$1 / WD@SEL$1
  49.    5 - SEL$1 / D@SEL$1
  50.    6 - SEL$1 / D@SEL$1
  51.    7 - SEL$1 / EX@SEL$1
  52.    8 - SEL$1 / EX@SEL$1

  53. Outline Data
  54. -------------

  55.   /*+
  56.       BEGIN_OUTLINE_DATA
  57.       IGNORE_OPTIM_EMBEDDED_HINTS
  58.       OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
  59.       ALL_ROWS
  60.       OUTLINE_LEAF(@"SEL$1")
  61.       INDEX(@"SEL$1" "WD"@"SEL$1" ("WD_ZLSZ"."WDBH"))
  62.       INDEX_RS_ASC(@"SEL$1" "D"@"SEL$1" ("KH_KHDD"."CT_HYID"))
  63.       INDEX_RS_ASC(@"SEL$1" "EX"@"SEL$1" ("KH_KHDD_EXTEND"."DDBH"))
  64.       LEADING(@"SEL$1" "WD"@"SEL$1" "D"@"SEL$1" "EX"@"SEL$1")
  65.       USE_NL(@"SEL$1" "D"@"SEL$1")
  66.       USE_NL(@"SEL$1" "EX"@"SEL$1")
  67.       END_OUTLINE_DATA
  68.   */

  69. Predicate Information (identified by operation id):
  70. ---------------------------------------------------

  71.    5 - filter(("D"."DP_DATETIME_DATE">=TO_DATE(' 2015-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("D"."DP_DEPTID"='XC' OR
  72.               "D"."DS_DEPTID"='XC') AND "D"."ZKFX"='1' AND "D"."PNR_HCGLGJ"='1' AND "D"."DP_DATETIME_DATE"<=TO_DATE(' 2015-02-28 23:59:59', 'syyyy-mm-dd
  73.               hh24:mi:ss')))
  74.    6 - access("D"."CT_HYID"="WD"."WDBH")
  75.        filter("D"."CT_HYID" IS NOT NULL)
  76.    7 - filter("EX"."WDZC_CODE" LIKE '%8410A%')
  77.    8 - access("D"."DDBH"="EX"."DDBH")

  78. Column Projection Information (identified by operation id):
  79. -----------------------------------------------------------

  80.    1 - (#keys=0) COUNT(*)[22]
  81.    3 - "D"."DDBH"[VARCHAR2,30]
  82.    4 - "WD"."WDBH"[VARCHAR2,10]
  83.    5 - "D"."DDBH"[VARCHAR2,30]
  84.    6 - "D".ROWID[ROWID,10]
  85.    8 - "EX".ROWID[ROWID,10]


  86. 已选择70行。


  87. SQL> select * from user_ind_columns where table_name='KH_KHDD' AND COLUMN_NAME ='DP_DATETIME_DATE';
  88. INDEX_NAME                     TABLE_NAME                     COLUMN_NAME                                                                      COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
  89. ------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------------- ------------- ----------- -------
  90. IDX_KH_KHDD_002                KH_KHDD                        DP_DATETIME_DATE                                                                               1             7           0 ASC
  91. IDX_KH_KHDD_003                KH_KHDD                        DP_DATETIME_DATE                                                                               1             7           0 ASC
  92. IDX_DP_DATETIME_NEW            KH_KHDD                        DP_DATETIME_DATE                                                                               1             7           0 ASC
  93. IDX_DP_DATETIME_NEW2           KH_KHDD                        DP_DATETIME_DATE                                                                               1             7           0 ASC
  94. IDX_KH_KHDD_DCCD               KH_KHDD                        DP_DATETIME_DATE                                                                               5             7           0 ASC
  95. IDX_KH_KHDD_DCPDZDD            KH_KHDD                        DP_DATETIME_DATE                                                                               1             7           0 ASC
  96. 6 rows selected



复制代码


论坛徽章:
2
懒羊羊
日期:2015-03-04 14:52:112015年新春福章
日期:2015-03-06 11:58:18
发表于 2015-3-3 01:01 | 显示全部楼层
我也不知道....

使用道具 举报

回复
论坛徽章:
14
福特
日期:2013-07-31 12:37:26水瓶座
日期:2016-01-12 08:52:14巨蟹座
日期:2015-08-20 15:28:412015年新春福章
日期:2015-03-06 11:59:47暖羊羊
日期:2015-03-04 14:54:57马上有房
日期:2014-11-18 09:25:48优秀写手
日期:2014-10-21 06:00:13马上加薪
日期:2014-10-20 12:04:08三菱
日期:2013-10-15 17:48:46Jeep
日期:2013-09-12 17:40:27
发表于 2015-3-3 08:14 | 显示全部楼层
收集一下统计信息试试。

使用道具 举报

回复
论坛徽章:
311
行业板块每日发贴之星
日期:2012-07-12 18:47:29双黄蛋
日期:2011-08-12 17:31:04咸鸭蛋
日期:2011-08-18 15:13:51迷宫蛋
日期:2011-08-18 16:58:25紫蛋头
日期:2011-08-31 10:57:28ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47蜘蛛蛋
日期:2011-10-20 15:51:25迷宫蛋
日期:2011-10-29 11:12:59ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41鲜花蛋
日期:2011-11-09 20:33:30
发表于 2015-3-3 08:43 | 显示全部楼层
执行计划的第1步是读取索引 wd_zlsz.DX_WD_ZLSZ_WDBH,和表Kh_khdd通过关系."CT_HYID"="WD"."WDBH",
发生联系,优化器认为这样走效率更高。

你可以把时间范围缩小至一天,看看是否走该字段的索引就明白。

使用道具 举报

回复
认证徽章
论坛徽章:
184
2013年新春福章
日期:2013-05-27 10:23:002013年新春福章
日期:2013-05-27 10:23:002013年新春福章
日期:2013-06-05 15:29:212013年新春福章
日期:2013-06-05 15:29:212013年新春福章
日期:2013-05-27 10:23:002013年新春福章
日期:2013-06-05 15:29:21马上有房
日期:2014-03-03 16:14:44马上有对象
日期:2014-02-18 16:44:082014年新春福章
日期:2014-03-04 16:55:19ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:42
发表于 2015-3-3 08:49 | 显示全部楼层
id=5这步,
|*  5 |     TABLE ACCESS BY INDEX ROWID| KH_KHDD          |     20 |      3 |   123 |   380   (0)| 00:00:05 |  15835 |00:20:31.05 |
oracle估算返回3行,实际返回15835行,明显表的统计信息有有问题,先收集一下统计信息。

使用道具 举报

回复
认证徽章
论坛徽章:
40
2014年新春福章
日期:2014-02-18 16:43:09喜羊羊
日期:2015-05-18 16:24:25慢羊羊
日期:2015-06-12 13:08:22暖羊羊
日期:2015-07-02 16:06:20暖羊羊
日期:2015-07-06 16:28:55狮子座
日期:2015-07-29 17:14:43摩羯座
日期:2015-09-02 13:58:47白羊座
日期:2015-09-08 10:39:06天枰座
日期:2015-09-17 21:41:53摩羯座
日期:2015-10-29 21:07:02
发表于 2015-3-3 09:39 | 显示全部楼层
你可以通过加HINT让语句走你期望的索引,然后看一下执行计划的COST值,再与未加HINT前实际走的执行计划的COST值相比较。一般都会发现后者大于前者。

而造成没有走你期望的索引,一般是以下几个原因:
1、统计信息不准或缺失。
2、我们期望的是错的。可能使用这个索引在执行计划的某一步上是较低的COST,但却使后续的处理步骤的COST值增大。从而从整体上看,是得不偿失的。
3、CBO判断失误。虽然使用期望的索引的执行计划的COST值较大,但实际的执行时间却是更快的。这个通常要用固化执行计划的相关方法来处理了。

使用道具 举报

回复
论坛徽章:
25
双子座
日期:2015-07-16 14:18:24蒙奇·D·路飞
日期:2016-12-31 10:56:17乌索普
日期:2016-11-17 17:21:49妮可·罗宾
日期:2016-06-26 22:18:42白羊座
日期: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
 楼主| 发表于 2015-3-3 11:59 | 显示全部楼层
tianya_2011 发表于 2015-3-3 08:49
id=5这步,
|*  5 |     TABLE ACCESS BY INDEX ROWID| KH_KHDD          |     20 |      3 |   123 |    ...

确实,收集之后就走DP_DATETIME_DATE索引了。谢谢!

使用道具 举报

回复
认证徽章
论坛徽章:
184
2013年新春福章
日期:2013-05-27 10:23:002013年新春福章
日期:2013-05-27 10:23:002013年新春福章
日期:2013-06-05 15:29:212013年新春福章
日期:2013-06-05 15:29:212013年新春福章
日期:2013-05-27 10:23:002013年新春福章
日期:2013-06-05 15:29:21马上有房
日期:2014-03-03 16:14:44马上有对象
日期:2014-02-18 16:44:082014年新春福章
日期:2014-03-04 16:55:19ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:42
发表于 2015-3-3 13:17 | 显示全部楼层
不客气

使用道具 举报

回复
论坛徽章:
0
发表于 2015-3-5 17:15 | 显示全部楼层
果然还是统计信息过期了啊

使用道具 举报

回复

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

本版积分规则 发表回复

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