oracle bind value peeking繫結變數窺視
SQL> var a number;
SQL> exec :a:=10;
SQL> exec :a:=10;
PL/SQL 過程已成功完成。
SQL> select * from dept where deptno=:a;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6a2y9jbwu5fz9, child number 0
-------------------------------------
select * from dept where deptno=:a
Plan hash value: 2852011669
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
SQL_ID 6a2y9jbwu5fz9, child number 0
-------------------------------------
select * from dept where deptno=:a
Plan hash value: 2852011669
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | | | 1 (100)|
|
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 19 | 1 (0)| 00:
00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)|
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | | | 1 (100)|
|
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 19 | 1 (0)| 00:
00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|
--------------------------------------------------------------------------------
-------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 /
2 - SEL$1 /
--------------------------------------------------------------------------------
|
--------------------------------------------------------------------------------
-------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 /
2 - SEL$1 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
--------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
INDEX_RS_ASC(@"SEL$1" " ("DEPT"."DEPTNO"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :A (NUMBER): 10
Predicate Information (identified by operation id):
---------------------------------------------------
--------------------------------------------------------------------------------
INDEX_RS_ASC(@"SEL$1" " ("DEPT"."DEPTNO"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :A (NUMBER): 10
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - access("DEPTNO"=:A)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "DEPTNO"[NUMBER,22], "DEPT"."DNAME"[VARCHAR2,14],
"DEPT"."LOC"[VARCHAR2,13]
2 - "DEPT".ROWID[ROWID,10], "DEPTNO"[NUMBER,22]
--------------------------------------------------------------------------------
2 - access("DEPTNO"=:A)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "DEPTNO"[NUMBER,22], "DEPT"."DNAME"[VARCHAR2,14],
"DEPT"."LOC"[VARCHAR2,13]
2 - "DEPT".ROWID[ROWID,10], "DEPTNO"[NUMBER,22]
已選擇51行。
SQL> exec :a:=20;
PL/SQL 過程已成功完成。
SQL> select * from dept where deptno=:a;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
---------- -------------- -------------
20 RESEARCH DALLAS
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6a2y9jbwu5fz9, child number 0
-------------------------------------
select * from dept where deptno=:a
Plan hash value: 2852011669
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
SQL_ID 6a2y9jbwu5fz9, child number 0
-------------------------------------
select * from dept where deptno=:a
Plan hash value: 2852011669
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | | | 1 (100)|
|
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 19 | 1 (0)| 00:
00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)|
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | | | 1 (100)|
|
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 19 | 1 (0)| 00:
00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|
--------------------------------------------------------------------------------
-------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 /
2 - SEL$1 /
--------------------------------------------------------------------------------
|
--------------------------------------------------------------------------------
-------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 /
2 - SEL$1 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
--------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
INDEX_RS_ASC(@"SEL$1" " ("DEPT"."DEPTNO"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :A (NUMBER): 10 --小結：說明對於繫結變數，再次執行繫結變數的值可能還是首次執行繫結變數的值，這樣可能選擇差的執行計劃
Predicate Information (identified by operation id):
---------------------------------------------------
--------------------------------------------------------------------------------
INDEX_RS_ASC(@"SEL$1" " ("DEPT"."DEPTNO"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :A (NUMBER): 10 --小結：說明對於繫結變數，再次執行繫結變數的值可能還是首次執行繫結變數的值，這樣可能選擇差的執行計劃
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - access("DEPTNO"=:A)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "DEPTNO"[NUMBER,22], "DEPT"."DNAME"[VARCHAR2,14],
"DEPT"."LOC"[VARCHAR2,13]
2 - "DEPT".ROWID[ROWID,10], "DEPTNO"[NUMBER,22]
--------------------------------------------------------------------------------
2 - access("DEPTNO"=:A)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "DEPTNO"[NUMBER,22], "DEPT"."DNAME"[VARCHAR2,14],
"DEPT"."LOC"[VARCHAR2,13]
2 - "DEPT".ROWID[ROWID,10], "DEPTNO"[NUMBER,22]
已選擇51行。
sqlplus as sysdba
alter system flush shared_pool;
conn
alter system flush shared_pool;
conn
SQL> exec :a:=20;
PL/SQL 過程已成功完成。
SQL> select * from dept where deptno=:a;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
---------- -------------- -------------
20 RESEARCH DALLAS
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6a2y9jbwu5fz9, child number 0
-------------------------------------
select * from dept where deptno=:a
Plan hash value: 2852011669
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
SQL_ID 6a2y9jbwu5fz9, child number 0
-------------------------------------
select * from dept where deptno=:a
Plan hash value: 2852011669
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | | | 1 (100)|
|
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 19 | 1 (0)| 00:
00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)|
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | | | 1 (100)|
|
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 19 | 1 (0)| 00:
00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|
--------------------------------------------------------------------------------
-------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 /
2 - SEL$1 /
--------------------------------------------------------------------------------
|
--------------------------------------------------------------------------------
-------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 /
2 - SEL$1 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
--------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
INDEX_RS_ASC(@"SEL$1" " ("DEPT"."DEPTNO"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :A (NUMBER): 20 --小結：重新解析sql會運用繫結變數最新的值
Predicate Information (identified by operation id):
---------------------------------------------------
--------------------------------------------------------------------------------
INDEX_RS_ASC(@"SEL$1" " ("DEPT"."DEPTNO"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :A (NUMBER): 20 --小結：重新解析sql會運用繫結變數最新的值
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - access("DEPTNO"=:A)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "DEPTNO"[NUMBER,22], "DEPT"."DNAME"[VARCHAR2,14],
"DEPT"."LOC"[VARCHAR2,13]
2 - "DEPT".ROWID[ROWID,10], "DEPTNO"[NUMBER,22]
--------------------------------------------------------------------------------
2 - access("DEPTNO"=:A)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "DEPTNO"[NUMBER,22], "DEPT"."DNAME"[VARCHAR2,14],
"DEPT"."LOC"[VARCHAR2,13]
2 - "DEPT".ROWID[ROWID,10], "DEPTNO"[NUMBER,22]
已選擇51行。
SQL> spool off
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-749803/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 繫結變數之繫結變數窺探(Bind Peeking)變數
- 繫結變數和BIND PEEKING變數
- oracle繫結變數窺視(zt)Oracle變數
- Oracle 變數繫結與變數窺視合集Oracle變數
- 繫結變數、BIND PEEKING、histogram(柱狀圖)的使用變數Histogram
- 【原創】Oracle 變數繫結與變數窺視合集Oracle變數
- Oracle 繫結變數窺探Oracle變數
- oracle 繫結變數（bind variable）Oracle變數
- 繫結變數、BIND PEEKING、histogram(柱狀圖)的使用(zt)變數Histogram
- Event 10053 執行計劃 繫結變數 Bind peeking變數
- 繫結變數窺視測試案例變數
- 繫結變數的正確使用，bind peeking，Adaptive Cursor Sharing變數APT
- 繫結變數窺測變數
- 如何解決執行計劃繫結變數bind peeking重新硬解析變數
- Oracle繫結變數分級（Bind Graduation）Oracle變數
- [SQL]oracle 繫結變數(bind variable)[轉載]SQLOracle變數
- 繫結變數窺測的演變變數
- oracle 11g 新特性之動態繫結變數窺視(二)Oracle變數
- oracle 11g 新特性之動態繫結變數窺視(一)Oracle變數
- 索引失效系列——繫結變數引起的peeking索引變數
- 繫結變數優缺點、使用、繫結變數窺探、 Oracle自適應共享遊標變數Oracle
- 10g繫結變數窺探變數
- 【SQL 調優】繫結變數窺測SQL變數
- 透過v$sql_bind_capture 檢視繫結變數。SQLAPT變數
- 通過v$sql_bind_capture 檢視繫結變數。SQLAPT變數
- Oracle 繫結變數Oracle變數
- 檢視繫結變數變數
- v$sql_bind_capture 獲取繫結變數SQLAPT變數
- Oracle之繫結變數Oracle變數
- v$sql_bind_capture 獲取繫結變數資訊SQLAPT變數
- ORACLE 繫結變數用法總結Oracle變數
- Oracle 中bind peeking的處理Oracle
- Oracle 繫結變數 詳解Oracle變數
- 【ORACLE】Oracle繫結變數知識梳理Oracle變數
- ORACLE 獲取繫結變數值Oracle變數
- oracle繫結變數的測試Oracle變數
- 檢視未繫結變數的sql變數SQL
- 繫結變數變數