Adaptive Cursor Sharing (ACS) has been introduced in Oracle
11g (11.1.0.7) to address the potential threat of cursor sharing and bind
variables. 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.2.0.4 (and 12.1.0.2 both CDB and non-CDB
results were same as 11.2.0.4)
rajesh@ORA11G>
create table t
2 as
3
select *
4 from
all_objects ;
Table created.
rajesh@ORA11G>
create index t_idx on t(object_id);
Index created.
rajesh@ORA11G>
exec dbms_stats.gather_table_stats(user,'T');
PL/SQL
procedure successfully completed.
rajesh@ORA11G>
select count(*) ,min(object_id) ,
2
max(object_id)
3 from
t ;
COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID)
----------
-------------- --------------
84525 2 88459
rajesh@ORA11G>
set serveroutput off
rajesh@ORA11G>
variable x number
rajesh@ORA11G>
exec :x := 1000000;
PL/SQL procedure
successfully completed.
rajesh@ORA11G>
select sum(data_object_id) from t
2
where object_id > :x ;
SUM(DATA_OBJECT_ID)
-------------------
rajesh@ORA11G>
select * from table(dbms_xplan.display_cursor(format=>'+peeked_binds'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID 78fpxb47zcyus, child
number 0
-------------------------------------
select
sum(data_object_id) from t where object_id > :x
Plan hash
value: 1789076273
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| | 3 (100)| |
| 1 |
SORT AGGREGATE | |
1 | 7 | | |
| 2 |
TABLE ACCESS BY INDEX ROWID| T
| 1 |
7 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN
| T_IDX | 1 | |
2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Peeked Binds
(identified by position):
--------------------------------------
1 - :X (NUMBER):
1000000
Predicate
Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID">:X)
25 rows
selected.
rajesh@ORA11G>
exec :x := 55;
PL/SQL
procedure successfully completed.
rajesh@ORA11G>
select sum(data_object_id) from t
2
where object_id > :x ;
SUM(DATA_OBJECT_ID)
-------------------
384877210
rajesh@ORA11G>
select * from table(dbms_xplan.display_cursor(format=>'+peeked_binds'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID 78fpxb47zcyus, child
number 0
-------------------------------------
select
sum(data_object_id) from t where object_id > :x
Plan hash
value: 1789076273
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| | 3 (100)| |
| 1 |
SORT AGGREGATE | |
1 | 7 | | |
| 2 |
TABLE ACCESS BY INDEX ROWID| T
| 1 | 7 |
3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN
| T_IDX | 1 | |
2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Peeked Binds
(identified by position):
--------------------------------------
1 - :X (NUMBER):
1000000
Predicate
Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID">:X)
25 rows
selected.
rajesh@ORA11G>
exec :x := 55;
PL/SQL
procedure successfully completed.
rajesh@ORA11G>
select sum(data_object_id) from t
2
where object_id > :x ;
SUM(DATA_OBJECT_ID)
-------------------
384877210
rajesh@ORA11G>
select * from table(dbms_xplan.display_cursor(format=>'+peeked_binds'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID 78fpxb47zcyus, child
number 1
-------------------------------------
select
sum(data_object_id) from t where object_id > :x
Plan hash
value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | |
337 (100)| |
| 1 |
SORT AGGREGATE | |
1 | 7 | | |
|* 2 | TABLE ACCESS FULL| T | 84474 |
577K| 337 (1)| 00:00:05 |
---------------------------------------------------------------------------
Peeked Binds
(identified by position):
--------------------------------------
1 - :X (NUMBER): 55
Predicate
Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID">:X)
24 rows
selected.
rajesh@ORA11G>
select sql_id ,
2
child_number ,
3
executions ,
4
parse_calls ,
5 buffer_gets ,
6
is_bind_sensitive ,
7
is_bind_aware
8 from
v$sql
9
where sql_id ='78fpxb47zcyus' ;
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS
BUFFER_GETS I I
-------------
------------ ---------- ----------- ----------- - -
78fpxb47zcyus 0 2 3 1435 Y N
78fpxb47zcyus 1 1 0 1211 Y Y
rajesh@ORA11G>
From this example we therefore conclude a couple of things
- ACS is not limited to the typical “Skewed column data” / “Histogram” case, we can see that I explicitly did not created any histograms, still the cursor is marked as “BIND_SENSITIVE”
- However 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 variable that created the child cursor.
- 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, and the same mistake has to make again for ACS to detect the problem.
Now, let’s have a look at the point that “ACS does not work with sql’s
available in plsql”
rajesh@ORA11G>
create or replace package demo_pkg
2 as
3
procedure static_sql ;
4
procedure dynamic_sql_with_binds;
5
procedure dynamic_sql_with_literals;
6
procedure dynamic_sql_with_refcursor;
7 end;
8 /
Package
created.
rajesh@ORA11G>
create or replace package body demo_pkg
2 as
3
g_sql_id varchar2(20);
4
procedure print_sqlids
5
as
6
begin
7
select prev_sql_id into g_sql_id
8
from v$session
9
where sid = userenv('sid') ;
10
--dbms_output.put_line('sql_id = '||g_sql_id) ;
11
end print_sqlids;
12
13
procedure print_execution_details(p_val varchar2 default 'Binds')
14
as
15
begin
16
if p_val <> 'Binds' then
17
for x in ( select
18 t2.sql_id
19 , t2.child_number
20 , t2.executions
21 , t2.parse_calls
22 , t2.buffer_gets
23 , t2.is_bind_sensitive
24 , t2.is_bind_aware,
25
t2.disk_reads,t2.direct_writes
26
from v$sql t1, v$sql t2
27
where t1.sql_id = g_sql_id
28
and t1.force_matching_signature = t2.force_matching_signature
29
order by child_number )
30
loop
31 dbms_output.new_line;
32 dbms_output.put_line( rpad('sql_id',20) ||':
'|| rpad(x.sql_id,20,' ') );
33 dbms_output.put_line(
rpad('child_number',20) ||': '|| rpad(x.child_number,13) );
34 dbms_output.put_line(
rpad('executions',20) ||': '|| rpad(x.executions,12) );
35 dbms_output.put_line(
rpad('parse_calls',20) ||': '|| rpad(x.parse_calls,13) );
36 dbms_output.put_line(
rpad('buffer_gets',20) ||': '|| rpad(x.buffer_gets,13) );
37 dbms_output.put_line(
rpad('is_bind_sensitive',20) ||': '|| rpad(x.is_bind_sensitive,19) );
38 dbms_output.put_line(
rpad('is_bind_aware',20) ||': '|| rpad(x.is_bind_aware,15) );
39 dbms_output.put_line(
rpad('disk_reads',20) ||': '|| rpad(x.disk_reads,13) );
40 dbms_output.put_line(
rpad('direct_writes',20) ||': '|| rpad(x.direct_writes,15) );
41 dbms_output.put_line(
rpad('*',20,'*')||'Plan_table_output'||rpad('*',20,'*'));
42 for k in (select * from
table(dbms_xplan.display_cursor(
43
x.sql_id,x.child_number,format=>'+peeked_binds')))
44 loop
45
dbms_output.put_line(k.plan_table_output);
46 end loop;
47
end loop;
48
else
49
for x in ( select
50 sql_id
51 , child_number
52 , executions
53 , parse_calls
54 , buffer_gets
55 , is_bind_sensitive
56 , is_bind_aware,
57 disk_reads,direct_writes
58
from v$sql
59
where sql_id = g_sql_id
60
order by child_number )
61
loop
62 dbms_output.new_line;
63 dbms_output.put_line(
rpad('sql_id',20) ||': '|| rpad(x.sql_id,20,' ') );
64 dbms_output.put_line(
rpad('child_number',20) ||': '|| rpad(x.child_number,13) );
65 dbms_output.put_line(
rpad('executions',20) ||': '|| rpad(x.executions,12) );
66 dbms_output.put_line(
rpad('parse_calls',20) ||': '|| rpad(x.parse_calls,13) );
67 dbms_output.put_line(
rpad('buffer_gets',20) ||': '|| rpad(x.buffer_gets,13) );
68 dbms_output.put_line(
rpad('is_bind_sensitive',20) ||': '|| rpad(x.is_bind_sensitive,19) );
69 dbms_output.put_line(
rpad('is_bind_aware',20) ||': '|| rpad(x.is_bind_aware,15) );
70 dbms_output.put_line(
rpad('disk_reads',20) ||': '|| rpad(x.disk_reads,13) );
71 dbms_output.put_line(
rpad('direct_writes',20) ||': '|| rpad(x.direct_writes,15) );
72 dbms_output.put_line(
rpad('*',20,'*')||'Plan_table_output'||rpad('*',20,'*'));
73 for k in (select * from
table(dbms_xplan.display_cursor(
74 x.sql_id,x.child_number,format=>'+peeked_binds')))
75 loop
76
dbms_output.put_line(k.plan_table_output);
77 end loop;
78
end loop;
79
end if;
80
end print_execution_details;
81
82
procedure static_sql
83
as
84
l_total int;
85
l_object_id int := 1000000 ;
86
l_print_sqlid boolean := true;
87
begin
88
for x in 1..10
89
loop
90 select sum(data_object_id)
91 into l_total
92 from t
93 where object_id >
l_object_id ;
94
95 if l_print_sqlid then
96
print_sqlids;
97 l_print_sqlid :=
false;
98 l_object_id := 55;
99 end if;
100 end loop;
101
102 print_execution_details;
103 end static_sql;
104
105 procedure dynamic_sql_with_binds
106 as
107 l_total int;
108 l_object_id int := 1000000 ;
109 l_print_sqlid boolean := true;
110 begin
111 for x in 1..10
112 loop
113 execute immediate ' select
sum(data_object_id)
114 from t
115 where object_id > :b1 '
into l_total
116 using l_object_id ;
117
118 if l_print_sqlid then
119 print_sqlids;
120 l_print_sqlid :=
false;
121 l_object_id := 55;
122 end if;
123 end loop;
124
125 print_execution_details;
126 end dynamic_sql_with_binds;
127
128 procedure dynamic_sql_with_literals
129 as
130 l_total int;
131 l_object_id int := 1000000 ;
132 l_print_sqlid boolean := true;
133 begin
134 for x in 1..10
135 loop
136 execute immediate ' select
sum(data_object_id)
137 from t
138 where object_id >
'||l_object_id
139 into l_total ;
140
141 if l_print_sqlid then
142 print_sqlids;
143 l_print_sqlid :=
false;
144 l_object_id := 55;
145 end if;
146 end loop;
147
148
print_execution_details('Literals');
149 end dynamic_sql_with_literals;
150
151 procedure dynamic_sql_with_refcursor
152 as
153 c sys_refcursor;
154 l_total int;
155 l_object_id int := 1000000 ;
156 l_print_sqlid boolean := true;
157 begin
158 for x in 1..10
159 loop
160 open c for 'select
sum(data_object_id)
161 from t
162 where object_id
> :b2' using l_object_id ;
163 fetch c into l_total ;
164 close c;
165 if l_print_sqlid then
166 print_sqlids;
167 l_print_sqlid :=
false;
168 l_object_id := 55;
169 end if;
170 end loop;
171 print_execution_details ;
172 end dynamic_sql_with_refcursor;
173
174 end demo_pkg;
175 /
Package body
created.
rajesh@ORA11G>
So here are the execution details
rajesh@ORA11G>
rajesh@ORA11G>
rajesh@ORA11G>
alter system flush shared_pool;
System altered.
rajesh@ORA11G>
exec demo_pkg.static_sql;
sql_id : c3c89xda9z8bc
child_number : 0
executions : 10
parse_calls : 1
buffer_gets : 12981
is_bind_sensitive : Y
is_bind_aware : N
disk_reads : 0
direct_writes : 0
********************Plan_table_output********************
SQL_ID c3c89xda9z8bc, child number 0
-------------------------------------
SELECT
SUM(DATA_OBJECT_ID) FROM T WHERE OBJECT_ID > :B1
Plan hash
value: 1789076273
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| | 3 (100)| |
| 1 |
SORT AGGREGATE | |
1 | 7 | | |
| 2 |
TABLE ACCESS BY INDEX ROWID| T
| 1 | 7 |
3 (0)| 00:00:01 |
|* 3 |
INDEX RANGE SCAN | T_IDX
| 1 | |
2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Peeked Binds
(identified by position):
--------------------------------------
1 - :B1 (NUMBER): 1000000
Predicate
Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID">:B1)
PL/SQL
procedure successfully completed.
rajesh@ORA11G>
alter system flush shared_pool;
System altered.
rajesh@ORA11G>
exec demo_pkg.dynamic_sql_with_binds;
sql_id : 8x1x7q4y4r1u4
child_number : 0
executions : 10
parse_calls : 1
buffer_gets : 12981
is_bind_sensitive : Y
is_bind_aware : N
disk_reads : 0
direct_writes : 0
********************Plan_table_output********************
SQL_ID 8x1x7q4y4r1u4, child number 0
-------------------------------------
select sum(data_object_id) from t
where object_id > :b1
Plan hash
value: 1789076273
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| | 3 (100)| |
| 1 |
SORT AGGREGATE |
| 1 | 7 | | |
| 2 |
TABLE ACCESS BY INDEX ROWID| T
| 1 | 7 |
3 (0)| 00:00:01 |
|* 3 |
INDEX RANGE SCAN | T_IDX
| 1 | |
2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Peeked Binds
(identified by position):
--------------------------------------
1 - :B1 (NUMBER): 1000000
Predicate
Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID">:B1)
PL/SQL
procedure successfully completed.
rajesh@ORA11G>
alter system flush shared_pool;
System altered.
rajesh@ORA11G>
exec demo_pkg.dynamic_sql_with_literals;
sql_id : 38f6ubw8h346r
child_number : 0
executions : 1
parse_calls : 1
buffer_gets : 84
is_bind_sensitive : N
is_bind_aware : N
disk_reads : 0
direct_writes : 0
********************Plan_table_output********************
SQL_ID 38f6ubw8h346r, child number 0
-------------------------------------
select sum(data_object_id) from t
where object_id > 1000000
Plan hash
value: 1789076273
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| | 3 (100)| |
| 1 |
SORT AGGREGATE | |
1 | 7 | | |
| 2 |
TABLE ACCESS BY INDEX ROWID| T
| 1 | 7 |
3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN
| T_IDX | 1 | |
2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID">1000000)
sql_id : b21hpyvv9hbqk
child_number : 0
executions : 9
parse_calls : 1
buffer_gets : 10899
is_bind_sensitive : N
is_bind_aware : N
disk_reads : 0
direct_writes : 0
********************Plan_table_output********************
SQL_ID b21hpyvv9hbqk, child number 0
-------------------------------------
select sum(data_object_id) from t
where object_id > 55
Plan hash
value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | |
337 (100)| |
| 1 |
SORT AGGREGATE | |
1 | 7 | | |
|* 2 | TABLE ACCESS FULL| T
| 84474 | 577K| 337
(1)| 00:00:05 |
---------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID">55)
PL/SQL
procedure successfully completed.
rajesh@ORA11G>
alter system flush shared_pool;
System altered.
rajesh@ORA11G>
exec demo_pkg.dynamic_sql_with_refcursor;
sql_id : ggnmd73zzuqw3
child_number : 0
executions : 2
parse_calls : 3
buffer_gets : 1517
is_bind_sensitive : Y
is_bind_aware : N
disk_reads : 0
direct_writes : 0
********************Plan_table_output********************
SQL_ID ggnmd73zzuqw3, child
number 0
-------------------------------------
select
sum(data_object_id) from t where object_id > :b2
Plan hash
value: 1789076273
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| | 3 (100)| |
| 1 |
SORT AGGREGATE | |
1 | 7 | |
|
| 2 |
TABLE ACCESS BY INDEX ROWID| T
| 1 | 7 |
3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN
| T_IDX | 1 | |
2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Peeked Binds
(identified by position):
--------------------------------------
1 - :B2 (NUMBER): 1000000
Predicate
Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID">:B2)
sql_id : ggnmd73zzuqw3
child_number : 1
executions : 8
parse_calls : 7
buffer_gets : 9688
is_bind_sensitive : Y
is_bind_aware : Y
disk_reads : 0
direct_writes : 0
********************Plan_table_output********************
SQL_ID ggnmd73zzuqw3, child
number 1
-------------------------------------
select
sum(data_object_id) from t where object_id > :b2
Plan hash
value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | |
337 (100)| |
| 1 |
SORT AGGREGATE | |
1 | 7 | | |
|* 2 | TABLE ACCESS FULL| T
| 84474 | 577K| 337
(1)| 00:00:05 |
---------------------------------------------------------------------------
Peeked Binds
(identified by position):
--------------------------------------
1 - :B2 (NUMBER): 55
Predicate
Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID">:B2)
PL/SQL
procedure successfully completed.
rajesh@ORA11G>
We can see that ACS does not work with several of the user variations;
both static and dynamic sql using bind variables does not create a second child
cursor and re-used the same execution plan for all the ten executions.
The clear winner in this case is dynamic sql using literals that
creates two different child cursors (but subjected to sql-injections and hard
parsing issues)
The fourth variant using Dynamic sql with ref-cursors that is not
impacted by the plsql optimization (session cached cursors) in this case ACS
worked and created a second child cursor but subjected to many parse calls as
execution calls ( parse per execution )
The shown behaviour of ACS not working is officially documented in My
Oracle Support, 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.
rajesh@ORA11G>
show parameter session_cached
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
session_cached_cursors integer 50
rajesh@ORA11G>
alter session set session_cached_cursors=0;
Session
altered.
rajesh@ORA11G>
alter system flush shared_pool;
System altered.
rajesh@ORA11G>
exec demo_pkg.static_sql;
sql_id : c3c89xda9z8bc
child_number : 0
executions : 2
parse_calls : 3
buffer_gets : 1517
is_bind_sensitive : Y
is_bind_aware : N
disk_reads : 0
direct_writes : 0
********************Plan_table_output********************
SQL_ID c3c89xda9z8bc, child
number 0
-------------------------------------
SELECT
SUM(DATA_OBJECT_ID) FROM T WHERE OBJECT_ID > :B1
Plan hash
value: 1789076273
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| | 3 (100)| |
| 1 |
SORT AGGREGATE | |
1 | 7 | | |
| 2 |
TABLE ACCESS BY INDEX ROWID| T
| 1 | 7 |
3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN
| T_IDX | 1 | |
2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Peeked Binds
(identified by position):
--------------------------------------
1 - :B1 (NUMBER): 1000000
Predicate
Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID">:B1)
sql_id : c3c89xda9z8bc
child_number : 1
executions : 8
parse_calls : 7
buffer_gets : 9688
is_bind_sensitive : Y
is_bind_aware : Y
disk_reads : 0
direct_writes : 0
********************Plan_table_output********************
SQL_ID c3c89xda9z8bc, child
number 1
-------------------------------------
SELECT
SUM(DATA_OBJECT_ID) FROM T WHERE OBJECT_ID > :B1
Plan hash
value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | |
337 (100)| |
| 1 |
SORT AGGREGATE | |
1 | 7 | | |
|* 2 | TABLE ACCESS FULL| T | 84474 | 577K|
337 (1)| 00:00:05 |
---------------------------------------------------------------------------
Peeked Binds
(identified by position):
--------------------------------------
1 - :B1 (NUMBER): 55
Predicate
Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID">:B1)
PL/SQL
procedure successfully completed.
rajesh@ORA11G>
rajesh@ORA11G>
alter system flush shared_pool;
System altered.
rajesh@ORA11G>
exec demo_pkg.dynamic_sql_with_binds;
sql_id : 8x1x7q4y4r1u4
child_number : 0
executions : 2
parse_calls : 3
buffer_gets : 1517
is_bind_sensitive : Y
is_bind_aware : N
disk_reads : 0
direct_writes : 0
********************Plan_table_output********************
SQL_ID 8x1x7q4y4r1u4, child
number 0
-------------------------------------
select sum(data_object_id) from t
where object_id > :b1
Plan hash
value: 1789076273
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| | 3 (100)| |
| 1 |
SORT AGGREGATE | |
1 | 7 | | |
| 2 |
TABLE ACCESS BY INDEX ROWID| T
| 1 |
7 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX | 1 | |
2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Peeked Binds
(identified by position):
--------------------------------------
1 - :B1 (NUMBER): 1000000
Predicate
Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID">:B1)
sql_id : 8x1x7q4y4r1u4
child_number :
1
executions : 8
parse_calls : 7
buffer_gets : 9688
is_bind_sensitive : Y
is_bind_aware : Y
disk_reads : 0
direct_writes : 0
********************Plan_table_output********************
SQL_ID 8x1x7q4y4r1u4, child
number 1
-------------------------------------
select sum(data_object_id) from t
where object_id > :b1
Plan hash
value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | |
337 (100)| |
| 1 |
SORT AGGREGATE | |
1 | 7 | | |
|* 2 | TABLE ACCESS FULL| T | 84474 | 577K|
337 (1)| 00:00:05 |
---------------------------------------------------------------------------
Peeked Binds
(identified by position):
--------------------------------------
1 - :B1 (NUMBER): 55
Predicate
Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID">:B1)
PL/SQL
procedure successfully completed.
rajesh@ORA11G>
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 is cached
in a private memory area of the process.
