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)
