动力论坛源码网站后台地址是什么,求个网站带图片素材,wordpress漏洞检测,企业管理咨询公司排行本文总结一下ORACLE数据库中如何获取SQL绑定变量值的方法#xff0c;在SQL优化调优过程中#xff0c;经常会用到这方面的知识点。在此梳理、总结一下这方面的知识点#xff0c;方面日后查找、翻阅。方法1#xff1a;查询V$SQLV$SQL视图中的BIND_DATA字段用来存储绑定变量的… 本文总结一下ORACLE数据库中如何获取SQL绑定变量值的方法在SQL优化调优过程中经常会用到这方面的知识点。在此梳理、总结一下这方面的知识点方面日后查找、翻阅。方法1查询V$SQLV$SQL视图中的BIND_DATA字段用来存储绑定变量的值但是从这个视图查询绑定变量的值有很大的局限性它的记录频率受_cursor_bind_capture_interval隐含参数控制默认值为900表示每900秒记录一次绑定值也就是说在900内绑定变量值的改变不会反应在这个视图中。除非你调整隐含参数_cursor_bind_capture_interval它记录的仅仅最后一次捕获的绑定变量值。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 TESTSQL 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 SQLSHOW USER;USER is SYSSQL 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_ID 2 ,SQL_TEXT 3 ,LITERAL_HASH_VALUE 4 ,HASH_VALUE 5 ,DBMS_SQLTUNE.EXTRACT_BINDS(BIND_DATA) BIND_DATA 6 FROM V$SQL 7 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, N AME ULL, 1, NVARCHAR2(128), 2000, NULL, NULL, 128, 04-SEP-17, K KKK, 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_ID 2 ,SQL_TEXT 3 ,LITERAL_HASH_VALUE 4 ,HASH_VALUE 5 ,DBMS_SQLTUNE.EXTRACT_BIND(BIND_DATA,1).VALUE_STRING BIND_DATA 6 FROM V$SQL 7 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,这个是因为绑定变量何时被捕获是有一定规律的含有绑定变量的sql语句被硬解析时当含有绑定变量的sql语句以软解析或者软软解析方式重复执行时该SQL语句中的绑定变量的具体输入值也可能被ORACLE捕获只不过默认情况下这种捕获操作 受隐含参数_cursor_bind_capture_interval影响默认需要间隔15(900秒)分钟才会做一次SQL exec :NAME :kerry;PL/SQL procedure successfully completed.SQL / ID NAME---------- -------------------------------- 1000 kerrySQL 方法2查询wrh$_sqlstatV$SQL中有BIND_DATA字段当SQL被解析时就会放到BIND_DATA字段中最终会被存入wrh$_sqlstat。关于wrh$_sqlstat的介绍如下所示:wrh$_sqlstat contains 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_string 2 from wrh$_sqlstat 3 where sql_id0r7m5jyz9ng09;no rows selected如上测试所示这个获取绑定变量值的方法有一定的缺陷性有可能V$SQL快照信息没有被捕获到导致wrh$_sqlstat 里面查不到对应的信息。注意如果有1个的绑定值可以使用如下查询select dbms_sqltune.extract_bind(bind_data, 1).value_string from wrh$_sqlstat where 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如果有多个绑定变量使用类似下面SQLselect 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/方法3v$sql_bind_capture使用V$SQL_BIND_CAPTURE获取绑定变量的值也有一些限制如果STATISTICS_LEVEL设置成BASIC那绑定变量的捕捉就会关闭(Bind capture is disabled when the STATISTICS_LEVEL initialization parameter is set to BASIC.)默认是900秒捕捉一次绑定变量值由_cursor_bind_capture_interval参数控制。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_CAPTURED 6 FROM V$SQL_BIND_CAPTURE 7 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 kerrySQL 如上所示我给变量赋予新的值然后重新执行一次你执行上面SQL发现绑定变量的值为kerry了之前的值KD就无法找到了。所以这个也是这个视图的一个局限性。(注意实验结果结果也有可能是KD这个取决于实验的时间间隔与隐含参数_cursor_bind_capture_interval的值有关系)This view can be joined with V$SQLAREA on (HASH_VALUE, ADDRESS) and with V$SQL on (HASH_VALUE, CHILD_ADDRESS).--SET PAUSE ONSET PAUSE Press Return to ContinueSET PAGESIZE 60SET LINESIZE 300COLUMN sql_text FORMAT A120COLUMN sql_id FORMAT A13COLUMN bind_name FORMAT A10COLUMN bind_value FORMAT A26SELECT sql_id, t.sql_text sql_text, b.name bind_name, b.value_string bind_valueFROM v$sql tJOIN v$sql_bind_capture b using (sql_id)WHERE b.value_string is not nullAND sql_idsqlid/SELECT b.sql_id, t.sql_text sql_text, b.name bind_name, b.value_string bind_valueFROM v$sql tJOIN v$sql_bind_capture b on t.hash_value b.hash_value and t.child_address b.child_addressWHERE b.value_string is not nullAND b.sql_idsqlid/方法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_CAPTURED FROM DBA_HIST_SQLBIND WHERE SQL_ID SQL_ID AND SNAP_ID SNAP_ID; SELECT SNAP_ID, NAME, POSITION, VALUE_STRING, LAST_CAPTURED, WAS_CAPTURED FROM DBA_HIST_SQLBIND WHERE SQL_ID SQL_ID; SQL SELECT SNAP_ID, 2 NAME, 3 POSITION, 4 VALUE_STRING, 5 LAST_CAPTURED, 6 WAS_CAPTURED 7 FROM DBA_HIST_SQLBIND 8 WHERE SQL_ID SQL_ID;Enter value for sql_id: 1t2r2p48w4p0gold 8: WHERE SQL_ID SQL_IDnew 8: WHERE SQL_ID 1t2r2p48w4p0gno 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_CAPTURED FROM DBA_HIST_SQLBIND 7 8 WHERE SQL_ID SQL_ID;Enter value for sql_id: 1t2r2p48w4p0gold 8: WHERE SQL_ID SQL_IDnew 8: WHERE SQL_ID 1t2r2p48w4p0gno rows selectedSQL 方法5dbms_xplan.display_cursorsql_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:NAME Plan 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 / TESTSEL$1 Outline Data------------- /* 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 TESTSEL$1) END_OUTLINE_DATA */ Peeked Binds (identified by position):-------------------------------------- 1 - :NAME (VARCHAR2(30), CSID873): 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]方法610046事件捕获绑定变量alter session set events 10046 trace name context forever, level 4; --level4 表示启用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_interval10; 修改绑定变量捕获的时间间隔。
