生成不同的cursor_child_no

我们可以通过更改statistics_level来模拟

SQL> show parameter level;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_syslog_level                   string      
plsql_optimize_level                 integer     2
statistics_level                     string      TYPICAL

SQL> alter session set statistics_level=typical;
Session altered

SQL> select * from test2 where owner = 'SCOTT';
OWNER                          OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY  NAMESPACE EDITION_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
SCOTT                          PK_DEPT                                                                                                              87107          87107 INDEX               2013-8-24 1 2013-8-24 12: 2013-08-24:12:04:21 VALID   N         N         N                  4 
SCOTT                          DEPT                                                                                                                 87106          87106 TABLE               2013-8-24 1 2013-8-24 12: 2013-08-24:12:04:21 VALID   N         N         N                  1 
SCOTT                          EMP                                                                                                                  87108          87108 TABLE               2013-8-24 1 2014-2-16 14: 2013-08-24:12:04:21 VALID   N         N         N                  1 
SCOTT                          PK_EMP                                                                                                               87109          87109 INDEX               2013-8-24 1 2013-8-24 12: 2013-08-24:12:04:21 VALID   N         N         N                  4 
SCOTT                          BONUS                                                                                                                87110          87110 TABLE               2013-8-24 1 2013-8-24 12: 2013-08-24:12:04:21 VALID   N         N         N                  1 
SCOTT                          SALGRADE                                                                                                             87111          87111 TABLE               2013-8-24 1 2013-8-24 12: 2013-08-24:12:04:21 VALID   N         N         N                  1 
SCOTT                          T                                                                                                                    89011          89011 TABLE               2014-1-24 1 2014-1-24 16: 2014-01-24:16:59:35 VALID   N         N         N                  1 
SCOTT                          ADD_EMPLOYEE                                                                                                         89010                PROCEDURE           2014-1-24 1 2014-1-24 16: 2014-01-24:16:50:21 VALID   N         N         N                  1 
SCOTT                          T_PK                                                                                                                 89012          89012 INDEX               2014-1-24 1 2014-1-24 16: 2014-01-24:16:59:35 VALID   N         N         N                  4 
SCOTT                          Y_UNIQUE                                                                                                             89013          89013 INDEX               2014-1-24 1 2014-1-24 16: 2014-01-24:16:59:35 VALID   N         N         N                  4 
10 rows selected

SQL> alter session set statistics_level=all;
Session altered

SQL> select * from test2 where owner = 'SCOTT';
OWNER                          OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY  NAMESPACE EDITION_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
SCOTT                          PK_DEPT                                                                                                              87107          87107 INDEX               2013-8-24 1 2013-8-24 12: 2013-08-24:12:04:21 VALID   N         N         N                  4 
SCOTT                          DEPT                                                                                                                 87106          87106 TABLE               2013-8-24 1 2013-8-24 12: 2013-08-24:12:04:21 VALID   N         N         N                  1 
SCOTT                          EMP                                                                                                                  87108          87108 TABLE               2013-8-24 1 2014-2-16 14: 2013-08-24:12:04:21 VALID   N         N         N                  1 
SCOTT                          PK_EMP                                                                                                               87109          87109 INDEX               2013-8-24 1 2013-8-24 12: 2013-08-24:12:04:21 VALID   N         N         N                  4 
SCOTT                          BONUS                                                                                                                87110          87110 TABLE               2013-8-24 1 2013-8-24 12: 2013-08-24:12:04:21 VALID   N         N         N                  1 
SCOTT                          SALGRADE                                                                                                             87111          87111 TABLE               2013-8-24 1 2013-8-24 12: 2013-08-24:12:04:21 VALID   N         N         N                  1 
SCOTT                          T                                                                                                                    89011          89011 TABLE               2014-1-24 1 2014-1-24 16: 2014-01-24:16:59:35 VALID   N         N         N                  1 
SCOTT                          ADD_EMPLOYEE                                                                                                         89010                PROCEDURE           2014-1-24 1 2014-1-24 16: 2014-01-24:16:50:21 VALID   N         N         N                  1 
SCOTT                          T_PK                                                                                                                 89012          89012 INDEX               2014-1-24 1 2014-1-24 16: 2014-01-24:16:59:35 VALID   N         N         N                  4 
SCOTT                          Y_UNIQUE                                                                                                             89013          89013 INDEX               2014-1-24 1 2014-1-24 16: 2014-01-24:16:59:35 VALID   N         N         N                  4 
10 rows selected

这样就可以了。来看测试结果

SQL> SELECT sql_text,sql_id,v.CHILD_NUMBER FROM v$sql v WHERE sql_text LIKE '%select * from test2 where owner=%';
SQL_TEXT                                                                         SQL_ID        CHILD_NUMBER
-------------------------------------------------------------------------------- ------------- ------------
select * from test2 where owner='SCOTT'                                          bgh72xjfxj4hh            0
select * from test2 where owner='SCOTT'                                          bgh72xjfxj4hh            1
SELECT * FROM v$sql WHERE sql_text LIKE '%select * from test2 where owner=%'     fk44m72475jcj            0
SELECT sql_text,sql_id,v.CHILD_NUMBER FROM v$sql v WHERE sql_text LIKE '%select  ahk2c85rbyqfy            0

通过sql_id看plan可以看到其中的区别

SQL> select * from table(dbms_xplan.display_cursor(sql_id =>'bgh72xjfxj4hh',cursor_child_no =>0,format =>'ALL ALLSTATS LAST NOTE -projection -Alias'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  bgh72xjfxj4hh, child number 0
-------------------------------------
select * from test2 where owner='SCOTT'
Plan hash value: 300966803
--------------------------------------------------------------------------------
| Id  | Operation         | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Tim
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |      1 |        |       |   347 (100)|
|*  1 |  TABLE ACCESS FULL| TEST2 |      1 |     14 |  2898 |   347   (1)| 00:00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OWNER"='SCOTT')
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement (level=2)
22 rows selected

SQL> select * from table(dbms_xplan.display_cursor(sql_id =>'bgh72xjfxj4hh',cursor_child_no =>1,format =>'ALL ALLSTATS LAST NOTE -projection -Alias'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  bgh72xjfxj4hh, child number 1
-------------------------------------
select * from test2 where owner='SCOTT'
Plan hash value: 300966803
----------------------------------------------------------------------------
| Id  | Operation         | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |        |       |   347 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST2 |     14 |  2898 |   347   (1)| 00:00:05 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OWNER"='SCOTT')
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement (level=2)
   - Warning: basic plan statistics not available. These are only collected when
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system leve
25 rows selected

SQL>