To understand this blog post you have to know what bind variable peeking is. You can found a very good explanation into this Kerry Osborne’s blog post.
So when dealing with performance issues linked to bind variable peeking you have to know:
- The peeked values (The ones that generate the execution plan)
- The passed values (The ones that have been passed to the sql statement)
Kerry Osborne helped us to retrieve the peeked values from v$sql_plan view into this blog post, but what about the passed values ? For those ones, Tanel Poder helped us to retrieve the passed values from v$sql_monitor into this blog post (This is reliable compare to v$sql_bind_capture)
Great ! So we know how to extract the peeked and the passed values. Another interesting point is that v$sql_monitor contains also the sql_exec_id field (see this blog post for more details about this field).
Here we are: It looks like that as of 11.2 we are able to retrieve the passed and peeked values per execution (If the statement is “monitored” which means CPU + I/O wait time >= 5 seconds per default (can be changed thanks to the _sqlmon_threshold hidden parameter).
But as your are dealing with performance issues related to bind variable peeking it is likely that the sql is monitored 😉
So let’s write the sql to do so, and let’s test it.
The sql script is the following:
SQL> !cat binds_peeked_passed.sql
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='YYYY/MM/DD HH24:MI:SS';
alter session set nls_timestamp_format='YYYY/MM/DD 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 ;
Now let’s test it:
SQL> var my_owner varchar2(50) SQL> var my_date varchar2(30) SQL> var my_object_id number SQL> exec :my_owner :='BDT' PL/SQL procedure successfully completed. SQL> exec :my_date := '01-jan-2001' PL/SQL procedure successfully completed. SQL> exec :my_object_id :=1 PL/SQL procedure successfully completed. SQL> select /*+ MONITOR */ count(*),min(object_id),max(object_id) from bdt2 where owner=:my_owner and created > :my_date and object_id > :my_object_id; COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID) ---------- -------------- -------------- 6974365 2 18233 SQL> @binds_peeked_passed.sql Enter value for sql_id: SQL_ID STARTING_TIME END_TIME RUN_TIME_SEC PLAN_HASH_VALUE BIND_NAME BIND_POS PEEKED PASSED ------------- ------------------- ------------------- ------------ --------------- -------------------- ---------- -------------------- -------------------- bu9367qrhq28t 2013/04/29 11:01:02 2013/04/29 11:01:07 5.678 1047781245 :MY_OWNER 1 BDT BDT bu9367qrhq28t 2013/04/29 11:01:02 2013/04/29 11:01:07 5.678 1047781245 :MY_DATE 2 01-jan-2001 01-jan-2001 bu9367qrhq28t 2013/04/29 11:01:02 2013/04/29 11:01:07 5.678 1047781245 :MY_OBJECT_ID 3 1 1
As this is the first execution then peeked values = passed values. Note that I used the “MONITOR” hint to force the sql to be monitored and then get an entry into v$sql_monitor.
Let’s put new passed values:
SQL> exec :my_date := '01-jan-2002' PL/SQL procedure successfully completed. SQL> exec :my_object_id :=100 PL/SQL procedure successfully completed. SQL> select /*+ MONITOR */ count(*),min(object_id),max(object_id) from bdt2 where owner=:my_owner and created > :my_date and object_id > :my_object_id; COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID) ---------- -------------- -------------- 6923776 101 18233 SQL> @binds_peeked_passed.sql Enter value for sql_id: SQL_ID STARTING_TIME END_TIME RUN_TIME_SEC PLAN_HASH_VALUE BIND_NAME BIND_POS PEEKED PASSED ------------- ------------------- ------------------- ------------ --------------- -------------------- ---------- -------------------- -------------------- bu9367qrhq28t 2013/04/29 11:01:02 2013/04/29 11:01:07 5.678 1047781245 :MY_OWNER 1 BDT BDT bu9367qrhq28t 2013/04/29 11:01:02 2013/04/29 11:01:07 5.678 1047781245 :MY_DATE 2 01-jan-2001 01-jan-2001 bu9367qrhq28t 2013/04/29 11:01:02 2013/04/29 11:01:07 5.678 1047781245 :MY_OBJECT_ID 3 1 1 bu9367qrhq28t 2013/04/29 11:07:21 2013/04/29 11:07:25 4.139 1047781245 :MY_OWNER 1 BDT BDT bu9367qrhq28t 2013/04/29 11:07:21 2013/04/29 11:07:25 4.139 1047781245 :MY_DATE 2 01-jan-2001 01-jan-2002 bu9367qrhq28t 2013/04/29 11:07:21 2013/04/29 11:07:25 4.139 1047781245 :MY_OBJECT_ID 3 1 100
So as you can see, peeked values are the same and passed are not: bind variable peeking in action 😉
Conclusion:
We are able to retrieve peeked and passed values per execution.
Remarks:
- You need Diagnostic and tuning licenses pack to query v$active_session_history and v$sql_monitor.
- The query is not able to retrieve the DATE values (if any) from the v$sql_plan (check the code): This is because I don’t want to create a new function into the database. If you want to extract the DATE datatype then you could create the display_raw function (see Kerry Osborne’s blog post for this) and modify the sql.
- If you know how to extract DATE values from RAW type without creating new function please tell me so that i can update the code 😉
bdt's blog 