ANBOB™

几年前写过一篇取绑定变量值的笔记 about get bind_data column in v$sql(绑定变量值)， 另外在10.2中提供了 V$SQL_BIND_CAPTURE 视图查询变量值，但是该视图不是所有SQL游标的每次执行变量值，只是当一个新SQL Cursor第一次执行时，每15分钟取得一个采样值，频率是有参数_cursor_bind_capture_interval 控制。如果当前数据库正在执行的一个长SQL出现性能问题，想取得运行时的变量时，在之前的版本中可以使用Errorstack level 3 event dump该会话的cursor信息，从trace文件中取得变量。 在11g r2版本的SQL Monitoring 相关的视图V$SQL_MONITOR.BIND_XML也可以方便的取变量值，这个字段默认是XML格式，有参数_sqlmon_binds_xml_format控制。当然从如果v$sql_monitor取变量的前提是该SQL已经进入sql monitor, 如当前sql使用了parallel或运行时间超过5秒 cpu time or I/O time(有参数_sqlmon_threshold控制)， 下面整理了一个脚本先列出当前正在sql monitor 中执行的sql，交互输入sql id，列出该sqlid 正在执行的sql 变量值和已执行完的变量值，已执行完”status=done”的SQL在该视图中11g中是保留60s, 12c 中为5s ,也许是因为新特性
oracle 12c new feature: Automatic Report Capturing Feature转移到历史表中。

-- Purpose: report sql monitor bind info
-- file: sqlmonitor.sql
-- author: weejar(anbob.com)
-- date: 2017/3/24
-- Note: ORACLE version 11.2+ Supported  ,request Diag+Tuning pack licenses

col sql_text for a60 trunc
col key for 9999999999999
col inst_sid_ser for a13
col username for a24
col mod_action for a32
col R_MB for 9999999
col W_MB for 9999
col px for 99
col program for a30 trunc
col IS_BIND for a8
col sql_id for a15
col username for a10

prompt  Copyright (c) 2017 anbob.com

prompt  Note:SQL Monitoring for SQL statements run more than 5 seconds or use parallel. 'done' state sql keep 60s in 11g, 5s in 12c+
prompt
prompt  list current sql is runing in sql monitor: 

select key, inst_id||'_'||sid||' '||session_serial# inst_sid_ser,username||case when regexp_substr(program,' \(.+') <> ' (TNS V1-V3)' then regexp_substr(program,' \(.+') end username,
sql_id,sql_text,PROGRAM,round(elapsed_time/1000000,1) elaps_s,nvl2(binds_xml,'BIND','NOBIND') IS_BIND
from gv$sql_monitor
where  status='EXECUTING' 
order by inst_id,elapsed_time
/
 
undefine bsqlid
prompt
accept bsqlid prompt 'Please enter monitor sql id :'
 
col key format 999999999999
col sql_exec_start for a25
col sql_text for a60 trunc
col status for a16
col etime for 999999.99
col px for 99
col bname for a30
col bvalue for a30
break on sql_id on plan_hash_value on sql_exec_id on PX on disk_reads on buffer_gets on etime 

select sid, 
username||case when regexp_substr(program,' \(.+') <> ' (TNS V1-V3)' then regexp_substr(program,' \(.+') end username,
sql_id, sql_exec_id, to_char(sql_exec_start,'DD-Mon-YY HH24:MI:SS') sql_exec_start,status, sql_plan_hash_value plan_hash_value,
 elapsed_time/1000000 etime, buffer_gets, disk_reads,PX_SERVERS_ALLOCATED PX,b.bname,
 --b.data_type,
 b.bvalue
 from v$sql_monitor m,xmltable('/binds/bind' passing xmltype(m.BINDS_XML)
                                           columns bname varchar2(30) path '/bind/@name', 
                                                   data_type varchar2(30) path '/bind/@dtystr', 
                                                   bvalue varchar2(4000) path '/bind') b 
 where sql_id='&&bsqlid'
 order by sql_id, sql_exec_id
 /
 

打赏

微信扫一扫，打赏作者吧～