Month: May 2013

Retrieve and visualize ASM Disk Group Usage per database with R

If you are using Oracle Enterprise Manager and ASM, then you may already be familiar with the following page that displays the Disk Group Usage per database for a particular Disk Group:

oem_archive_dg_usage
And it is most likely that you don’t need to read this little post, as it provides a R script to achieve more or less the same result ;-).

But if you are not using Oracle Enterprise Manager, then you may find this post useful.

I created a R script “graph_asm_dg_usage.r ” (You can download it from this repository) that provides:

  1. A graph similar to the OEM one.
  2. A pdf file that contains the graph.
  3. A text file that contains the values used to build the graph.

The graph is available from both outputs (X11 and the pdf file). In case the X11 environment does not work, the pdf file is generated anyway.

Let’s launch it to see the result:

./graph_asm_dg_usage.r 
Building the thin jdbc connection string....

host ?: bdt_host
port ?: 1521
service_name for ASM ?: +ASM
sys as sysasm password ?: dontreadthis
Display which disk group (no quotation marks, no +) ?: ARCHIVE
Loading required package: methods
Loading required package: DBI
Loading required package: rJava
Please enter any key to exit:

The output is the following:

archive_dg_usage

As you can see you are prompted for:

  • jdbc thin “like” details to connect to the ASM instance (You can launch the R script outside the host hosting the ASM instance).
  • oracle sys user password.
  • The ASM disk group you want to focus on.

Remarks:

  • It has been tested on a 11.2.0.3 asm instance.
  • If you want to install R, you should begin to read  “Getting Starting” from this link.

TAF is not working on Rac One Node in case of node failure ? Well it depends

Martin Bach did a good study of Rac One Node capability and especially of what happens with session failover during a database relocation or during a node failure into this blog post.

He showed us that:

  1. during a database relocation (initiated manually with srvctl relocate database) the TAF works as expected.
  2. during a node failure the TAF did not work and you will be disconnected (for a running select or a new execution)

It’s important: I said “during a node failure“, it means no instances are available during the select.

But what’s about a session already connected to a TAF service before the node failure that :

  • will be inactive during the node failure
  • will launch a select after the node failure (means an instance is back)

For this one an node failure is transparent, let’s see that in action:

First check my database is a Rac One Node:

srvctl config database -d BDTO | grep -i type
Type: RACOneNode

Now let’s check that the bdto_taf service I’ll connect on is a TAF one:

srvctl config service -s BDTO_TAF -d BDTO | egrep -i "taf|failover"
Service name: BDTO_TAF
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 0
TAF failover delay: 0
TAF policy specification: BASIC

Well everything is in place to test.

So connect to my Rac One Node database using the TAF service:

sqlplus bdt/"test"@bdto_taf

SQL*Plus: Release 11.2.0.3.0 Production on Thu May 16 14:39:36 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';

Session altered.

SQL> select sysdate||' '||host_name from v$instance;

SYSDATE||''||HOST_NAME
--------------------------------------------------------------------------------
2013/05/16 14:39:47 BDTSRV1

Now let’s kill smon:

ps -ef | grep -i smon | grep -i BDTO_1 
oracle    8035     1  0 14:30 ?        00:00:00 ora_smon_BDTO_1

kill -9 `ps -ef | grep -i smon | grep -i BDTO_1 | awk '{print $2}'`

Let’s wait an instance is back on a node (Important step)

In this case the instance has been relocated on the other node, let’s see it is up:

ps -ef | grep -i smon | grep -i BDTO_1
oracle   29266     1  0 14:54 ?        00:00:00 ora_smon_BDTO_1

and come back to our “idle” sqlplus session to re-launch the sql:

SQL> /

SYSDATE||''||HOST_NAME
--------------------------------------------------------------------------------
16-MAY-13 BDTSRV2

Oh ! As you can see the “idle” sqlplus session has not been disconnected and we have been able to re-launch the query.

As you can see we lost our nls_date_format setting. This is expected as explained into Martin’s Book “Pro Oracle Database 11g RAC on Linux“:

”

Also, note that while TAF can reauthorize a session, it does not restore the session to its previous
state. Therefore, following an instance failure, you will need to restore any session variables, PL/SQL
package variables, and instances of user-defined types. TAF will not restore these values automatically,
so you will need to extend your applications to restore them manually.

”

Conclusion:

If you are using a TAF service to connect to a Rac One Node database (of course through the OCI), then a node failure is transparent if the session is “idle” during the time there is no instance available.

When you think of it, it is quite normal as the TAF is more or less nothing more than a OCI / SQL*net feature, so once the TAF service is back, then TAF can work as expected.

Remarks:

  • If the “idle” connection has an “opened” transaction, you’ll receive the “ORA-25402: transaction must roll back” oracle error (This is not so transparent anymore 🙂 ).
  • Do not test TAF connected as the oracle sys user as SYSDBA Sessions do not failover with SRVCTL TAF configured, see MOS [ID 1342992.1]
  • If you are using srvctl to test TAF”s reaction on node failure then you should read the MOS note [ID 1324574.1] first (as the TAF may not work depending of the version and srvctl options being used)
  • For a good explanation of Rac One Node, you should have a look to this Aman Sharma’s blog post.

Why I wrote this post ?

Because into Martin’s post, I put a comment on “December 13, 2012 at 19:08” trying to explain this behavior. I came back to this comment yesterday and I realized that my comment was not so clear ! I hope this post is clear 😉

ASM Preferred Read: Collect performance metrics thanks to my amsiostat utility and SLOB 2

Some times ago I provided a way to see the ASM Preferred Read feature in action thanks to Kevin Closson’s SLOB kit and my asmiostat utility into this blog post. Since that time Kevin released SLOB 2.

One of its new feature is that it now supports RAC so that we don’t need to launch SLOB on each node anymore as it has been done into my previous post.

Let’s see how we can show the ASM preferred read feature in action and collect performance metrics thanks to SLOB 2.

First, let’s create two services SLOB_BDT1 and SLOB_BDT2 respectively on BDT_1 and BDT_2 instances:

$ srvctl add service -s SLOB_BDT2 -d BDTO -r BDTO_2
$ srvctl start service -s SLOB_BDT2 -d BDTO
$ srvctl add service -s SLOB_BDT1 -d BDTO -r BDTO_1
$ srvctl start service -s SLOB_BDT1 -d BDTO
$ srvctl status service -s SLOB_BDT1 -d BDTO
Service SLOB_BDT1 is running on instance(s) BDTO_1
$ srvctl status service -s SLOB_BDT2  -d BDTO
Service SLOB_BDT2 is running on instance(s) BDTO_2

Now let’s configure the slob.conf so that the SLOB’s sessions will be distributed over those 2 services with a “round-robin” manner and no updates triggered:

$ grep -i sqlnet slob.conf
ADMIN_SQLNET_SERVICE=slob_bdt
SQLNET_SERVICE_BASE=slob_bdt
SQLNET_SERVICE_MAX=2
$ grep -i UPDATE_PCT slob.conf | head -1
UPDATE_PCT=0

Let’s configure the ASM preferred read parameters:

SQL>  alter system set asm_preferred_read_failure_groups='BDT_ONLY.WIN' sid='+ASM1';

System altered.

SQL> alter system set asm_preferred_read_failure_groups='BDT_ONLY.JMO' sid='+ASM2';

System altered.

As we want to see the ASM preferred read in action, we need to configure our BDT_1 and BDT_2 instances in such a way that the SLOB run will generate physical IOs.

For this purpose, I use those settings:

alter system set "_db_block_prefetch_limit"=0 scope=spfile sid='*';
alter system set "_db_block_prefetch_quota"=0  scope=spfile sid='*';
alter system set "_db_file_noncontig_mblock_read_count"=0 scope=spfile sid='*';
alter system set "cpu_count"=1 scope=spfile sid='*';
alter system set "db_cache_size"=4m scope=spfile sid='*';
alter system set "shared_pool_size"=500m scope=spfile sid='*';
alter system set "sga_target"=0 scope=spfile sid='*';

You can find a very good description, on how to use SLOB for PIO testing into this flashdba’s blog post.

Now we are ready to launch the SLOB 2 run:

$ ./runit.sh 16

and see the preferred read in action as well as its associated performance metrics thanks to my asmiostat utility that way:

$ ./real_time.pl -type=asmiostat -dg=BDT_ONLY -show=dg,inst,fg

with the following result:

asm_preferred_read_slob2

Great, data have been read from their preferred read failure groups 😉 We can also see their respectives performance metrics.

Conclusion:

Thanks to Kevin’s SLOB 2 kit and my asmiostat utility we are now able to check the ASM preferred read performance metrics with a single SLOB run.

UPDATE: The asmiostat utility is not part of the real_time.pl script anymore. A new utility called asm_metrics.pl has been created. See “ASM metrics are a gold mine. Welcome to asm_metrics.pl, a new utility to extract and to manipulate them in real time” for more information.

Diagnose Adaptive Cursor Sharing (ACS) per execution for non monitored sql

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

Diagnose Adaptive Cursor Sharing (ACS) per execution in 11.2

As you know oracle introduced a new feature “Adaptive cursor sharing (ACS)”  in 11g. You can find a very good explanation of what it is into this Maria Colgan’s blog post.

So, as Maria said: “A bind aware cursor may use different plans for different bind values, depending on how selective the predicates containing the bind variable are.”

That’s fine, but I would like to see per execution of a given sql_id, if the Adaptive Cursor Sharing feature came into play.

Let’s define “When ACS comes into play” means: ACS comes into play for a particular execution: 

  1. if the peeked values (The ones that generate the execution plan) changed compare to the previous execution.
  2. if this execution is not the first one that has been executed after the initial hard parse.

For this, I adapted the query that I use to retrieve “peeked” and “passed” bind values per execution into this blog post that way:

SQL>!cat binds_peeked_passed_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
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,
--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,
run_t.bind_data passed,
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.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 ;

So, I simply added this line:

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"

This new line:

  1. Will result in ‘YES’  if the value of a “peeked” bind variable changed compare to the previous execution.
  2. Will result in “NO” if this is the first execution after the hard parse or the value of a peeked variable did not change compare to the previous execution.

Let’s test it:

There is a data skew on the owner column which has one index on it. The data distribution is the following:

SQL> select owner,count(*) from bdt2 group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
BDT                              13848830
ME                                 100098

Let’s query the table that way:

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)
---------- -------------- --------------
  13848830              2          18233

SQL> set pagesi 0
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID  bu9367qrhq28t, child number 0
-------------------------------------
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

Plan hash value: 1047781245

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       | 12726 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| BDT2 |    13M|   224M| 12726   (6)| 00:01:59 |
---------------------------------------------------------------------------

So a Full Table Scan occured and the “peeked” and “passed” bind variables are:

SQL> @binds_peeked_passed_acs.sql
Enter value for sql_id: 

SQL_ID        STARTING_TIME       END_TIME            RUN_TIME_SEC PLAN_HASH_VALUE BIND_NAME              BIND_POS PEEKED               PASSED               ACS
------------- ------------------- ------------------- ------------ --------------- -------------------- ---------- -------------------- -------------------- ---
bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05        6.788      1047781245 :MY_OWNER                     1 BDT                  BDT                  NO
bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05        6.788      1047781245 :MY_DATE                      2 01-jan-2001          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                    1                    NO

and no ACS into play.

Now, let’s change the bind values of the owner column and check the “peeked” and “passed” values:

SQL> exec :my_owner :='ME';

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)
---------- -------------- --------------
    100098              2          18233

SQL> @binds_peeked_passed_acs.sql
Enter value for sql_id: 

SQL_ID        STARTING_TIME       END_TIME            RUN_TIME_SEC PLAN_HASH_VALUE BIND_NAME              BIND_POS PEEKED               PASSED               ACS
------------- ------------------- ------------------- ------------ --------------- -------------------- ---------- -------------------- -------------------- ---
bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05        6.788      1047781245 :MY_OWNER                     1 BDT                  BDT                  NO
bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05        6.788      1047781245 :MY_DATE                      2 01-jan-2001          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                    1                    NO
bu9367qrhq28t 2013/05/03 14:21:05 2013/05/03 14:21:13        8.005      1047781245 :MY_OWNER                     1 BDT                  ME                   NO
bu9367qrhq28t 2013/05/03 14:21:05 2013/05/03 14:21:13        8.005      1047781245 :MY_DATE                      2 01-jan-2001          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                    1                    NO

So, same “peeked” values while the “passed” ones are not the same (and still not ACS triggered) as we can check that way (See Maria Colgan’s blog post):

SQL> l
  1* select child_number, executions, buffer_gets,is_bind_sensitive, is_bind_aware  from v$sql where sql_id='bu9367qrhq28t'
SQL> /

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE    IS_BIND_AWARE
------------ ---------- ----------- -------------------- --------------------
           0          2      360295 Y                    N

Now let’s run the query a second time with the ‘ME’ value for the owner column field:

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)
---------- -------------- --------------
    100098              2          18233

And the execution plan has changed:
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID  bu9367qrhq28t, child number 1
-------------------------------------
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

Plan hash value: 2372635759

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |  1511 (100)|          |
|   1 |  SORT AGGREGATE              |           |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| BDT2      |   100K|  1661K|  1511   (1)| 00:00:15 |
|*  3 |    INDEX RANGE SCAN          | BDT_OWNER |   100K|       |   213   (1)| 00:00:02 |
------------------------------------------------------------------------------------------

As you can see the execution plan changed. Well, let’s see the result of my sql:

SQL>@binds_peeked_passed_acs.sql
Enter value for sql_id: 

SQL_ID        STARTING_TIME       END_TIME            RUN_TIME_SEC PLAN_HASH_VALUE BIND_NAME              BIND_POS PEEKED               PASSED               ACS
------------- ------------------- ------------------- ------------ --------------- -------------------- ---------- -------------------- -------------------- ---
bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05        6.788      1047781245 :MY_OWNER                     1 BDT                  BDT                  NO
bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05        6.788      1047781245 :MY_DATE                      2 01-jan-2001          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                    1                    NO
bu9367qrhq28t 2013/05/03 14:21:05 2013/05/03 14:21:13        8.005      1047781245 :MY_OWNER                     1 BDT                  ME                   NO
bu9367qrhq28t 2013/05/03 14:21:05 2013/05/03 14:21:13        8.005      1047781245 :MY_DATE                      2 01-jan-2001          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                    1                    NO
bu9367qrhq28t 2013/05/03 14:27:14 2013/05/03 14:27:15        1.448      2372635759 :MY_OWNER                     1 ME                   ME                   YES
bu9367qrhq28t 2013/05/03 14:27:14 2013/05/03 14:27:15        1.448      2372635759 :MY_DATE                      2 01-jan-2001          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                    1                    NO

Great! It detected that ACS came into play for this execution. Fine but what’s new compare to checking:

SQL>select child_number, executions, buffer_gets,is_bind_sensitive, is_bind_aware  from v$sql where sql_id='bu9367qrhq28t';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE    IS_BIND_AWARE
------------ ---------- ----------- -------------------- --------------------
           0          2      360295 Y                    N
           1          1        1576 Y                    Y

What’s new is that you can check if ACS came into play per execution. Let’s run the sql 3 times with 2 changes of the bind value and check the result:

SQL>exec :my_owner :='BDT'

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)
---------- -------------- --------------
  13848830              2          18233

SQL> exec :my_owner :='ME';

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)
---------- -------------- --------------
    100098              2          18233

SQL> /
  COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID)
---------- -------------- --------------
    100098              2          18233

You don’t have more informations from v$sql (you can see that ACS came into play but you don’t know for which execution):

SQL> l
  1* select child_number, executions, buffer_gets,is_bind_sensitive, is_bind_aware  from v$sql where sql_id='bu9367qrhq28t'
SQL> /

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE    IS_BIND_AWARE
------------ ---------- ----------- -------------------- --------------------
           0          2      360295 Y                    N
           1          3        3152 Y                    Y
           2          1      180104 Y                    Y

while you can have the details per execution that way:

QL> @binds_peeked_passed_acs.sql
Enter value for sql_id: 

SQL_ID        STARTING_TIME       END_TIME            RUN_TIME_SEC PLAN_HASH_VALUE BIND_NAME              BIND_POS PEEKED               PASSED               ACS
------------- ------------------- ------------------- ------------ --------------- -------------------- ---------- -------------------- -------------------- ---
bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05        6.788      1047781245 :MY_OWNER                     1 BDT                  BDT                  NO
bu9367qrhq28t 2013/05/03 14:17:59 2013/05/03 14:18:05        6.788      1047781245 :MY_DATE                      2 01-jan-2001          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                    1                    NO
bu9367qrhq28t 2013/05/03 14:21:05 2013/05/03 14:21:13        8.005      1047781245 :MY_OWNER                     1 BDT                  ME                   NO
bu9367qrhq28t 2013/05/03 14:21:05 2013/05/03 14:21:13        8.005      1047781245 :MY_DATE                      2 01-jan-2001          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                    1                    NO
bu9367qrhq28t 2013/05/03 14:27:14 2013/05/03 14:27:15        1.448      2372635759 :MY_OWNER                     1 ME                   ME                   YES
bu9367qrhq28t 2013/05/03 14:27:14 2013/05/03 14:27:15        1.448      2372635759 :MY_DATE                      2 01-jan-2001          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                    1                    NO
bu9367qrhq28t 2013/05/03 14:32:49 2013/05/03 14:32:55        6.859      1047781245 :MY_OWNER                     1 BDT                  BDT                  YES
bu9367qrhq28t 2013/05/03 14:32:49 2013/05/03 14:32:55        6.859      1047781245 :MY_DATE                      2 01-jan-2001          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                    1                    NO
bu9367qrhq28t 2013/05/03 14:33:05 2013/05/03 14:33:06        1.879      2372635759 :MY_OWNER                     1 ME                   ME                   YES
bu9367qrhq28t 2013/05/03 14:33:05 2013/05/03 14:33:06        1.879      2372635759 :MY_DATE                      2 01-jan-2001          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                    1                    NO
bu9367qrhq28t 2013/05/03 14:33:12 2013/05/03 14:33:13        1.879      2372635759 :MY_OWNER                     1 ME                   ME                   NO
bu9367qrhq28t 2013/05/03 14:33:12 2013/05/03 14:33:13        1.879      2372635759 :MY_DATE                      2 01-jan-2001          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                    1                    NO

Conclusion:

You know for which executions ACS came into play and furthermore which “peeked” bind variable value changed compare to the previous execution (ACS column=’YES’).

Remarks:

  1. You need Diagnostic and tuning licenses pack to query v$active_session_history and v$sql_monitor.
  2. The query rely on the fact that the sql is monitored (which  means CPU + I/O wait time >= 5 seconds per default that can be changed thanks to the _sqlmon_threshold hidden parameter)
  3. If you are ready to get rid of the “passed” values, then you can check this post for non monitored sql: Diagnose Adaptive Cursor Sharing (ACS) per execution for non monitored sql