标签：bind 变量值 SQL value ---------- orcl sql ORACLE id
文档课题：ORACLE数据库获取SQL绑定变量值.
数据库：oracle 11.2.0.4
1、查v$sql视图
1.1、理论知识
v$sql视图中字段BIND_DATA存储绑定变量值，但从该视图查询存在很大局限性，其记录频率受_cursor_bind_capture_interval隐含参数控制，默认值为900，即每900秒记录一次绑定值，意味着900内绑定变量值的改变不会反应到该视图，其记录的仅仅是最后一次捕获的绑定变量值，除非调整隐含参数_cursor_bind_capture_interval.另外BIND_DATA数据类型为RAW，需要进行转换.
隐含参数的查询.
SYS@orcl> col name for a50
SYS@orcl> col value for a15
SYS@orcl> SELECT nam.ksppinm NAME, val.ksppstvl VALUE FROM x$ksppi nam, x$ksppsv val WHERE nam.indx = val.indx and nam.ksppinm like '%_cursor_bind_capture_interval%';
NAME VALUE
-------------------------------------------------- ---------------
_cursor_bind_capture_interval 900
1.2、实际操作
1.2.1、测试数据
SCOTT@orcl> show user;
USER is "SCOTT"
SCOTT@orcl> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SCOTT@orcl> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------ ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
1.2.2、设置绑定变量
SCOTT@orcl> variable ename varchar2(10);
SCOTT@orcl> exec :ename :='SMITH';
PL/SQL procedure successfully completed.
SCOTT@orcl> set line 200
SCOTT@orcl> select * from emp where ename=:ename;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 2
1.2.3、查询绑定变量
方法a：
SYS@orcl> col sql_id for a14
SYS@orcl> col sql_text for a32
SYS@orcl> col HASH_VALUE FOR 99999999999
SYS@orcl> col bind_data for a32
SYS@orcl> select sql_id,
sql_text,
literal_hash_value,
hash_value,
dbms_sqltune.extract_binds(bind_data) bind_data
from v$sql
where sql_text like 'select * from emp%';
SQL_ID SQL_TEXT LITERAL_HASH_VALUE HASH_VALUE BIND_DATA(NAME, POSITION, DUP_PO
-------------- -------------------------------- ------------------ ------------ --------------------------------
b5d9vztnsvpj4 select * from emp where ename=:e 0 1770903076 SQL_BIND_SET(SQL_BIND(NULL, 1, N
name ULL, 1, 'VARCHAR2(32)', 873, NUL
L, NULL, 32, '19-MAY-23', 'SMITH
', ANYDATA()))
方法b：
SYS@orcl> select sql_id,
sql_text,
literal_hash_value,
hash_value,
dbms_sqltune.extract_bind(bind_data,1).value_string bind_data
from v$sql
where sql_text like 'select * from emp%';
SQL_ID SQL_TEXT LITERAL_HASH_VALUE HASH_VALUE BIND_DATA
-------------- -------------------------------- ------------------ ------------ --------------------------------
b5d9vztnsvpj4 select * from emp where ename=:e 0 1770903076 SMITH
name
1.2.4、测试其它绑定变量值
--此时给ename变量赋值ALLEN，验证查询结果是否有变化.
SCOTT@orcl> exec :ename :='ALLEN';
PL/SQL procedure successfully completed.
SCOTT@orcl> select * from emp where ename=:ename;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------ ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
--查询绑定变量值.
SYS@orcl> select sql_id,
2 sql_text,
literal_hash_value,
hash_value,
dbms_sqltune.extract_binds(bind_data) bind_data
from v$sql
where sql_text like 'select * from emp%';
SQL_ID SQL_TEXT LITERAL_HASH_VALUE HASH_VALUE BIND_DATA(NAME, POSITION, DUP_PO
-------------- -------------------------------- ------------------ ------------ --------------------------------
b5d9vztnsvpj4 select * from emp where ename=:e 0 1770903076 SQL_BIND_SET(SQL_BIND(NULL, 1, N
name ULL, 1, 'VARCHAR2(32)', 873, NUL
L, NULL, 32, '19-MAY-23', 'SMITH
', ANYDATA()))
SYS@orcl> select sql_id,
sql_text,
literal_hash_value,
hash_value,
dbms_sqltune.extract_bind(bind_data,1).value_string bind_data
from v$sql
where sql_text like 'select * from emp%';
SQL_ID SQL_TEXT LITERAL_HASH_VALUE HASH_VALUE BIND_DATA
-------------- -------------------------------- ------------------ ------------ --------------------------------
b5d9vztnsvpj4 select * from emp where ename=:e 0 1770903076 SMITH
name
说明：可以看到绑定变量值依然为"SMITH"，注意此处绑定变量的修改间隔时间未超过900s.
2、查wrh$_sqlstat视图
2.1、理论知识
v$sql中有BIND_DATA字段，当SQL被解析时，就会放到BIND_DATA字段中，最终会被存入wrh$_sqlstat.wrh$_sqlstat存储v$sql的执行统计信息的快照历史记录，从此视图可以查询历史绑定变量的值，但也有可能v$sql的快照信息没有被捕获到，导致wrh$_sqlstat里面查不到对应的信息..(思考：满足什么条件才会被捕获?)
如下所示：
SYS@orcl> select dbms_sqltune.extract_bind(bind_data, 1).value_string
from wrh$_sqlstat
3 where sql_id = 'b5d9vztnsvpj4';
no rows selected
注意：如有多个绑定变量，使用如下sql
select dbms_sqltune.extract_bind(bind_data, 1).value_string
||'-'|| dbms_sqltune.extract_bind(bind_data, 2).value_string
||'-'|| dbms_sqltune.extract_bind(bind_data, 3).value_string
||'-'|| dbms_sqltune.extract_bind(bind_data, 4).value_string
||'-'|| dbms_sqltune.extract_bind(bind_data, 5).value_string
||'-'|| dbms_sqltune.extract_bind(bind_data, 6).value_string
from wrh$_sqlstat
where sql_id = 'b5d9vztnsvpj4';
3、查v$sql_bind_capture
3.1、理论知识
使用v$sql_bind_capture获取绑定变量的值，也存在限制：
a、如果statistics_level设置成basic，那么绑定变量的捕捉就会关闭;
b、默认900秒捕捉一次绑定变量，由_cursor_bind_capture_interval参数控制;
c、v$sql_bind_capture视图中记录的绑定变量只对where条件后面的绑定变量进行捕获，对于dml操作，v$sql_bind_capture无法获取绑定变量的值
3.2、实际操作
col name for a12
col datatype_string for a24
col value_string for a32
select name, datatype_string, value_string, max_length, last_captured
from v$sql_bind_capture
where sql_id = 'b5d9vztnsvpj4';
NAME DATATYPE_STRING VALUE_STRING MAX_LENGTH LAST_CAPTURED
------------ ------------------------ -------------------------------- ---------- ------------------
:ENAME VARCHAR2(32) SMITH 32 20-MAY-23
说明：v$sql_bind_capture视图可查看绑定变量，但只能捕获最后一次记录的绑定变量值，且两次捕获的间隔也受隐含参数由_cursor_bind_capture_interval
控制，默认900秒后才会重新开始捕获，900s内绑定变量值的改变不会反应在该视图，此情况与v$sql获取绑定变量值相同.
3.3、测试_cursor_bind_capture_interval参数
--将_cursor_bind_capture_interval值调小，以便测试.
SYS@orcl> alter system set "_cursor_bind_capture_interval"=10;
System altered.
SCOTT@orcl> exec :ename :='JONES'
PL/SQL procedure successfully completed.
SCOTT@orcl> select * from emp where ename=:ename;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------ ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
--10s钟后执行如下查询.
SYS@orcl> select name, datatype_string, value_string, max_length, last_captured
from v$sql_bind_capture
3 where sql_id = 'b5d9vztnsvpj4';
NAME DATATYPE_STRING VALUE_STRING MAX_LENGTH LAST_CAPTURED
------------ ------------------------ -------------------------------- ---------- ------------------
:ENAME VARCHAR2(32) JONES 32 20-MAY-23
说明：如上所示，在给绑定变量赋予新值，10s后查询发现值更改为"JONES"，此前的"SMITH"无法找到，此为该视图的缺陷.
4、查dba_hist_sqlbind视图
4.1、理论知识
dba_hist_sqlbind是视图v$sql_bind_capture历史快照，从该视图可以查到多个绑定变量的值，但依然会遇到问题，历史快照有可能没有被捕获到dba_hist_sqlbind.
4.2、相关测试
SYS@orcl> select snap_id, name, position, value_string, last_captured, was_captured
from dba_hist_sqlbind
3 where sql_id = '&sql_id';
Enter value for sql_id: b5d9vztnsvpj4
old 3: where sql_id = '&sql_id'
new 3: where sql_id = 'b5d9vztnsvpj4'
no rows selected
SYS@orcl> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
select snap_id, name, position, value_string, last_captured, was_captured
from dba_hist_sqlbind
3 where sql_id = '&sql_id';
Enter value for sql_id: b5d9vztnsvpj4
old 3: where sql_id = '&sql_id'
new 3: where sql_id = 'b5d9vztnsvpj4'
no rows selected
5、查dbms_xplan.display_cursor
5.1、理论知识
sql_id：指定位于库缓存计划中sql语句的父游标，默认值为null.当使用默认值时当前会话的最后一条sql语句的执行计划将被返回，可以通过查询v$sql或v$sqlarea的sql_id列来获得sql语句的sql_id.
child_number：指定父游标下子游标的序号，即指定被返回执行计划的sql语句的子游标，默认值为0，如果为null，则sql_id所指父游标下所有子游标的执行计划都将被返回.
format：控制sql语句执行计划的输出部分.
5.2、实际操作
SYS@orcl> select * from table(dbms_xplan.display_cursor('b5d9vztnsvpj4',0,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID b5d9vztnsvpj4, child number 0
-------------------------------------
select * from emp where ename=:ename
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPT_PARAM('_optimizer_null_aware_antijoin' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
OPT_PARAM('_optimizer_use_feedback' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "EMP"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :ENAME (VARCHAR2(30), CSID=873): 'SMITH'
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"=:ENAME)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMP"."EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10],
"EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22],
"EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22],
"EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
56 rows selected.
6、10046事件捕获绑定变量
alter session set events '10046 trace name context forever, level 4'; --level=4表示启用SQL_TRACE并捕捉跟踪文件中的绑定变量
说明： v$sql,v$sql_bind_capture、dba_hist_sqlbind只能捕获查询SQL，也就是只对WHERE条件后面的绑定变量进行捕获，而10046事件还能捕获DML的绑定变量值.
注意：以上内容基本来自以下网址，笔者仅实际操作过一遍.
参考网址：http://www.taodudu.cc/news/show-1112406.html?action=onClick
标签：bind,
变量值,
SQL,
value,
----------,
orcl,
sql,
ORACLE,
id
From： https://blog.51cto.com/u_12991611/6317606