advanced里显示的peeking到的绑定变量值数据来源于哪里?

48 阅读 0 评论 32 点赞

我是靠谱客的博主义气蚂蚁，最近开发中收集的这篇文章主要介绍advanced里显示的peeking到的绑定变量值数据来源于哪里?，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

数据库:10.2.0.4,这里的数据来自于实际生产环境
原来认为peeking到的绑定变量值来自于v$sql.bind_data,但从下面的实际数据可以知道不是这样的

SQL> select * from table(dbms_xplan.display_cursor('c97wwc7dg3fj1',0,'advanced')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------ SQL_ID c97wwc7dg3fj1, child number 0 ------------------------------------- SELECT ID FROM PRODUCT WHERE CATALOGID = :B2 AND INPUTDATE > :B1 AND ROWNUM=1 ORDER BY INPUTDATE Plan hash value: 2627205153 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 25 (100)| | | 1 | SORT ORDER BY | | 1 | 18 | 25 (8)| 00:00:01 | |* 2 | COUNT STOPKEY | | | | | | |* 3 | VIEW | index$_join$_001 | 15 | 270 | 24 (5)| 00:00:01 | |* 4 | HASH JOIN | | | | | | |* 5 | INDEX RANGE SCAN| IDX_PRODUCT_CBID | 15 | 270 | 4 (25)| 00:00:01 | |* 6 | INDEX RANGE SCAN| IDX_PRODUCT_INPUTDATE | 15 | 270 | 23 (9)| 00:00:01 | ---------------------------------------------------------------------------------------------- .......省略不相关的内容 Peeked Binds (identified by position): -------------------------------------- 1 - :B2 (NUMBER): 2114 --这里的peeking到的绑定变量值显示是2114 2 - :B1 (DATE): 11/23/10 14:35:52 Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(ROWNUM=1) 3 - filter(("CATALOGID"=:B2 AND "INPUTDATE">:B1)) 4 - access(ROWID=ROWID) 5 - access("CATALOGID"=:B2) 6 - access("INPUTDATE">:B1) .......省略不相关的内容 SQL> select sql_id,sql_text,child_number,dbms_sqltune.extract_binds(bind_data) from v$sql where sql_id='c97wwc7dg3fj1'; SQL_ID SQL_TEXT CHILD_NUMBER DBMS_SQLTUNE.EXTRACT_BINDS(BIN ------------- ---------------------------------------------------------------------------------------------------- ------------ ------------------------------ c97wwc7dg3fj1 SELECT ID FROM PRODUCT WHERE CATALOGID = :B2 AND INPUTDATE > :B1 AND ROWNUM=1 ORDER BY INPUTDATE 0 <Object> --上面对象的内容和下面的显示是一致的,只不过一个是raw data,一个是字面显示而已,这里显示是3817 SQL> select sql_id,child_number,name,position,last_captured,value_string from v$sql_bind_capture where sql_id='c97wwc7dg3fj1'; SQL_ID CHILD_NUMBER NAME POSITION LAST_CAPTURED VALUE_STRING ------------- ------------ ------------------------------ ---------- ------------- ----------------- c97wwc7dg3fj1 0 :B2 1 2011-2-23 14: 3817 c97wwc7dg3fj1 0 :B1 2 2011-2-23 14: 02/23/11 14:56:05 --使用display_cursor里显示的peeking到的绑定变量值,得到的执行计划是一致的 SQL> explain plan for SELECT ID FROM PRODUCT WHERE CATALOGID = 2114 AND INPUTDATE > to_date('2010-11-23 14:35:52','yyyy-mm-dd hh24:mi:ss') AND ROWNUM=1 ORDER BY INPUTDATE; Explained SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- Plan hash value: 2627205153 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 18 | 25 (8)| 00:00:01 | | 1 | SORT ORDER BY | | 1 | 18 | 25 (8)| 00:00:01 | |* 2 | COUNT STOPKEY | | | | | | |* 3 | VIEW | index$_join$_001 | 15 | 270 | 24 (5)| 00:00:01 | |* 4 | HASH JOIN | | | | | | |* 5 | INDEX RANGE SCAN| IDX_PRODUCT_CBID | 15 | 270 | 4 (25)| 00:00:01 | |* 6 | INDEX RANGE SCAN| IDX_PRODUCT_INPUTDATE | 15 | 270 | 23 (9)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(ROWNUM=1) 3 - filter("CATALOGID"=2114 AND "INPUTDATE">TO_DATE(' 2010-11-23 14:35:52', 'syyyy-mm-dd hh24:mi:ss')) 4 - access(ROWID=ROWID) 5 - access("CATALOGID"=2114) 6 - access("INPUTDATE">TO_DATE(' 2010-11-23 14:35:52', 'syyyy-mm-dd hh24:mi:ss')) --使用v$sql里给的绑定变量的值,得到的执行计划是不一致的 SQL> explain plan for SELECT ID FROM PRODUCT WHERE CATALOGID = 3817 AND INPUTDATE > to_date('2011-02-23 14:56:05','yyyy-mm-dd hh24:mi:ss') AND ROWNUM=1 ORDER BY INPUTDATE; Explained SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------- Plan hash value: 3097945269 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 18 | 2 (0)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | |* 2 | TABLE ACCESS BY INDEX ROWID| PRODUCT | 1 | 18 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IDX_PRODUCT_INPUTDATE | 1 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM=1) 2 - filter("CATALOGID"=3817) 3 - access("INPUTDATE">TO_DATE(' 2011-02-23 14:56:05', 'syyyy-mm-dd hh24:mi:ss')) --而且在我查看的过程中,v$sql中的绑定变量值是在不断改变的 SQL> select sql_id,sql_text,child_number,dbms_sqltune.extract_binds(bind_data) from v$sql where sql_id='c97wwc7dg3fj1'; SQL_ID SQL_TEXT CHILD_NUMBER DBMS_SQLTUNE.EXTRACT_BINDS(BIN ------------- ---------------------------------------------------------------------------------------------------- ------------ ------------------------------ c97wwc7dg3fj1 SELECT ID FROM PRODUCT WHERE CATALOGID = :B2 AND INPUTDATE > :B1 AND ROWNUM=1 ORDER BY INPUTDATE 0 <Object> SQL> select sql_id,child_number,name,position,last_captured,value_string from v$sql_bind_capture where sql_id='c97wwc7dg3fj1'; SQL_ID CHILD_NUMBER NAME POSITION LAST_CAPTURED VALUE_STRING ------------- ------------ ------------------------------ ---------- ------------- ------------------------------------------ c97wwc7dg3fj1 0 :B2 1 2011-2-23 15: 48350 c97wwc7dg3fj1 0 :B1 2 2011-2-23 15: 02/23/11 15:06:01 SQL> select * from table(dbms_xplan.display_cursor('c97wwc7dg3fj1',0,'advanced')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------- SQL_ID c97wwc7dg3fj1, child number 0 ------------------------------------- SELECT ID FROM PRODUCT WHERE CATALOGID = :B2 AND INPUTDATE > :B1 AND ROWNUM=1 ORDER BY INPUTDATE Plan hash value: 2627205153 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 25 (100)| | | 1 | SORT ORDER BY | | 1 | 18 | 25 (8)| 00:00:01 | |* 2 | COUNT STOPKEY | | | | | | |* 3 | VIEW | index$_join$_001 | 15 | 270 | 24 (5)| 00:00:01 | |* 4 | HASH JOIN | | | | | | |* 5 | INDEX RANGE SCAN| IDX_PRODUCT_CBID | 15 | 270 | 4 (25)| 00:00:01 | |* 6 | INDEX RANGE SCAN| IDX_PRODUCT_INPUTDATE | 15 | 270 | 23 (9)| 00:00:01 | ---------------------------------------------------------------------------------------------- ....省略不相关内容 Peeked Binds (identified by position): -------------------------------------- 1 - :B2 (NUMBER): 2114 ---这里显示的绑定变量值却没有改变 2 - :B1 (DATE): 11/23/10 14:35:52

显然,实际执行计划中显示的peeking到的绑定变量值不是来源于v$sql.bind_data,那它来源于哪里呢?

select * from table(dbms_xplan.display_cursor('c97wwc7dg3fj1',0,'advanced')); 用10046 level 4跟踪得到这样的语句: PARSING IN CURSOR #8 len=553 dep=2 uid=0 oct=3 lid=0 tim=1268020644144506 hv=4162833643 ad='4fff3428' SELECT /*+ opt_param('parallel_execution_enabled', 'false') */ EXTRACTVALUE(VALUE(D), '/bind/@nam'), EXTRACTVALUE(VALUE(D), '/bind/@pos'), EXTRACTVALUE(VALUE(D), '/bind/@ppo'), EXTRACTVALUE(VALUE(D), '/bind/@dty'), EXTRACTVALUE(VALUE(D), '/bind/@csi'), EXTRACTVALUE(VALUE(D), '/bind/@frm'), EXTRACTVALUE(VALUE(D), '/bind/@pre'), EXTRACTVALUE(VALUE(D), '/bind/@scl'), EXTRACTVALUE(VALUE(D), '/bind/@mxl'), EXTRACTVALUE(VALUE(D), '/bind/@captured'), EXTRACTVALUE(VALUE(D), '/bind') FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(:B1 ), '/*/peeked_binds/bind'))) D END OF STMT PARSE #8:c=0,e=76,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=1268020644144502 BINDS #8: kkscoacd Bind#0 oacdty=112 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00 oacflg=01 fl2=1206001 frm=01 csi=31 siz=4000 off=0 kxsbbbfp=2b9ff945a070 bln=4000 avl=40 flg=05 value= Dump of memory from 0x00002B9FF945A070 to 0x00002B9FF945A098 2B9FF945A070 00002600 00002842 00000100 00E47096 [.&..B(.......p..] 2B9FF945A080 01000000 26001F00 00000000 00000000 [.......&........] 2B9FF945A090 FFFF0000 1B00FAC2 [........] EXEC #8:c=2000,e=1712,p=0,cr=3,cu=0,mis=0,r=0,dep=2,og=1,tim=1268020644146273 和peeking到的绑定变量值相关的语句应该是这个.oacdty=112 这个是什么类型的数据呢? 似乎和xmltype相关:

其实想想,一个sql语句的执行计划生成时peeking到的绑定变量值,要么在v$sql中,要么就应该在v$sql_plan,其实它确实是存在于v$sql_plan.other_xml中:

create or replace function display_raw (rawval raw, type varchar2) return varchar2 is cn number; cv varchar2(32); cd date; cc char(32); cnv nvarchar2(32); cr rowid; begin if (type = 'NUMBER') then dbms_stats.convert_raw_value(rawval, cn); return to_char(cn); elsif (type = 'VARCHAR2') then dbms_stats.convert_raw_value(rawval, cv); return cv; elsif (type = 'DATE') then dbms_stats.convert_raw_value(rawval, cd); return to_char(cd,'yyyy-mm-dd hh24:mi:ss'); elsif (type = 'CHAR') then dbms_stats.convert_raw_value(rawval, cc); return cc; elsif (type = 'NVARCHAR2') then dbms_stats.CONVERT_RAW_VALUE_NVARCHAR(rawval, cnv); return to_char(cnv); elsif (type = 'ROWID') then dbms_stats.CONVERT_RAW_VALUE_ROWID(rawval, cr); return RowidToChar(cr); else return 'UNKNOWN DATATYPE'; end if; end; 这个函数以直观可读的形式显示raw data. SELECT NAME,POSITION,DECODE(DATATYPE,2,'NUMBER',12,'DATE') DATATYPE, DISPLAY_RAW(RAW_VALUE,DECODE(DATATYPE,2,'NUMBER',12,'DATE')) Peeked_Bind_VALUE FROM ( SELECT /*+ opt_param('parallel_execution_enabled', 'false') */ EXTRACTVALUE(VALUE(D), '/bind/@nam') name, EXTRACTVALUE(VALUE(D), '/bind/@pos') position, EXTRACTVALUE(VALUE(D), '/bind/@dty') DATATYPE, EXTRACTVALUE(VALUE(D), '/bind/@captured') CAPTURED_DATE, EXTRACTVALUE(VALUE(D), '/bind') RAW_VALUE from xmltable('/*/peeked_binds/bind' passing (select xmltype(other_xml) as xmlval from v$sql_plan where sql_id = 'c97wwc7dg3fj1' and child_number = 0 and other_xml is not null)) d ); NAME POSITION DATATYPE PEEKED_BIND_VALUE ---------- -------- ---------- -------------------- :B2 1 NUMBER 1523 :B1 2 DATE 2010-09-16 17:39:18 我为观察的sql涉及到的对象添加了一个更合理的索引,上面的查询是在添加索引之后执行的,所以和前面的显示值不一样,但和当前的display_cursor中的显示值是一致的.

那么v$sql.bind_data中存储的是什么呢?

这里有一个绑定变量定时捕捉机制,捕捉的时间间隔由_cursor_bind_capture_interval这个隐含参数控制,默认是15分钟捕捉一次 NAME VALUE ISDEFAULT ISMOD ISADJ ------------------------------ ------------------------- --------- ---------- ----- _cursor_bind_capture_interval 900 TRUE FALSE FALSE SQL> col last_captured for a20; SQL> select sql_id,sql_text,child_number,dbms_sqltune.extract_binds(bind_data) from v$sql where sql_id='c97wwc7dg3fj1'; SQL_ID SQL_TEXT CHILD_NUMBER DBMS_SQLTUNE.EXTRACT_BINDS(BIN ------------- ---------------------------------------------------------------------------------------------------- ------------ ------------------------------ c97wwc7dg3fj1 SELECT ID FROM PRODUCT WHERE CATALOGID = :B2 AND INPUTDATE > :B1 AND ROWNUM=1 ORDER BY INPUTDATE 0 <Object> SQL> select sql_id,child_number,name,position,last_captured,value_string from v$sql_bind_capture where sql_id='c97wwc7dg3fj1'; SQL_ID CHILD_NUMBER NAME POSITION LAST_CAPTURED VALUE_STRING ------------- ------------ ------------------------------ ---------- -------------------- -------------------------------------------------------------------------------- c97wwc7dg3fj1 0 :B2 1 2011-2-23 15:32:44 55968 c97wwc7dg3fj1 0 :B1 2 2011-2-23 15:32:44 02/23/11 15:32:34 SQL> select sql_id,sql_text,child_number,dbms_sqltune.extract_binds(bind_data) from v$sql where sql_id='c97wwc7dg3fj1'; SQL_ID SQL_TEXT CHILD_NUMBER DBMS_SQLTUNE.EXTRACT_BINDS(BIN ------------- ---------------------------------------------------------------------------------------------------- ------------ ------------------------------ c97wwc7dg3fj1 SELECT ID FROM PRODUCT WHERE CATALOGID = :B2 AND INPUTDATE > :B1 AND ROWNUM=1 ORDER BY INPUTDATE 0 <Object> SQL> select sql_id,child_number,name,position,last_captured,value_string from v$sql_bind_capture where sql_id='c97wwc7dg3fj1'; SQL_ID CHILD_NUMBER NAME POSITION LAST_CAPTURED VALUE_STRING ------------- ------------ ------------------------------ ---------- -------------------- -------------------------------------------------------------------------------- c97wwc7dg3fj1 0 :B2 1 2011-2-23 15:48:41 118 c97wwc7dg3fj1 0 :B1 2 2011-2-23 15:48:41 05/25/07 16:33:35 SQL> select sql_id,sql_text,child_number,dbms_sqltune.extract_binds(bind_data) from v$sql where sql_id='c97wwc7dg3fj1'; SQL_ID SQL_TEXT CHILD_NUMBER DBMS_SQLTUNE.EXTRACT_BINDS(BIN ------------- ---------------------------------------------------------------------------------------------------- ------------ ------------------------------ c97wwc7dg3fj1 SELECT ID FROM PRODUCT WHERE CATALOGID = :B2 AND INPUTDATE > :B1 AND ROWNUM=1 ORDER BY INPUTDATE 0 <Object> SQL> select sql_id,child_number,name,position,last_captured,value_string from v$sql_bind_capture where sql_id='c97wwc7dg3fj1'; SQL_ID CHILD_NUMBER NAME POSITION LAST_CAPTURED VALUE_STRING ------------- ------------ ------------------------------ ---------- -------------------- -------------------------------------------------------------------------------- c97wwc7dg3fj1 0 :B2 1 2011-2-23 16:06:33 753 c97wwc7dg3fj1 0 :B1 2 2011-2-23 16:06:33 08/11/10 10:19:58 SQL> select sql_id,child_number,name,position,last_captured,value_string from v$sql_bind_capture where sql_id='c97wwc7dg3fj1'; SQL_ID CHILD_NUMBER NAME POSITION LAST_CAPTURED VALUE_STRING ------------- ------------ ---------- ---------- -------------------- ------------------ c97wwc7dg3fj1 0 :B2 1 2011-2-23 16:21:33 821 c97wwc7dg3fj1 0 :B1 2 2011-2-23 16:21:33 07/13/06 09:35:57

至少从我监控的这段时间来看,v$sql.bind_data的数据和v$sql_bind_capture显示的内容是一致的(当然,我确定不了这一点,因为前者数据来源于x$kglcursor_child,而后者数据来源于x$kqlfbc),只不过前者是raw data,后者是它的字面显示而已.从这里的LAST_CAPTURED的时间间隔推测,这两者显示的很可能是绑定变量定时捕捉机制最近一次捕捉到的绑定变量值.上次的值不再保存记录.

说到绑定变量值:dba_hist_sqlstat.bind_data 和dba_hist_sqlbind显示的内容也是一致的,只不过前者是raw data,后者是它的字面显示而已,当然它们都是历史数据.很可能它们记录的只是snap时v$sql.bind_data中的数据而已,也就是说也是定时捕捉机制捕捉到的绑定变量值,应该也不是生成执行计划时peeking到的绑定变量值.当然,这些都是推测,因为我exec dbms_workload_repository.create_snapshot();生成快照时,这两者的相关数据并没有增加数据行.应该来说是这里的这个sql负载达不到被捕捉的标准所以没有被捕捉到,相应的绑定变量数据自然也没有记录下来了. 同样的道理display_awr('advanced') 中显示的peeking到的绑定变量值应该来源于dba_hist_sql_plan.other_xml.

另外:

create or replace function func_zsj_test(v_id number) return varchar2 is v_object_name varchar2(32); begin select object_name into v_object_name from zsj_objs where id=v_id; return v_object_name; end; / variable v_id number; exec :v_id:=1; select func_zsj_test(:v_id) from dual; exec :v_id:=5678; select func_zsj_test(:v_id) from dual; select sql_id,sql_text,bind_data,dbms_sqltune.extract_binds(bind_data) from v$sql s where sql_text like '%func_zsj_test%' AND sql_text not like '%v$sql%'; --bind_data is null select sql_id,name,position,last_captured,value_string from v$sql_bind_capture where sql_id='8mnsupdugszcp'; --last_captured is null,value_string is null --这里只捕获sql语句的绑定变量值,并不捕获过程,函数里的绑定变量值

总结一下:
一个sql语句的执行计划生成时peeking到的绑定变量值保存在v$sql_plan.other_xml中,当然它是以xmltype的形式保存的,要以直观可读的形式显示的话,需要转换一下.awr历史数据的sql的执行计划生成时的绑定变量值保存在dba_hist_sql_plan.other_xml中.
v$sql.bind_data和v$sql_bind_capture显示的内容是一致的,很可能是绑定变量定时捕捉机制最近一次捕捉到的绑定变量值.同理awr中sql的绑定变量定时捕捉机制捕捉到的绑定变量值记录在dba_hist_sqlstat.bind_data 和dba_hist_sqlbind中了.