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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 強型別語言變數和資料型別的理解變數資料型別
- JavaScript - 變數、值、型別JavaScript變數型別
- 如何用FGA得到繫結變數的值變數
- [20200326]繫結變數抓取與NULL值.txt變數Null
- [20191213]共享池繫結變數的值在哪裡.txt變數
- 型別的本質：對變數、型別、指標的理解型別變數指標
- 淺談變數型別之外的變數命名變數型別
- CSS變數的作用域和預設值CSS變數
- PLC結構化文字（ST）——變數型別和變數屬性變數型別
- Python中類變數、成員變數、區域性變數的區別Python變數
- PHP變數型別PHP變數型別
- Java 變數型別Java變數型別
- 變數型別-Set變數型別
- 成員變數、全域性變數、例項變數、類變數、靜態變數和區域性變數的區別變數
- c++中的變數型別_C ++中的變數C++變數型別
- JavaScript變數型別檢測總結JavaScript變數型別
- js基本語法之 值型別(資料型別)(變數型別)JS資料型別變數
- [20191216]共享池繫結變數的值在哪裡2.txt變數
- [20211227]抽取跟蹤檔案中的繫結變數值.txt變數
- 修改全域性變數時，可變型別和不可變型別的區別變數型別
- [20180930]in list與繫結變數個數.txt變數
- [20210120]in list與繫結變數個數.txt變數
- Python3學習 (變數+值型別+引用型別+列表的可變+元組的不可變+運算子號)Python變數型別
- 在繫結變數下使用outline變數
- MySQL高階特性——繫結變數MySql變數
- [20180930]in list與繫結變數.txt變數
- [20180912]PLSLQ與繫結變數.txt變數
- Vue select 繫結動態變數Vue變數
- 資料型別，變數資料型別變數
- 變數型別轉換變數型別
- Go：變數宣告的多種方法與預設值Go變數
- 當char型變數遇上char*型的指標變數指標
- C++變數總結束 | 輸出各種變數的值C++變數
- Rust的變數型別__Data typeRust變數型別
- Shell-變數高階用法 (3) 有型別變數變數型別
- JS中的變數賦值深入理解JS變數賦值
- 自己對Java中if變數賦值的理解Java變數賦值
- C++教程-----C++變數型別和變數的定義C++變數型別