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 / DEPT@SEL$1
2 - SEL$1 / DEPT@SEL$1
--------------------------------------------------------------------------------
|
--------------------------------------------------------------------------------
-------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DEPT@SEL$1
2 - SEL$1 / DEPT@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"@"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"@"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 / DEPT@SEL$1
2 - SEL$1 / DEPT@SEL$1
--------------------------------------------------------------------------------
|
--------------------------------------------------------------------------------
-------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DEPT@SEL$1
2 - SEL$1 / DEPT@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"@"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"@"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 / DEPT@SEL$1
2 - SEL$1 / DEPT@SEL$1
--------------------------------------------------------------------------------
|
--------------------------------------------------------------------------------
-------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DEPT@SEL$1
2 - SEL$1 / DEPT@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"@"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"@"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博客 ” ，链接：https://blog.itpub.net/9240380/viewspace-749803/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- redhat要持续学习哟加加油
- 升级redhat 6.8 默认gdb 7.2到gdb 8.3.1操作指南
- redhat 6.8升级默认cmake 2.8到cmake 3.9
- mysql source code源代安装的前提条件requirement
- mysql handler语句之一
- mysql show open tables相关知识体系之一
- mysql snapshot read快照读及current read当前读与锁lock之一
- mysql 5.7 _rowid虚列之一
- mysql innodb lock锁之record lock之一
- mysql隐式提交事务transaction一点笔记
