Into this blog post: Diagnose Adaptive Cursor Sharing (ACS) per execution in 11.2 I provided a way to check if ACS came into play per execution of a given sql.
You should read the previous post to understand this one.
As you can see I retrieved for the bind variables the “peeked” and the “passed” values.
The “passed” values come from the v$sql_monitor.binds_xml column: This information could be useful but is not mandatory to check if ACS came into play (as the check rely on the “peeked” values).
So we can get rid of the “passed” values (and then of the v$sql_monitor view) to check where ACS came into play per execution for non monitored sql.
For this purpose, let’s modify the sql introduced into the previous post that way:
SQL> !cat binds_peeked_acs.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 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, --first_value(pee.bind_data) over (partition by pee.sql_id,pee.bind_name,pee.bind_pos order by end_time rows 1 preceding) previous_peeked, case when pee.bind_data = first_value(pee.bind_data) over (partition by pee.sql_id,pee.bind_name,pee.bind_pos order by end_time rows 1 preceding) then 'NO' else 'YES' end "ACS" from ( 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_child_number, 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_child_number,sql_exec_id,sql_exec_start ) ash where pee.sql_id=ash.sql_id and pee.child_number=ash.sql_child_number and pee.sql_id like nvl('&sql_id',pee.sql_id) order by 1,2,3,7 ;
Let’s see the result with the same test as Diagnose Adaptive Cursor Sharing (ACS) per execution in 11.2:
SQL> @binds_peeked_acs.sql Enter value for sql_id: bu9367qrhq28t SQL_ID STARTING_TIME END_TIME RUN_TIME_SEC PLAN_HASH_VALUE BIND_NAME BIND_POS PEEKED ACS ------------- ------------------- ------------------- ------------ --------------- -------------------- ---------- -------------------- --- bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05 6.788 1047781245 :MY_OWNER 1 BDT NO bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05 6.788 1047781245 :MY_DATE 2 01-jan-2001 NO bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05 6.788 1047781245 :MY_OBJECT_ID 3 1 NO bu9367qrhq28t 2013/05/03 14:21:05 2013/05/03 14:21:13 8.005 1047781245 :MY_OWNER 1 BDT NO bu9367qrhq28t 2013/05/03 14:21:05 2013/05/03 14:21:13 8.005 1047781245 :MY_DATE 2 01-jan-2001 NO bu9367qrhq28t 2013/05/03 14:21:05 2013/05/03 14:21:13 8.005 1047781245 :MY_OBJECT_ID 3 1 NO bu9367qrhq28t 2013/05/03 14:27:14 2013/05/03 14:27:15 1.448 2372635759 :MY_OWNER 1 ME YES bu9367qrhq28t 2013/05/03 14:27:14 2013/05/03 14:27:15 1.448 2372635759 :MY_DATE 2 01-jan-2001 NO bu9367qrhq28t 2013/05/03 14:27:14 2013/05/03 14:27:15 1.448 2372635759 :MY_OBJECT_ID 3 1 NO bu9367qrhq28t 2013/05/03 14:32:49 2013/05/03 14:32:55 6.859 1047781245 :MY_OWNER 1 BDT YES bu9367qrhq28t 2013/05/03 14:32:49 2013/05/03 14:32:55 6.859 1047781245 :MY_DATE 2 01-jan-2001 NO bu9367qrhq28t 2013/05/03 14:32:49 2013/05/03 14:32:55 6.859 1047781245 :MY_OBJECT_ID 3 1 NO bu9367qrhq28t 2013/05/03 14:33:05 2013/05/03 14:33:06 1.879 2372635759 :MY_OWNER 1 ME YES bu9367qrhq28t 2013/05/03 14:33:05 2013/05/03 14:33:06 1.879 2372635759 :MY_DATE 2 01-jan-2001 NO bu9367qrhq28t 2013/05/03 14:33:05 2013/05/03 14:33:06 1.879 2372635759 :MY_OBJECT_ID 3 1 NO bu9367qrhq28t 2013/05/03 14:33:12 2013/05/03 14:33:13 1.879 2372635759 :MY_OWNER 1 ME NO bu9367qrhq28t 2013/05/03 14:33:12 2013/05/03 14:33:13 1.879 2372635759 :MY_DATE 2 01-jan-2001 NO bu9367qrhq28t 2013/05/03 14:33:12 2013/05/03 14:33:13 1.879 2372635759 :MY_OBJECT_ID 3 1 NO
So, same result as in the previous post except that the bind variable “passed” values have been lost.
Conclusion:
We are able to check for which execution ACS came into play for non monitored sql (as we get rid of the bind variable “passed” values and as a consequence we don’t query the v$sql_monitor view anymore).
Remark:
You need to purchase the Diagnostic Pack in order to be allowed to query the “v$active_session_history” view