暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / ORACLE数据库获取SQL绑定变量值

ORACLE数据库获取SQL绑定变量值

原创 Leo 2023-05-20
1317

文档课题：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

绑定变量查询oracle
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

相关阅读

【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
824次阅读
2025-04-18 14:18:38
Oracle数据库一键巡检并生成HTML结果，免费脚本速来下载!
陈举超
613次阅读
2025-04-20 10:07:02
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
554次阅读
2025-04-22 00:13:51
【活动】分享你的压箱底干货文档，三篇解锁进阶奖励！
墨天轮编辑部
545次阅读
2025-04-17 17:02:24
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
513次阅读
2025-04-22 00:20:37
一页概览：Oracle GoldenGate
甲骨文云技术
512次阅读
2025-04-30 12:17:56
火焰图--分析复杂SQL执行计划的利器
听见风的声音
480次阅读
2025-04-17 09:30:30
OR+DBLINK的关联SQL优化思路
布衣
389次阅读
2025-05-05 19:28:36
Oracle数据库Hint大全，31个使用案例，速来下载！
陈举超
373次阅读
2025-04-16 21:25:19
Oracle19C低版本一天遭遇两BUG（ORA-04031/ORA-00600）
潇湘秦
343次阅读
2025-04-16 17:05:16