DBMS_XPLAN.DISPLAY_CURSOR v$sql bind-data

来源：互联网发布：sendto python buffer 编辑：程序博客网时间：2024/05/25 16:40

看了白鳝的一片关于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()

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