sql Bind variable ....
http://anuj-singh.blogspot.com/2021/02/sql-report.html
define sqlid='aca4xvmz0rzup'
set linesize 300 pagesize 300
col name for a10
col value_string for a50
col datatype_string for a50
col bind_value for a20
col bind_name for a20
col sql_text for a50 wrap
col is_bind_sensitive for a15
col is_bind_aware for a15
select
b.sql_id,
b.hash_value,
USERS_EXECUTING,
is_bind_sensitive,
is_bind_aware,
b.last_captured,
b.name bind_name,
b.value_string bind_value,
t.sql_text sql_text
from gv$sql t , gv$sql_bind_capture b
where 1=1
--and b.value_string is not null
and t.sql_id=b.sql_id
and t.inst_id=b.inst_id
AND b.sql_id='&sqlid'
/
define sqlid='0za9fv0j1vgkk'
set linesize 300 pagesize 300
col name for a10
col value_string for a50
col datatype_string for a50
col bind_value for a20
col bind_name for a20
col sql_text for a50 wrap
col is_bind_sensitive for a15
col is_bind_aware for a15
select
b.sql_id,
b.hash_value,
b.last_captured,
b.name bind_name,
b.value_string bind_value,
t.sql_text sql_text
from DBA_HIST_SQLTEXT t , gv$sql_bind_capture b
where 1=1
--and b.value_string is not null
and t.sql_id=b.sql_id
AND b.sql_id='&sqlid'
/
select sh.snap_id, sql.sql_id, sql.name,sql.NAME from DBA_HIST_SQLBIND sql , DBA_HIST_SNAPSHOT sh
where 1=1
and SQL_ID='&SQLID'
and sql.snap_id=sh.snap_id
and sql.instance_number=sh.instance_number
-- and sq.instance_number='&INST_NO' -- For info from a specific instance
-- and snap_id = '&SNAP_ID' -- For a specific snap id.
--and snap_id between '&BEGIN_SNAP' and '&END_SNAP' -- For a range of snap id.
-- and sh.begin_interval_time between to_date('12/12/2021 10:00,'DD/MM/YYYY HH24:MI') and to_date('12/12/2021 12:00,'DD/MM/YYYY HH24:MI')
;
SNAP_ID SQL_ID NAME NAME
---------- ------------- ---------- ----------
56650 0za9fv0j1vgkk :B7 :B7
56650 0za9fv0j1vgkk :B6 :B6
56650 0za9fv0j1vgkk :B5 :B5
56650 0za9fv0j1vgkk :B21 :B21
56650 0za9fv0j1vgkk :B21 :B21
56650 0za9fv0j1vgkk :B27 :B27
56650 0za9fv0j1vgkk :B11 :B11
56650 0za9fv0j1vgkk :B10 :B10
56650 0za9fv0j1vgkk :B34 :B34
5665
====== set line 200 pagesize 300 define 1='g6abypytc0004' col username format a15 col sid format 9999 col datatype_string format a15 head 'DATA TYPE' col child_nume format 999999 head 'CHILD|NUMBER' col position format 999 head 'POS' col name format a20 col value_string format a40 col bind_string format a40 col type_name format a15 var v_sql_id varchar2(30) exec :v_sql_id := '&1' break on inst_id on child_address on child_number on plan_hash_value with plans as ( select distinct inst_id, address, sql_id, child_address, child_number, plan_hash_value from gv$sql_plan where sql_id = :v_sql_id ) select b.inst_id , b.child_address , b.child_number , p.plan_hash_value , b.position , b.name , b.value_string , anydata.GETTYPENAME(b.value_anydata) type_name -- use the anydata values as they are sometimes more reliable dependent on oracle version , case anydata.GETTYPENAME(b.value_anydata) when 'SYS.VARCHAR' then anydata.accessvarchar(b.value_anydata) when 'SYS.VARCHAR2' then anydata.accessvarchar2(b.value_anydata) when 'SYS.CHAR' then anydata.accesschar(b.value_anydata) when 'SYS.DATE' then to_char(anydata.accessdate(b.value_anydata),'yyyy-mm-dd hh24:mi:ss') when 'SYS.TIMESTAMP' then to_char(anydata.accesstimestamp(b.value_anydata),'yyyy-mm-dd hh24:mi:ss') when 'SYS.NUMBER' then to_char(anydata.accessnumber(b.value_anydata)) end bind_string from GV$SQL_BIND_CAPTURE b join plans p on p.address = b.address and p.inst_id = b.inst_id and p.sql_id = b.sql_id and p.child_address = b.child_address and p.child_number = b.child_number where b.sql_id = :v_sql_id order by b.inst_id, b.child_address, b.child_number, b.position / undef 1
COL binds_begin_time FOR A25
COL binds_instance_number HEAD INST FOR 9999
COL binds_name HEAD NAME FOR A15
COL binds_value_string HEAD VALUE_STRING FOR A100 WRAP
COL binds_position HEAD POS FOR 9999
COL binds_dup_position HEAD DPOS FOR 9999
SELECT
sn.begin_interval_time binds_begin_time
, sn.dbid
, sn.instance_number binds_instance_number
, sb.sql_id
, sb.name binds_name
, sb.position binds_position
, sb.dup_position binds_dup_position
, sb.datatype_string
-- , sb.character_sid
-- , sb.precision
-- , sb.scale
, sb.was_captured
, sb.last_captured
, sb.value_string binds_value_string
FROM
dba_hist_snapshot sn
, dba_hist_sqlbind sb
WHERE
sn.snap_id = sb.snap_id
AND sn.dbid = sb.dbid
AND sn.instance_number = sb.instance_number
AND sb.sql_id = '&1'
AND &2
AND begin_interval_time >= &3
AND end_interval_time <= &4
ORDER BY
sn.begin_interval_time
, sn.dbid
, sn.instance_number
, sb.sql_id
, sb.name
, sb.position
/
==============================
set linesize 500 pagesize 300
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
define SQL_ID='XXXXXXX'
col sql_id format a13
col name format a12 trunc
col pos format 999
col dtyp format 99999
col dtyps format a15
col lcap format a17
col value_string format a40
col snapid format 999999
select sql_id
,snap_id snapid
,to_char(last_captured,'YYMMDD HH24:MI:SS') lcap
,name
,position pos
,datatype dtyp
,datatype_string dtyps
,value_string
from DBA_HIST_SQLBIND
where SQL_ID ='&&sql_id'
--and SNAP_ID between bsnap and esnap
and snap_id BETWEEN :BgnSnap AND :EndSnap
order by snap_id, last_captured, position
;
============
https://unbeta.wordpress.com/2018/08/15/gather-latest-captured-bind-values/
set lines 500 verify off serveroutput on serveroutput on format wrapped feedback off
define SQL_ID='gqkr2um43ga39'
define HISTORY=1000
declare
v_date date;
v_snap_id number;
v_name varchar2(30);
v_datatype varchar2(15);
v_captured varchar2(8);
v_value_string varchar2(4000);
begin
-- for v_counter in 0..&HISTORY-
for v_counter in 0..2
loop
SELECT last_captured
INTO v_date
FROM (SELECT last_captured, ROWNUM AS idx
FROM ( SELECT /*+ NO_QUERY_TRANSFORMATION(DBA_HIST_SQLBIND) */ last_captured
FROM dba_hist_sqlbind
WHERE sql_id = '&SQL_ID' AND last_captured IS NOT NULL
ORDER BY last_captured DESC))
WHERE idx = v_counter + 1;
SELECT MAX (snap_id)
INTO v_snap_id
FROM dba_hist_sqlbind
WHERE sql_id = '&SQL_ID' AND last_captured = v_date;
if v_date IS NOT NULL
then
v_name := 'Bind Name';
v_datatype := 'Type';
v_captured := 'Captured';
v_value_string := 'Value';
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE(' SQL ID: ' || '&SQL_ID');
DBMS_OUTPUT.PUT_LINE(' Capture #: ' || TO_CHAR(v_counter+1));
DBMS_OUTPUT.PUT_LINE('Capture Date: ' || TO_CHAR(v_date, 'YYYY-MM-DD HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE(' Snap ID: ' || v_snap_id);
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE(' ' || rpad(v_name,30) || ' ' || rpad(v_datatype,15) || ' ' || rpad(v_captured,8) || ' ' || v_value_string);
DBMS_OUTPUT.PUT_LINE(' ' || rpad('-',30,'-') || ' ' || rpad('-',15,'-') || ' ' || rpad('-',8,'-') || ' ' || rpad('-',24,'-'));
end if;
for bind in (select distinct name from dba_hist_sqlbind where sql_id='&SQL_ID' order by 1) loop
select distinct name, datatype_string, was_captured, value_string into v_name, v_datatype, v_captured, v_value_string from (
select name, datatype_string, was_captured, value_string
from dba_hist_sqlbind
where sql_id='&SQL_ID'
and name = bind.name
and last_captured = v_date and snap_id = v_snap_id
union all
select name, datatype_string, was_captured, value_string
from dba_hist_sqlbind
where sql_id='&SQL_ID'
and name = bind.name
and last_captured is null
and snap_id = v_snap_id)
where rownum < 2;
DBMS_OUTPUT.PUT_LINE(' ' || rpad(v_name,30) || ' ' || rpad(v_datatype,15) || ' ' || rpad(v_captured,8) || ' ' || v_value_string);
end loop;
end loop;
end;
/
====================
set linesize 400 pagesize 300
define sql_id='f705bwx3q0ydq'
col KEY1 for a15
col KEY2 for a15
col KEY3 for a15
col KEY4 for a15
col REPORT_PARAMETERS for a20
col REPORT_SUMMARY for a50 wrap
col COMPONENT_NAME for a20
col REPORT_NAME for a20
col report_id new_v report_id
select * from
(
select * from DBA_HIST_REPORTS
where 1=1
and key1 = '&&sql_id'
and COMPONENT_NAME = 'sqlmonitor'
order by generation_time desc
)
where rownum = 1;
SNAP_ID DBID INSTANCE_NUMBER REPORT_ID COMPONENT_ID SESSION_ID SESSION_SERIAL# PERIOD_START_T PERIOD_END_TIM GENERATION_TIM COMPONENT_NAME REPORT_NAME REPORT_PARAMETERS KEY1 KEY2 KEY3 KEY4 GENERATION_COST_SECONDS REPORT_SUMMARY CON_DBID CON_ID
---------- ---------- --------------- ---------- ------------ ---------- --------------- -------------- -------------- -------------- -------------------- -------------------- -------------------- --------------- --------------- --------------- --------------- ----------------------- -------------------------------------------------- ---------- ----------
15923 3962735431 1 20591 263 487 26095 14-04-22 07:46 14-04-22 07:46 14-04-22 07:47 sqlmonitor main /orarep/sqlmonitor/m f705bwx3q0ydq 16790304 04:14:2022 07:4 6#5305194#52924 0 <report_repository_summary><sql sql_id="f705bwx3q0 3962735431 1
ain?sql_id=f705bwx3q 6:26 02#0#0 ydq" sql_exec_start="04/14/2022 07:46:26" sql_exec
0ydq&sql_exec_id=167 _id="16790304"><status>DONE (ALL ROWS)</status><sq
90304&sql_exec_start l_text>select count(*) from dba_autotask_window_cl
=04:14:2022 07:46:26 ients c, (select window_name, max(log_date) max_lo
&last_refresh_time=0 g_date </sql_text><first_refresh_time>04/14/2022 0
90304&sql_exec_start
define report_id=20591
SELECT DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL(RID => &&report_id , TYPE => 'text') FROM dual;====
from Web !!!!
https://clarodba.wordpress.com/2023/10/09/showing-bind-values-from-sql-monitor-views-even-when-the-sql-monitor-report-itself-does-not-show-it-correctly/
def sqlid="48zqmd3dv8001"
def exec=""
def phv=""
def ins="1"
def datini="sysdate-30"
def datfin="sysdate"
col bind_name format a10 wrap
col used_value format a40
col peeked_value format a40
WITH
reports as (
select *
from (
select snap_id, INSTANCE_NUMBER, r.key1 as sql_id, to_number(r.key2) as sql_exec_id, d.REPORT, r.report_summary,
to_number(EXTRACTVALUE(XMLType(r.report_summary),'/report_repository_summary/sql/plan_hash')) SQL_PLAN_HASH_VALUE
from DBA_HIST_REPORTS_DETAILS d
natural join DBA_HIST_REPORTS r
where r.component_name = 'sqlmonitor'
AND r.report_name = 'main'
AND KEY1='&sqlid'
and ('&exec' is null or KEY2 = to_number('&exec'))
and ('&ins' is null or INSTANCE_NUMBER = to_number(nvl('&ins','1')))
and GENERATION_TIME between &datini and &datfin
)
where ('&phv' is null or SQL_PLAN_HASH_VALUE = to_number('&phv'))
),
reports_u as (
select
snap_id, INSTANCE_NUMBER, sql_id, sql_exec_id, bind_name, bind_pos, bind_type, bind_typestr,
case
when bind_type in (1,2,96) /*varchar2,number,char*/ then bind_data
when bind_type = 12 /*date*/ then to_char(to_date(bind_data,'mm/dd/yyyy hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
when bind_type = 180 /*timestamp*/ then
to_char(
to_timestamp(
to_char(
dbms_stats.convert_raw_to_date(hextoraw(substr(bind_data,1,14)))
,'yyyy-mm-dd hh24:mi:ss')
,'yyyy-mm-dd hh24:mi:ss')
+ numtodsinterval(nvl(to_number(hextoraw(substr(bind_data,15,8)),'XXXXXXXX')/1e9,0), 'SECOND')
,'yyyy-mm-dd hh24:mi:ss.ff9')
else 'Not printable'
end used_value
from reports r
cross join xmltable
(
'/report/sql_monitor_report/binds/bind' passing xmltype(REPORT)
columns
bind_name varchar2(30) path './@name',
bind_pos number path './@pos',
bind_type number path './@dty',
bind_typestr varchar2(30) path './@dtystr',
bind_data varchar2(1000) path '.'
) c
),
reports_p as (
select
snap_id, INSTANCE_NUMBER, sql_id, sql_exec_id, bind_name, bind_pos, bind_type, bind_typestr,
case
when bind_type = 1 /*varchar2*/ then utl_raw.cast_to_varchar2(bind_data)
when bind_type = 2 /*number*/ then to_char(utl_raw.cast_to_number (bind_data))
when bind_type = 96 /*char*/ then to_char(utl_raw.cast_to_varchar2(bind_data))
when bind_type = 12 /*date*/ then TO_CHAR(dbms_stats.convert_raw_to_date(bind_data),'yyyy-mm-dd hh24:mi:ss')
when bind_type = 180 /*timestamp*/ then
to_char(
to_timestamp(
to_char(
dbms_stats.convert_raw_to_date(hextoraw(substr(bind_data,1,14)))
,'yyyy-mm-dd hh24:mi:ss')
,'yyyy-mm-dd hh24:mi:ss')
+ numtodsinterval(nvl(to_number(hextoraw(substr(bind_data,15,8)),'XXXXXXXX')/1e9,0), 'SECOND')
,'yyyy-mm-dd hh24:mi:ss.ff9')
else 'Not printable'
end peeked_value
from reports r
cross join xmltable
(
'/report/sql_monitor_report/plan/operation/other_xml/peeked_binds/bind'
passing xmltype(REPORT)
columns
bind_name varchar2(30) path './@nam',
bind_pos number path './@pos',
bind_type number path './@dty',
bind_typestr varchar2(30) path './@dtystr',
bind_data varchar2(1000) path '.'
) p
),
reports_binds as (
select snap_id, INSTANCE_NUMBER, sql_id, sql_exec_id, bind_name, bind_pos,
u.bind_type, u.bind_typestr,
u.used_value, p.peeked_value
from reports_p p
full join reports_u u using (snap_id, INSTANCE_NUMBER, sql_id, sql_exec_id, bind_name, bind_pos)
order by bind_pos
),
sqlmon as (
select INST_ID, sql_id, sql_exec_id, BINDS_XML, SQL_CHILD_ADDRESS, SQL_PLAN_HASH_VALUE
from gv$sql_monitor m
where SQL_ID='&sqlid'
and ('&exec' is null or SQL_EXEC_ID = to_number('&exec'))
and ('&ins' is null or INST_ID = to_number(nvl('&ins','1')))
and ('&phv' is null or SQL_PLAN_HASH_VALUE = to_number('&phv'))
and SQL_PLAN_HASH_VALUE <> 0
and nvl(PX_QCSID,SID) = SID and nvl(PX_QCINST_ID,INST_ID) = INST_ID --don't show Parallel slaves
and SQL_EXEC_START between &datini and &datfin
),
sqlmon_u as (
select
INST_ID, sql_id, sql_exec_id, bind_name, bind_pos, bind_type, bind_typestr,
case
when bind_type in (1,2,96) /*varchar2,number,char*/ then bind_data
when bind_type = 12 /*date*/ then to_char(to_date(bind_data,'mm/dd/yyyy hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
when bind_type = 180 /*timestamp*/ then
to_char(
to_timestamp(
to_char(
dbms_stats.convert_raw_to_date(hextoraw(substr(bind_data,1,14)))
,'yyyy-mm-dd hh24:mi:ss')
,'yyyy-mm-dd hh24:mi:ss')
+ numtodsinterval(nvl(to_number(hextoraw(substr(bind_data,15,8)),'XXXXXXXX')/1e9,0), 'SECOND')
,'yyyy-mm-dd hh24:mi:ss.ff9')
else 'Not printable'
end as used_value
from sqlmon
cross join xmltable
(
'/binds/bind' passing xmltype(BINDS_XML)
columns
bind_name varchar2(30) path './@name',
bind_pos number path './@pos',
bind_type number path './@dty',
bind_typestr varchar2(30) path './@dtystr',
bind_data varchar2(1000) path '.'
) u
where BINDS_XML is not null
),
sqlmon_plan_p as (
select
m.INST_ID, m.sql_id, m.sql_exec_id, x.bind_name, x.bind_pos, x.bind_type,
case
when bind_type = 1 /*varchar2*/ then utl_raw.cast_to_varchar2(bind_data)
when bind_type = 2 /*number*/ then to_char(utl_raw.cast_to_number (bind_data))
when bind_type = 96 /*char*/ then to_char(utl_raw.cast_to_varchar2(bind_data))
when bind_type = 12 /*date*/ then TO_CHAR(dbms_stats.convert_raw_to_date(bind_data),'yyyy-mm-dd hh24:mi:ss')
when bind_type = 180 /*timestamp*/ then
to_char(
to_timestamp(
to_char(
dbms_stats.convert_raw_to_date(hextoraw(substr(bind_data,1,14)))
,'yyyy-mm-dd hh24:mi:ss')
,'yyyy-mm-dd hh24:mi:ss')
+ numtodsinterval(nvl(to_number(hextoraw(substr(bind_data,15,8)),'XXXXXXXX')/1e9,0), 'SECOND')
,'yyyy-mm-dd hh24:mi:ss.ff9')
else 'Not printable'
end peeked_value
from sqlmon m, gv$sql_plan p,
xmltable
(
'/*/peeked_binds/bind' passing xmltype(p.OTHER_XML)
columns
bind_name varchar2(30) path './@nam',
bind_pos number path './@pos',
bind_type number path './@dty',
bind_data varchar2(1000) path '.'
) x
where p.OTHER_XML is not null
and m.inst_id = p.inst_id
and m.sql_id = p.sql_id
and m.SQL_CHILD_ADDRESS = p.child_address
),
sqlmon_binds as (
select INST_ID, sql_id, sql_exec_id, bind_name, bind_pos,
u.bind_type, u.bind_typestr,
u.used_value, p.peeked_value
from sqlmon_plan_p p
full join sqlmon_u u using (INST_ID, sql_id, sql_exec_id, bind_name, bind_pos)
)
select 'HIST' src, snap_id, instance_number as inst, sql_id, sql_exec_id,
bind_name, bind_pos, bind_type, bind_typestr, used_value, peeked_value
from reports_binds
UNION ALL
select 'MEM' src, NULL as snap_id, inst_id as inst, sql_id, sql_exec_id,
bind_name, bind_pos, bind_type, bind_typestr, used_value, peeked_value
from sqlmon_binds
order by src, snap_id, inst, sql_id, sql_exec_id, bind_pos, used_value, peeked_value
/
1 comment:
Post a Comment