ORACLE11G the Connect by statement execution Plan change

From 10.2.0.3 to 11.2.0.4 's friend, if careful you will find that the following SQL performs less efficiently in 11.2.0.4 (this SQL is primarily to obtain access to connection users)
Select Privilege#,level from sysauth$ connect by Grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and Privilege#>0
If you are in touch with an Oracle version and are more careful, you may further find that the SQL in 11.2.0.2 is: select/*+ connect_by_filtering/privilege#, level from Sysauth $ connect by Grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0 that is to say, use/*+ connect_b y_filtering/hint. I'm here to illustrate the problem with a simple test.

In the 11.2.0.4 environment
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;

The PL/SQL process has completed successfully.

Time used: 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>/

Time used: 00:00:00.01

Execution plan
----------------------------------------------------------
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)


Statistical information
----------------------------------------------------------
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
Here you can see that the execution plan used here uses the Connect by NO filtering with Start-with, which is logically read as 7.

In the 10.2.0.3 environment
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
The execution plan is used here for connect by with filtering, and logically read as 4, which is more efficient for this SQL using the connect by with filtering.

It is obvious here that the execution plan for the Connect by query changed from 10g connect by and filtering into 11g connect by NO filtering with SW (UNIQUE), which made the execution plan change. But Oracle has a general feature that when new features are introduced, the new features are typically masked with implicit parameters or event. Here is the exception, we can pass "_optimizer_connect_by_elim_dups" = False and "_ Connect_by_use_union_all "=" Old_plan_mode "to block changes in 11g about connect by execution plan, which restores the execution plan to 10G connect by and Filtering mode
14:30:45 sql> alter session set "_optimizer_connect_by_elim_dups" = false;

The session has changed.

Time used: 00:00:00.00
14:30:46 sql> alter session set "_connect_by_use_union_all" = "Old_plan_mode";

The session has changed.

Time used: 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;

The PL/SQL process has completed successfully.

Time used: 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;

Time used: 00:00:00.01

Execution plan
----------------------------------------------------------
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)


Statistical information
----------------------------------------------------------
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 may be due to the lack of mature connect by the use of hint/*+ connect_by_filtering * * to the fixed execution plan
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>/

Time used: 00:00:00.00

Execution plan
----------------------------------------------------------
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)


Statistical information
----------------------------------------------------------
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) and connect by with filtering, there is no obvious pros and cons, only in a specific case, the actual test, select the appropriate SQL of their own execution plan

Original connection: http://www.xifenfei.com/5585.html have problems can be directly linked to mobile phone (13429648788)