oracle bind value peeking绑定变量窥视

最新推荐文章于 2021-08-31 15:56:38 发布

clg10051

最新推荐文章于 2021-08-31 15:56:38 发布

阅读量114

点赞数

文章标签：数据库

SQL> var a number;
SQL> exec :a:=10;

PL/SQL 过程已成功完成。

SQL> select * from dept where deptno=:a;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        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     |

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)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      |

--------------------------------------------------------------------------------
-------


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")

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):
---------------------------------------------------

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]

已选择51行。

SQL> exec :a:=20;

PL/SQL 过程已成功完成。

SQL> select * from dept where deptno=:a;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

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):
---------------------------------------------------

已选择51行。

sqlplus sys/system@orcl as sysdba
alter system flush shared_pool;
conn scott/system@orcl

SQL> exec :a:=20;

PL/SQL 过程已成功完成。

SQL> select * from dept where deptno=:a;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

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):
---------------------------------------------------

已选择51行。

SQL> spool off

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-749803/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-749803/

clg10051

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle bind value peeking绑定变量窥视

SQL> var a number;SQL> exec :a:=10;PL/SQL 过程已成功完成。SQL> select * from dept where deptno=:a; D...
复制链接

扫一扫