cursor_sharing=force強制繫結變數不會把變數值預設當成varchar2型別的理解
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 繫結變數和cursor_sharing變數
- 引用型別變數的強制轉換型別變數
- v$sql_bind_capture與timestamp型別的繫結變數的數值SQLAPT型別變數
- zt_繫結變數和cursor_sharing變數
- 查詢繫結變數的值變數
- 強型別語言變數和資料型別的理解變數資料型別
- JavaScript - 變數、值、型別JavaScript變數型別
- 獲取sql繫結變數的值SQL變數
- ORACLE 獲取繫結變數值Oracle變數
- 如何獲取繫結變數值變數
- SQL*Plus中使用DATE型別的繫結變數SQL型別變數
- 繫結變數變數
- 關於繫結變數的SQL繫結什麼值變數SQL
- 如何用FGA得到繫結變數的值變數
- 如何得到繫結變數的輸入值變數
- 繫結變數之繫結變數窺探(Bind Peeking)變數
- 從不繫結變數與繫結變數兩種情況討論柱狀圖的作用變數
- Oracle 繫結變數Oracle變數
- 表變數和臨時表的差別 （以前把表變數叫成變數表了，哎。。。）變數
- 繫結變數窺測的演變變數
- 如何理解DDD中的不變性，不變數變數
- Oracle 變數繫結與變數窺視合集Oracle變數
- 型別的本質：對變數、型別、指標的理解型別變數指標
- 繫結變數的測試變數
- 檢視繫結變數變數
- 繫結變數窺測變數
- PLSQL使用繫結變數SQL變數
- Oracle之繫結變數Oracle變數
- 關於繫結變數變數
- 淺談變數型別之外的變數命名變數型別
- 利用FORCE_MATCHING_SIGNATURE捕獲非繫結變數SQL變數SQL
- CSS變數的作用域和預設值CSS變數
- 【優化】使用繫結變數 OR 不使用繫結變數，這不是問題！優化變數
- C＃變數型別（1）：引用型別和值型別 (轉)變數型別
- PHP變數型別PHP變數型別
- Java 變數型別Java變數型別
- 變數型別-Set變數型別
- 成員變數、全域性變數、例項變數、類變數、靜態變數和區域性變數的區別變數