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
/
define 1='ckz8r5cvwyui'
define 2=3134758917
col Full_Notes for a100
SELECT /*+ opt_param('parallel_execution_enabled', 'false') */
' sql_profile: ' || extractvalue(xmlval, '/*/info[@type = "sql_profile"]')||'
sql_patch: ' || extractvalue(xmlval, '/*/info[@type = "sql_patch"]')||'
baseline: ' || extractvalue(xmlval, '/*/info[@type = "baseline"]')||'
outline: ' || extractvalue(xmlval, '/*/info[@type = "outline"]')||'
dyn_sampling: ' || extractvalue(xmlval, '/*/info[@type = "dynamic_sampling"]')||'
dop: ' || extractvalue(xmlval, '/*/info[@type = "dop"]')||'
dop_reason: ' || extractvalue(xmlval, '/*/info[@type = "dop_reason"]')||'
pdml_reason: ' || extractvalue(xmlval, '/*/info[@type = "pdml_reason"]')||'
idl_reason: ' || extractvalue(xmlval, '/*/info[@type = "idl_reason"]')||'
queuing_reason: ' || extractvalue(xmlval, '/*/info[@type = "queuing_reason"]')||'
px_in_memory: ' || extractvalue(xmlval, '/*/info[@type = "px_in_memory"]')||'
px_in_memory_imc:' || extractvalue(xmlval, '/*/info[@type = "px_in_memory_imc"]')||'
row_shipping: ' || extractvalue(xmlval, '/*/info[@type = "row_shipping"]')||'
index_size: ' || extractvalue(xmlval, '/*/info[@type = "index_size"]')||'
result_checksum: ' || extractvalue(xmlval, '/*/info[@type = "result_checksum"]')||'
card_feedback: ' || extractvalue(xmlval, '/*/info[@type = "cardinality_feedback"]')||'
perf_feedback: ' || extractvalue(xmlval, '/*/info[@type = "performance_feedback"]')||'
xml_suboptimal: ' || extractvalue(xmlval, '/*/info[@type = "xml_suboptimal"]')||'
adaptive_plan: ' || extractvalue(xmlval, '/*/info[@type = "adaptive_plan"]')||'
spd_used: ' || extractvalue(xmlval, '/*/spd/cu')||'
spd_valid: ' || extractvalue(xmlval, '/*/spd/cv')||'
gtt_sess_stat: ' || extractvalue(xmlval, '/*/info[@type = "gtt_session_st"]')||'
db_version: ' || extractvalue(xmlval, '/*/info[@type = "db_version"]')||'
plan_hash_full: ' || extractvalue(xmlval, '/*/info[@type = "plan_hash_full"]')||'
plan_hash: ' || extractvalue(xmlval, '/*/info[@type = "plan_hash"]')||'
plan_hash_2: ' || extractvalue(xmlval, '/*/info[@type = "plan_hash_2"]') as Full_Notes
from
(select xmltype(other_xml) xmlval from
(select other_xml
from dba_hist_sql_plan
where sql_id = '&&1'
and plan_hash_value = nvl('&&2',0)
and other_xml is not null
-- and not exists (select 1 from gv$sql_plan where sql_id = '&&1' and plan_hash_value = nvl('&&2',0) and other_xml is not null)
union all
select other_xml
from gv$sql_plan
where sql_id = '&&1'
and plan_hash_value = nvl('&&2',0)
and other_xml is not null
-- and (inst_id, child_number) in (select inst_id, child_number from gv$sql_plan where sql_id = '&&1' and plan_hash_value = nvl('&&2', 0) and rownum <= 1)
)
);
FULL_NOTES
----------------------------------------------------------------------------------------------------
sql_profile:
sql_patch:
baseline:
outline:
dyn_sampling:
dop:
dop_reason:
pdml_reason:
idl_reason:
queuing_reason:
px_in_memory:
px_in_memory_imc:
row_shipping:
index_size:
result_checksum:
card_feedback: yes
perf_feedback:
xml_suboptimal:
adaptive_plan:
spd_used:
spd_valid:
gtt_sess_stat:
db_version: 12.2.0.1
plan_hash_full: 3951519781
plan_hash: 3134758917
plan_hash_2: 3951519781
=======
define sql_id='0nrnusum1gq7t'
set linesi 200 pages 999 feed off verify off
col bind_name format a20
col end_time format a19
col start_time format a19
col peeked format a20
col passed format a20
alter session set nls_date_format ='DD/MM/YYYY HH24:MI:SS';
alter session set nls_timestamp_format='DD/MM/YYYY HH24:MI:SS';
select
pee.sql_id,
ash.starting_time,
ash.end_time,
(EXTRACT(HOUR FROM ash.run_time) * 3600
+ EXTRACT(MINUTE FROM ash.run_time) * 60
+ EXTRACT(SECOND FROM ash.run_time)) run_time_sec,
pee.plan_hash_value,
pee.bind_name,
pee.bind_pos,
pee.bind_data peeked,
run_t.bind_data passed
from
(
select
p.sql_id,
p.sql_child_address,
p.sql_exec_id,
c.bind_name,
c.bind_pos,
c.bind_data
from
v$sql_monitor p,
xmltable
(
'/binds/bind' passing xmltype(p.binds_xml)
columns bind_name varchar2(30) path '/bind/@name',
bind_pos number path '/bind/@pos',
bind_data varchar2(30) path '/bind'
) c
where
p.binds_xml is not null
) run_t
,
(
select
p.sql_id,
p.child_number,
p.child_address,
c.bind_name,
c.bind_pos,
p.plan_hash_value,
case
when c.bind_type = 1 then utl_raw.cast_to_varchar2(c.bind_data)
when c.bind_type = 2 then to_char(utl_raw.cast_to_number(c.bind_data))
when c.bind_type = 96 then to_char(utl_raw.cast_to_varchar2(c.bind_data))
else 'Sorry: Not printable try with DBMS_XPLAN.DISPLAY_CURSOR'
end bind_data
from
v$sql_plan p,
xmltable
(
'/*/peeked_binds/bind' passing xmltype(p.other_xml)
columns bind_name varchar2(30) path '/bind/@nam',
bind_pos number path '/bind/@pos',
bind_type number path '/bind/@dty',
bind_data raw(2000) path '/bind'
) c
where
p.other_xml is not null
) pee,
(
select
sql_id,
sql_exec_id,
max(sample_time - sql_exec_start) run_time,
max(sample_time) end_time,
sql_exec_start starting_time
from
v$active_session_history
group by sql_id,sql_exec_id,sql_exec_start
) ash
where
pee.sql_id=run_t.sql_id and
pee.sql_id=ash.sql_id and
run_t.sql_exec_id=ash.sql_exec_id and
pee.child_address=run_t.sql_child_address and
pee.bind_name=run_t.bind_name and
pee.bind_pos=run_t.bind_pos
and pee.sql_id like nvl('&sql_id',pee.sql_id)
order by 1,2,3,7 ;
=================================
snap info !!!!define start_time='' define end_time='' col PSEC for 9999.999 select to_char(end_interval_time,'mm-dd hh24') snap_time ,SNAP_ID , instance_number , avg(v_ps) pSec from ( select end_interval_time , instance_number , v/ela v_ps ,SNAP_ID from ( select round(s.end_interval_time,'hh24') end_interval_time , s.instance_number , (case when s.begin_interval_time = s.startup_time then value else value - lag(value,1) over (partition by sy.stat_id , sy.dbid , s.instance_number , s.startup_time order by sy.snap_id) end)/1000000 v , (cast(s.end_interval_time as date) - cast(s.begin_interval_time as date))*24*3600 ela ,s.SNAP_ID from dba_hist_snapshot s , dba_hist_sys_time_model sy where s.dbid = sy.dbid and s.instance_number = sy.instance_number and s.snap_id = sy.snap_id and sy.stat_name = 'DB time' --and s.end_interval_time > to_date(&start_time,'MMDDYYYY') --and s.end_interval_time < to_date(&end_time,'MMDDYYYY') and s.end_interval_time > sysdate -1 )) group by to_char(end_interval_time,'mm-dd hh24'),SNAP_ID, instance_number / set linesize 300 pagesize 300 col ELAPSED for a27 select SNAP_ID, END_INTERVAL_TIME - BEGIN_INTERVAL_TIME as elapsed , (cast(END_INTERVAL_TIME as date) - cast(BEGIN_INTERVAL_TIME as date)) *86400 as elapsed2 from dba_hist_snapshot where 1=1 --and rownum < 24 and end_interval_time > sysdate -1 order by 1; ===========================================================================Bind variable report !!! set sqlblanklines on trimspool on trimout on feedback off linesize 255 pagesize 50000 timing off head off var isdigits number col sql_text for a140 word_wrap define sql_id='7chqwp8a4f2cd' --define SNAP_ID=3405 begin select case regexp_substr(replace(name,':',''),'[[:digit:]]') when replace(name,':','') then 1 end into :isdigits from DBA_HIST_SQL_BIND_METADATA b where b.sql_id = '&&sql_id' and rownum < 2 order by position; end; / -- -- Create variable statements -- select 'variable ' || case :isdigits when 1 then replace(name,':','N') else substr(name,2,30) end || ' ' || replace(datatype_string,'CHAR(','VARCHAR2(') txt from DBA_HIST_SQL_BIND_METADATA where sql_id='&&sql_id'; -- -- Set variable values from DBA_HIST_SQLSTAT -- select 'begin' txt from dual; SELECT case :isdigits when 1 then replace(b.name,':',':N') else b.name end || ' := ' || case when b.datatype = 1 then '''' else null end || a.value_string || case when b.datatype = 1 then '''' else null end || ';' txt from table( select dbms_sqltune.extract_binds(bind_data) from DBA_HIST_SQLSTAT where sql_id like nvl('&&sql_id',sql_id) and snap_id like nvl('&&snap_id',snap_id) and rownum < 2 and bind_data is not null) a, DBA_HIST_SQL_BIND_METADATA b where b.sql_id = '&&sql_id' and a.position = b.position order by b.position; select 'end;' txt from dual; select '/' txt from dual; -- -- Generate statement -- select regexp_replace(sql_text,'(select |SELECT )','select /* test &&sql_id */ ',1,1) sql_text from ( select case :isdigits when 1 then replace(sql_text,':',':N') else sql_text end ||';' sql_text from dba_hist_sqltext where sql_id = '&&sql_id'); undef sql_id undef snap_id set feedback on head on
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 400 pagesize 300
col FIRST_TM for a27
col LAST_TM for a27
col NAME for a15
col VALUE_STRING for a80
define 1='5tpxq8mnbtkj9'
select ash.session_id, ash.session_serial#,
coalesce(ash.sql_exec_start,sb.last_captured) sql_exec_start,
ash.user_id, ash.sql_id, ash.sql_plan_hash_value,
ash.MAX_PGA, ash.MAX_TEMP,
ash.first_tm, ash.last_tm, sb.name, sb.value_string
from (
select session_id, session_serial#, sql_exec_start,
user_id, sql_id, /*sql_child_number,*/ sql_plan_hash_value,
/*sql_adaptive_plan_resolved*/
max(pga_allocated) max_pga,
max(temp_space_allocated) max_temp,
min(sample_time) first_tm , max(sample_time) last_tm
from dba_hist_active_sess_history where sql_id='&&1'
and sql_child_number=0 /* only check parent process if parallel query */
and sql_exec_start is not null /* Query actually ran ? */
-- and trunc(sample_time)>=trunc(sysdate-1)
-- order by sql_plan_hash_value,sql_exec_start desc
--
group by session_id, session_serial#, sql_exec_start,
user_id, sql_id, sql_child_number, sql_plan_hash_value,
sql_adaptive_plan_resolved
-- order by sql_exec_start
) ash
full JOIN
( select sb2.sql_id, sb2.last_captured, sb2.position, sb2.name,
sb2.value_string from (
select distinct sb.sql_id,sb.LAST_CAPTURED,sb.SNAP_ID, row_number() over (partition by last_captured order by snap_id) rn
from dba_hist_sqlbind sb
where sql_id='&&1'
group by sb.sql_id,sb.LAST_CAPTURED,sb.SNAP_ID
order by last_captured,rn
) a
join dba_hist_sqlbind sb2
on a.sql_id=sb2.sql_id
and a.last_captured=sb2.LAST_CAPTURED
and a.snap_id=sb2.SNAP_ID
where a.rn=1 and sb2.dup_position is null
-- order by sb2.sql_id,sb2.last_captured,sb2.position
) SB
on sb.sql_id=ash.sql_id and sb.last_captured=ash.sql_exec_start
order by sql_exec_start desc, name;
=====================
set serveroutput ondefine sql_id='4s17ktwqy8ak8' declare v_sql_id varchar(100); v_fulltext clob; v_childnumber number; begin v_sql_id := '&sql_id'; --v_childnumber := 'childnumber'; SELECT LISTAGG(SQL_text, '') within group (order by piece) INTO v_fulltext FROM v$sqltext WHERE sql_id =v_sql_id; for I in (select name,VALUE_STRING from v$sql_bind_capture where sql_id = V_SQL_ID --and child_number = V_CHILDNUMBER ) LOOP v_fulltext := regexp_replace(v_fulltext,i.name||' ',i.value_string); end LOOP; DBMS_OUTPUT.PUT_LINE(v_fulltext); end; ============= --- from webhttps://clarodba.wordpress.com/2023/10/11/showing-captured-bind-values-from-execution-plans-and-captured-binds-joelkallmanday/ col bind_name format a10 wrap col peeked_value format a40 col datatype head "DATA_TYPE" for a20 col src for a9 def sqlid="fb3gqgd87ac8h" def datini=sysdate-1 def datfin=sysdate select src, INST_ID, sql_id, plan_hash_value, child_number, c.bind_name, c.bind_pos, c.bind_type, decode(bind_type,1,'VARCHAR2',2,'NUMBER',12,'DATE',96,'CHAR',180,'TIMESTAMP',181,'TIMESTAMP WITH TZ',231,'TIMESTAMP WITH LTZ',to_char(bind_type)) datatype, case when c.bind_type = 1 /*varchar2*/ then utl_raw.cast_to_varchar2(c.bind_data) when c.bind_type = 2 /*number*/ then to_char(utl_raw.cast_to_number(c.bind_data)) when c.bind_type = 96 /*char*/ then to_char(utl_raw.cast_to_varchar2(c.bind_data)) when c.bind_type = 12 /*date*/ then TO_CHAR(dbms_stats.convert_raw_to_date(c.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 as peeked_value from ( select 'SQLPLAN-M' src, sql_id, plan_hash_value, OTHER_XML, child_number, INST_ID from gv$sql_plan UNION ALL select 'SQLPLAN-H' src, sql_id, plan_hash_value, OTHER_XML, to_number(NULL) as child_number, to_number(NULL) as INST_ID from dba_hist_sql_plan join dba_hist_snapshot using (DBID) where END_INTERVAL_TIME between &datini and &datfin ) 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 raw(2000) path '.' ) c where sql_id = '&sqlid' and p.other_xml is not null order by src, inst_id, sql_id, plan_hash_value, child_number, bind_pos, peeked_value / col bind_name format a10 wrap col captured_value format a40 col DATATYPE_STRING for a20 col src for a9 def sqlid="4s17ktwqy8ak8" def datini=sysdate-1 def datfin=sysdate select DISTINCT src, INST_ID, sql_id, plan_hash_value, child_number, name as bind_name, position as bindpos, DATATYPE_STRING, case when DATATYPE in (1,2,96) /*varchar2,number,char*/ then VALUE_STRING when DATATYPE = 12 /*date*/ then to_char(anydata.accessDate(value_anydata),'yyyy-mm-dd hh24:mi:ss') when DATATYPE = 180 /*timestamp*/ then to_char(anydata.accesstimestamp(value_anydata),'yyyy-mm-dd hh24:mi:ss.ff9') else 'Not printable' END captured_value from ( select 'CAPTURE-M' src, sql_id, child_number, PLAN_HASH_VALUE, INST_ID, name, position, datatype, DATATYPE_STRING, value_string, value_anydata from gv$sql_bind_capture join gv$sql s using (INST_ID, sql_id, child_number) UNION ALL select 'CAPTURE-H' src, sql_id, to_number(NULL) as child_number, to_number(NULL) PLAN_HASH_VALUE, INSTANCE_NUMBER as INST_ID, name, position, datatype, DATATYPE_STRING, value_string, value_anydata from dba_hist_sqlbind join dba_hist_snapshot using (DBID, INSTANCE_NUMBER, SNAP_ID) where END_INTERVAL_TIME between &datini and &datfin ) c where sql_id = '&sqlid' order by src, INST_ID, sql_id, plan_hash_value, child_number, position, captured_value /
1 comment:
Post a Comment