关于查询转换的一些简单分析(二) (r3笔记第68天)

关于查询转换的作用,在之前的一篇博文里面也大概说了视图合并和子查询嵌套的部分。http://blog.itpub.net/23718752/viewspace-1310272/ 今天来举一个实例来分析一下。 这条sql语句是在生产环境中运行的,目前执行速度在0.1秒左右。我们来看看从查询转换的角度来看,对于同一条sql语句,反复的变化就能够折腾出不少的东西来。 sql语句如下:

select  soc
  from SERVICE_DETAILS ser  --大表,数据量过亿,做了分区
where  agreement_no in (select subscriber_no
                          from  subscriber  --中级表,数据量百万
                          where sub_status = 'A'
                           and  (subscriber_no, prim_resource_tp) in (select agreement_no, resource_type from  agreement_resource where resource_value='0910600138')  --大表,数据量千万,做了分区
                            )
   and soc_status =  'A'

执行计划如下:

Plan hash value:  1540053094
-----------------------------------------------------------------------------------------------------------------------------------
|  Id  | Operation                                | Name                   | Rows   | Bytes | Cost (%CPU)| Time     | Pstart| Pstop  |
-----------------------------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                         |                        |     7  |   196 |     7  (15)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS                             |                        |       |       |            |           |       |       |
|   2 |   NESTED LOOPS                           |                         |     7 |   196 |     7  (15)| 00:00:01 |       |        |
|   3 |    VIEW                                  | VW_NSO_1               |      1 |    13 |     5   (0)| 00:00:01 |       |       |
|   4 |     HASH  UNIQUE                          |                        |     1 |    36 |             |          |       |       |
|   5 |      NESTED LOOPS                         |                        |       |       |            |          |       |        |
|   6 |       NESTED LOOPS                       |                         |     1 |    36 |     5   (0)| 00:00:01 |       |       |
|   7 |         PARTITION RANGE ALL               |                        |     1 |    25 |      4   (0)| 00:00:01 |     1 |    11 |
|   8 |         TABLE ACCESS BY LOCAL  INDEX ROWID| AGREEMENT_RESOURCE     |     1 |    25 |     4   (0)| 00:00:01 |      1 |    11 |
|*  9 |          INDEX RANGE SCAN                |  AGREEMENT_RESOURCE_1IX |     1 |       |     3   (0)| 00:00:01 |     1 |    11  |
|* 10 |        INDEX UNIQUE SCAN                 | SUBSCRIBER_PK          |      1 |       |     1   (0)| 00:00:01 |       |       |
|* 11 |       TABLE  ACCESS BY INDEX ROWID        | SUBSCRIBER             |     1 |    11 |     1    (0)| 00:00:01 |       |       |
|* 12 |    INDEX RANGE SCAN                       | SERVICE_DETAILS_PK     |     9 |       |     1   (0)| 00:00:01 |       |        |
|* 13 |   TABLE ACCESS BY INDEX ROWID            | SERVICE_DETAILS         |     7 |   105 |     1   (0)| 00:00:01 |       |        |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate  Information (identified by operation  id):
---------------------------------------------------
   9 -  access("RESOURCE_VALUE"='0910600138')
  10 -  access("SUBSCRIBER_NO"="AGREEMENT_NO")
  11 - filter("SUB_STATUS"='A' AND  "PRIM_RESOURCE_TP"="RESOURCE_TYPE")
  12 -  access("AGREEMENT_NO"="SUBSCRIBER_NO")
  13 - filter("SOC_STATUS"='A')

对应的索引信息如下。

INDEX_NAME                      TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST                     TABLE_TYPE STATUS   NUM_ROWS LAST_ANAL  G
------------------------------ ---------- ---------- --------- ---  ------------------------------ ---------- ------ ---------- ---------  -
SERVICE_DETAILS_PK             INDXS01    NORMAL     UNIQUE    NO   AGREEMENT_NO,SOC,SOC_SEQ_NO    TABLE      VALID   117595228 23-OCT-14  N

INDEX_NAME                     TABLESPACE INDEX_TYPE UNIQUENES  PAR COLUMN_LIST                    TABLE_TYPE STATUS   NUM_ROWS LAST_ANAL  G
------------------------------ ---------- ---------- --------- ---  ------------------------------ ---------- ------ ---------- ---------  -
SUBSCRIBER_1IX                 INDXM01    NORMAL     NONUNIQUE NO   CH_NODE_ID                     TABLE      VALID     6994331 23-OCT-14  N
SUBSCRIBER_2IX                 INDXM01    NORMAL     NONUNIQUE NO   EXTERNAL_ID                    TABLE      VALID     6994329 23-OCT-14  N
SUBSCRIBER_3IX                 INDXM01    NORMAL     NONUNIQUE NO   CUSTOMER_ID,SUB_STATUS         TABLE      VALID     6994331 23-OCT-14  N
SUBSCRIBER_PK                  INDXM01    NORMAL     UNIQUE    NO   SUBSCRIBER_NO                  TABLE      VALID     6994331 23-OCT-14  N

INDEX_NAME                     TABLESPACE INDEX_TYPE UNIQUENES  PAR COLUMN_LIST                    TABLE_TYPE STATUS   NUM_ROWS LAST_ANAL  G
------------------------------ ---------- ---------- --------- ---  ------------------------------ ---------- ------ ---------- ---------  -
AGREEMENT_RESOURCE_1IX                    NORMAL     NONUNIQUE YES  RESOURCE_VALUE,RESOURCE_TYPE   TABLE      N/A      25762156 22-OCT-14  N
AGREEMENT_RESOURCE_2IX                    NORMAL     NONUNIQUE YES  FROM_RESOURCE_VAL,RESOURCE_TYPE TABLE      N/A      25762156 22-OCT-14 N                                                                     
AGREEMENT_RESOURCE_3IX                    NORMAL     NONUNIQUE YES  RESOURCE_SCOPE_ID              TABLE      N/A             0 22-OCT-14  N
AGREEMENT_RESOURCE_PK                     NORMAL     UNIQUE    YES  AGREEMENT_NO,RESRC_SEQ_NO,AGREEMENT_KEY  TABLE      N/A      25762156  22-OCT-14 N

这个查询能够从千万级的记录中耗时0.1秒就查出数据,确实是比较理想的。我们来试着折腾一下这条sql语句。 -->改动第一步,从in 切换为exists 我们先从语句的等价性来看,in切换为exists,标黄部分为变化的部分,语句的效果是等价的。

select  soc
  from SERVICE_DETAILS ser
where exists  (select subscriber_no
                           from subscriber
                         where ser.agreement_no=subscriber.subscriber_no
           and sub_status = 'A'
                           and (subscriber_no,  prim_resource_tp) in (select agreement_no, resource_type from agreement_resource  where resource_value='0910600138')
                           )
   and  soc_status = 'A'

但是执行计划就让人大失所望。

Plan hash value:  3038362059
-----------------------------------------------------------------------------------------------------------------------------
|  Id  | Operation                            | Name                  | Rows  |  Bytes | Cost (%CPU)| Time     | Pstart| Pstop  |
------------------------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                     |                       |     7 |    105 |   300M  (1)|999:59:59 |        |       |
|*  1 |  FILTER                              |                        |       |       |            |          |       |       |
|*  2 |   TABLE ACCESS FULL                  |  SERVICE_DETAILS       |    96M|  1384M|   530K  (1)| 01:46:06 |       |        |
|   3 |   NESTED LOOPS                       |                       |      1 |    36 |     3   (0)| 00:00:01 |       |       |
|*  4 |    TABLE ACCESS  BY INDEX ROWID       | SUBSCRIBER            |     1 |    11 |     1   (0)|  00:00:01 |       |       |
|*  5 |     INDEX UNIQUE SCAN                |  SUBSCRIBER_PK         |     1 |       |     1   (0)| 00:00:01 |       |        |
|   6 |    PARTITION RANGE ALL               |                       |      1 |    25 |     2   (0)| 00:00:01 |     1 |    11 |
|*  7 |     TABLE ACCESS  BY LOCAL INDEX ROWID| AGREEMENT_RESOURCE    |     1 |    25 |     2   (0)|  00:00:01 |     1 |    11 |
|*  8 |      INDEX RANGE SCAN                |  AGREEMENT_RESOURCE_PK |     4 |       |     2   (0)| 00:00:01 |     1 |    11  |
------------------------------------------------------------------------------------------------------------------------------
Predicate  Information (identified by operation  id):
---------------------------------------------------
   1 - filter(  EXISTS (SELECT 0 FROM "PRDAPPO"."SUBSCRIBER"  "SUBSCRIBER","PRDAPPO"."AGREEMENT_RESOURCE"
               "AGREEMENT_RESOURCE" WHERE "AGREEMENT_NO"=:B1 AND "RESOURCE_VALUE"='0910600138'  AND
              "PRIM_RESOURCE_TP"="RESOURCE_TYPE" AND  "SUBSCRIBER"."SUBSCRIBER_NO"=:B2 AND "SUB_STATUS"='A'))
   2 -  filter("SOC_STATUS"='A')
   4 - filter("SUB_STATUS"='A')
   5 -  access("SUBSCRIBER"."SUBSCRIBER_NO"=:B1)
   7 -  filter("RESOURCE_VALUE"='0910600138' AND  "PRIM_RESOURCE_TP"="RESOURCE_TYPE")
   8 -  access("AGREEMENT_NO"=:B1)

发现了全表扫描,而且资源消耗很高。时间消耗已经得到了理论的最高值。 可见,在某些场景中,exists和in还是有所不同,在优化器分析的过程中还是对一些细节做了区别处理。稍后来解释。 -->改动第二步,去除in,exists 我们尝试去除语句中的in部分。直接把相关的表放在from之后。查询条件也等价保持不变。这种方法看似没有经过任何的调优,但是从查询转换的角度来说,可能oracle更能够合理的做出分析。

select   ser.soc
  from subscriber sub,agreement_resource agr,SERVICE_DETAILS  ser
where  sub.sub_status = 'A' and sub.subscriber_no=agr.agreement_no and  sub.prim_resource_tp= agr.resource_type  and resource_value='0910600138'
and   ser.agreement_no =sub.subscriber_no
   and ser.soc_status = 'A'  

执行计划如下。

Plan hash value:  3783316108
--------------------------------------------------------------------------------------------------------------------------------
|  Id  | Operation                             | Name                   | Rows  |  Bytes | Cost (%CPU)| Time     | Pstart| Pstop  |
--------------------------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                      |                        |     8 |    408 |     6   (0)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS                          |                        |       |       |            |          |        |       |
|   2 |   NESTED LOOPS                        |                         |     8 |   408 |     6   (0)| 00:00:01 |       |       |
|   3 |     NESTED LOOPS                       |                        |     1 |    36 |      5   (0)| 00:00:01 |       |       |
|   4 |     PARTITION RANGE ALL                |                        |     1 |    25 |     4   (0)| 00:00:01 |     1 |     11 |
|   5 |      TABLE ACCESS BY LOCAL INDEX ROWID| AGREEMENT_RESOURCE      |     1 |    25 |     4   (0)| 00:00:01 |     1 |    11 |
|*  6 |        INDEX RANGE SCAN                | AGREEMENT_RESOURCE_1IX |     1 |       |     3    (0)| 00:00:01 |     1 |    11 |
|*  7 |     TABLE ACCESS BY INDEX ROWID        | SUBSCRIBER             |     1 |    11 |     1   (0)| 00:00:01 |       |        |
|*  8 |      INDEX UNIQUE SCAN                | SUBSCRIBER_PK          |      1 |       |     1   (0)| 00:00:01 |       |       |
|*  9 |    INDEX  RANGE SCAN                   | SERVICE_DETAILS_PK     |     9 |       |     1    (0)| 00:00:01 |       |       |
|* 10 |   TABLE ACCESS BY INDEX ROWID          | SERVICE_DETAILS        |     7 |   105 |     1   (0)| 00:00:01 |       |        |
--------------------------------------------------------------------------------------------------------------------------------
Predicate  Information (identified by operation  id):
---------------------------------------------------
   6 -  access("RESOURCE_VALUE"='0910600138')
   7 - filter("SUB"."SUB_STATUS"='A'  AND "SUB"."PRIM_RESOURCE_TP"="AGR"."RESOURCE_TYPE")
   8 -  access("SUB"."SUBSCRIBER_NO"="AGR"."AGREEMENT_NO")
   9 -  access("SER"."AGREEMENT_NO"="SUB"."SUBSCRIBER_NO")
  10 -  filter("SER"."SOC_STATUS"='A')

-->改动第三步,从视图合并的角度调整 从视图合并的角度来看,可以使用下面的方式进行调整,定义一个子查询,然后再和service_details关联。

select   ser.soc
  from 
(select  subscriber_no
                           from subscriber
                          where sub_status = 'A'
                           and (subscriber_no,  prim_resource_tp) in (select agreement_no, resource_type from agreement_resource  where resource_value='0910600138')) temp,
SERVICE_DETAILS ser
where  agreement_no =temp.subscriber_no
   and soc_status =  'A'

执行计划如下:

Plan hash value:  3927794511
---------------------------------------------------------------------------------------------------------------------------------
|  Id  | Operation                              | Name                   | Rows  |  Bytes | Cost (%CPU)| Time     | Pstart| Pstop  |
---------------------------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                       |                        |     8 |    408 |     7  (15)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS                           |                        |       |       |            |           |       |       |
|   2 |   NESTED LOOPS                         |                         |     8 |   408 |     7  (15)| 00:00:01 |       |       |
|   3  |    NESTED LOOPS                        |                        |     1 |     36 |     6  (17)| 00:00:01 |       |       |
|   4 |     SORT UNIQUE                         |                        |     1 |    25 |     4   (0)| 00:00:01  |       |       |
|   5 |      PARTITION RANGE ALL               |                         |     1 |    25 |     4   (0)| 00:00:01 |     1 |    11 |
|   6  |       TABLE ACCESS BY LOCAL INDEX ROWID| AGREEMENT_RESOURCE     |     1 |     25 |     4   (0)| 00:00:01 |     1 |    11 |
|*  7 |        INDEX RANGE SCAN                 | AGREEMENT_RESOURCE_1IX |     1 |       |     3   (0)| 00:00:01  |     1 |    11 |
|*  8 |     TABLE ACCESS BY INDEX ROWID        | SUBSCRIBER              |     1 |    11 |     1   (0)| 00:00:01 |       |       |
|*  9 |       INDEX UNIQUE SCAN                 | SUBSCRIBER_PK          |     1 |        |     1   (0)| 00:00:01 |       |       |
|* 10 |    INDEX RANGE SCAN                     | SERVICE_DETAILS_PK     |     9 |       |     1   (0)| 00:00:01 |        |       |
|* 11 |   TABLE ACCESS BY INDEX ROWID          |  SERVICE_DETAILS        |     7 |   105 |     1   (0)| 00:00:01 |       |        |
---------------------------------------------------------------------------------------------------------------------------------
Predicate  Information (identified by operation  id):
---------------------------------------------------
   7 -  access("RESOURCE_VALUE"='0910600138')
   8 - filter("SUB_STATUS"='A' AND  "PRIM_RESOURCE_TP"="RESOURCE_TYPE")
   9 -  access("SUBSCRIBER_NO"="AGREEMENT_NO")
  10 -  access("AGREEMENT_NO"="SUBSCRIBER_NO")
  11 -  filter("SOC_STATUS"='A')

-->改动第4步,从子查询解嵌套的角度调整 回到exists的那个问题,执行的时候,exists的性能要比in差很远(在这个例子中,需要视业务场景而定) 我们看看怎么调整让它也快起来。我们加入hint,使得子查询解嵌套被禁用。优先从子查询中先输出数据。

select  soc
  from SERVICE_DETAILS ser
where exists  (select /*+ UNNEST */ subscriber_no
                           from subscriber
                         where  ser.agreement_no=subscriber.subscriber_no
          and sub_status = 'A'
                            and (subscriber_no, prim_resource_tp) in (select  agreement_no, resource_type from agreement_resource where  resource_value='0910600138')
                           )
   and  soc_status = 'A'

执行计划如下。可以看到又得到了我们预期的效果。

Plan hash value:  2142047497
-----------------------------------------------------------------------------------------------------------------------------------
|  Id  | Operation                                | Name                   | Rows   | Bytes | Cost (%CPU)| Time     | Pstart| Pstop  |
-----------------------------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                         |                        |     7  |   196 |     7  (15)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS                             |                        |       |       |            |           |       |       |
|   2 |   NESTED LOOPS                           |                         |     7 |   196 |     7  (15)| 00:00:01 |       |        |
|   3 |    VIEW                                  | VW_SQ_1                |      1 |    13 |     5   (0)| 00:00:01 |       |       |
|   4 |     HASH  UNIQUE                          |                        |     1 |    36 |             |          |       |       |
|   5 |      NESTED LOOPS                         |                        |       |       |            |          |       |        |
|   6 |       NESTED LOOPS                       |                         |     1 |    36 |     5   (0)| 00:00:01 |       |       |
|   7 |         PARTITION RANGE ALL               |                        |     1 |    25 |      4   (0)| 00:00:01 |     1 |    11 |
|   8 |         TABLE ACCESS BY LOCAL  INDEX ROWID| AGREEMENT_RESOURCE     |     1 |    25 |     4   (0)| 00:00:01 |      1 |    11 |
|*  9 |          INDEX RANGE SCAN                |  AGREEMENT_RESOURCE_1IX |     1 |       |     3   (0)| 00:00:01 |     1 |    11  |
|* 10 |        INDEX UNIQUE SCAN                 | SUBSCRIBER_PK          |      1 |       |     1   (0)| 00:00:01 |       |       |
|* 11 |       TABLE  ACCESS BY INDEX ROWID        | SUBSCRIBER             |     1 |    11 |     1    (0)| 00:00:01 |       |       |
|* 12 |    INDEX RANGE SCAN                       | SERVICE_DETAILS_PK     |     9 |       |     1   (0)| 00:00:01 |       |        |
|* 13 |   TABLE ACCESS BY INDEX ROWID            | SERVICE_DETAILS         |     7 |   105 |     1   (0)| 00:00:01 |       |        |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate  Information (identified by operation  id):
---------------------------------------------------
   9 -  access("RESOURCE_VALUE"='0910600138')
  10 -  access("SUBSCRIBER_NO"="AGREEMENT_NO")
  11 - filter("SUB_STATUS"='A' AND  "PRIM_RESOURCE_TP"="RESOURCE_TYPE")
  12 -  access("SER"."AGREEMENT_NO"="ITEM_0")
  13 - filter("SOC_STATUS"='A')

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-11-28

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

深入解析:半连接与反连接的原理和等价改写方法

半连接的原理及等价改写 1. 什么是半连接 当两张表进行关联,只返回匹配上的数据并且只会返回一张的表的数据,半连接一般就是指的在子查询中出现 IN 和 EXI...

3357
来自专栏乐沙弥的世界

NULL 值与索引(一)

    NULL值是关系数据库系统布尔型(true,false,unknown)中比较特殊类型的一种值,通常称为UNKNOWN或空值,即是未知的,不确定的。由...

2702
来自专栏乐沙弥的世界

SQL 基础--> 视图(CREATE VIEW)

视图来源于表,所有对视图数据的修改最终都会被反映到视图的基表中,这些修改必须服从基表的完整性约束,并同样会触发定义

713
来自专栏杨建荣的学习笔记

关于统计信息过期的性能落差(r5笔记第36天)

今天客户反馈某一个应用部署补丁的时候,执行了一个脚本一个多小时还没有执行完。 语句是下面这样的形式。 insert into em1_rater_00068_0...

4166
来自专栏杨建荣的学习笔记

生产环境大型sql语句调优实战第一篇(一) (r2笔记第31天)

在生产环境中有一条sql语句的性能极差,在早晨非高峰时段运行抽取数据,平均要花费40分钟,有时候竟然要跑10个多小时。 sql语句比较长,需要点耐心往下看。我对...

3904
来自专栏杨建荣的学习笔记

循序渐进调优union相关的sql(r2笔记23天)

今天在生产中发现一条sql语句消耗了大量的cpu资源。使用top -c来查看。 PID USER PR NI VIRT RES SHR S...

3706
来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第四篇(r2笔记41天)

生产中有一条sql语句消耗了大量的cpu资源,执行时间在18秒左右, Session:PRODBUSER (1560:61133)SQL ID:1hg2wcua...

2145
来自专栏杨建荣的学习笔记

通过使用hint unnest调优sql语句(r4笔记第38天)

生产环境中有一条sql语句通过sql_monitor看到执行的时间实在是太惊人了,竟然达到了13个小时,而且还没有执行完。 SessionAPPC (20015...

3148
来自专栏杨建荣的学习笔记

执行计划中的COLLECTION ITERATOR PICKLER FETCH导致的性能问题 (r5笔记第49天)

今天开发的同事找到我,让我评估一个sql语句。因为这条语句被应用监控组给抓取出来了,需要尽快进行性能调优。 sql语句比较长,是由几个Union连接起来的子查询...

3605
来自专栏杨建荣的学习笔记

sql_profile的使用(一) (r2笔记29天)

今天看了老熊关于sql_profile的讲解,受益匪浅,自己在本机也做了一通,感觉好记性不如烂笔头还是得多总结总测试才能真正理解。 准备的数据如下,创建两个表,...

2789

扫码关注云+社区

领取腾讯云代金券