本文总结一下ORACLE数据库中如何获取SQL绑定变量值的方法,在SQL优化调优过程中,经常会用到这方面的知识点。在此梳理、总结一下,方面日后查找、翻阅。
方法1:查询V$SQL
V$SQL视图中的BIND_DATA字段用来存储绑定变量的值,但是从这个视图查询绑定变量的值,有很大的局限性:
1: 它的记录频率受_cursor_bind_capture_interval隐含参数控制,默认值为900,表示每900秒记录一次绑定值,也就是说在900内,绑定变量值的改变不会反应在这个视图中。除非你调整隐含参数_cursor_bind_capture_interval
2: 它记录的仅仅最后一次捕获的绑定变量值。
3:BIND_DATA数据类型为RAW,需要进行转换。
可以使用下面两种方式来查看绑定变量的值。
COL SQL_ID FOR A14;COL SQL_TEXT FOR A32;COL HASH_VALUE FOR 99999999999;COL BIND_DATA FOR A32;SELECT SQL_ID,SQL_TEXT,LITERAL_HASH_VALUE,HASH_VALUE,DBMS_SQLTUNE.EXTRACT_BINDS(BIND_DATA) BIND_DATAFROM V$SQLWHERE SQL_TEXT LIKE ''SELECT * FROM TEST%';COL SQL_ID FOR A14;COL SQL_TEXT FOR A32;COL HASH_VALUE FOR 99999999999;COL BIND_DATA FOR A32;SELECT SQL_ID,SQL_TEXT,LITERAL_HASH_VALUE,HASH_VALUE,DBMS_SQLTUNE.EXTRACT_BIND(BIND_DATA,1).VALUE_STRING BIND_DATAFROM V$SQLWHERE SQL_TEXT LIKE 'SELECT * FROM TEST%';
如下实验所示,我们在一个会话中使用绑定变量的查询SQL语句,然后,我们来尝试获取绑定变量的值,如下所示:
SQL> SHOW USER;USER is "TEST"SQL> DESC TEST;Name Null? Type----------------------------------------- -------- ----------------------------ID NUMBER(10)NAME VARCHAR2(32)SQL>SQL> VARIABLE NAME NVARCHAR2(32);SQL> EXEC :NAME :='KKKK';PL/SQL procedure successfully completed.SQL> SELECT * FROM TEST WHERE NAME=:NAME;no rows selectedSQL>SQL>SHOW USER;USER is "SYS"SQL> COL SQL_ID FOR A14;SQL> COL SQL_TEXT FOR A32;SQL> COL HASH_VALUE FOR 99999999999;SQL> COL BIND_DATA FOR A32;SQL> SELECT SQL_ID2 ,SQL_TEXT3 ,LITERAL_HASH_VALUE4 ,HASH_VALUE5 ,DBMS_SQLTUNE.EXTRACT_BINDS(BIND_DATA) BIND_DATA6 FROM V$SQL7 WHERE SQL_TEXT LIKE 'SELECT * FROM TEST%';SQL_ID SQL_TEXT LITERAL_HASH_VALUE HASH_VALUE BIND_DATA(NAME, POSITION, DUP_PO-------------- -------------------------------- ------------------ ------------ --------------------------------0r7m5jyz9ng09 SELECT * FROM TEST WHERE NAME=:N 0 3197778953 SQL_BIND_SET(SQL_BIND(NULL, 1, NAME ULL, 1, 'NVARCHAR2(128)', 2000,NULL, NULL, 128, '04-SEP-17', 'KKKK', ANYDATA()))SQL> COL SQL_ID FOR A14;SQL> COL SQL_TEXT FOR A32;SQL> COL HASH_VALUE FOR 99999999999;SQL> COL BIND_DATA FOR A32;SQL> SELECT SQL_ID2 ,SQL_TEXT3 ,LITERAL_HASH_VALUE4 ,HASH_VALUE5 ,DBMS_SQLTUNE.EXTRACT_BIND(BIND_DATA,1).VALUE_STRING BIND_DATA6 FROM V$SQL7 WHERE SQL_TEXT LIKE 'SELECT * FROM TEST%';SQL_ID SQL_TEXT LITERAL_HASH_VALUE HASH_VALUE BIND_DATA-------------- -------------------------------- ------------------ ------------ --------------------------------0r7m5jyz9ng09 SELECT * FROM TEST WHERE NAME=:N 0 3197778953 KKKK
如果此时你给变量NAME赋值为kerry,然后你使用上面SQL语句查询,你会发现绑定变量的值依然为"KKKK",这个是因为绑定变量何时被捕获是有一定规律的:
1含有绑定变量的sql语句被硬解析时
2当含有绑定变量的sql语句以软解析或者软软解析方式重复执行时,该SQL语句中的绑定变量的具体输入值也可能被ORACLE捕获,只不过默认情况下这种捕获操作
受隐含参数_cursor_bind_capture_interval影响,默认需要间隔15(900秒)分钟才会做一次
SQL> exec :NAME :='kerry';
PL/SQL procedure successfully completed.
SQL> /
ID NAME
---------- --------------------------------
1000 kerry
SQL>
方法2:查询wrh$_sqlstat
V$SQL中有BIND_DATA字段,当SQL被解析时,就会放到BIND_DATA字段中,最终会被存入wrh$_sqlstat。关于wrh$_sqlstat的介绍如下所示:
wrh$_sqlstatcontains a history for SQL execution statistics and stores snapshots of v$sql view.
wrh$_sqlstat中存储是v$sql的执行统计信息的快照的历史记录,那么从这里可以查询到一些历史绑定变量的值,但是也有可能v$sql的快照信息没有被捕获到(如满足什么条件才会被捕获呢?)。如下截图所示
SQL> select dbms_sqltune.extract_bind(bind_data, 1).value_string2 from wrh$_sqlstat3 where sql_id='0r7m5jyz9ng09';no rows selected
如上测试所示,这个获取绑定变量值的方法有一定的缺陷性,有可能V$SQL快照信息没有被捕获到,导致wrh$_sqlstat
里面查不到对应的信息。
注意,如果有1个的绑定值,可以使用如下查询
select dbms_sqltune.extract_bind(bind_data, 1).value_stringfrom wrh$_sqlstatwhere sql_id = '1t2r2p48w4p0g'
如果有2个绑定值,可以使用如下查询
select dbms_sqltune.extract_bind(bind_data, 1).value_string||''--'||dbms_sqltune.extract_bind(bind_data, 2).value_stringfrom wrh$_sqlstatwhere sql_id = '1t2r2p48w4p0g'
如果有多个绑定变量,使用类似下面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_stringfrom wrh$_sqlstatwhere sql_id = '1t2r2p48w4p0g'/
方法3:v$sql_bind_capture
使用V$SQL_BIND_CAPTURE获取绑定变量的值,也有一些限制:
1、如果STATISTICS_LEVEL设置成BASIC,那绑定变量的捕捉就会关闭(Bind capture is disabled when the STATISTICS_LEVEL initialization parameter is set to BASIC.)
2、默认是900秒捕捉一次绑定变量值,由_cursor_bind_capture_interval参数控制。
3、V$SQL_BIND_CAPTURE视图中记录的绑定变量只对WHERE条件后面的绑定进行捕获,这点需要使用的时候注意。
对于DML操作,V$SQL_BIND_CAPTURE无法获取绑定变量的值。
SQL> COL NAME FOR A12;SQL> COL DATATYPE_STRING FOR A24;SQL> COL VALUE_STRING FOR A32;SQL> SELECT NAME,2 DATATYPE_STRING,3 VALUE_STRING,4 MAX_LENGTH,5 LAST_CAPTURED6 FROM V$SQL_BIND_CAPTURE7 WHERE SQL_ID = '1t2r2p48W4P0g';NAME DATATYPE_STRING VALUE_STRING MAX_LENGTH LAST_CAPT------------ ------------------------ -------------------------------- ---------- ---------:NAME NVARCHAR2(128) KD 128 04-SEP-17SQL>
v$sql_bind_capture视图,可以查看绑定变量,但是这个视图不太给力,只能捕获最后一次记录的绑定变量值。而且两次捕获的间隔也是受隐含参数_cursor_bind_capture_interval控制。默认是900秒后才会重新开始捕获。在900内,绑定变量值的改变不会反应在这个视图中。这个跟v$sql获取变量值是一样的。
SQL> EXEC :NAME :='kerry';
PL/SQL procedure successfully completed.
SQL>/
ID NAME
---------- --------------------------------
1000 kerry
SQL>
如上所示,我给变量赋予新的值,然后重新执行一次,你执行上面SQL,发现绑定变量的值为kerry了,之前的值KD就无法找到了。所以这个也是这个视图的一个局限性。(注意,实验结果结果也有可能是KD,这个取决于实验的时间间隔与隐含参数_cursor_bind_capture_interval的值有关系)
This view can be joined withV$SQLAREAon (HASH_VALUE,ADDRESS) and withV$SQLon (HASH_VALUE,CHILD_ADDRESS).
--SET PAUSE ONSET PAUSE 'Press Return to Continue'SET PAGESIZE 60SET LINESIZE 300COLUMN sql_text FORMAT A120COLUMN sql_id FORMAT A13COLUMN bind_name FORMAT A10COLUMN bind_value FORMAT A26SELECTsql_id,t.sql_text sql_text,b.name bind_name,b.value_string bind_valueFROMv$sql tJOINv$sql_bind_capture b using (sql_id)WHEREb.value_string is not nullANDsql_id='&sqlid'/SELECTb.sql_id,t.sql_text sql_text,b.name bind_name,b.value_string bind_valueFROMv$sql tJOINv$sql_bind_capture b on t.hash_value = b.hash_value and t.child_address = b.child_addressWHEREb.value_string is not nullANDb.sql_id='&sqlid'/
方法4:查询视图DBA_HIST_SQLBIND.
DBA_HIST_SQLBIND是视图V$SQL_BIND_CAPTURE历史快照.所以从视图DBA_HIST_SQLBIND能查到多个绑定变量的值。但是这里依然会遇到一个问题,就是有可能历史快照没有被捕获到DBA_HIST_SQLBIND下。如下测试所示:
SELECT SNAP_ID,NAME,POSITION,VALUE_STRING,LAST_CAPTURED,WAS_CAPTUREDFROM DBA_HIST_SQLBINDWHERE SQL_ID = '&SQL_ID'AND SNAP_ID = &SNAP_ID;SELECT SNAP_ID,NAME,POSITION,VALUE_STRING,LAST_CAPTURED,WAS_CAPTUREDFROM DBA_HIST_SQLBINDWHERE SQL_ID = '&SQL_ID';
SQL> SELECT SNAP_ID,2 NAME,3 POSITION,4 VALUE_STRING,5 LAST_CAPTURED,6 WAS_CAPTURED7 FROM DBA_HIST_SQLBIND8 WHERE SQL_ID = '&SQL_ID';Enter value for sql_id: 1t2r2p48w4p0gold 8: WHERE SQL_ID = '&SQL_ID'new 8: WHERE SQL_ID = '1t2r2p48w4p0g'no rows selectedSQL> exec dbms_workload_repository.create_snapshot();PL/SQL procedure successfully completed.SQL> SELECT SNAP_ID,2 NAME,3 POSITION,4 VALUE_STRING,5 LAST_CAPTURED,6 WAS_CAPTUREDFROM DBA_HIST_SQLBIND7 8 WHERE SQL_ID = '&SQL_ID';Enter value for sql_id: 1t2r2p48w4p0gold 8: WHERE SQL_ID = '&SQL_ID'new 8: WHERE SQL_ID = '1t2r2p48w4p0g'no rows selectedSQL>
方法5:dbms_xplan.display_cursor
sql_id:指定位于库缓存执行计划中SQL语句的父游标。默认值为null。当使用默认值时当前会话的最后一条
SQL语句的执行计划将被返回。可以通过查询V$SQL或V$SQLAREA的SQL_ID列来获得SQL语句的SQL_ID。
child_number指定父游标下子游标的序号。即指定被返回执行计划的SQL语句的子游标。默认值为0。如果为null,
则sql_id所指父游标下所有子游标的执行计划都将被返回。
format控制SQL语句执行计划的输出部分,即哪些可以显示哪些不显示。
select * from table(dbms_xplan.display_cursor('1t2r2p48w4p0g', 0, 'ADVANCED'));-------------------------------------SELECT * FROM TEST WHERE NAME=:NAMEPlan hash value: 1357081020--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 3 (100)| ||* 1 | TABLE ACCESS FULL| TEST | 1 | 31 | 3 (0)| 00:00:01 |--------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------1 - SEL$1 / TEST@SEL$1Outline Data-------------/*+BEGIN_OUTLINE_DATAIGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_FEATURES_ENABLE('11.2.0.1')DB_VERSION('11.2.0.1')ALL_ROWSOUTLINE_LEAF(@"SEL$1")FULL(@"SEL$1" "TEST"@"SEL$1")END_OUTLINE_DATA*/Peeked Binds (identified by position):--------------------------------------1 - :NAME (VARCHAR2(30), CSID=873): 'KKK'Predicate Information (identified by operation id):---------------------------------------------------1 - filter("NAME"=:NAME)Column Projection Information (identified by operation id):-----------------------------------------------------------1 - "TEST"."ID"[NUMBER,22], "NAME"[VARCHAR2,32]
方法6:10046事件捕获绑定变量
altersessionset events '10046 trace name context forever, level 4'; --level=4表示启用SQL_TRACE并捕捉跟踪文件中的绑定变量。
实验在此略过,其实ORACLE中seq$表更新频繁的分析案例中已经展示如何使用10046事件捕获绑定变量的值。另外v$sql,v$sql_bind_capture、dba_hist_sqlbind只能捕获查询SQL(确切的说,只对WHERE条件后面的绑定变量进行捕获)的绑定变量,但10046也能捕获DML的SQL的值
最后如果需要可以通过alter system set "_cursor_bind_capture_interval"=10;修改绑定变量捕获的时间间隔。