查看: 678|回复: 14

【sql优化】

[复制链接]
论坛徽章:
0
发表于 2018-4-23 11:47 | 显示全部楼层 |阅读模式
现有一个查询需要查到两个物化视图macro_transfer_log(大概230w行)和atomic_transfer_log(大概440w行),两个表统计信息均为最新,我按采样100%收集的,两个表都有time列(varchar2类型),作为主键的第一列,实际应用中只需要查最近一个月的数据(占数据的1/12左右),在macro表的条件中我用了time>=to_char(sysdate-32,'yyyymmddhh24miss'),效率确实提高了,12秒左右结束查询,但是在atomic那个表加入time列查询的时候,就出现了很奇怪的执行计划,下面贴上执行计划
来自安卓客户端来自客户端
论坛徽章:
0
 楼主| 发表于 2018-4-23 12:02 | 显示全部楼层
第二个执行计划的20,32,33行,为什么返回数都是1行,应该40w左右才对啊
来自安卓客户端来自客户端

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2018-4-23 12:23 | 显示全部楼层
执行计划没发上去,重发一下
来自安卓客户端来自客户端

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2018-4-23 12:41 | 显示全部楼层
本帖最后由 离校小菜鸟 于 2018-4-23 12:46 编辑

PLAN_TABLE_OUTPUT Plan hash value: 1761297910

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                        |     1 |   155 | 68016   (1)| 00:13:37 |
|*  1 |  HASH JOIN OUTER                        |                        |     1 |   155 | 68016   (1)| 00:13:37 |
|   2 |   NESTED LOOPS OUTER                    |                        |     1 |   113 | 13971   (1)| 00:02:48 |
|   3 |    VIEW                                 |                        |     1 |    71 |    61   (2)| 00:00:01 |
|   4 |     SORT AGGREGATE                      |                        |     1 |   170 |            |          |
|*  5 |      HASH JOIN SEMI                     |                        |     1 |   170 |    61   (2)| 00:00:01 |
|*  6 |       HASH JOIN SEMI                    |                        |     1 |   114 |    56   (2)| 00:00:01 |
|*  7 |        MAT_VIEW ACCESS BY INDEX ROWID   | MV_MACRO_TRANSFER_LOG  |     1 |    58 |    50   (0)| 00:00:01 |
|*  8 |         INDEX RANGE SCAN                | MACRO_TRANSFER_LOG_PK  | 22735 |       |     4   (0)| 00:00:01 |
|*  9 |        TABLE ACCESS FULL                | CATEGORY_DEFINITION    |   555 | 31080 |     5   (0)| 00:00:01 |
|* 10 |       TABLE ACCESS FULL                 | CATEGORY_DEFINITION    |   555 | 31080 |     5   (0)| 00:00:01 |
|* 11 |    VIEW                                 |                        |     1 |    42 | 13910   (1)| 00:02:47 |
|  12 |     SORT GROUP BY                       |                        |     1 |    16 | 13910   (1)| 00:02:47 |
|  13 |      VIEW                               |                        |     1 |    16 | 13909   (1)| 00:02:47 |
|  14 |       SORT GROUP BY                     |                        |     1 |   235 | 13909   (1)| 00:02:47 |
|* 15 |        HASH JOIN SEMI                   |                        |     1 |   235 | 13908   (1)| 00:02:47 |
|* 16 |         HASH JOIN SEMI                  |                        |     1 |   179 | 13903   (1)| 00:02:47 |
|* 17 |          HASH JOIN                      |                        |     1 |   123 | 13897   (1)| 00:02:47 |
|* 18 |           MAT_VIEW ACCESS BY INDEX ROWID| MV_MACRO_TRANSFER_LOG  |     1 |    90 |    32   (0)| 00:00:01 |
|* 19 |            INDEX RANGE SCAN             | MACRO_TRANSFER_LOG_PK  | 13641 |       |     4   (0)| 00:00:01 |
|  20 |           INDEX FAST FULL SCAN          | ATOMIC_TRANSFER_LOG_PK |  4307K|   135M| 13830   (1)| 00:02:46 |
|* 21 |          TABLE ACCESS FULL              | CATEGORY_DEFINITION    |   555 | 31080 |     5   (0)| 00:00:01 |
|* 22 |         TABLE ACCESS FULL               | CATEGORY_DEFINITION    |   555 | 31080 |     5   (0)| 00:00:01 |
|  23 |   VIEW                                  |                        |     1 |    42 | 54044   (1)| 00:10:49 |
|  24 |    HASH GROUP BY                        |                        |     1 |    29 | 54044   (1)| 00:10:49 |
|  25 |     VIEW                                |                        |     1 |    29 | 54043   (1)| 00:10:49 |
|  26 |      HASH GROUP BY                      |                        |     1 |   288 | 54043   (1)| 00:10:49 |
|* 27 |       HASH JOIN SEMI                    |                        |     1 |   288 | 54042   (1)| 00:10:49 |
|* 28 |        HASH JOIN SEMI                   |                        |     1 |   232 | 54036   (1)| 00:10:49 |
|* 29 |         HASH JOIN OUTER                 |                        |     1 |   176 | 54031   (1)| 00:10:49 |
|* 30 |          MAT_VIEW ACCESS BY INDEX ROWID | MV_MACRO_TRANSFER_LOG  |     1 |   109 |    32   (0)| 00:00:01 |
|* 31 |           INDEX RANGE SCAN              | MACRO_TRANSFER_LOG_PK  | 13641 |       |     4   (0)| 00:00:01 |
|  32 |          MAT_VIEW ACCESS FULL           | MV_ATOMIC_TRANSFER_LOG |  4307K|   275M| 53963   (1)| 00:10:48 |
|* 33 |         TABLE ACCESS FULL               | CATEGORY_DEFINITION    |   555 | 31080 |     5   (0)| 00:00:01 |
|* 34 |        TABLE ACCESS FULL                | CATEGORY_DEFINITION    |   555 | 31080 |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

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

   1 - access("A"."PATTERN"="QUEUING"."PATTERN"(+))
   5 - access("M"."ORG_SRC_LOC"="PORT_ID")
   6 - access("M"."ORG_DST_LOC"="PORT_ID")
   7 - filter("M"."MACRO_CMP_TIME">='2018041713000000' AND "M"."MACRO_CMP_TIME"<'2018041714000000')
   8 - access("M"."TIME">=TO_CHAR(SYSDATE@!-32,'yyyymmddhh24miss')||'00')
   9 - filter("PORT_ID" IS NOT NULL AND ("TOOL_ID" IS NOT NULL OR "TOOL_GROUP" IS NOT NULL OR
              "MASTER_TOOL_GROUP" IS NOT NULL OR "MODULE" IS NOT NULL OR "BAY_ID" IS NOT NULL))
  10 - filter("PORT_ID" IS NOT NULL AND ("TOOL_ID" IS NOT NULL OR "TOOL_GROUP" IS NOT NULL OR
              "MASTER_TOOL_GROUP" IS NOT NULL OR "MODULE" IS NOT NULL OR "BAY_ID" IS NOT NULL))
  11 - filter("M"."PATTERN"="A"."PATTERN"(+))
  15 - access("M"."ORG_SRC_LOC"="PORT_ID")
  16 - access("M"."ORG_DST_LOC"="PORT_ID")
  17 - access("M"."COMMAND_ID"="A"."COMMAND_ID")
  18 - filter("M"."MACRO_CMP_TIME">='2018041713000000' AND "M"."MACRO_CMP_TIME"<'2018041714000000')
  19 - access("M"."TIME">=TO_CHAR(SYSDATE@!-32,'yyyymmddhh24miss')||'00')
       filter("M"."STATUS"='0' OR "M"."STATUS"='2' OR "M"."STATUS"='4')
  21 - filter("PORT_ID" IS NOT NULL AND ("TOOL_ID" IS NOT NULL OR "TOOL_GROUP" IS NOT NULL OR
              "MASTER_TOOL_GROUP" IS NOT NULL OR "MODULE" IS NOT NULL OR "BAY_ID" IS NOT NULL))
  22 - filter("PORT_ID" IS NOT NULL AND ("TOOL_ID" IS NOT NULL OR "TOOL_GROUP" IS NOT NULL OR
              "MASTER_TOOL_GROUP" IS NOT NULL OR "MODULE" IS NOT NULL OR "BAY_ID" IS NOT NULL))
  27 - access("M"."ORG_SRC_LOC"="PORT_ID")
  28 - access("M"."ORG_DST_LOC"="PORT_ID")
  29 - access("M"."COMMAND_ID"="COMMAND_ID"(+))
  30 - filter("M"."MACRO_CMP_TIME">='2018041713000000' AND "M"."MACRO_CMP_TIME"<'2018041714000000')
  31 - access("M"."TIME">=TO_CHAR(SYSDATE@!-32,'yyyymmddhh24miss')||'00')
       filter("M"."STATUS"='0' OR "M"."STATUS"='2' OR "M"."STATUS"='4')
  33 - filter("PORT_ID" IS NOT NULL AND ("TOOL_ID" IS NOT NULL OR "TOOL_GROUP" IS NOT NULL OR
              "MASTER_TOOL_GROUP" IS NOT NULL OR "MODULE" IS NOT NULL OR "BAY_ID" IS NOT NULL))
  34 - filter("PORT_ID" IS NOT NULL AND ("TOOL_ID" IS NOT NULL OR "TOOL_GROUP" IS NOT NULL OR
              "MASTER_TOOL_GROUP" IS NOT NULL OR "MODULE" IS NOT NULL OR "BAY_ID" IS NOT NULL))

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2018-4-23 12:42 | 显示全部楼层
PLAN_TABLE_OUTPUT
Plan hash value: 3260196469

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                        |     1 |   155 |  1427   (1)| 00:00:18 |
|*  1 |  HASH JOIN OUTER                        |                        |     1 |   155 |  1427   (1)| 00:00:18 |
|   2 |   NESTED LOOPS OUTER                    |                        |     1 |   113 |   743   (1)| 00:00:09 |
|   3 |    VIEW                                 |                        |     1 |    71 |    61   (2)| 00:00:01 |
|   4 |     SORT AGGREGATE                      |                        |     1 |   170 |            |          |
|*  5 |      HASH JOIN SEMI                     |                        |     1 |   170 |    61   (2)| 00:00:01 |
|*  6 |       HASH JOIN SEMI                    |                        |     1 |   114 |    56   (2)| 00:00:01 |
|*  7 |        MAT_VIEW ACCESS BY INDEX ROWID   | MV_MACRO_TRANSFER_LOG  |     1 |    58 |    50   (0)| 00:00:01 |
|*  8 |         INDEX RANGE SCAN                | MACRO_TRANSFER_LOG_PK  | 22735 |       |     4   (0)| 00:00:01 |
|*  9 |        TABLE ACCESS FULL                | CATEGORY_DEFINITION    |   555 | 31080 |     5   (0)| 00:00:01 |
|* 10 |       TABLE ACCESS FULL                 | CATEGORY_DEFINITION    |   555 | 31080 |     5   (0)| 00:00:01 |
|* 11 |    VIEW                                 |                        |     1 |    42 |   682   (1)| 00:00:09 |
|  12 |     SORT GROUP BY                       |                        |     1 |    16 |   682   (1)| 00:00:09 |
|  13 |      VIEW                               |                        |     1 |    16 |   681   (1)| 00:00:09 |
|  14 |       SORT GROUP BY                     |                        |     1 |   252 |   681   (1)| 00:00:09 |
|* 15 |        HASH JOIN SEMI                   |                        |     1 |   252 |   680   (1)| 00:00:09 |
|* 16 |         HASH JOIN SEMI                  |                        |     1 |   196 |   675   (1)| 00:00:09 |
|  17 |          NESTED LOOPS                   |                        |     1 |   140 |   669   (1)| 00:00:09 |
|* 18 |           MAT_VIEW ACCESS BY INDEX ROWID| MV_MACRO_TRANSFER_LOG  |     1 |    90 |    32   (0)| 00:00:01 |
|* 19 |            INDEX RANGE SCAN             | MACRO_TRANSFER_LOG_PK  | 13641 |       |     4   (0)| 00:00:01 |
|* 20 |           INDEX RANGE SCAN              | ATOMIC_TRANSFER_LOG_PK |     1 |    50 |   637   (1)| 00:00:08 |
|* 21 |          TABLE ACCESS FULL              | CATEGORY_DEFINITION    |   555 | 31080 |     5   (0)| 00:00:01 |
|* 22 |         TABLE ACCESS FULL               | CATEGORY_DEFINITION    |   555 | 31080 |     5   (0)| 00:00:01 |
|  23 |   VIEW                                  |                        |     1 |    42 |   683   (1)| 00:00:09 |
|  24 |    HASH GROUP BY                        |                        |     1 |    29 |   683   (1)| 00:00:09 |
|  25 |     VIEW                                |                        |     1 |    29 |   682   (1)| 00:00:09 |
|  26 |      HASH GROUP BY                      |                        |     1 |   305 |   682   (1)| 00:00:09 |
|* 27 |       HASH JOIN SEMI                    |                        |     1 |   305 |   681   (1)| 00:00:09 |
|* 28 |        HASH JOIN SEMI                   |                        |     1 |   249 |   676   (1)| 00:00:09 |
|  29 |         NESTED LOOPS OUTER              |                        |     1 |   193 |   670   (1)| 00:00:09 |
|* 30 |          MAT_VIEW ACCESS BY INDEX ROWID | MV_MACRO_TRANSFER_LOG  |     1 |   109 |    32   (0)| 00:00:01 |
|* 31 |           INDEX RANGE SCAN              | MACRO_TRANSFER_LOG_PK  | 13641 |       |     4   (0)| 00:00:01 |
|  32 |          MAT_VIEW ACCESS BY INDEX ROWID | MV_ATOMIC_TRANSFER_LOG |     1 |    84 |   638   (1)| 00:00:08 |
|* 33 |           INDEX RANGE SCAN              | ATOMIC_TRANSFER_LOG_PK |     1 |       |   637   (1)| 00:00:08 |
|* 34 |         TABLE ACCESS FULL               | CATEGORY_DEFINITION    |   555 | 31080 |     5   (0)| 00:00:01 |
|* 35 |        TABLE ACCESS FULL                | CATEGORY_DEFINITION    |   555 | 31080 |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

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

   1 - access("A"."PATTERN"="QUEUING"."PATTERN"(+))
   5 - access("M"."ORG_SRC_LOC"="PORT_ID")
   6 - access("M"."ORG_DST_LOC"="PORT_ID")
   7 - filter("M"."MACRO_CMP_TIME">='2018041713000000' AND "M"."MACRO_CMP_TIME"<'2018041714000000')
   8 - access("M"."TIME">=TO_CHAR(SYSDATE@!-32,'yyyymmddhh24miss')||'00')
   9 - filter("PORT_ID" IS NOT NULL AND ("TOOL_ID" IS NOT NULL OR "TOOL_GROUP" IS NOT NULL OR
              "MASTER_TOOL_GROUP" IS NOT NULL OR "MODULE" IS NOT NULL OR "BAY_ID" IS NOT NULL))
  10 - filter("PORT_ID" IS NOT NULL AND ("TOOL_ID" IS NOT NULL OR "TOOL_GROUP" IS NOT NULL OR
              "MASTER_TOOL_GROUP" IS NOT NULL OR "MODULE" IS NOT NULL OR "BAY_ID" IS NOT NULL))
  11 - filter("M"."PATTERN"="A"."PATTERN"(+))
  15 - access("M"."ORG_SRC_LOC"="PORT_ID")
  16 - access("M"."ORG_DST_LOC"="PORT_ID")
  18 - filter("M"."MACRO_CMP_TIME">='2018041713000000' AND "M"."MACRO_CMP_TIME"<'2018041714000000')
  19 - access("M"."TIME">=TO_CHAR(SYSDATE@!-32,'yyyymmddhh24miss')||'00')
       filter("M"."STATUS"='0' OR "M"."STATUS"='2' OR "M"."STATUS"='4')
  20 - access("A"."TIME">=TO_CHAR(SYSDATE@!-32,'yyyymmddhh24miss')||'00' AND
              "M"."COMMAND_ID"="A"."COMMAND_ID")
       filter("M"."COMMAND_ID"="A"."COMMAND_ID")
  21 - filter("PORT_ID" IS NOT NULL AND ("TOOL_ID" IS NOT NULL OR "TOOL_GROUP" IS NOT NULL OR
              "MASTER_TOOL_GROUP" IS NOT NULL OR "MODULE" IS NOT NULL OR "BAY_ID" IS NOT NULL))
  22 - filter("PORT_ID" IS NOT NULL AND ("TOOL_ID" IS NOT NULL OR "TOOL_GROUP" IS NOT NULL OR
              "MASTER_TOOL_GROUP" IS NOT NULL OR "MODULE" IS NOT NULL OR "BAY_ID" IS NOT NULL))
  27 - access("M"."ORG_SRC_LOC"="PORT_ID")
  28 - access("M"."ORG_DST_LOC"="PORT_ID")
  30 - filter("M"."MACRO_CMP_TIME">='2018041713000000' AND "M"."MACRO_CMP_TIME"<'2018041714000000')
  31 - access("M"."TIME">=TO_CHAR(SYSDATE@!-32,'yyyymmddhh24miss')||'00')
       filter("M"."STATUS"='0' OR "M"."STATUS"='2' OR "M"."STATUS"='4')
  33 - access("TIME"(+)>=TO_CHAR(SYSDATE@!-32,'yyyymmddhh24miss')||'00' AND
              "M"."COMMAND_ID"="COMMAND_ID"(+))
       filter("M"."COMMAND_ID"="COMMAND_ID"(+))
  34 - filter("PORT_ID" IS NOT NULL AND ("TOOL_ID" IS NOT NULL OR "TOOL_GROUP" IS NOT NULL OR
              "MASTER_TOOL_GROUP" IS NOT NULL OR "MODULE" IS NOT NULL OR "BAY_ID" IS NOT NULL))
  35 - filter("PORT_ID" IS NOT NULL AND ("TOOL_ID" IS NOT NULL OR "TOOL_GROUP" IS NOT NULL OR
              "MASTER_TOOL_GROUP" IS NOT NULL OR "MODULE" IS NOT NULL OR "BAY_ID" IS NOT NULL))

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2018-4-23 13:08 | 显示全部楼层
按第二个执行计划查,也就是加入了atomic表的time列条件以后,跑几分钟都跑不出结果
来自安卓客户端来自客户端

使用道具 举报

回复
认证徽章
论坛徽章:
8
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:002010新春纪念徽章
日期:2010-03-01 11:07:24ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262013年新春福章
日期:2013-02-25 14:51:24沸羊羊
日期:2015-03-04 14:51:522015年新春福章
日期:2015-03-06 11:57:31
发表于 2018-4-23 13:51 | 显示全部楼层
1、atomic物化视图上加入time列后执行计划,计划中20行、32行及33行的rows列出现1,一点也不奇怪,因为本身这几行的索引就是唯一索引;
2、嗯,往第二个物化视图上加time上,计划发生了变化,也许问题就是出在了17行的NL和29行的NL OUTER上了。

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2018-4-23 14:09 | 显示全部楼层
sqysl 发表于 2018-4-23 13:51
1、atomic物化视图上加入time列后执行计划,计划中20行、32行及33行的rows列出现1,一点也不奇怪,因为本身 ...

但是计划的20和33行,采用的是索引范围扫描,返回行数应该是time>=sysdate-32过滤后的行数才对啊,怎么会是1呢
来自安卓客户端来自客户端

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2018-4-23 14:16 | 显示全部楼层
sqysl 发表于 2018-4-23 13:51
1、atomic物化视图上加入time列后执行计划,计划中20行、32行及33行的rows列出现1,一点也不奇怪,因为本身 ...

突然又懂了,第二个执行计划是每次查出一条,然后nl,难怪这么慢
来自安卓客户端来自客户端

使用道具 举报

回复
认证徽章
论坛徽章:
8
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:002010新春纪念徽章
日期:2010-03-01 11:07:24ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262013年新春福章
日期:2013-02-25 14:51:24沸羊羊
日期:2015-03-04 14:51:522015年新春福章
日期:2015-03-06 11:57:31
发表于 2018-4-23 14:16 | 显示全部楼层
本帖最后由 sqysl 于 2018-4-23 14:17 编辑
离校小菜鸟 发表于 2018-4-23 14:09
但是计划的20和33行,采用的是索引范围扫描,返回行数应该是time>=sysdate-32过滤后的行数才对啊,怎么会 ...

1、范围扫描并不代表就一定返回多个数据行,因为这个节点上有条件,它只返回满足条件的数据;
2、20行和33行,只是一次NL循环返回的数据,因此,应该是time条件加上连接条件的结果。

使用道具 举报

回复

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

本版积分规则 发表回复

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