mysql更改执行计划_11g中 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的执行计划