oracle11g中 connect by 语句执行计划改变

从10.2.0.3升级到11.2.0.4的朋友，如果细心会发现，以下sql在11.2.0.4中执行效率变低(该sql主要是获取连接用户获取权限信息)
select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
如果你接触是Oracle版本比较多,而且还比较细心,你可能会进一步发现在11.2.0.2中该条sql是:select /*+ connect_by_filtering */ privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0 也就是说使用了/*+ connect_by_filtering */提示.我这里通过简单测试说明问题.

在11.2.0.4环境中
14:16:19 SQL> set autot trace exp stat
14:16:20 SQL> set time on
14:16:20 SQL> set timing on
14:16:20 SQL> var a1 number;
14:16:20 SQL> exec :a1:=6;
 
PL/SQL 过程已成功完成。
 
已用时间:  00: 00: 00.00
14:16:20 SQL> select privilege#,level from sysauth$ connect by grantee#=prior
14:16:20 SQL> privilege# and privilege#>0 start with grantee#=:a1  and privilege#>0
14:16:22 SQL> /
 
已用时间:  00: 00: 00.01
 
执行计划
----------------------------------------------------------
Plan hash value: 2624122540
 
------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |            |     7 |   182 |     3  (34)| 00:00:01 |
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|            |       |       |            |          |
|   2 |   INDEX FAST FULL SCAN                  | I_SYSAUTH1 |   618 |  4944 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("GRANTEE#"=PRIOR "PRIVILEGE#")
       filter("PRIVILEGE#">0 AND "GRANTEE#"=TO_NUMBER(:A1) AND "PRIVILEGE#">0)
 
 
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        599  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed
这里可以看出来这里使用的执行计划使用了CONNECT BY NO FILTERING WITH START-WITH,逻辑读为7.

10.2.0.3环境中
14:32:57 SQL> set lines 150
14:33:00 SQL> set autot trace exp stat
14:33:01 SQL> set time on
14:33:01 SQL> set timing on
14:33:01 SQL> var a1 number;
14:33:01 SQL> exec :a1:=6;
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.00
14:33:01 SQL> select privilege#,level from sysauth$ connect by grantee#=prior
14:33:01 SQL> privilege# and privilege#>0 start with grantee#=:a1  and privilege#>0  ;
 
Elapsed: 00:00:00.00
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2620769641
 
----------------------------------------------------------------------------------------
| Id  | Operation                 | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |            |     3 |    24 |     2   (0)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING|            |       |       |            |          |
|*  2 |   INDEX RANGE SCAN        | I_SYSAUTH1 |     3 |    24 |     2   (0)| 00:00:01 |
|   3 |   NESTED LOOPS            |            |       |       |            |          |
|   4 |    CONNECT BY PUMP        |            |       |       |            |          |
|*  5 |    INDEX RANGE SCAN       | I_SYSAUTH1 |     3 |    24 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("GRANTEE#"=PRIOR "PRIVILEGE#")
       filter("PRIVILEGE#">0)
   2 - access("GRANTEE#"=TO_NUMBER(:A1) AND "PRIVILEGE#">0)
   5 - access("GRANTEE#"=PRIOR "PRIVILEGE#" AND "PRIVILEGE#">0)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        583  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed
这里执行计划使用的为CONNECT BY WITH FILTERING,而且逻辑读为4,对于这个sql来说,使用CONNECT BY WITH FILTERING执行效率更高.

这里可以很明显的看到:connect by查询的执行计划从10g的CONNECT BY WITH FILTERING变为了11g中的CONNECT BY NO FILTERING WITH SW (UNIQUE)，从而使得执行计划发生改变。但是Oracle一般有个特性,就是当引入新特性之时,一般都会伴随隐含参数或者event来屏蔽新特性.这里也例外,我们可以通过”_optimizer_connect_by_elim_dups” = false和”_connect_by_use_union_all” = “old_plan_mode”来屏蔽11g中关于connect by执行计划的改变,使得执行计划恢复到10G的CONNECT BY WITH FILTERING方式
14:30:45 SQL> alter session set "_optimizer_connect_by_elim_dups" = false;
 
会话已更改。
 
已用时间:  00: 00: 00.00
14:30:46 SQL> alter session set "_connect_by_use_union_all" = "old_plan_mode";
 
会话已更改。
 
已用时间:  00: 00: 00.00
14:30:46 SQL> set autot trace exp stat
14:30:46 SQL> set time on
14:30:46 SQL> set timing on
14:30:46 SQL> var a1 number;
14:30:46 SQL> exec :a1:=6;
 
PL/SQL 过程已成功完成。
 
已用时间:  00: 00: 00.00
14:30:46 SQL> select privilege#,level from sysauth$ connect by grantee#=prior
14:30:46 SQL> privilege# and privilege#>0 start with grantee#=:a1  and privilege#>0 ;
 
已用时间:  00: 00: 00.01
 
执行计划
----------------------------------------------------------
Plan hash value: 2620769641
 
----------------------------------------------------------------------------------------
| Id  | Operation                 | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |            |     2 |    16 |     2   (0)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING|            |       |       |            |          |
|*  2 |   INDEX RANGE SCAN        | I_SYSAUTH1 |     2 |    16 |     2   (0)| 00:00:01 |
|   3 |   NESTED LOOPS            |            |       |       |            |          |
|   4 |    CONNECT BY PUMP        |            |       |       |            |          |
|*  5 |    INDEX RANGE SCAN       | I_SYSAUTH1 |     2 |    16 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("GRANTEE#"=PRIOR "PRIVILEGE#")
       filter("PRIVILEGE#">0)
   2 - access("GRANTEE#"=TO_NUMBER(:A1) AND "PRIVILEGE#">0)
   5 - access("GRANTEE#"=PRIOR "PRIVILEGE#" AND "PRIVILEGE#">0)
 
 
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        599  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed
11.2.0.2中也许是考虑到connect by 不够成熟,因此使用了hint /*+ connect_by_filtering */ 来固定执行计划
14:22:09 SQL> select /*+ connect_by_filtering */ privilege#,level from sysauth$ connect by grantee#=prior
14:22:09 SQL>  privilege# and privilege#>0 start with grantee#=:a1  and privilege#>0
14:22:10 SQL> /
 
已用时间:  00: 00: 00.00
 
执行计划
----------------------------------------------------------
Plan hash value: 2620769641
 
----------------------------------------------------------------------------------------
| Id  | Operation                 | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |            |     7 |   182 |     8  (25)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING|            |       |       |            |          |
|*  2 |   INDEX RANGE SCAN        | I_SYSAUTH1 |     2 |    16 |     2   (0)| 00:00:01 |
|   3 |   NESTED LOOPS            |            |     5 |   105 |     4   (0)| 00:00:01 |
|   4 |    CONNECT BY PUMP        |            |       |       |            |          |
|*  5 |    INDEX RANGE SCAN       | I_SYSAUTH1 |     2 |    16 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("GRANTEE#"=PRIOR "PRIVILEGE#")
       filter("PRIVILEGE#">0)
   2 - access("GRANTEE#"=TO_NUMBER(:A1) AND "PRIVILEGE#">0)
   5 - access("GRANTEE#"="connect$_by$_pump$_002"."prior privilege# " AND
              "PRIVILEGE#">0)
 
 
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        599  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed
CONNECT BY NO FILTERING WITH SW (UNIQUE)和CONNECT BY WITH FILTERING,没有明显的优劣之分,只有在特定的情况下,进行了实际的测试,选择合适自己的sql的执行计划

原文连接:http://www.xifenfei.com/5585.html 有问题可直接联系手机(13429648788)