Wednesday, May 20, 2015

Adaptive cursor sharing doesnot work for sqls inside plsql

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.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)