生成不同的cursor_child_no
我们可以通过更改statistics_level来模拟SQL> show parameter level;NAMETYPEVALUE------------------------------------ ----------- ------------------------------audit_sys
·
我们可以通过更改statistics_level来模拟
SQL> show parameter level;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_syslog_level string
plsql_optimize_level integer 2
statistics_level string TYPICALSQL> 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> 
汇聚全球AI编程工具，助力开发者即刻编程。
更多推荐
0
0- 0
已为社区贡献3条内容
所有评论(0)