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

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

[性能调整] Oracle sql 执行计划

[复制链接]
论坛徽章:
1
优秀写手
日期:2014-04-16 06:00:13
发表于 2017-2-16 18:15 | 显示全部楼层 |阅读模式
各位好:
        请教一个关于Oracle SQL执行计划的问题。详细描述在附件的txt文档中,图片是执行计划的截图。为了方便查阅,我把附件中的内容也粘贴到正文一份。跪求大神指点,万分感谢。

遇到一个奇怪的现象,求大神指点(大概的意思是把子查询的结果集放到一个表众,执行计划就变了,查询速度也快了。)

环境介绍
表eic_code_forward_node 总数据量600w左右;经过条件type和forward_time过滤后28744条;索引情况:是单列索引order_code和单列索引forward_time
表eic_code_forward_req  总数据量600w左右;经过条件phone和type过滤后1140条;索引情况:是单列索引order_code和单列索引phone
表aa(phone子查询的结果集) 数据量是670
单独执行phone子查询的时间是:Elapsed: 00:00:00.71(不到1秒)
表的统计信息重新收集过(先删除再收集,dbms_stats.gather_schema_stats方式)
如果文字的执行计划不好看可以查看图片。

======================================================================================================
======================================================================================================

疑问
1、理想的执行计划是这样的吗?1.先子查询phone--》2.俩个表分别依据条件过滤数据--》3.俩表的结果集连接合并?
2、为什么把子查询的结果集放到表里以后由nested loops变为hash join?
3、经过条件过滤后的结果集都不大,nested loops和hash join为何逻辑读差那么大??




sql1如下:
select n.*, r.*
  from "eic_code_forward_node" n, "eic_code_forward_req" r
where n."order_code" = r."order_code"
   and r."phone" in
       (
           select distinct ("mobile")
          from "command_order_update"
         where "seriesCodeNum" like '%abd%'
           and "errorCode" = '0000'
           and "order_date" >=
               to_date('2017-02-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
           and "order_date" <
               to_date('2017-02-16 14:00:00', 'yyyy-mm-dd hh24:mi:ss')
                )
   and r."type" = '3'
   and n."forward_time" >= '2017-02-16 00:00:00:000'
   and n."forward_time" < '2017-02-16 14:00:00:000';
sql1执行计划
Elapsed: 00:05:33.88

Execution Plan
----------------------------------------------------------
Plan hash value: 1029444222

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                                     |           1 |         687 |          11   (0)| 00:00:01 |             |             |
|   1 |  NESTED LOOPS SEMI                    |                                     |           1 |         687 |          11   (0)| 00:00:01 |             |             |
|   2 |   NESTED LOOPS                            |                                     |           1 |         631 |           7   (0)| 00:00:01 |             |             |
|   3 |    TABLE ACCESS BY INDEX ROWID            | eic_code_forward_node             |           1 |         493 |           4   (0)| 00:00:01 |             |             |
|*  4 |     INDEX RANGE SCAN                    | IND_EIC_CODE_FORWARD_NODE_TIME |           1 |             |           3   (0)| 00:00:01 |             |             |
|*  5 |    TABLE ACCESS BY INDEX ROWID            | eic_code_forward_req             |           1 |         138 |           3   (0)| 00:00:01 |             |             |
|*  6 |     INDEX RANGE SCAN                    | IND_EIC_CODE_FORWARD_REQ_CODE  |           1 |             |           2   (0)| 00:00:01 |             |             |
|   7 |   PARTITION RANGE SINGLE            |                                     |           1 |          56 |           4   (0)| 00:00:01 |          67 |          67 |
|*  8 |    TABLE ACCESS BY LOCAL INDEX ROWID| command_order_update             |           1 |          56 |           4   (0)| 00:00:01 |          67 |          67 |
|*  9 |     INDEX RANGE SCAN                    | IND_COMMAND_ORDER_UPDATE_TIME  |           2 |             |           2   (0)| 00:00:01 |          67 |          67 |
--------------------------------------------------------------------------------------------------------------------------------------

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

   4 - access("N"."forward_time">='2017-02-16 00:00:00:000' AND "N"."forward_time"<'2017-02-16 14:00:00:000')
   5 - filter("R"."type"='3')
   6 - access("N"."order_code"="R"."order_code")
   8 - filter("seriesCodeNum" LIKE '%abcd%' AND "errorCode"='0000' AND "R"."phone"="mobile")
   9 - access("order_date">=TO_DATE(' 2017-02-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "order_date"<TO_DATE(' 2017-02-16
              14:00:00', 'syyyy-mm-dd hh24:mi:ss'))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
  154091753  consistent gets
          0  physical reads
          0  redo size
      83697  bytes sent via SQL*Net to client
        696  bytes received via SQL*Net from client
         18  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        242  rows processed


======================================================================================================
======================================================================================================



sql2如下:               
select n.*, r.*
  from "eic_code_forward_node" n, "eic_code_forward_req" r
where n."order_code" = r."order_code"
      -- and n."forward_status" = '0'
   and r."phone" in
       (select * from aa)
   and r."type" = '3'
   and n."forward_time" >= '2017-02-16 00:00:00:000'
   and n."forward_time" < '2017-02-16 14:00:00:000';

--做这样的修改:把phone的子查询放到aa表   
create table aa as select distinct ("mobile")
          from "command_order_update"
         where "seriesCodeNum" like '%abcd%'
           and "errorCode" = '0000'
           and "order_date" >=
               to_date('2017-02-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
           and "order_date" <
               to_date('2017-02-16 14:00:00', 'yyyy-mm-dd hh24:mi:ss');   
sql2执行计划如下
Elapsed: 00:00:00.15

Execution Plan
----------------------------------------------------------
Plan hash value: 1111991184

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                                |     1 |   683 |    11        (10)| 00:00:01 |
|*  1 |  HASH JOIN SEMI               |                                |     1 |   683 |    11        (10)| 00:00:01 |
|   2 |   NESTED LOOPS                      |                                |     1 |   631 |     7         (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| eic_code_forward_node               |     1 |   493 |     4         (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN              | IND_EIC_CODE_FORWARD_NODE_TIME |     1 |       |     3         (0)| 00:00:01 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| eic_code_forward_req               |     1 |   138 |     3         (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN              | IND_EIC_CODE_FORWARD_REQ_CODE  |     1 |       |     2         (0)| 00:00:01 |
|   7 |   TABLE ACCESS FULL              | AA                               |   670 | 34840 |     3         (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

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

   1 - access("R"."phone"="AA"."mobile")
   4 - access("N"."forward_time">='2017-02-16 00:00:00:000' AND "N"."forward_time"<'2017-02-16
              14:00:00:000')
   5 - filter("R"."type"='3')
   6 - access("N"."order_code"="R"."order_code")

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      53508  consistent gets
          0  physical reads
          0  redo size
      83363  bytes sent via SQL*Net to client
        696  bytes received via SQL*Net from client
         18  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        242  rows processed

                          
                          
                          
                          

sql1

sql1

sql2

sql2

环境说明.txt

7.57 KB, 下载次数: 9

sql和执行计划

论坛徽章:
1
2017金鸡报晓
日期:2017-02-08 14:09:13
发表于 2017-2-17 09:27 | 显示全部楼层
走nested loops是因为这些表的统计信息不是最新的,评估出来的cost就不准,评估这些表过滤后的结果集都是1,那就倾向走nested loops。收集新的统计信息再试试。

使用道具 举报

回复
认证徽章
论坛徽章:
6
2013年新春福章
日期:2013-02-25 14:51:24马上加薪
日期:2015-01-29 10:29:42优秀写手
日期:2014-12-25 06:00:12青年奥林匹克运动会-高尔夫
日期:2014-09-25 21:54:05马上有对象
日期:2015-01-24 21:15:50秀才
日期:2016-12-21 16:55:07
发表于 2017-2-17 10:10 | 显示全部楼层
本帖最后由 xw721xw 于 2017-2-17 10:46 编辑





dbms_stats.gather_schema_stats方式中method_opt这个值是咋样的呢?












使用道具 举报

回复
论坛徽章:
1
优秀写手
日期:2014-04-16 06:00:13
 楼主| 发表于 2017-2-17 10:15 | 显示全部楼层
cuicuigo 发表于 2017-2-17 09:27
走nested loops是因为这些表的统计信息不是最新的,评估出来的cost就不准,评估这些表过滤后的结果集都是1 ...

非常感谢!
主要的俩个表的统计信息重新收集了,忽略了子查询表的统计信息。我重新收集试试。

使用道具 举报

回复
论坛徽章:
1
秀才
日期:2017-02-22 15:16:26
发表于 2017-2-17 10:23 | 显示全部楼层
本帖最后由 latch_free 于 2017-2-17 10:27 编辑

1、理想的执行计划是这样的吗?1.先子查询phone--》2.俩个表分别依据条件过滤数据--》3.俩表的结果集连接合并?
        如果表eic_code_forward_node、eic_code_forward_req的执行统计信息是最新的,这个执行计划是对的
2、为什么把子查询的结果集放到表里以后由nested loops变为hash join?
        表command_order_update的统计信息不准,评估出来的结果为1,当然走nest loop semi消耗少;而换成中间表后评估出来的结果为670,在使用嵌套循环反连接就相当于全表扫描中间表,并各列值比较;当然是用hash join消耗少;
3、经过条件过滤后的结果集都不大,nested loops和hash join为何逻辑读差那么大??
        对表command_order_update的处理消耗是要计算到整个执行计划中的,而你单独建表之后,这部分消耗就不计算在内了;

使用道具 举报

回复
论坛徽章:
1
优秀写手
日期:2014-04-16 06:00:13
 楼主| 发表于 2017-2-17 10:31 | 显示全部楼层
cuicuigo 发表于 2017-2-17 09:27
走nested loops是因为这些表的统计信息不是最新的,评估出来的cost就不准,评估这些表过滤后的结果集都是1 ...

再请教一个问题,这个sql的最佳执行计划您觉的应该是什么样的?
结果集不大为什么nested loops效率那么差?2w左右的结果集和700左右的结果集nested loops需要那么久吗?很费解!

使用道具 举报

回复
论坛徽章:
1
优秀写手
日期:2014-04-16 06:00:13
 楼主| 发表于 2017-2-17 10:46 | 显示全部楼层
latch_free 发表于 2017-2-17 10:23
1、理想的执行计划是这样的吗?1.先子查询phone--》2.俩个表分别依据条件过滤数据--》3.俩表的结果集连接合 ...

Elapsed: 00:00:12.41

Execution Plan
----------------------------------------------------------
Plan hash value: 1813041867

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                                     |           1 |         687 |          76   (0)| 00:00:01 |             |             |
|   1 |  NESTED LOOPS SEMI                    |                                     |           1 |         687 |          76   (0)| 00:00:01 |             |             |
|   2 |   NESTED LOOPS                            |                                     |           1 |         631 |           7   (0)| 00:00:01 |             |             |
|   3 |    TABLE ACCESS BY INDEX ROWID            | eic_code_forward_node             |           1 |         493 |           4   (0)| 00:00:01 |             |             |
|*  4 |     INDEX RANGE SCAN                    | IND_EIC_CODE_FORWARD_NODE_TIME |           1 |             |           3   (0)| 00:00:01 |             |             |
|*  5 |    TABLE ACCESS BY INDEX ROWID            | eic_code_forward_req             |           1 |         138 |           3   (0)| 00:00:01 |             |             |
|*  6 |     INDEX RANGE SCAN                    | IND_EIC_CODE_FORWARD_REQ_CODE  |           1 |             |           2   (0)| 00:00:01 |             |             |
|*  7 |   TABLE ACCESS BY GLOBAL INDEX ROWID| command_order_update             |           1 |          56 |          69   (0)| 00:00:01 |          67 |          67 |
|*  8 |    INDEX RANGE SCAN                    | IND_COMMAND_ORDER_UPDATE_M     |          72 |             |           2   (0)| 00:00:01 |             |             |
--------------------------------------------------------------------------------------------------------------------------------------

重新收集command_order_update表的统计信息后的计划,比没有收集之前好点。但是还是没有按最优的走。
收集统计信息的方式:
dbms_stats.gather_table_stats(ownname => 'aaa',
                                tabname => '"command_order_update"',
                                degree  => 4,                                
                                method_opt => 'for all indexed columns')
之前的俩个表也是以这样的方式收集。

使用道具 举报

回复
论坛徽章:
1
优秀写手
日期:2014-04-16 06:00:13
 楼主| 发表于 2017-2-17 10:50 | 显示全部楼层
latch_free 发表于 2017-2-17 10:23
1、理想的执行计划是这样的吗?1.先子查询phone--》2.俩个表分别依据条件过滤数据--》3.俩表的结果集连接合 ...

单独执行子查询的执行计划是对的
Elapsed: 00:00:00.15

Execution Plan
----------------------------------------------------------
Plan hash value: 2026862490

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                       |   780 | 43680 |  4407         (1)| 00:00:53 |       |       |
|   1 |  HASH UNIQUE                |                       |   780 | 43680 |  4407         (1)| 00:00:53 |       |       |
|   2 |   PARTITION RANGE SINGLE|                       |   780 | 43680 |  4406         (1)| 00:00:53 |    67 |    67 |
|*  3 |    TABLE ACCESS FULL        | command_order_update |   780 | 43680 |  4406         (1)| 00:00:53 |    67 |    67 |
----------------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      16228  consistent gets
          0  physical reads
          0  redo size
      17520  bytes sent via SQL*Net to client
       1004  bytes received via SQL*Net from client
         46  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        670  rows processed
真郁闷放到子查询里预估的行就是72

使用道具 举报

回复
论坛徽章:
1
优秀写手
日期:2014-04-16 06:00:13
 楼主| 发表于 2017-2-17 10:52 | 显示全部楼层
vfast21 发表于 2017-2-17 10:50
单独执行子查询的执行计划是对的
Elapsed: 00:00:00.15

看错了,不是走的索引是走的分区。

使用道具 举报

回复
论坛徽章:
1
优秀写手
日期:2014-04-16 06:00:13
 楼主| 发表于 2017-2-17 10:58 | 显示全部楼层
xw721xw 发表于 2017-2-17 10:10
dbms_stats.gather_schema_stats方式中method_opt这个值是咋样的呢?

dbms_stats.gather_table_stats(ownname => 'dfaf',
                                tabname => '"command_order_update"',
                                degree  => 4,                                
                                method_opt => 'for all indexed columns')
另外俩个表同样的

使用道具 举报

回复

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

本版积分规则

久等啦!10张门票开启你的DTCC2017之旅~

2017中国数据库技术大会将于2017年5月11-13日如约而至,本届大会以“数据驱动•价值发现”为主题,共设定2大主场和21个技术专场,云集海内外120+位技术大牛,共同探讨Oracle、MySQL、NoSQL、云端数据库、区块链、深度学习等领域的前瞻性热点话题。
即日起,填写DTCC2017会前调查问卷,即有机会赢取价值2600元的大会门票1张!仅限10张!
----------------------------------------
活动截止时间:2017年5月5日统一公布

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