Hello,
Sometimes, when you have to troubleshoot complex performance issues related to execution plans for queries that use bind variables, knowing the bind values used by the Optimizer becomes very important for the troubleshooting process. And in these cases, not only the bind value used at runtime for a particular execution is important, but also the value used at PARSING time (known as PEEKED value).
So, one wonderful tool to analyze SQL Executions is the SQL Monitor Report. If you don’t know much about SQL Monitor, please read here where Maria Colgan explains very well how to get the most out of this tool.
Well, although SQL Monitor is a very useful tool that I use all the time for performance analysis (I myself have written a few posts about it, find them here), there is something that several times made me sad and lose time: not always the SQL Monitor report shows all bind variables used and/or peeked.
Take for example this SQL Monitor report:
Note that all the PEEKED values as well as the TIMESTAMP datatype value in the Value column (the values used at runtime) are not shown correctly. Actually, the values presented are in RAW format, the way Oracle stores them in the database. Easy to understand that seeing values in the RAW format does not help much right?
I can’t confirm if this is true in all databases and all versions, but all that I have access at this point have the same “issue”. The database where the above report was generated is version 19.20.
So, in this post I will provide you a script that shows all the bind variable values for any generated SQL Monitor report in your database, either if it’s in memory (GV$SQL_MONITOR) or in the AWR repository (persisted in DBA_HIST_REPORTS%).
The script shows both the USED and PEEKED values, for all most common data types including TIMESTAMP. I did not implement it yet for TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE, but it’s not that difficult so I will probably come up with an updated version soon.
As usual, I will provide you a test case so you can test the script even if you don’t have real data to test against.
So, let’s start creating a table and some data:
create table t (
col_n number, col_ch char, col_vc varchar2(10),
col_dt date, col_ts timestamp);
Table created.
define mydt="2023/10/01 12:01:12"
define myts="2023/10/01 12:01:12.123"
insert into t values (
1, 'A', 'Edu$!123', to_date('&mydt','yyyy-mm-dd hh24:mi:ss'), to_timestamp('&myts','yyyy-mm-dd hh24:mi:ss.ff'));
1 row created.
Note that I defined two SQL Plus variables with a DATE and a TIMESTAMP values, just to make it easy to reuse the same values later on for the queries I’m going to execute.
Let me format the output for DATE and TIMESTAMP types and check the inserted data:
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss.ff';
col col_N for 999
col col_ts for a26
select * from t;
Session altered.
Session altered.
COL_N C COL_VC COL_DT COL_TS
----- - ---------- ------------------- --------------------------
1 A Edu$!123 2023-10-01 12:01:12 2023-10-01 12:01:12.123000
1 row selected.
Ok, now I will run a query with bind variables twice: the first execution will parse the query and generate the execution plan with bind values passed; the second one will use different values but use the same plan (with the same peeked values):
set serveroutput on
declare
n number;
c char;
vc varchar2(10);
dt date;
ts timestamp;
function run_query
return number is
rn number;
begin
select /*+ monitor */ COL_N
into rn
from t
where COL_VC!='MYQUERY'
and COL_N = n
and COL_CH = c
and COL_VC like vc
and COL_DT = dt
and COL_TS = ts
;
return rn;
exception when no_data_found then return (-1);
end;
begin
n := 1;
c := 'A';
vc := 'Edu%';
dt := to_date('&mydt','yyyy-mm-dd hh24:mi:ss');
ts := to_timestamp('&myts','yyyy-mm-dd hh24:mi:ss.ff');
dbms_output.put_line(run_query);
n := 2;
c := 'B';
vc := 'ABC%';
dt := sysdate;
ts := systimestamp;
dbms_output.put_line(run_query);
end;
/
1
-1
(EDIT 2023/10/10: I just noticed that I included parameters in my function but I never used their values, and it worked because the variables were declared globally so the function have access to them. The code was adjusted).
Note: I am using a PL/SQL block to declare the variables with the proper data type. I am not using SQL Plus bind variables because they cannot be declared directly as DATE or TIMESTAMP.
My PL/SQL block has a function that executes the SQL with the variables received as parameters, and returns the number value in COL_N column, or -1 if no record is found.
After running the PL/SQL block, we will have the two executions stored in the V$SQL_MONITOR view. Please note that I included in the query a string MYQUERY to help identify the query. If you need to rerun the test again you may want to change the string:
col sql_text for a60 trunc
select sql_id, sql_exec_start, sql_exec_id, sql_text
from v$sql_monitor
where sql_text like 'SELECT /*+ monitor */%MYQUERY%';
SQL_ID SQL_EXEC_START SQL_EXEC_ID SQL_TEXT
------------- ------------------- ----------- ------------------------------------------------------------
gs8mw9wayq4dm 2023-10-09 15:35:54 16777216 SELECT /*+ monitor */ COL_N FROM T WHERE COL_VC!='MYQUERY' A
gs8mw9wayq4dm 2023-10-09 15:35:54 16777217 SELECT /*+ monitor */ COL_N FROM T WHERE COL_VC!='MYQUERY' A
Run the following query to generate the SQL Monitor for the last execution:
alter session set events = 'emx_control compress_xml=none';
set lines 32767 trimout on trimspool on head off feed off echo off timing off termout off long 1000000 longchunksize 1000000 ver off pages 0
spool sqlmon.html
select DBMS_SQL_MONITOR.report_sql_monitor(
sql_id => max(a.sql_id),
sql_exec_id => max(a.sql_exec_id),
sql_exec_start => max(a.sql_exec_start),
report_level=>'ALL',type=>'ACTIVE')
from v$sql_monitor a
where sql_text like 'SELECT /*+ monitor */%MYQUERY%';
spool off
Then, open the SQL Monitor in a web browser, click on the SQL Text tab and expand the SQL Binds section:
See? Again, all the PEEKED values are presented as RAW, as well as the TIMESTAMPs.
My friend Marcus Vinicius Miguel Pedro found the Doc ID 2111950.1 that points to a Bug, but at least until Oracle v. 19.20 which I am using, the bug is still there. And the note mentions only the TIMESTAMP issue, but not the PEEKED values.
So, here I present the script you can use to properly get all PEEKED and USED values, for data types NUMBER, CHAR, VARCHAR2, DATE and TIMESTAMP.
def sqlid="gs8mw9wayq4dm"
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
/
SRC SNAP_ID INST SQL_ID SQL_EXEC_ID BIND_NAME BIND_POS BIND_TYPE BIND_TYPESTR USED_VALUE PEEKED_VALUE
---- ---------- ---------- ------------- ----------- ---------- ---------- ---------- ------------------------------ ---------------------------------------- ----------------------------------------
HIST 214 1 gs8mw9wayq4dm 16777216 :B5 1 2 NUMBER 1 1
HIST 214 1 gs8mw9wayq4dm 16777216 :B4 2 96 CHAR(32) A A
HIST 214 1 gs8mw9wayq4dm 16777216 :B3 3 1 VARCHAR2(32) Edu% Edu%
HIST 214 1 gs8mw9wayq4dm 16777216 :B2 4 12 DATE 2023-10-01 12:01:12 2023-10-01 12:01:12
HIST 214 1 gs8mw9wayq4dm 16777216 :B1 5 180 TIMESTAMP 2023-10-01 12:01:12.123000000 2023-10-01 12:01:12.123000000
HIST 214 1 gs8mw9wayq4dm 16777217 :B5 1 2 NUMBER 2 1
HIST 214 1 gs8mw9wayq4dm 16777217 :B4 2 96 CHAR(32) B A
HIST 214 1 gs8mw9wayq4dm 16777217 :B3 3 1 VARCHAR2(32) ABC% Edu%
HIST 214 1 gs8mw9wayq4dm 16777217 :B2 4 12 DATE 2023-10-09 15:35:54 2023-10-01 12:01:12
HIST 214 1 gs8mw9wayq4dm 16777217 :B1 5 180 TIMESTAMP 2023-10-09 15:35:54.432043000 2023-10-01 12:01:12.123000000
MEM 1 gs8mw9wayq4dm 16777216 :B5 1 2 NUMBER 1 1
MEM 1 gs8mw9wayq4dm 16777216 :B4 2 96 CHAR(32) A A
MEM 1 gs8mw9wayq4dm 16777216 :B3 3 1 VARCHAR2(32) Edu% Edu%
MEM 1 gs8mw9wayq4dm 16777216 :B2 4 12 DATE 2023-10-01 12:01:12 2023-10-01 12:01:12
MEM 1 gs8mw9wayq4dm 16777216 :B1 5 180 TIMESTAMP 2023-10-01 12:01:12.123000000 2023-10-01 12:01:12.123000000
MEM 1 gs8mw9wayq4dm 16777217 :B5 1 2 NUMBER 2 1
MEM 1 gs8mw9wayq4dm 16777217 :B4 2 96 CHAR(32) B A
MEM 1 gs8mw9wayq4dm 16777217 :B3 3 1 VARCHAR2(32) ABC% Edu%
MEM 1 gs8mw9wayq4dm 16777217 :B2 4 12 DATE 2023-10-09 15:35:54 2023-10-01 12:01:12
MEM 1 gs8mw9wayq4dm 16777217 :B1 5 180 TIMESTAMP 2023-10-09 15:35:54.432043000 2023-10-01 12:01:12.123000000
20 rows selected.
As you can see in the putput above, all the variables were presented with their correct, readable values.
Please note that I have included in the script the following filters you can use:
- SQL_ID (mandatory)
- SQL_EXEC_ID
- PHV (SQL Plan Hash Value)
- INS (instance)
- DATINI and DATFIN (for the period to research)
That it for today.
I hope you can find it useful.
EDIT: I forgot to mention some of the sources that helped me to decode the information and build the script: Jonathan Lewis and Bertrand Drouvot, and Tanel Poder. What a Team hum 🙂 ?
See you next time!
EDUARDO CLARO 
Pingback: Showing captured bind values from Execution Plans and Captured binds #JoelKallmanDay | EDUARDO CLARO