Search

Top 60 Oracle Blogs

Recent comments

Fundamentals

NYOUG Summer 2014 Conference: Understanding Oracle Locking Internals

Thank you for all those who attended my session: Understanding Oracle Locking Internals today at the Summer 2014 conference of New York Oracle User Group in Manhattan. You can download the presentation and the scripts I used in demos here.

Presentation: http://www.proligence.com/pres/nyoug14/understanding_oracle_locking.pdf
Scripts: http://www.proligence.com/pres/nyoug14/understanding_oracle_locking_scripts.zip

As always, your feedback will be highly appreciated.

Star Transformation And Cardinality Estimates

If you want to make use of Oracle's cunning Star Transformation feature then you need to be aware of the fact that the star transformation logic - as the name implies - assumes that you are using a proper star schema.

Here is a nice example of what can happen if you attempt to use star transformation but your model obviously doesn't really correspond to what Oracle expects:

drop table d;

purge table d;

drop table t;

purge table t;

create table t
as
select
rownum as id
, mod(rownum, 100) + 1 as fk1
, 1000 + mod(rownum, 10) + 1 as fk2
, 2000 + mod(rownum, 100) + 1 as fk3
, rpad('x', 100) as filler
from
dual
connect by
level <= 1000000
;

exec dbms_stats.gather_table_stats(null, 't')

create bitmap index t_fk1 on t (fk1);

Parallel Downgrade

There are many reasons why a parallel execution might not run with the expected degree of parallelism (DOP), beginning with running out of parallel slaves (PARALLEL_MAX_SERVERS or PROCESSES reached), PARALLEL_ADAPTIVE_MULTI_USER, downgrades at execution time via the Resource Manager, or the more recent features like PARALLEL_DEGREE_LIMIT or the Auto DOP introduced in Oracle 11.2.

Logical I/O Evolution - Part 3: 11g

Preface (with apologies to Kevin Closson)

This blog post is too long

Introduction

In the previous part of this series I've already demonstrated that the logical I/O optimization of the Table Prefetching feature depends on the order of the row sources - and 11g takes this approach a big step further.

It is very interesting that 11g does not require any particular feature like Table Prefetching or Nested Loop Join Batching (another new feature introduced in 11g) to take advantage of the Logical I/O optimization - it seems to be available even with the most basic form of a Nested Loop join.

Logical I/O - Evolution: Part 2 - 9i, 10g Prefetching

In the initial part of this series I've explained some details regarding logical I/O using a Nested Loop Join as example.

To recap I've shown in particular:

- Oracle can re-visit pinned buffers without performing logical I/O

- There are different variants of consistent gets - a "normal" one involving buffer pin/unpin cycles requiring two latch acquisitions and a short-cut variant that visits the buffer while holding the corresponding "cache buffers chains" child latch ("examination") and therefore only requiring a single latch acquisition

- Although two statements use a similar execution plan and produce the same number of logical I/Os one is significantly faster and scales better than the other one

Logical I/O - Evolution: Part 1 - Baseline

Forward to Part 2

This is the first part in a series of blog posts that shed some light on the enhancements Oracle has introduced with the recent releases regarding the optimizations of logical I/O.http://www.blogger.com/img/blank.gif

Before we can appreciate the enhancements, though, we need to understand the baseline. This is what this blog post is about.

The example used throughout this post is based on a simple Nested Loop Join which is one area where Oracle has introduced significant enhancements.

It started its life as a comparison of using unique vs. non-unique indexes as part of a Nested Loop Join and their influence on performance and scalability.

This comparison on its own is very educating and also allows to demonstrate and explain some of the little details regarding logical I/O.

Things worth to mention and remember (I) - Parallel Execution Control

Continue to Part II

Motivated by Arup Nanda's great "100 Things You Probably Didn't Know About Oracle Database" and inspired by Jonathan Lewis' "Philosophy" series I'll start a new series about things worth to mention and remember, but in contrast to my usual posts I'll furthermore set a new challenge for myself - trying to write as short and concise as Jonathan does in his series. It is probably no coincidence that Charles Hooper and myself contributed by far the longest chapter to "Expert Oracle Practices" :-)

So don't expect any lengthy descriptions and testcases in this series - also I don't intend to provide here ground-breaking material. All this has probably been published several times in the past, nevertheless I believe it's worth to mention these things again (and again...).

Here we go, the first one worth to mention and remember is important for those that have an Enterprise Edition license and make use of the Parallel Execution feature:

If you haven't enabled Parallel DML on session level (ALTER SESSION ENABLE/FORCE PARALLEL DML) you are not able to force parallel DML execution on statement level using explicit PARALLEL hints for the DML operation. (This one is probably known)

If you disable Parallel Query on session level (ALTER SESSION DISABLE PARALLEL QUERY) explicit PARALLEL hints on statement level for a query operation will overrule this and therefore still use Parallel Query. The session setting only overrules the object level parallel settings, not the PARALLEL hint (This is probably less known)

Although there is probably a rationale behind this it is still obvious I think that there is a major inconsistency here.

In particular the latter is unfortunate if you have existing code with embedded PARALLEL hints and want to ensure that the execution is done serially without any code changes in order to not waste any parallel slaves processes meant to be used by other concurrent executions.

To me it would be much more intuitive and sensible if the setting on session level overruled anything else and therefore allowed to control the code using that general setting, as it does with parallel DML.

By the way, a parallel DML degree forced via ALTER SESSION FORCE PARALLEL DML PARALLEL N will then still be overruled by explicit PARALLEL hints applied to the DML operation, it's just that PARALLEL DML is simply not possible if not at least enabled at session level.

Actually I'm not aware of any officially supported way (which means without fiddling with any undocumented parameters) of preventing the Parallel Query execution of statements with embedded PARALLEL hints on session level besides using the Resource Manager and limiting the Parallel Degree to 1.

Note that this approach has again its own quirks and drawbacks, since obviously the execution plan generated based on the optimizer assumption of Parallel Execution will be effectively downgraded to serial execution at runtime rather than being re-optimized for serial execution. In many cases not a good idea - an execution plan generated for serial execution might look quite different and might be much more efficient when being executed serially.

As a final note, if you think you can work around this by using the undocumented "_optimizer_ignore_hints" parameter, you will be in for a surprise that apparently the APPEND, PARALLEL and NO_PARALLEL hints do not get ignored and still apply and therefore parallelize query execution.

Addendum March 2011: One further oddity should not be left unmentioned: In above mentioned scenario of parallel query being disabled on SESSION level via ALTER SESSION DISABLE PARALLEL QUERY and the usage of PARALLEL hints the following applies:

- Potentially a plan based on parallel execution is generated by the optimizer (as outlined)
- But the costing of the operations is based on serial execution, so the costs of parallel full table scans or index scans are not scaled down based on the degree of parallelism as usually

This means that the resulting execution plans are possibly different to a session where parallel query is enabled - it is more likely to get a serial execution plan favoured by the optimizer in the scenario with parallel query disabled due to the costing based on serial costs.

Nevertheless at runtime parallel execution (with the computed parallel degree) will be used if a parallel plan has been generated by the optimizer.

Adaptive Cursor Sharing

This post was motivated by a recent discussion I had where I mentioned that Adaptive Cursor Sharing (ACS) does not work with embedded SQL in PL/SQL, but when looking for an existing post realized that I didn't find any good and freely available examples of that limitation of ACS.

Therefore I decided to put together this post that outlines what ACS is supposed to do, its inherent limitations and finally shed some light on the specific limitation that started its life with the description that "it does not work with embedded SQL in PL/SQL".

Adaptive Cursor Sharing (ACS) has been introduced in Oracle 11g to address the potential threat of cursor sharing and bind variables - yes, you have read correctly: The usage of bind variables can be a possible threat if different values of bind variables are executed using the same execution plan, but actually would require different execution plans to prevent poor performing executions.

So ACS allows Oracle to detect such problems and selectively "unshare" cursors, which means that Oracle creates one or more additional child cursors for different (ranges) of bind values.

However, there are a couple of limitations to this strategy. Let's have a look at a working example of ACS. Therefore I run the following script against database versions 11.1.0.7 and 11.2.0.2:

--------------------------------------------------------------------------------
--
-- File name: adaptive_cursor_sharing_1.sql
--
-- Purpose: Demonstrate a simple case of adaptive cursor sharing
--
-- Author: Randolf Geist
--
-- Last tested: December 2010
--
-- Versions: 11.1.0.7
-- 11.2.0.2
--------------------------------------------------------------------------------

set echo on define on tab off

drop table t;
purge table t;
create table t as select * from dba_objects, (select rownum from dual connect by level <= 20);
create index i on t(object_id);

exec dbms_stats.gather_table_stats(user, 'T', method_opt => 'for all columns size 1')

set echo on

select count(*), min(object_id)
, max(object_id) from t;

set echo on linesize 200 heading off newpage none tab off serveroutput off

alter session set statistics_level = all;

alter system flush buffer_cache;

alter system flush shared_pool;

var x number

exec :x := 100000

-- pause

set timing on

select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
);

column prev_sql_id new_value sql_id noprint
column sid new_value session_id noprint

select
prev_sql_id
, sid
from
v$session
where
sid = sys_context('userenv', 'sid');

select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));

exec :x := 1

select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
);

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

exec :x := 1

select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
);

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

set heading on

select
sql_id
, child_number
, executions
, parse_calls
, buffer_gets
, is_bind_sensitive
, is_bind_aware
from
v$sql
where
sql_id = '&sql_id';

and I get the following result from 11.2.0.2 (and 11.1.0.7):

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

SQL>
SQL> drop table t;

Table dropped.

SQL> purge table t;

Table purged.

SQL> create table t as select * from dba_objects, (select rownum from dual connect by level <= 20);

Table created.

SQL> create index i on t(object_id);

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'T', method_opt => 'for all columns size 1')

PL/SQL procedure successfully completed.

SQL>
SQL> set echo on
SQL>
SQL> select count(*), min(object_id)
2 , max(object_id) from t;

COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID)
---------- -------------- --------------
1457480 2 75795

SQL>
SQL> set echo on linesize 200 heading off newpage none tab off serveroutput off
SQL>
SQL> alter session set statistics_level = all;

Session altered.

SQL>
SQL> alter system flush buffer_cache;

System altered.

SQL>
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> var x number
SQL>
SQL> exec :x := 100000

PL/SQL procedure successfully completed.

SQL>
SQL> -- pause
SQL>
SQL> set timing on
SQL>
SQL> select
2 sum(row_num)
3 from
4 (
5 select
6 row_number() over (partition by object_type order by object_name) as row_num
7 , t.*
8 from
9 t
10 where
11 object_id > :x
12 );

Elapsed: 00:00:00.09
SQL>
SQL> column prev_sql_id new_value sql_id noprint
SQL> column sid new_value session_id noprint
SQL>
SQL> select
2 prev_sql_id
3 , sid
4 from
5 v$session
6 where
7 sid = sys_context('userenv', 'sid');

Elapsed: 00:00:00.15
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));
old 1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'))
new 1: select * from table(dbms_xplan.display_cursor('fmbq5ytmh0hng',null,'ALLSTATS LAST'))
SQL_ID fmbq5ytmh0hng, child number 0
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 1399240396

-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | 3 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | 3 | | | |
| 2 | VIEW | | 1 | 13 | 0 |00:00:00.01 | 3 | 3 | | | |
| 3 | WINDOW SORT | | 1 | 13 | 0 |00:00:00.01 | 3 | 3 | 1024 | 1024 | |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 13 | 0 |00:00:00.01 | 3 | 3 | | | |
|* 5 | INDEX RANGE SCAN | I | 1 | 13 | 0 |00:00:00.01 | 3 | 3 | | | |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

25 rows selected.

Elapsed: 00:00:01.74
SQL>
SQL> exec :x := 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> select
2 sum(row_num)
3 from
4 (
5 select
6 row_number() over (partition by object_type order by object_name) as row_num
7 , t.*
8 from
9 t
10 where
11 object_id > :x
12 );
2.7361E+11

Elapsed: 00:00:17.12
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
SQL_ID fmbq5ytmh0hng, child number 0
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 1399240396

--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:17.12 | 1460K| 35371 | 10532 | | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:17.12 | 1460K| 35371 | 10532 | | | | |
| 2 | VIEW | | 1 | 13 | 1457K|00:00:16.94 | 1460K| 35371 | 10532 | | | | |
| 3 | WINDOW SORT | | 1 | 13 | 1457K|00:00:16.66 | 1460K| 35371 | 10532 | 93M| 3313K| 37M (1)| 84992 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 13 | 1457K|00:00:12.01 | 1460K| 24839 | 0 | | | | |
|* 5 | INDEX RANGE SCAN | I | 1 | 13 | 1457K|00:00:01.53 | 3221 | 3220 | 0 | | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

25 rows selected.

Elapsed: 00:00:00.17
SQL>
SQL> exec :x := 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> select
2 sum(row_num)
3 from
4 (
5 select
6 row_number() over (partition by object_type order by object_name) as row_num
7 , t.*
8 from
9 t
10 where
11 object_id > :x
12 );
2.7361E+11

Elapsed: 00:00:05.10
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
SQL_ID fmbq5ytmh0hng, child number 1
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 2719131525

----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:05.10 | 21539 | 29966 | 8375 | | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:05.10 | 21539 | 29966 | 8375 | | | | |
| 2 | VIEW | | 1 | 1457K| 1457K|00:00:04.98 | 21539 | 29966 | 8375 | | | | |
| 3 | WINDOW SORT | | 1 | 1457K| 1457K|00:00:04.80 | 21539 | 29966 | 8375 | 74M| 2971K| 37M (1)| 67584 |
|* 4 | TABLE ACCESS FULL| T | 1 | 1457K| 1457K|00:00:01.38 | 21532 | 21528 | 0 | | | | |
----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("OBJECT_ID">:X)

24 rows selected.

Elapsed: 00:00:00.05
SQL>
SQL> set heading on
SQL>
SQL> select
2 sql_id
3 , child_number
4 , executions
5 , parse_calls
6 , buffer_gets
7 , is_bind_sensitive
8 , is_bind_aware
9 from
10 v$sql
11 where
12 sql_id = '&sql_id';
old 12: sql_id = '&sql_id'
new 12: sql_id = 'fmbq5ytmh0hng'
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I
------------- ------------ ---------- ----------- ----------- - -
fmbq5ytmh0hng 0 2 3 1460762 Y N
fmbq5ytmh0hng 1 1 0 21539 Y Y

Elapsed: 00:00:00.00
SQL>

So I intentionally use bind variables in a dangerous way here: The "object_id > :x" predicate is a potential threat - depending on the value of :x the usage of the index on OBJECT_ID can be a very good or a very bad idea.

From this example we therefore can tell a couple of things you should know about ACS:

1. ACS is not limited to the typical "skewed column data" / "histogram" case. We can see that I explicitly did not create any histograms (method_opt "for all columns size 1"), still ACS marked the cursor as "BIND_SENSITIVE" and eventually created a second child cursor with a more appropriate execution plan for the bind value, because it detected the "expression > :x" predicate.

2. However, and this one of the most important aspects, for ACS to detect the problem, it first has to go wrong at least once. As you can see from the example it was only the second execution using the "bad" bind value that created the child cursor. So depending on how "wrong" things can go - think of a more complex execution plan including multiple joins - it might already be "too late" for your application: If for example a query is not going to return in a reasonable amount of time your application might effectively be "broken" even if ACS might be able to "fix" the problem at the next execution...

3. Another important point to consider is that the ACS information is not stored persistently but only exists in the Shared Pool. This means, if the information is aged out of the Shared Pool for whatever reason, the same mistake has to be made again for ACS to detect the problem. So if your problematic cursors age out of the Shared Pool it will potentially go "wrong" again with the next execution before ACS can come for help.

From this you can see that - although ACS is a great feature - it is not a "Silver Bullet" for all possible cases. You still might need to help your database for those special cases where ACS is not able to support your application properly. How you can help your database? More on that later.

Now let's have a look at the point that ACS "does not work with embedded SQL in PL/SQL". Therefore I put together the following script that in principle does the same as the first script, but this time uses the most commonly used techniques in PL/SQL to execute SQL:

--------------------------------------------------------------------------------
--
-- File name: adaptive_cursor_sharing_2.sql
--
-- Purpose: Demonstrate adaptive cursor sharing is not working with PL/SQL when
-- optimizations are enabled, in particular holding cursors open
-- See bug 8357294
--
-- Author: Randolf Geist
--
-- Last tested: December 2010
--
-- Versions: 11.1.0.7
-- 11.2.0.2
--------------------------------------------------------------------------------

set echo on define on tab off

drop table t;
purge table t;
create table t as select * from dba_objects, (select rownum from dual connect by level <= 20);
create index i on t(object_id);

exec dbms_stats.gather_table_stats(user, 'T', method_opt => 'for all columns size 1')

set echo on

select count(*), min(object_id)
, max(object_id) from t;

set echo on linesize 200 heading on tab off serveroutput off

set timing on

-- This is a workaround that disables also the cursor optimizations in PL/SQL
-- In particular the PL/SQL hold cursor open optimization
-- Therefore the cursor gets reparsed and ACS kicks in
alter session set session_cached_cursors = 0;
--
-- Does this make any difference? No
-- alter session set plsql_optimize_level = 0;

variable sql_id varchar2(255)

-- Static SQL
alter system flush buffer_cache;

alter system flush shared_pool;

declare
x integer;
n_dummy number;
begin
for i in 1..10 loop
if i = 1 then
x := 100000;
else
x := 1;
end if;
select
sum(row_num)
into
n_dummy
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > x
);
if i = 1 then
select
prev_sql_id
into
:sql_id
from
v$session
where
sid = sys_context('userenv', 'sid');
end if;
end loop;
end;
/

select
sql_id
, child_number
, executions
, parse_calls
, buffer_gets
, is_bind_sensitive
, is_bind_aware
from
v$sql
where
sql_id = :sql_id;

set pagesize 0

select * from table(dbms_xplan.display_cursor(:sql_id,null,'+PEEKED_BINDS'));

set pagesize 9999

-- dynamic SQL with binds
alter system flush buffer_cache;

alter system flush shared_pool;

declare
x integer;
n_dummy number;
begin
for i in 1..10 loop
if i = 1 then
x := 100000;
else
x := 1;
end if;
execute immediate '
select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
)' into n_dummy using x;
if i = 1 then
select
prev_sql_id
into
:sql_id
from
v$session
where
sid = sys_context('userenv', 'sid');
end if;
end loop;
end;
/

select
sql_id
, child_number
, executions
, parse_calls
, buffer_gets
, is_bind_sensitive
, is_bind_aware
from
v$sql
where
sql_id = :sql_id;

set pagesize 0

select * from table(dbms_xplan.display_cursor(:sql_id,null,'+PEEKED_BINDS'));

set pagesize 9999

-- dynamic SQL with literals
alter system flush buffer_cache;

alter system flush shared_pool;

declare
x integer;
n_dummy number;
begin
for i in 1..10 loop
if i = 1 then
x := 100000;
else
x := 1;
end if;
execute immediate '
select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > ' || x || '
)' into n_dummy;
if i = 1 then
select
prev_sql_id
into
:sql_id
from
v$session
where
sid = sys_context('userenv', 'sid');
end if;
end loop;
end;
/

select
b.sql_id
, b.child_number
, b.executions
, b.parse_calls
, b.buffer_gets
, b.is_bind_sensitive
, b.is_bind_aware
from
v$sql a, v$sql b
where
a.force_matching_signature = b.force_matching_signature
and a.sql_id = :sql_id;

set pagesize 0

select
c.*
from
v$sql a
, v$sql b
, table(dbms_xplan.display_cursor(b.sql_id)) c
where
a.force_matching_signature = b.force_matching_signature
and a.sql_id = :sql_id;

set pagesize 9999

-- dynamic SQL with binds + OPEN
alter system flush buffer_cache;

alter system flush shared_pool;

declare
x integer;
n_dummy number;
c sys_refcursor;
begin
for i in 1..10 loop
if i = 1 then
x := 100000;
else
x := 1;
end if;
open c for '
select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
)' using x;
fetch c into n_dummy;
close c;
if i = 1 then
select
prev_sql_id
into
:sql_id
from
v$session
where
sid = sys_context('userenv', 'sid');
end if;
end loop;
end;
/

select
sql_id
, child_number
, executions
, parse_calls
, buffer_gets
, is_bind_sensitive
, is_bind_aware
from
v$sql
where
sql_id = :sql_id;

set pagesize 0

select * from table(dbms_xplan.display_cursor(:sql_id,null,'+PEEKED_BINDS'));

set pagesize 9999

So this script executes the same query as used in the first script ten times in a row, again deliberately using the same bind values in the same order: The first execution can benefit from the index, all following executions would better use a full table scan.

These are the different techniques used:

1. Static embedded SQL which are sometimes in PL/SQL are also called "implicit cursors". They should exhibit the same characteristics as explicitly declared cursors, which I haven't included in this script.

2. Dynamic SQL using EXECUTE IMMEDIATE and binds, so the cursor can still be shared

3. Dynamic SQL using EXECUTE IMMEDIATE and literals, which leads to different SQLs and therefore different parent cursors with potentially different executions plans since both will be parsed / optimized independently from each other

4. Dynamic SQL using OPEN / FETCH / CLOSE and binds, so the cursor again can be shared

And this is the result that I get from 11.2.0.2 (11.1.0.7 showed the same):

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

SQL>
SQL> drop table t;

Table dropped.

SQL> purge table t;

Table purged.

SQL> create table t as select * from dba_objects, (select rownum from dual connect by level <= 20);

Table created.

SQL> create index i on t(object_id);

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'T', method_opt => 'for all columns size 1')

PL/SQL procedure successfully completed.

SQL>
SQL> set echo on
SQL>
SQL> select count(*), min(object_id)
2 , max(object_id) from t;

COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID)
---------- -------------- --------------
1457480 2 75819

SQL>
SQL> set echo on linesize 200 heading on tab off serveroutput off
SQL>
SQL> set timing on
SQL>
SQL> -- This is a workaround that disables also the cursor optimizations in PL/SQL
SQL> -- In particular the PL/SQL hold cursor open optimization
SQL> -- Therefore the cursor gets reparsed and ACS kicks in
SQL> -- alter session set session_cached_cursors = 0;
SQL> --
SQL> -- Does this make any difference? No
SQL> -- alter session set plsql_optimize_level = 0;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> variable sql_id varchar2(255)
SQL>
SQL> -- Static SQL
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.03
SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.63
SQL>
SQL> declare
2 x integer;
3 n_dummy number;
4 begin
5 for i in 1..10 loop
6 if i = 1 then
7 x := 100000;
8 else
9 x := 1;
10 end if;
11 select
12 sum(row_num)
13 into
14 n_dummy
15 from
16 (
17 select
18 row_number() over (partition by object_type order by object_name) as row_num
19 , t.*
20 from
21 t
22 where
23 object_id > x
24 );
25 if i = 1 then
26 select
27 prev_sql_id
28 into
29 :sql_id
30 from
31 v$session
32 where
33 sid = sys_context('userenv', 'sid');
34 end if;
35 end loop;
36 end;
37 /

PL/SQL procedure successfully completed.

Elapsed: 00:02:50.68
SQL>
SQL> select
2 sql_id
3 , child_number
4 , executions
5 , parse_calls
6 , buffer_gets
7 , is_bind_sensitive
8 , is_bind_aware
9 from
10 v$sql
11 where
12 sql_id = :sql_id;

SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I
------------- ------------ ---------- ----------- ----------- - -
g89tq3q521rbg 0 10 1 13146414 Y N

Elapsed: 00:00:00.17
SQL>
SQL> set pagesize 0
SQL>
SQL> select * from table(dbms_xplan.display_cursor(:sql_id,null,'+PEEKED_BINDS'));
SQL_ID g89tq3q521rbg, child number 0
-------------------------------------
SELECT SUM(ROW_NUM) FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY
OBJECT_TYPE ORDER BY OBJECT_NAME) AS ROW_NUM , T.* FROM T WHERE
OBJECT_ID > :B1 )

Plan hash value: 1399240396

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | | 13 | 169 | 18 (6)| 00:00:01 |
| 3 | WINDOW SORT | | 13 | 507 | 18 (6)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 13 | 507 | 17 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I | 13 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :B1 (NUMBER): 100000

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:B1)

29 rows selected.

Elapsed: 00:00:01.86
SQL>
SQL> set pagesize 9999
SQL>
SQL> -- dynamic SQL with binds
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.09
SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.84
SQL>
SQL> declare
2 x integer;
3 n_dummy number;
4 begin
5 for i in 1..10 loop
6 if i = 1 then
7 x := 100000;
8 else
9 x := 1;
10 end if;
11 execute immediate '
12 select
13 sum(row_num)
14 from
15 (
16 select
17 row_number() over (partition by object_type order by object_name) as row_num
18 , t.*
19 from
20 t
21 where
22 object_id > :x
23 )' into n_dummy using x;
24 if i = 1 then
25 select
26 prev_sql_id
27 into
28 :sql_id
29 from
30 v$session
31 where
32 sid = sys_context('userenv', 'sid');
33 end if;
34 end loop;
35 end;
36 /

PL/SQL procedure successfully completed.

Elapsed: 00:03:17.26
SQL>
SQL> select
2 sql_id
3 , child_number
4 , executions
5 , parse_calls
6 , buffer_gets
7 , is_bind_sensitive
8 , is_bind_aware
9 from
10 v$sql
11 where
12 sql_id = :sql_id;

SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I
------------- ------------ ---------- ----------- ----------- - -
3snga4x0swy99 0 10 1 13146434 Y N

Elapsed: 00:00:00.14
SQL>
SQL> set pagesize 0
SQL>
SQL> select * from table(dbms_xplan.display_cursor(:sql_id,null,'+PEEKED_BINDS'));
SQL_ID 3snga4x0swy99, child number 0
-------------------------------------
select sum(row_num) from (
select row_number() over (partition by object_type
order by object_name) as row_num , t.*
from t where
object_id > :x )

Plan hash value: 1399240396

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | | 13 | 169 | 18 (6)| 00:00:01 |
| 3 | WINDOW SORT | | 13 | 507 | 18 (6)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 13 | 507 | 17 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I | 13 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :X (NUMBER): 100000

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

31 rows selected.

Elapsed: 00:00:01.35
SQL>
SQL> set pagesize 9999
SQL>
SQL> -- dynamic SQL with literals
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.05
SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.66
SQL>
SQL> declare
2 x integer;
3 n_dummy number;
4 begin
5 for i in 1..10 loop
6 if i = 1 then
7 x := 100000;
8 else
9 x := 1;
10 end if;
11 execute immediate '
12 select
13 sum(row_num)
14 from
15 (
16 select
17 row_number() over (partition by object_type order by object_name) as row_num
18 , t.*
19 from
20 t
21 where
22 object_id > ' || x || '
23 )' into n_dummy;
24 if i = 1 then
25 select
26 prev_sql_id
27 into
28 :sql_id
29 from
30 v$session
31 where
32 sid = sys_context('userenv', 'sid');
33 end if;
34 end loop;
35 end;
36 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:52.29
SQL>
SQL> select
2 b.sql_id
3 , b.child_number
4 , b.executions
5 , b.parse_calls
6 , b.buffer_gets
7 , b.is_bind_sensitive
8 , b.is_bind_aware
9 from
10 v$sql a, v$sql b
11 where
12 a.force_matching_signature = b.force_matching_signature
13 and a.sql_id = :sql_id;

SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I
------------- ------------ ---------- ----------- ----------- - -
2tgumwrfx40bu 0 1 1 53 N N
03zrk5tr7zsn4 0 9 1 193851 N N

Elapsed: 00:00:00.10
SQL>
SQL> set pagesize 0
SQL>
SQL> select
2 c.*
3 from
4 v$sql a
5 , v$sql b
6 , table(dbms_xplan.display_cursor(b.sql_id)) c
7 where
8 a.force_matching_signature = b.force_matching_signature
9 and a.sql_id = :sql_id;
SQL_ID 2tgumwrfx40bu, child number 0
-------------------------------------
select sum(row_num) from (
select row_number() over (partition by object_type
order by object_name) as row_num , t.*
from t where
object_id > 100000 )

Plan hash value: 1399240396

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (100)| |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
| 2 | VIEW | | 13 | 195 | 18 (6)| 00:00:01 |
| 3 | WINDOW SORT | | 13 | 507 | 18 (6)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 13 | 507 | 17 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I | 13 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">100000)

SQL_ID 03zrk5tr7zsn4, child number 0
-------------------------------------
select sum(row_num) from (
select row_number() over (partition by object_type
order by object_name) as row_num , t.*
from t where
object_id > 1 )

Plan hash value: 2719131525

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 20588 (100)| |
| 1 | SORT AGGREGATE | | 1 | 15 | | | |
| 2 | VIEW | | 1457K| 20M| | 20588 (1)| 00:04:08 |
| 3 | WINDOW SORT | | 1457K| 54M| 72M| 20588 (1)| 00:04:08 |
|* 4 | TABLE ACCESS FULL| T | 1457K| 54M| | 5894 (2)| 00:01:11 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("OBJECT_ID">1)

51 rows selected.

Elapsed: 00:00:01.24
SQL>
SQL> set pagesize 9999
SQL>
SQL> -- dynamic SQL with binds + OPEN
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.04
SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.66
SQL>
SQL> declare
2 x integer;
3 n_dummy number;
4 c sys_refcursor;
5 begin
6 for i in 1..10 loop
7 if i = 1 then
8 x := 100000;
9 else
10 x := 1;
11 end if;
12 open c for '
13 select
14 sum(row_num)
15 from
16 (
17 select
18 row_number() over (partition by object_type order by object_name) as row_num
19 , t.*
20 from
21 t
22 where
23 object_id > :x
24 )' using x;
25 fetch c into n_dummy;
26 close c;
27 if i = 1 then
28 select
29 prev_sql_id
30 into
31 :sql_id
32 from
33 v$session
34 where
35 sid = sys_context('userenv', 'sid');
36 end if;
37 end loop;
38 end;
39 /

PL/SQL procedure successfully completed.

Elapsed: 00:01:12.12
SQL>
SQL> select
2 sql_id
3 , child_number
4 , executions
5 , parse_calls
6 , buffer_gets
7 , is_bind_sensitive
8 , is_bind_aware
9 from
10 v$sql
11 where
12 sql_id = :sql_id;

SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I
------------- ------------ ---------- ----------- ----------- - -
3snga4x0swy99 0 2 3 1460762 Y N
3snga4x0swy99 1 8 7 172312 Y Y

Elapsed: 00:00:00.16
SQL>
SQL> set pagesize 0
SQL>
SQL> select * from table(dbms_xplan.display_cursor(:sql_id,null,'+PEEKED_BINDS'));
SQL_ID 3snga4x0swy99, child number 0
-------------------------------------
select sum(row_num) from (
select row_number() over (partition by object_type
order by object_name) as row_num , t.*
from t where
object_id > :x )

Plan hash value: 1399240396

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | | 13 | 169 | 18 (6)| 00:00:01 |
| 3 | WINDOW SORT | | 13 | 507 | 18 (6)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 13 | 507 | 17 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I | 13 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :X (NUMBER): 100000

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

SQL_ID 3snga4x0swy99, child number 1
-------------------------------------
select sum(row_num) from (
select row_number() over (partition by object_type
order by object_name) as row_num , t.*
from t where
object_id > :x )

Plan hash value: 2719131525

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 20588 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
| 2 | VIEW | | 1457K| 18M| | 20588 (1)| 00:04:08 |
| 3 | WINDOW SORT | | 1457K| 54M| 72M| 20588 (1)| 00:04:08 |
|* 4 | TABLE ACCESS FULL| T | 1457K| 54M| | 5894 (2)| 00:01:11 |
-------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :X (NUMBER): 1

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("OBJECT_ID">:X)

61 rows selected.

Elapsed: 00:00:01.36
SQL>
SQL> set pagesize 9999
SQL>

So this script shows some interesting results:

1. It can be seen that ACS does not seem to work with several of the used variations: Both the static and the dynamic SQL using EXECUTE IMMEDIATE and binds do not create a second child cursor and re-use the same execution plan for all ten executions. Although the cursor has been marked as BIND_SENSITIVE, it has not been marked as BIND_AWARE.

Both execution variants also share the fact that there seems to be a PL/SQL optimization in place that allows to keep the cursors open - there is only a single PARSE call recorded in V$SQL for this SQL statement, although we loop through the same code ten times and therefore should in principle see as many parse as execution calls.

You would even see the same optimization in place when explicit cursors were used with explicit OPEN / FETCH / CLOSE calls: Albeit the explicit CLOSE you would still see only a single PARSE call for the statement - PL/SQL holds the cursor open for maximum performance and scalibility.

2. Another interesting side note is that the new feature "Cardinality Feedback" that has been introduced with Oracle 11.2 seems also to be not working in these examples, but this is definitely something for another post.

"Cardinality Feedback" allows Oracle to compare the cardinality estimates of the cost-based optimizer with the actual cardinalities at execution time and perform "instant tuning" by automatically adding OPT_ESTIMATE and other hints to the statement to correct the cardinality estimates and do a re-optimization of the "tuned" statement. Again this information only resides in the Shared Pool and will be potentially aged out like the ACS information.

3. The clear winner in terms of both elapsed and performed logical I/O is the third variant that creates two different cursors using literals. For both cursors the optimal execution plan has been found and nothing had to go "wrong" first to be fixed afterwards, therefore the total elapsed time and number of buffer gets is significantly lower than in the other cases. This gives us already a hint regarding the point "How can we help the database in such cases?"

Note also that although the number of buffer gets is significantly lower compared to the other cases, the elapsed time is not that much different, which is another example that buffer gets are not necessarily a good indicator for the overall work performed. In this particular case a lot of time is spent on writing and reading from the temporary tablespace for the WINDOW SORT operation, and since this operation has more or less to do the same work in all cases where the whole table needs to read in whatever efficient or inefficient way, the overall runtime does not differ as dramatically as the number of buffer gets. You could see this additional work in this example by checking the columns DISK_READS and DIRECT_WRITES from V$SQL and of course by checking the ASH / extended SQL trace profile.

4. The fourth variant using the dynamic SQL with OPEN obviously does not benefit from the PL/SQL optimization: V$SQL clearly shows that there have been as many PARSE calls as EXECUTE calls. Interestingly in this case ACS worked and created a second child cursor. This gives us already a hint what seems to prevent ACS from working.

The shown behaviour of ACS not working is officially documented in My Oracle Support, however not available in the Knowledge Base but only as bug number 8357294: ADAPTIVE CURSOR SHARING DOESN'T WORK FOR SQL FROM PL/SQL. It is interesting to note that the very detailed bug description does not mention a fix release or patch, but does mention that setting "session_cached_cursors" to 0 allows ACS again to work.

And indeed, when running the same script again with "session_cached_cursors" set to 0, I get this result:

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

SQL>
SQL> drop table t;

Table dropped.

SQL> purge table t;

Table purged.

SQL> create table t as select * from dba_objects, (select rownum from dual connect by level <= 20);

Table created.

SQL> create index i on t(object_id);

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'T', method_opt => 'for all columns size 1')

PL/SQL procedure successfully completed.

SQL>
SQL> set echo on
SQL>
SQL> select count(*), min(object_id)
2 , max(object_id) from t;

COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID)
---------- -------------- --------------
1457480 2 75821

SQL>
SQL> set echo on linesize 200 heading on tab off serveroutput off
SQL>
SQL> set timing on
SQL>
SQL> -- This is a workaround that disables also the cursor optimizations in PL/SQL
SQL> -- In particular the PL/SQL hold cursor open optimization
SQL> -- Therefore the cursor gets reparsed and ACS kicks in
SQL> alter session set session_cached_cursors = 0;

Session altered.

Elapsed: 00:00:00.00
SQL> --
SQL> -- Does this make any difference? No
SQL> -- alter session set plsql_optimize_level = 0;
SQL>
SQL> variable sql_id varchar2(255)
SQL>
SQL> -- Static SQL
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.05
SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.71
SQL>
SQL> declare
2 x integer;
3 n_dummy number;
4 begin
5 for i in 1..10 loop
6 if i = 1 then
7 x := 100000;
8 else
9 x := 1;
10 end if;
11 select
12 sum(row_num)
13 into
14 n_dummy
15 from
16 (
17 select
18 row_number() over (partition by object_type order by object_name) as row_num
19 , t.*
20 from
21 t
22 where
23 object_id > x
24 );
25 if i = 1 then
26 select
27 prev_sql_id
28 into
29 :sql_id
30 from
31 v$session
32 where
33 sid = sys_context('userenv', 'sid');
34 end if;
35 end loop;
36 end;
37 /

PL/SQL procedure successfully completed.

Elapsed: 00:01:00.82
SQL>
SQL> select
2 sql_id
3 , child_number
4 , executions
5 , parse_calls
6 , buffer_gets
7 , is_bind_sensitive
8 , is_bind_aware
9 from
10 v$sql
11 where
12 sql_id = :sql_id;

SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I
------------- ------------ ---------- ----------- ----------- - -
g89tq3q521rbg 0 2 3 1460742 Y N
g89tq3q521rbg 1 8 7 172312 Y Y

Elapsed: 00:00:00.16
SQL>
SQL> set pagesize 0
SQL>
SQL> select * from table(dbms_xplan.display_cursor(:sql_id,null,'+PEEKED_BINDS'));
SQL_ID g89tq3q521rbg, child number 0
-------------------------------------
SELECT SUM(ROW_NUM) FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY
OBJECT_TYPE ORDER BY OBJECT_NAME) AS ROW_NUM , T.* FROM T WHERE
OBJECT_ID > :B1 )

Plan hash value: 1399240396

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | | 13 | 169 | 18 (6)| 00:00:01 |
| 3 | WINDOW SORT | | 13 | 507 | 18 (6)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 13 | 507 | 17 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I | 13 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :B1 (NUMBER): 100000

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:B1)

SQL_ID g89tq3q521rbg, child number 1
-------------------------------------
SELECT SUM(ROW_NUM) FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY
OBJECT_TYPE ORDER BY OBJECT_NAME) AS ROW_NUM , T.* FROM T WHERE
OBJECT_ID > :B1 )

Plan hash value: 2719131525

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 20588 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
| 2 | VIEW | | 1457K| 18M| | 20588 (1)| 00:04:08 |
| 3 | WINDOW SORT | | 1457K| 54M| 72M| 20588 (1)| 00:04:08 |
|* 4 | TABLE ACCESS FULL| T | 1457K| 54M| | 5894 (2)| 00:01:11 |
-------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :B1 (NUMBER): 1

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("OBJECT_ID">:B1)

57 rows selected.

Elapsed: 00:00:01.50
SQL>
SQL> set pagesize 9999
SQL>
SQL> -- dynamic SQL with binds
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.07
SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.70
SQL>
SQL> declare
2 x integer;
3 n_dummy number;
4 begin
5 for i in 1..10 loop
6 if i = 1 then
7 x := 100000;
8 else
9 x := 1;
10 end if;
11 execute immediate '
12 select
13 sum(row_num)
14 from
15 (
16 select
17 row_number() over (partition by object_type order by object_name) as row_num
18 , t.*
19 from
20 t
21 where
22 object_id > :x
23 )' into n_dummy using x;
24 if i = 1 then
25 select
26 prev_sql_id
27 into
28 :sql_id
29 from
30 v$session
31 where
32 sid = sys_context('userenv', 'sid');
33 end if;
34 end loop;
35 end;
36 /

PL/SQL procedure successfully completed.

Elapsed: 00:01:12.41
SQL>
SQL> select
2 sql_id
3 , child_number
4 , executions
5 , parse_calls
6 , buffer_gets
7 , is_bind_sensitive
8 , is_bind_aware
9 from
10 v$sql
11 where
12 sql_id = :sql_id;

SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I
------------- ------------ ---------- ----------- ----------- - -
3snga4x0swy99 0 2 3 1460762 Y N
3snga4x0swy99 1 8 7 172312 Y Y

Elapsed: 00:00:00.13
SQL>
SQL> set pagesize 0
SQL>
SQL> select * from table(dbms_xplan.display_cursor(:sql_id,null,'+PEEKED_BINDS'));
SQL_ID 3snga4x0swy99, child number 0
-------------------------------------
select sum(row_num) from (
select row_number() over (partition by object_type
order by object_name) as row_num , t.*
from t where
object_id > :x )

Plan hash value: 1399240396

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | | 13 | 169 | 18 (6)| 00:00:01 |
| 3 | WINDOW SORT | | 13 | 507 | 18 (6)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 13 | 507 | 17 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I | 13 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :X (NUMBER): 100000

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

SQL_ID 3snga4x0swy99, child number 1
-------------------------------------
select sum(row_num) from (
select row_number() over (partition by object_type
order by object_name) as row_num , t.*
from t where
object_id > :x )

Plan hash value: 2719131525

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 20588 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
| 2 | VIEW | | 1457K| 18M| | 20588 (1)| 00:04:08 |
| 3 | WINDOW SORT | | 1457K| 54M| 72M| 20588 (1)| 00:04:08 |
|* 4 | TABLE ACCESS FULL| T | 1457K| 54M| | 5894 (2)| 00:01:11 |
-------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :X (NUMBER): 1

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("OBJECT_ID">:X)

61 rows selected.

Elapsed: 00:00:01.10
SQL>
SQL> set pagesize 9999
SQL>
SQL> -- dynamic SQL with literals
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.03
SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.69
SQL>
SQL> declare
2 x integer;
3 n_dummy number;
4 begin
5 for i in 1..10 loop
6 if i = 1 then
7 x := 100000;
8 else
9 x := 1;
10 end if;
11 execute immediate '
12 select
13 sum(row_num)
14 from
15 (
16 select
17 row_number() over (partition by object_type order by object_name) as row_num
18 , t.*
19 from
20 t
21 where
22 object_id > ' || x || '
23 )' into n_dummy;
24 if i = 1 then
25 select
26 prev_sql_id
27 into
28 :sql_id
29 from
30 v$session
31 where
32 sid = sys_context('userenv', 'sid');
33 end if;
34 end loop;
35 end;
36 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:55.97
SQL>
SQL> select
2 b.sql_id
3 , b.child_number
4 , b.executions
5 , b.parse_calls
6 , b.buffer_gets
7 , b.is_bind_sensitive
8 , b.is_bind_aware
9 from
10 v$sql a, v$sql b
11 where
12 a.force_matching_signature = b.force_matching_signature
13 and a.sql_id = :sql_id;

SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I
------------- ------------ ---------- ----------- ----------- - -
2tgumwrfx40bu 0 1 1 53 N N
03zrk5tr7zsn4 0 9 9 193851 N N

Elapsed: 00:00:00.11
SQL>
SQL> set pagesize 0
SQL>
SQL> select
2 c.*
3 from
4 v$sql a
5 , v$sql b
6 , table(dbms_xplan.display_cursor(b.sql_id)) c
7 where
8 a.force_matching_signature = b.force_matching_signature
9 and a.sql_id = :sql_id;
SQL_ID 2tgumwrfx40bu, child number 0
-------------------------------------
select sum(row_num) from (
select row_number() over (partition by object_type
order by object_name) as row_num , t.*
from t where
object_id > 100000 )

Plan hash value: 1399240396

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (100)| |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
| 2 | VIEW | | 13 | 195 | 18 (6)| 00:00:01 |
| 3 | WINDOW SORT | | 13 | 507 | 18 (6)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 13 | 507 | 17 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I | 13 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">100000)

SQL_ID 03zrk5tr7zsn4, child number 0
-------------------------------------
select sum(row_num) from (
select row_number() over (partition by object_type
order by object_name) as row_num , t.*
from t where
object_id > 1 )

Plan hash value: 2719131525

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 20588 (100)| |
| 1 | SORT AGGREGATE | | 1 | 15 | | | |
| 2 | VIEW | | 1457K| 20M| | 20588 (1)| 00:04:08 |
| 3 | WINDOW SORT | | 1457K| 54M| 72M| 20588 (1)| 00:04:08 |
|* 4 | TABLE ACCESS FULL| T | 1457K| 54M| | 5894 (2)| 00:01:11 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("OBJECT_ID">1)

51 rows selected.

Elapsed: 00:00:01.17
SQL>
SQL> set pagesize 9999
SQL>
SQL> -- dynamic SQL with binds + OPEN
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.04
SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.73
SQL>
SQL> declare
2 x integer;
3 n_dummy number;
4 c sys_refcursor;
5 begin
6 for i in 1..10 loop
7 if i = 1 then
8 x := 100000;
9 else
10 x := 1;
11 end if;
12 open c for '
13 select
14 sum(row_num)
15 from
16 (
17 select
18 row_number() over (partition by object_type order by object_name) as row_num
19 , t.*
20 from
21 t
22 where
23 object_id > :x
24 )' using x;
25 fetch c into n_dummy;
26 close c;
27 if i = 1 then
28 select
29 prev_sql_id
30 into
31 :sql_id
32 from
33 v$session
34 where
35 sid = sys_context('userenv', 'sid');
36 end if;
37 end loop;
38 end;
39 /

PL/SQL procedure successfully completed.

Elapsed: 00:01:05.62
SQL>
SQL> select
2 sql_id
3 , child_number
4 , executions
5 , parse_calls
6 , buffer_gets
7 , is_bind_sensitive
8 , is_bind_aware
9 from
10 v$sql
11 where
12 sql_id = :sql_id;

SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I
------------- ------------ ---------- ----------- ----------- - -
3snga4x0swy99 0 2 3 1460762 Y N
3snga4x0swy99 1 8 7 172312 Y Y

Elapsed: 00:00:00.16
SQL>
SQL> set pagesize 0
SQL>
SQL> select * from table(dbms_xplan.display_cursor(:sql_id,null,'+PEEKED_BINDS'));
SQL_ID 3snga4x0swy99, child number 0
-------------------------------------
select sum(row_num) from (
select row_number() over (partition by object_type
order by object_name) as row_num , t.*
from t where
object_id > :x )

Plan hash value: 1399240396

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | | 13 | 169 | 18 (6)| 00:00:01 |
| 3 | WINDOW SORT | | 13 | 507 | 18 (6)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 13 | 507 | 17 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I | 13 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :X (NUMBER): 100000

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

SQL_ID 3snga4x0swy99, child number 1
-------------------------------------
select sum(row_num) from (
select row_number() over (partition by object_type
order by object_name) as row_num , t.*
from t where
object_id > :x )

Plan hash value: 2719131525

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 20588 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
| 2 | VIEW | | 1457K| 18M| | 20588 (1)| 00:04:08 |
| 3 | WINDOW SORT | | 1457K| 54M| 72M| 20588 (1)| 00:04:08 |
|* 4 | TABLE ACCESS FULL| T | 1457K| 54M| | 5894 (2)| 00:01:11 |
-------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :X (NUMBER): 1

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("OBJECT_ID">:X)

61 rows selected.

Elapsed: 00:00:01.57
SQL>
SQL> set pagesize 9999
SQL>

Now in all cases where the cursor is potentially shared we can see that ACS eventually kicks in and creates a second child cursor. Notice however that this comes at a price: All cases show now that there are as many PARSE calls as executions.

So this seems to mean that disabling the session cursor cache actually disabled also the PL/SQL optimization that prevents the repeated PARSE calls by keeping the cursors open.

Remember that the session cursor cache is merely a means to make a soft PARSE call even _softer_ by avoiding the otherwise required access to the Shared Pool library cache resources because the cursor information has been cached in a private memory area of the process, but the session cursor cache does not avoid the PARSE call itself - this can only be done on application resp. client side, not on database side.

In order to understand if the session cursor cache or the "keep cursors open" optimization affects ACS we can run the following simple script:

--------------------------------------------------------------------------------
--
-- File name: adaptive_cursor_sharing_3.sql
--
-- Purpose: Check if the session cursor cache affects adaptive cursor sharing
--
-- Author: Randolf Geist
--
-- Last tested: December 2010
--
-- Versions: 11.1.0.7
-- 11.2.0.2
--------------------------------------------------------------------------------

set echo on define on tab off

drop table t;
purge table t;
create table t as select * from dba_objects, (select rownum from dual connect by level <= 20);
create index i on t(object_id);

exec dbms_stats.gather_table_stats(user, 'T', method_opt => 'for all columns size 1')

set echo on

select count(*), min(object_id)
, max(object_id) from t;

set echo on linesize 200 heading off newpage none tab off serveroutput off

alter session set statistics_level = all;

-- alter session set session_cached_cursors = 0;

alter system flush buffer_cache;

alter system flush shared_pool;

var x number

exec :x := 100000

-- pause

set timing on

select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
);

column prev_sql_id new_value sql_id noprint
column sid new_value session_id noprint

select
prev_sql_id
, sid
from
v$session
where
sid = sys_context('userenv', 'sid');

select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));

exec :x := 100000

column value new_value value_1

select
name
, value
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
);

select
name
, value - &value_1 as diff
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));

exec :x := 100000

select
name
, value
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
);

select
name
, value - &value_1 as diff
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));

exec :x := 100000

select
name
, value
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
);

select
name
, value - &value_1 as diff
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));

exec :x := 100000

select
name
, value
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
);

select
name
, value - &value_1 as diff
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));

exec :x := 1

select
name
, value
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
);

select
name
, value - &value_1 as diff
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));

exec :x := 1

select
name
, value
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
);

select
name
, value - &value_1 as diff
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));

exec :x := 1

select
name
, value
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
);

select
name
, value - &value_1 as diff
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));

set heading on

select
sql_id
, child_number
, executions
, parse_calls
, buffer_gets
, is_bind_sensitive
, is_bind_aware
from
v$sql
where
sql_id = '&sql_id';

This script simply runs the same SQL that we already know sufficiently often to ensure that it gets cached in the session cursor cache by executing it at least three times. Then in one of the next executions the "bad" bind values is used to see if the caching in the session cursor cache somehow prevents ACS from kicking in. Here is the result, this time from 11.1.0.7 (but again I got the same from 11.2.0.2):

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

SQL>
SQL> drop table t;

Table dropped.

SQL> purge table t;

Table purged.

SQL> create table t as select * from dba_objects, (select rownum from dual connect by level <= 20);

Table created.

SQL> create index i on t(object_id);

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'T', method_opt => 'for all columns size 1')

PL/SQL procedure successfully completed.

SQL>
SQL> set echo on
SQL>
SQL> select count(*), min(object_id)
2 , max(object_id) from t;

COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID)
---------- -------------- --------------
1385560 2 71521

SQL>
SQL> set echo on linesize 200 heading off newpage none tab off serveroutput off
SQL>
SQL> alter session set statistics_level = all;

Session altered.

SQL>
SQL> -- alter session set session_cached_cursors = 0;
SQL>
SQL> alter system flush buffer_cache;

System altered.

SQL>
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> var x number
SQL>
SQL> exec :x := 100000

PL/SQL procedure successfully completed.

SQL>
SQL> -- pause
SQL>
SQL> set timing on
SQL>
SQL> select
2 sum(row_num)
3 from
4 (
5 select
6 row_number() over (partition by object_type order by object_name) as row_num
7 , t.*
8 from
9 t
10 where
11 object_id > :x
12 );

Elapsed: 00:00:00.01
SQL>
SQL> column prev_sql_id new_value sql_id noprint
SQL> column sid new_value session_id noprint
SQL>
SQL> select
2 prev_sql_id
3 , sid
4 from
5 v$session
6 where
7 sid = sys_context('userenv', 'sid');

Elapsed: 00:00:00.06
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));
old 1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'))
new 1: select * from table(dbms_xplan.display_cursor('fmbq5ytmh0hng',null,'ALLSTATS LAST'))
SQL_ID fmbq5ytmh0hng, child number 0
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 1399240396

-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | 3 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | 3 | | | |
| 2 | VIEW | | 1 | 12 | 0 |00:00:00.01 | 3 | 3 | | | |
| 3 | WINDOW SORT | | 1 | 12 | 0 |00:00:00.01 | 3 | 3 | 1024 | 1024 | |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 0 |00:00:00.01 | 3 | 3 | | | |
|* 5 | INDEX RANGE SCAN | I | 1 | 12 | 0 |00:00:00.01 | 3 | 3 | | | |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

25 rows selected.

Elapsed: 00:00:00.46
SQL>
SQL> exec :x := 100000

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> column value new_value value_1
SQL>
SQL> select
2 name
3 , value
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
session cursor cache hits 5058

Elapsed: 00:00:00.01
SQL>
SQL> select
2 sum(row_num)
3 from
4 (
5 select
6 row_number() over (partition by object_type order by object_name) as row_num
7 , t.*
8 from
9 t
10 where
11 object_id > :x
12 );

Elapsed: 00:00:00.01
SQL>
SQL> select
2 name
3 , value - &value_1 as diff
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
old 3: , value - &value_1 as diff
new 3: , value - 5058 as diff
session cursor cache hits 2

Elapsed: 00:00:00.00
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));
old 1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'))
new 1: select * from table(dbms_xplan.display_cursor('fmbq5ytmh0hng',null,'ALLSTATS LAST'))
SQL_ID fmbq5ytmh0hng, child number 0
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 1399240396

----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
| 2 | VIEW | | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
| 3 | WINDOW SORT | | 1 | 12 | 0 |00:00:00.01 | 3 | 1024 | 1024 | |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
|* 5 | INDEX RANGE SCAN | I | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

25 rows selected.

Elapsed: 00:00:00.10
SQL>
SQL> exec :x := 100000

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> select
2 name
3 , value
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
session cursor cache hits 5063

Elapsed: 00:00:00.00
SQL>
SQL> select
2 sum(row_num)
3 from
4 (
5 select
6 row_number() over (partition by object_type order by object_name) as row_num
7 , t.*
8 from
9 t
10 where
11 object_id > :x
12 );

Elapsed: 00:00:00.01
SQL>
SQL> select
2 name
3 , value - &value_1 as diff
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
old 3: , value - &value_1 as diff
new 3: , value - 5063 as diff
session cursor cache hits 2

Elapsed: 00:00:00.00
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));
old 1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'))
new 1: select * from table(dbms_xplan.display_cursor('fmbq5ytmh0hng',null,'ALLSTATS LAST'))
SQL_ID fmbq5ytmh0hng, child number 0
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 1399240396

----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
| 2 | VIEW | | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
| 3 | WINDOW SORT | | 1 | 12 | 0 |00:00:00.01 | 3 | 1024 | 1024 | |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
|* 5 | INDEX RANGE SCAN | I | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

25 rows selected.

Elapsed: 00:00:00.10
SQL>
SQL> exec :x := 100000

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL>
SQL> select
2 name
3 , value
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
session cursor cache hits 5069

Elapsed: 00:00:00.00
SQL>
SQL> select
2 sum(row_num)
3 from
4 (
5 select
6 row_number() over (partition by object_type order by object_name) as row_num
7 , t.*
8 from
9 t
10 where
11 object_id > :x
12 );

Elapsed: 00:00:00.01
SQL>
SQL> select
2 name
3 , value - &value_1 as diff
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
old 3: , value - &value_1 as diff
new 3: , value - 5069 as diff
session cursor cache hits 3

Elapsed: 00:00:00.00
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));
old 1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'))
new 1: select * from table(dbms_xplan.display_cursor('fmbq5ytmh0hng',null,'ALLSTATS LAST'))
SQL_ID fmbq5ytmh0hng, child number 0
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 1399240396

----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
| 2 | VIEW | | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
| 3 | WINDOW SORT | | 1 | 12 | 0 |00:00:00.01 | 3 | 1024 | 1024 | |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
|* 5 | INDEX RANGE SCAN | I | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

25 rows selected.

Elapsed: 00:00:00.11
SQL>
SQL> exec :x := 100000

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> select
2 name
3 , value
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
session cursor cache hits 5080

Elapsed: 00:00:00.00
SQL>
SQL> select
2 sum(row_num)
3 from
4 (
5 select
6 row_number() over (partition by object_type order by object_name) as row_num
7 , t.*
8 from
9 t
10 where
11 object_id > :x
12 );

Elapsed: 00:00:00.01
SQL>
SQL> select
2 name
3 , value - &value_1 as diff
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
old 3: , value - &value_1 as diff
new 3: , value - 5080 as diff
session cursor cache hits 3

Elapsed: 00:00:00.00
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));
old 1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'))
new 1: select * from table(dbms_xplan.display_cursor('fmbq5ytmh0hng',null,'ALLSTATS LAST'))
SQL_ID fmbq5ytmh0hng, child number 0
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 1399240396

----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
| 2 | VIEW | | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
| 3 | WINDOW SORT | | 1 | 12 | 0 |00:00:00.01 | 3 | 1024 | 1024 | |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
|* 5 | INDEX RANGE SCAN | I | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

25 rows selected.

Elapsed: 00:00:00.10
SQL>
SQL> exec :x := 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> select
2 name
3 , value
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
session cursor cache hits 5090

Elapsed: 00:00:00.00
SQL>
SQL> select
2 sum(row_num)
3 from
4 (
5 select
6 row_number() over (partition by object_type order by object_name) as row_num
7 , t.*
8 from
9 t
10 where
11 object_id > :x
12 );
2.5311E+11

Elapsed: 00:00:17.12
SQL>
SQL> select
2 name
3 , value - &value_1 as diff
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
old 3: , value - &value_1 as diff
new 3: , value - 5090 as diff
session cursor cache hits 3

Elapsed: 00:00:00.00
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));
old 1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'))
new 1: select * from table(dbms_xplan.display_cursor('fmbq5ytmh0hng',null,'ALLSTATS LAST'))
SQL_ID fmbq5ytmh0hng, child number 0
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 1399240396

--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:17.10 | 1388K| 44110 | 10005 | | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:17.10 | 1388K| 44110 | 10005 | | | | |
| 2 | VIEW | | 1 | 12 | 1385K|00:00:16.03 | 1388K| 44110 | 10005 | | | | |
| 3 | WINDOW SORT | | 1 | 12 | 1385K|00:00:16.03 | 1388K| 44110 | 10005 | 88M| 3227K| 55M (1)| 80896 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 1385K|00:00:09.70 | 1388K| 24154 | 0 | | | | |
|* 5 | INDEX RANGE SCAN | I | 1 | 12 | 1385K|00:00:01.39 | 3060 | 3059 | 0 | | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

25 rows selected.

Elapsed: 00:00:00.11
SQL>
SQL> exec :x := 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> select
2 name
3 , value
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
session cursor cache hits 5100

Elapsed: 00:00:00.00
SQL>
SQL> select
2 sum(row_num)
3 from
4 (
5 select
6 row_number() over (partition by object_type order by object_name) as row_num
7 , t.*
8 from
9 t
10 where
11 object_id > :x
12 );
2.5311E+11

Elapsed: 00:00:12.94
SQL>
SQL> select
2 name
3 , value - &value_1 as diff
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
old 3: , value - &value_1 as diff
new 3: , value - 5100 as diff
session cursor cache hits 3

Elapsed: 00:00:00.01
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));
old 1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'))
new 1: select * from table(dbms_xplan.display_cursor('fmbq5ytmh0hng',null,'ALLSTATS LAST'))
SQL_ID fmbq5ytmh0hng, child number 0
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 1399240396

--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:12.95 | 1388K| 44001 | 10005 | | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:12.95 | 1388K| 44001 | 10005 | | | | |
| 2 | VIEW | | 1 | 12 | 1385K|00:00:12.29 | 1388K| 44001 | 10005 | | | | |
| 3 | WINDOW SORT | | 1 | 12 | 1385K|00:00:12.29 | 1388K| 44001 | 10005 | 88M| 3232K| 55M (1)| 80896 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 1385K|00:00:06.93 | 1388K| 24059 | 0 | | | | |
|* 5 | INDEX RANGE SCAN | I | 1 | 12 | 1385K|00:00:00.01 | 3060 | 3059 | 0 | | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

25 rows selected.

Elapsed: 00:00:00.11
SQL>
SQL> exec :x := 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> select
2 name
3 , value
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
session cursor cache hits 5110

Elapsed: 00:00:00.01
SQL>
SQL> select
2 sum(row_num)
3 from
4 (
5 select
6 row_number() over (partition by object_type order by object_name) as row_num
7 , t.*
8 from
9 t
10 where
11 object_id > :x
12 );
2.5311E+11

Elapsed: 00:00:06.02
SQL>
SQL> select
2 name
3 , value - &value_1 as diff
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
old 3: , value - &value_1 as diff
new 3: , value - 5110 as diff
session cursor cache hits 3

Elapsed: 00:00:00.01
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));
old 1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'))
new 1: select * from table(dbms_xplan.display_cursor('fmbq5ytmh0hng',null,'ALLSTATS LAST'))
SQL_ID fmbq5ytmh0hng, child number 0
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 1399240396

--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:12.95 | 1388K| 44001 | 10005 | | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:12.95 | 1388K| 44001 | 10005 | | | | |
| 2 | VIEW | | 1 | 12 | 1385K|00:00:12.29 | 1388K| 44001 | 10005 | | | | |
| 3 | WINDOW SORT | | 1 | 12 | 1385K|00:00:12.29 | 1388K| 44001 | 10005 | 88M| 3232K| 55M (1)| 80896 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 1385K|00:00:06.93 | 1388K| 24059 | 0 | | | | |
|* 5 | INDEX RANGE SCAN | I | 1 | 12 | 1385K|00:00:00.01 | 3060 | 3059 | 0 | | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

SQL_ID fmbq5ytmh0hng, child number 1
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 2719131525

----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:05.99 | 21058 | 34648 | 7955 | | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:05.99 | 21058 | 34648 | 7955 | | | | |
| 2 | VIEW | | 1 | 1385K| 1385K|00:00:05.74 | 21058 | 34648 | 7955 | | | | |
| 3 | WINDOW SORT | | 1 | 1385K| 1385K|00:00:04.35 | 21058 | 34648 | 7955 | 69M| 2893K| 55M (1)| 64512 |
|* 4 | TABLE ACCESS FULL| T | 1 | 1385K| 1385K|00:00:00.01 | 21054 | 21049 | 0 | | | | |
----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("OBJECT_ID">:X)

49 rows selected.

Elapsed: 00:00:00.21
SQL>
SQL> set heading on
SQL>
SQL> select
2 sql_id
3 , child_number
4 , executions
5 , parse_calls
6 , buffer_gets
7 , is_bind_sensitive
8 , is_bind_aware
9 from
10 v$sql
11 where
12 sql_id = '&sql_id';
old 12: sql_id = '&sql_id'
new 12: sql_id = 'fmbq5ytmh0hng'
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I
------------- ------------ ---------- ----------- ----------- - -
fmbq5ytmh0hng 0 7 8 2777315 Y N
fmbq5ytmh0hng 1 1 0 21058 Y Y

Elapsed: 00:00:00.03
SQL>

So we can see that from the third execution on we seem to get a hit in the session cursor cache (increase from 2 to 3), but still ACS seems to work properly, although this time it had to go wrong twice before ACS created the second child cursor. We can also see that we still get as many PARSE calls as executions, so the session cursor cache does not avoid the parsing as outlined above.

All this seems to suggest that the ACS (and potentially the "Cardinality Feedback", too) code is only triggered by a PARSE call and not evaluated as part of an EXECUTE call.

But if this was the case, then by no means the problem would be limited to PL/SQL and its clever optimizations that try to hold cursors open to avoid repeated PARSE calls.

Actually one of the core principles that we are told to make an application as scalable as possible is to parse only once but execute many times. So let's try to simulate such a well behaving application with this simple Java code snippet that accesses the database via JDBC:

import java.sql.* ;

class JDBCQuery
{
public static void main( String args[] )
{
try
{
// Load the database driver
Class.forName( "oracle.jdbc.driver.OracleDriver" ) ;

// Get a connection to the database
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@host:port:sid",
"username", "pwd");

// Print all warnings
for( SQLWarning warn = conn.getWarnings(); warn != null; warn = warn.getNextWarning() )
{
System.out.println( "SQL Warning:" ) ;
System.out.println( "State : " + warn.getSQLState() ) ;
System.out.println( "Message: " + warn.getMessage() ) ;
System.out.println( "Error : " + warn.getErrorCode() ) ;
}

// Reset the buffer cache and shared pool
Statement ddl = conn.createStatement();
ddl.executeUpdate("alter system flush shared_pool");
ddl.executeUpdate("alter system flush buffer_cache");
ddl.close();

// Prepare the statement
String query =
"select\n" +
" sum(row_num)\n" +
"from\n" +
" (\n" +
" select\n" +
" row_number() over (partition by object_type order by object_name) as row_num\n" +
" , t.*\n" +
" from\n" +
" t\n" +
" where\n" +
" object_id > ?\n" +
" )";

PreparedStatement stmt = conn.prepareStatement(query);

System.out.println(query);

Integer i;
Integer id;

for (i = 1; i <= 10; i++)
{
if (i == 1)
{
id = 100000;
}
else
{
id = 1;
}

System.out.println( "Iteration: " + i.toString() ) ;

stmt.setInt(1, id);

long startTime = System.currentTimeMillis();

// Execute the query
ResultSet rs = stmt.executeQuery();

// Loop through the result set
while( rs.next() )
System.out.println( rs.getLong(1)) ;

// Close the result set
rs.close() ;

long endTime = System.currentTimeMillis();

float seconds = (endTime - startTime) / 1000F;

// Elapsed time
System.out.println(Float.toString(seconds) + " seconds.");
}

stmt.close() ;
conn.close() ;
}
catch( SQLException se )
{
System.out.println( "SQL Exception:" ) ;

// Loop through the SQL Exceptions
while( se != null )
{
System.out.println( "State : " + se.getSQLState() ) ;
System.out.println( "Message: " + se.getMessage() ) ;
System.out.println( "Error : " + se.getErrorCode() ) ;

se = se.getNextException() ;
}
}
catch( Exception e )
{
System.out.println( e ) ;
}
}
}

/*
Check the Shared Pool by this query:

select
sql_id
, child_number
, executions
, parse_calls
, buffer_gets
, disk_reads
, direct_writes
, is_bind_sensitive
, is_bind_aware
from
v$sql
where
sql_id = 'cq3a8ukkws0su';

*/

This (by no means elegant or clever) piece of Java code again tries to do the same as our previous examples: It executes the known query ten times but behaves well since it parses only once and executes ten times using the two different bind values as already done before.

I only tested this with the JDBC thin driver that comes with Oracle 11.2.0.2 but not with the thick driver. However, since I assume that 95%+ of all Java applications out there actually use the thin driver this probably covers the most relevant case.

The results correspond to those of the PL/SQL examples with the "keep cursors open" optimization enabled: Neither ACS nor "Cardinality Feedback" kick in, and the SQL is executed every time using the same execution plan. The SQL is only parsed once, so the problem is not really limited to PL/SQL, but to every application that tries to avoid parse calls.

Summary

ACS is a great feature, but you need to be aware of its limitations, the most important being:

- Things have to go wrong first before ACS will be able to provide a fix. Depending on how "wrong" things can go, this might mean that ACS is actually not able to provide an efficient fix for certain cases
- The ACS information can be aged out of the Shared Pool which might lead to repeated "things have to go wrong" cases
- ACS is only triggered by explicit PARSE calls, it is not activated if the application parses once and executes many times

If you have a case where you need to help (or "co-operate" as Jonathan Lewis calls it in his brilliant presentation of the same name) your database, the simplest strategy is to use literals instead of bind variables. If you can't do this due to the issues introduced by this strategy (excessive hard parses, increased CPU and Shared Pool usage, library cache contention etc.) a more complex strategy - if applicable - is to "know your data" and generate a minimum amount of cursors that try to cover the cases where a different execution plan is desired.

This means something like this in pseudo code:

- If "known value A that requires a special treatment" then
-- execute SQL with dummy hint "A" or additional non-modifying predicate "A" = "A" to make the SQL unique
- If "known value B that requires a special treatment" then
-- execute SQL with dummy hint "B" or additional non-modifying predicate "B" = "B" to make the SQL unique
- Else "in all other cases"
-- execute SQL as is

Notice that using comments to make the SQL unique might not lead to the expected result since there are cases/environments where comments are stripped prior to execution.

Update: From a comment by Sokrates below that for some reason didn't make to the comments section but also doesn't show up in the "Spam" comments of the blog control panel I see that above description "dummy hint" might need a bit more explanation.

By "dummy hint" I mean to say that you should use hint syntax rather than normal comment syntax to make the SQL unique, because the normal comment might get filtered out whereas the comment using hint syntax is supposed to be kept. By "dummy hint" I mean any invalid hint comment in order to prevent any influence on the optimizer, for example /*+ CASE_A */ rather than /* CASE_A */

Understanding the different modes of System Statistics aka. CPU Costing and the effects of multiple blocksizes - part 4

Back to part 3

Using objects residing in multiple blocksizes

I've already mentioned it several times on my blog but I would like to take the chance here again to stress the point that the cost based optimizer does a bad job when it comes to calculating costs for full table scans of objects residing in non-default block sizes. It really looks like that this feature has been introduced to support transportable tablespaces but it obviously hasn't been tested very thoroughly when it comes to cost calculation.

Each of the different modes has its deficiencies when dealing with objects in non-default blocksizes. The somehow odd thing is that the traditional I/O costing does the best job, and all system statistics based calculations are utterly wrong.

Traditional I/O based costing

The traditional I/O based costing simply scales the MBRC up or down according to the non-default blocksize to come to the same I/O read request size. So if you e.g. have a MBRC of 8 and a default blocksize of 8KB and now calculate the cost for an object residing in a 2KB tablespace, the MBRC will be multiplied 4, which results in a MBRC of 32. The I/O cost will be different although due to the different adjustment used with the higher MBRC setting. The adjusted MBRC for 32 is 16.39 whereas the adjusted MBRC for 8 is 6.59, so the calculated cost for the full table scan of the object residing in the 2KB tablespace will be higher. Likewise the same happens when using an object in a 16KB non-default tablespace. The MBRC will be reduced accordingly to 4 to get the same I/O read size again. Since adjusted MBRC for MBRC = 4 is 4.17, the cost calculated will actually be less for the object residing the 16KB tablespace.

Understanding the different modes of System Statistics aka. CPU Costing and the effects of multiple blocksizes - part 3

Back to part 2 Forward to part 4

System statistics in 9i

In 10g the CPU costing mode is enabled by default and is supported by the default NOWORKLOAD system statistics.

But you can use system statistics already in 9i, although you have to enable them explicitly.

Oracle 9i is out of support, but I guess there are still a lot of systems out there that are using the 9.2 release, therefore I find it worth to mention what you can do in 9i with system statistics. This can be helpful if you consider to test your application already in the 9i environment with system statistics before upgrading to 10g.

In most descriptions about 9i and system statistics only WORKLOAD system statistics are mentioned, but 9i also supports NOWORKLOAD system statistics, although not in the same full flavour as 10g does.

You can activate CPU costing in 9i by running DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD'), but this seems to work differently than in 10g and later.

Whereas in 10g and later this actually measures the IOSEEKTIM and IOTFRSPEED values, this seems to activate in 9i something that is comparable with the default NOWORKLOAD system statistics of 10g.

The SYS.AUX_STATS$ does not show any actual values, but tests revealed that 9i (at least 9.2.0.8) in that case seems to measure the CPU speed (in 10g this is the CPUSPEEDNW value) and uses the same default values for IOSEEKTIM and IOTFRSPEED as 10g does (10ms and 4096 bytes/ms resp.).

Running some tests showed that you arrive at the same I/O cost as you do in 10g with the default NOWORKLOAD system statistics.

exec dbms_stats.delete_system_stats;