`
womendu
  • 浏览: 1474800 次
  • 性别: Icon_minigender_2
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

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

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

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics