cursor_sharing=force绑定变量后,执行计划显示解析的sql对应的变量值加了双引号,很像转换成了varchar2类型,但是实际上并非如此,因为如果转换成了varchar2类型,那字段是number类型,还怎么走索引呢?
select * from salary where user_id=1;
select * from salary where user_id=2;
两条语句时,把user_id的值转换为变量时,解析后的sql执行计划显示user_id=:"SYS_B_0",但是执行计划仍旧走了索引,说明变量值并非真正的转换成了varchar2类型。
关于 cursor_sharing=force 绑定变量 后, 执行计划显示解析的sql对应的变量值加了双引号,盖老师的解释: 就 理解成一个占位符,原来1,100占的长度不同,现在变量替换,内存分配的长度都不同,但是类型属性还在。Oracle会做校验,确保输入类型符合。
手工设置绑定变量时,变量值对应的字段是什么类型则变量值就是什么类型
比如id是number类型,但是使用绑定变量如select * from salary where id=:id,这时不管:id是啥值,比如是5,代入到执行计划,都是把它当成number。
SQL> alter system set cursor_sharing=force;
SQL> create user test identified by 123456;
User created.
SQL> grant dba to test;
Grant succeeded.
SQL> conn test/123456
Connected.
SQL> create table test1 as select * from dba_objects;
Table created.
SQL> create table test2 as select * from dba_objects;
Table created.
SQL> desc test1
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
SQL> create index ind_objectid on test1(OBJECT_ID);
Index created.
SQL> create index ind_objectid2 on test2(OBJECT_ID);
Index created.
SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS ('TEST');
PL/SQL procedure successfully completed.
SQL> alter system flush shared_pool;
System altered.
SQL> set autotrace traceonly
SQL> select * from test1 where OBJECT_ID=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2958957202
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJECTID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=2)
Statistics
----------------------------------------------------------
46 recursive calls
0 db block gets
90 consistent gets
0 physical reads
0 redo size
1611 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from test1 where OBJECT_ID=8994;
Execution Plan
----------------------------------------------------------
Plan hash value: 2958957202
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJECTID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=8994)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1627 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off;
SQL> select sql_id,child_number,sql_text from v$sql where sql_text like '%test1%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ----------------------------------------------------------------------------------------------------
63td7cwx0n43j 0 select * from test1 where OBJECT_ID=: "SYS_B_0"
7749tvrvns89y 0 EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test1 where OBJECT_ID=8994
8utckadc0tfwf 0 EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test1 where OBJECT_ID=2
SQL> select * from table(dbms_xplan.display_cursor('63td7cwx0n43j',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 63td7cwx0n43j, child number 0
-------------------------------------
select * from test1 where OBJECT_ID=: "SYS_B_0"
Plan hash value: 2958957202
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 98 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | IND_OBJECTID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=:SYS_B_0)
19 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('7749tvrvns89y',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7749tvrvns89y, child number 0
EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test1 where
OBJECT_ID=8994
NOTE: cannot fetch plan for SQL_ID: 7749tvrvns89y, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
9 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('8utckadc0tfwf',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8utckadc0tfwf, child number 0
EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test1 where
OBJECT_ID=2
NOTE: cannot fetch plan for SQL_ID: 8utckadc0tfwf, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
9 rows selected.
SQL>
以上说明,在cursor_sharing=force情况下,
select * from test1 where OBJECT_ID=2和select * from test1 where OBJECT_ID=8994没有真实的执行计划
,被解析为select * from test1 where OBJECT_ID=:"SYS_B_0",像转换成了varchar2类型,
但是执行计划仍旧走了索引,说明变量值并非真正的转换成了varchar2类型。
SQL> variable i number;
SQL> exec :i :=100;
PL/SQL procedure successfully completed.
SQL> select * from test2 where OBJECT_ID=:i;
Execution Plan
----------------------------------------------------------
Plan hash value: 2958957202
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJECTID2 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=TO_NUMBER(:I))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1611 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> variable i varchar2(100);
SQL> exec :i :=100;
PL/SQL procedure successfully completed.
SQL> select * from test2 where OBJECT_ID=:i;
Execution Plan
----------------------------------------------------------
Plan hash value: 2958957202
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJECTID2 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=TO_NUMBER(:I))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1611 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> create index ind_objname2 on test2(object_name);
SQL> select * from test2 where object_name='STUDENT';
Execution Plan
----------------------------------------------------------
Plan hash value: 2253262559
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 196 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 2 | 196 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJNAME2 | 2 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='STUDENT')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select * from test2 where object_name='EMP';
Execution Plan
----------------------------------------------------------
Plan hash value: 2253262559
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 196 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 2 | 196 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJNAME2 | 2 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='EMP')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1612 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> variable i varchar2(100);
SQL> exec :i := 'DEPARTMENTS';
PL/SQL procedure successfully completed.
SQL> select * from test2 where object_name=:i;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2253262559
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 196 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 2 | 196 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJNAME2 | 2 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"=:I)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select sql_id,child_number,sql_text from v$sql where sql_text like '%test2%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ----------------------------------------------------------------------------------------------------
546169kbtwbpn 0 select * from test2 where object_name=:"SYS_B_0"
6w5ajr3sa8f6u 0 EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where object_name='STUDENT'
bnh26hasb65tk 0 EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where object_name='EMP'
fgg6b5j2yqfdv 0 EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where object_name=:i
gysqqgu0jaxmg 0 EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where OBJECT_ID=:i
gysqqgu0jaxmg 1 EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where OBJECT_ID=:i
4byjth2n03k8k 0 select * from test2 where OBJECT_ID=:i
4byjth2n03k8k 1 select * from test2 where OBJECT_ID=:i
0q3jgnatqzyyx 0 select * from test2 where object_name=:i
SQL> select * from table(dbms_xplan.display_cursor('546169kbtwbpn',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 546169kbtwbpn, child number 0
-------------------------------------
select * from test2 where object_name=:"SYS_B_0"
Plan hash value: 2857596457
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 2 | 196 | 4 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | IND_OBJNAME2 | 2 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"=:SYS_B_0)
19 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('6w5ajr3sa8f6u',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6w5ajr3sa8f6u, child number 0
EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where
object_name='STUDENT'
NOTE: cannot fetch plan for SQL_ID: 6w5ajr3sa8f6u, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
9 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('bnh26hasb65tk',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bnh26hasb65tk, child number 0
EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where
object_name='EMP'
NOTE: cannot fetch plan for SQL_ID: bnh26hasb65tk, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
9 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('fgg6b5j2yqfdv',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fgg6b5j2yqfdv, child number 0
EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where
object_name=:i
NOTE: cannot fetch plan for SQL_ID: fgg6b5j2yqfdv, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
9 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('gysqqgu0jaxmg',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gysqqgu0jaxmg, child number 0
EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where
OBJECT_ID=:i
NOTE: cannot fetch plan for SQL_ID: gysqqgu0jaxmg, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
9 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('4byjth2n03k8k',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4byjth2n03k8k, child number 0
-------------------------------------
select * from test2 where OBJECT_ID=:i
Plan hash value: 1534018530
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 98 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | IND_OBJECTID2 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access( "OBJECT_ID"=:I)
19 rows selected
SQL> select * from table(dbms_xplan.display_cursor('0q3jgnatqzyyx',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0q3jgnatqzyyx, child number 0
-------------------------------------
select * from test2 where object_name=:i
Plan hash value: 2857596457
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 2 | 196 | 4 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | IND_OBJNAME2 | 2 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"=:I)
19 rows selected.
以上说明,在手工设置绑定变量时, select * from test2 where OBJECT_ID=:i, 被解析为select * from test2where OBJECT_ID=:i ,变量值对应的字段是什么类型则变量值就是什么类型,为什么set autotrace traceonly使用了to_number,是因为sqlplus的工作原理explain plan 的限制,将所有输入作为 varchar ,然后转换
select * from salary where user_id=1;
select * from salary where user_id=2;
两条语句时,把user_id的值转换为变量时,解析后的sql执行计划显示user_id=:"SYS_B_0",但是执行计划仍旧走了索引,说明变量值并非真正的转换成了varchar2类型。
关于 cursor_sharing=force 绑定变量 后, 执行计划显示解析的sql对应的变量值加了双引号,盖老师的解释: 就 理解成一个占位符,原来1,100占的长度不同,现在变量替换,内存分配的长度都不同,但是类型属性还在。Oracle会做校验,确保输入类型符合。
手工设置绑定变量时,变量值对应的字段是什么类型则变量值就是什么类型
比如id是number类型,但是使用绑定变量如select * from salary where id=:id,这时不管:id是啥值,比如是5,代入到执行计划,都是把它当成number。
SQL> alter system set cursor_sharing=force;
SQL> create user test identified by 123456;
User created.
SQL> grant dba to test;
Grant succeeded.
SQL> conn test/123456
Connected.
SQL> create table test1 as select * from dba_objects;
Table created.
SQL> create table test2 as select * from dba_objects;
Table created.
SQL> desc test1
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
SQL> create index ind_objectid on test1(OBJECT_ID);
Index created.
SQL> create index ind_objectid2 on test2(OBJECT_ID);
Index created.
SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS ('TEST');
PL/SQL procedure successfully completed.
SQL> alter system flush shared_pool;
System altered.
SQL> set autotrace traceonly
SQL> select * from test1 where OBJECT_ID=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2958957202
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJECTID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=2)
Statistics
----------------------------------------------------------
46 recursive calls
0 db block gets
90 consistent gets
0 physical reads
0 redo size
1611 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from test1 where OBJECT_ID=8994;
Execution Plan
----------------------------------------------------------
Plan hash value: 2958957202
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJECTID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=8994)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1627 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off;
SQL> select sql_id,child_number,sql_text from v$sql where sql_text like '%test1%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ----------------------------------------------------------------------------------------------------
63td7cwx0n43j 0 select * from test1 where OBJECT_ID=: "SYS_B_0"
7749tvrvns89y 0 EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test1 where OBJECT_ID=8994
8utckadc0tfwf 0 EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test1 where OBJECT_ID=2
SQL> select * from table(dbms_xplan.display_cursor('63td7cwx0n43j',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 63td7cwx0n43j, child number 0
-------------------------------------
select * from test1 where OBJECT_ID=: "SYS_B_0"
Plan hash value: 2958957202
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 98 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | IND_OBJECTID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=:SYS_B_0)
19 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('7749tvrvns89y',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7749tvrvns89y, child number 0
EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test1 where
OBJECT_ID=8994
NOTE: cannot fetch plan for SQL_ID: 7749tvrvns89y, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
9 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('8utckadc0tfwf',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8utckadc0tfwf, child number 0
EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test1 where
OBJECT_ID=2
NOTE: cannot fetch plan for SQL_ID: 8utckadc0tfwf, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
9 rows selected.
SQL>
SQL> variable i number;
SQL> exec :i :=100;
PL/SQL procedure successfully completed.
SQL> select * from test2 where OBJECT_ID=:i;
Execution Plan
----------------------------------------------------------
Plan hash value: 2958957202
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJECTID2 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=TO_NUMBER(:I))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1611 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> variable i varchar2(100);
SQL> exec :i :=100;
PL/SQL procedure successfully completed.
SQL> select * from test2 where OBJECT_ID=:i;
Execution Plan
----------------------------------------------------------
Plan hash value: 2958957202
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJECTID2 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=TO_NUMBER(:I))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1611 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> create index ind_objname2 on test2(object_name);
SQL> select * from test2 where object_name='STUDENT';
Execution Plan
----------------------------------------------------------
Plan hash value: 2253262559
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 196 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 2 | 196 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJNAME2 | 2 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='STUDENT')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select * from test2 where object_name='EMP';
Execution Plan
----------------------------------------------------------
Plan hash value: 2253262559
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 196 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 2 | 196 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJNAME2 | 2 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='EMP')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1612 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> variable i varchar2(100);
SQL> exec :i := 'DEPARTMENTS';
PL/SQL procedure successfully completed.
SQL> select * from test2 where object_name=:i;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2253262559
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 196 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 2 | 196 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJNAME2 | 2 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"=:I)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select sql_id,child_number,sql_text from v$sql where sql_text like '%test2%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ----------------------------------------------------------------------------------------------------
546169kbtwbpn 0 select * from test2 where object_name=:"SYS_B_0"
6w5ajr3sa8f6u 0 EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where object_name='STUDENT'
bnh26hasb65tk 0 EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where object_name='EMP'
fgg6b5j2yqfdv 0 EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where object_name=:i
gysqqgu0jaxmg 0 EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where OBJECT_ID=:i
gysqqgu0jaxmg 1 EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where OBJECT_ID=:i
4byjth2n03k8k 0 select * from test2 where OBJECT_ID=:i
4byjth2n03k8k 1 select * from test2 where OBJECT_ID=:i
0q3jgnatqzyyx 0 select * from test2 where object_name=:i
SQL> select * from table(dbms_xplan.display_cursor('546169kbtwbpn',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 546169kbtwbpn, child number 0
-------------------------------------
select * from test2 where object_name=:"SYS_B_0"
Plan hash value: 2857596457
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 2 | 196 | 4 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | IND_OBJNAME2 | 2 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"=:SYS_B_0)
19 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('6w5ajr3sa8f6u',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6w5ajr3sa8f6u, child number 0
EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where
object_name='STUDENT'
NOTE: cannot fetch plan for SQL_ID: 6w5ajr3sa8f6u, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
9 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('bnh26hasb65tk',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bnh26hasb65tk, child number 0
EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where
object_name='EMP'
NOTE: cannot fetch plan for SQL_ID: bnh26hasb65tk, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
9 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('fgg6b5j2yqfdv',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fgg6b5j2yqfdv, child number 0
EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where
object_name=:i
NOTE: cannot fetch plan for SQL_ID: fgg6b5j2yqfdv, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
9 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('gysqqgu0jaxmg',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gysqqgu0jaxmg, child number 0
EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where
OBJECT_ID=:i
NOTE: cannot fetch plan for SQL_ID: gysqqgu0jaxmg, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
9 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('4byjth2n03k8k',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4byjth2n03k8k, child number 0
-------------------------------------
select * from test2 where OBJECT_ID=:i
Plan hash value: 1534018530
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 98 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | IND_OBJECTID2 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access( "OBJECT_ID"=:I)
19 rows selected
SQL> select * from table(dbms_xplan.display_cursor('0q3jgnatqzyyx',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0q3jgnatqzyyx, child number 0
-------------------------------------
select * from test2 where object_name=:i
Plan hash value: 2857596457
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 2 | 196 | 4 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | IND_OBJNAME2 | 2 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"=:I)
19 rows selected.
以上说明,在手工设置绑定变量时, select * from test2 where OBJECT_ID=:i, 被解析为select * from test2where OBJECT_ID=:i ,变量值对应的字段是什么类型则变量值就是什么类型,为什么set autotrace traceonly使用了to_number,是因为sqlplus的工作原理explain plan 的限制,将所有输入作为 varchar ,然后转换
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30126024/viewspace-2151531/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30126024/viewspace-2151531/