DBMS_XPLAN.DISPLAY_CURSOR v$sql bind-data

看了白鳝的一片关于v$sql 中bind_data的测试，俺也学习了一下
SQL> select * from v$version where rownum<2;


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL>  alter system flush shared_pool;


System altered.


SQL> var v1 varchar2(2);
SQL> exec :v1:='a';


PL/SQL procedure successfully completed.


SQL> select *  from test11 where id=:v1;


ID        ID1
-- ----------
ID3
---------------------------------------------------------------------------
a           1
11-APR-12 06.34.53.000000 PM




SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  9g1kvnud8041t, child number 0
-------------------------------------
select *  from test11 where id=:v1


Plan hash value: 1550834917


----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST11 |     1 |    29 |     2   (0)| 00:00:01 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------


   1 - SEL$1 / TEST11@SEL$1


Outline Data
-------------


  /*+


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      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")
      FULL(@"SEL$1" "TEST11"@"SEL$1")
      END_OUTLINE_DATA
  */


Peeked Binds (identified by position):


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------


   1 - :V1 (VARCHAR2(30), CSID=852): 'a'--------初次绑定的值


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("ID"=:V1)


Column Projection Information (identified by operation id):
-----------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


   1 - "ID"[VARCHAR2,2], "TEST11"."ID1"[NUMBER,22],
       "TEST11"."ID3"[TIMESTAMP,11]


Note
-----
   - dynamic sampling used for this statement (level=2)




52 rows selected.


SQL> select dbms_sqltune.extract_binds(bind_data) bind from v$sql WHERE SQL_TEXT LIKE '%FROM TEST11%';


BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRE
--------------------------------------------------------------------------------
SQL_BIND_SET()


SQL> select dbms_sqltune.extract_binds(bind_data) bind from v$sql WHERE SQL_TEXT LIKE '%from test11%';


BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRE
--------------------------------------------------------------------------------
SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 852, NULL, NULL, 32, '11
-APR-12', 'a', ANYDATA()))


SQL_BIND_SET()
SQL_BIND_SET()


SQL> exec :v1:='aa';


PL/SQL procedure successfully completed.


SQL> select * from test11 where id=:v1;


ID        ID1
-- ----------
ID3
---------------------------------------------------------------------------
aa         12
12-APR-01 12.00.00.000000 AM




SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  gq117a08wfuy0, child number 0
-------------------------------------
select * from test11 where id=:v1


Plan hash value: 1550834917


----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST11 |     1 |    29 |     2   (0)| 00:00:01 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------


   1 - SEL$1 / TEST11@SEL$1


Outline Data
-------------


  /*+


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      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")
      FULL(@"SEL$1" "TEST11"@"SEL$1")
      END_OUTLINE_DATA
  */


Peeked Binds (identified by position):


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------


   1 - :V1 (VARCHAR2(30), CSID=852): 'aa'------------------最近一次绑定的值


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("ID"=:V1)


Column Projection Information (identified by operation id):
-----------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


   1 - "ID"[VARCHAR2,2], "TEST11"."ID1"[NUMBER,22],
       "TEST11"."ID3"[TIMESTAMP,11]


Note
-----
   - dynamic sampling used for this statement (level=2)




52 rows selected.


SQL> select dbms_sqltune.extract_binds(bind_data) bind from v$sql WHERE SQL_TEXT LIKE '%from test11%';


BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRE
--------------------------------------------------------------------------------
SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 852, NULL, NULL, 32, '11
-APR-12', 'a', ANYDATA()))


SQL_BIND_SET()
SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 852, NULL, NULL, 32, '11
-APR-12', 'aa', ANYDATA()))


SQL_BIND_SET()

把最近几次的值全列出来
结果是跟白鳝的是不一样的，可能是版本不通造成的