If you have read Part 1 and Part 2 of this series, you are now ready to observe parsing (soft and hard), bind peeking, influence them, and observe how Adaptive Cursor Sharing tries to fix it.
Literals vs Bind Variables
We can say, that variables used inside PLSQL block are “Automatic Bind Variables”. So it means they are considered as bind variable and are not causing parse operations. That it is better to run stored PLSQL block then a lot of single SQL queries from application (from database point of view)
create user tstuser identified by tstuser default tablespace USERS; grant dba to tstuser; connect tstuser/tstuser drop table tab1_parse; create table tab1_parse as select level "ID", 'descr:' || level "DESCR" from dual connect by level <= 1000000;
PLSQL automatic bind variable
select sn.NAME,ms.VALUE from v$mystat ms, v$statname sn where ms.STATISTIC#=sn.STATISTIC# and (sn.NAME ='parse count (total)' or sn.NAME = 'parse count (hard)');
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 14
parse count (hard) 0
set serveroutput on
declare
cnt NUMBER;
res NUMBER;
strLine varchar2(1024);
begin
FOR cnt in 1 .. 40 LOOP
strLine := 'select /* AUTOMATIC_PLSQL_BIND */ id into res from tab1_parse where id = ' || cnt;
dbms_output.put_line(strLine);
select /* AUTOMATIC_PLSQL_BIND */ id into res from tab1_parse where id = cnt;
END LOOP;
end;
/
select sn.NAME,ms.VALUE from v$mystat ms, v$statname sn where ms.STATISTIC#=sn.STATISTIC# and (sn.NAME ='parse count (total)' or sn.NAME = 'parse count (hard)');
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 21
parse count (hard) 2
We run this query 40 times, each time with different variable ,and we can observe only few parses (2 hard and 5 soft)
Literals
select sn.NAME,ms.VALUE from v$mystat ms, v$statname sn where ms.STATISTIC#=sn.STATISTIC# and (sn.NAME ='parse count (total)' or sn.NAME = 'parse count (hard)');
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 46
parse count (hard) 3
SQL> set serveroutput on
declare
cnt NUMBER;
res NUMBER;
strLine varchar2(1024);
begin
FOR cnt in 1 .. 40 LOOP
strLine := 'select /* LITERALS */ id from tab1_parse where id = ' || cnt;
execute immediate strLine into res;
END LOOP;
end;
/
select sn.NAME,ms.VALUE from v$mystat ms, v$statname sn where ms.STATISTIC#=sn.STATISTIC# and (sn.NAME ='parse count (total)' or sn.NAME = 'parse count (hard)');
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 91
parse count (hard) 44
As you can see 41 more hard parses..
So let’s look inside of Libarary Cache and check hwo many cursors were created
col SQL_TEXT format a90 set lines 300 set pagesize 0 select sql_id,sql_text , plan_hash_value, child_number from v$sql where SQL_TEXT like 'select%LITERALS%tab1_parse%'; 0rsv2fkmwh1dn select /* LITERALS */ id from tab1_parse where id = 30 550118616 0 7mx44gd3sn1f4 select /* LITERALS */ id from tab1_parse where id = 5 550118616 0 2mxp3gs0cn1x7 select /* LITERALS */ id from tab1_parse where id = 14 550118616 0 0f1yf4chrn2yv select /* LITERALS */ id from tab1_parse where id = 28 550118616 0 a747dzf4th91r select /* LITERALS */ id from tab1_parse where id = 12 550118616 0 f04rdnjc6w9q3 select /* LITERALS */ id from tab1_parse where id = 2 550118616 0 gtynpkk3b0g68 select /* LITERALS */ id from tab1_parse where id = 31 550118616 0 5q6d9zs92wghs select /* LITERALS */ id from tab1_parse where id = 23 550118616 0 5s6qsatqy8gxu select /* LITERALS */ id from tab1_parse where id = 16 550118616 0 dqkknfja54kcf select /* LITERALS */ id from tab1_parse where id = 24 550118616 0 5aq1nq7hw4q2f select /* LITERALS */ id from tab1_parse where id = 26 550118616 0 8g6czcxb08u6y select /* LITERALS */ id from tab1_parse where id = 38 550118616 0 1jttnvc118x7v select /* LITERALS */ id from tab1_parse where id = 13 550118616 0 f61jazqvswyju select /* LITERALS */ id from tab1_parse where id = 7 550118616 0 6w8vh067dwyn4 select /* LITERALS */ id from tab1_parse where id = 22 550118616 0 0d3xk6ffhnzrq select /* LITERALS */ id from tab1_parse where id = 27 550118616 0 6kjkm4x1fp307 select /* LITERALS */ id from tab1_parse where id = 11 550118616 0 cs68fycdrt5v0 select /* LITERALS */ id from tab1_parse where id = 29 550118616 0 b0fvca727585h select /* LITERALS */ id from tab1_parse where id = 3 550118616 0 f3an29yat9au8 select /* LITERALS */ id from tab1_parse where id = 18 550118616 0 fhky8myppdaw6 select /* LITERALS */ id from tab1_parse where id = 10 550118616 0 90s8d3ydpxcd6 select /* LITERALS */ id from tab1_parse where id = 6 550118616 0 5f3v73ba81cru select /* LITERALS */ id from tab1_parse where id = 1 550118616 0 bhj8fzfcyjfd2 select /* LITERALS */ id from tab1_parse where id = 4 550118616 0 gd1hndrdaxn8v select /* LITERALS */ id from tab1_parse where id = 40 550118616 0 asz0cz8twtun3 select /* LITERALS */ id from tab1_parse where id = 32 550118616 0 bq95kbn66pw6w select /* LITERALS */ id from tab1_parse where id = 36 550118616 0 8px1t98srf11p select /* LITERALS */ id from tab1_parse where id = 9 550118616 0 23k49d9ww6535 select /* LITERALS */ id from tab1_parse where id = 19 550118616 0 c83x7nwb5a5g0 select /* LITERALS */ id from tab1_parse where id = 17 550118616 0 gbpzc7rt0q9k1 select /* LITERALS */ id from tab1_parse where id = 8 550118616 0 39391fskjka75 select /* LITERALS */ id from tab1_parse where id = 20 550118616 0 88w84prth6dbz select /* LITERALS */ id from tab1_parse where id = 35 550118616 0 1cbbfqbu06jys select /* LITERALS */ id from tab1_parse where id = 33 550118616 0 dr8dkufnjqwfa select /* LITERALS */ id from tab1_parse where id = 21 550118616 0 cqh80k5skyx88 select /* LITERALS */ id from tab1_parse where id = 15 550118616 0 cmz7snh5pv3fd select /* LITERALS */ id from tab1_parse where id = 34 550118616 0 bph6u4vjdz5p2 select /* LITERALS */ id from tab1_parse where id = 37 550118616 0 d37zs3ruhm8u8 select /* LITERALS */ id from tab1_parse where id = 39 550118616 0 gtbxr816hzszv select /* LITERALS */ id from tab1_parse where id = 25 550118616 0
There are 40 different Cursors with the same execution plan.
How many bytes in Library Cache are used by these Cursors
select sum(SHARABLE_MEM) from v$sql where plan_hash_value=550118616;
628081
It is more then 600KB. Against 99KB with variables
select sum(SHARABLE_MEM) from v$sql where SQL_TEXT like '%AUTOMATIC%';
99346
BIND PEEKING / ADAPTIVE CURSOR SHARING
Modify our table by adding column with skewed data
alter table tab1_parse add sex char(3); update tab1_parse set sex='M' where id between 1 and 700000; update tab1_parse set sex='F' where id between 700001 and 999000; update tab1_parse set sex='N' where id between 999001 and 1000000; update tab1_parse set sex='N/A' where id=1000000; commit; create index TAB1_PARSE_SEX on TAB1_PARSE(SEX);
By the way, let’s observe 12c feature called “Online Statistics Gathering for Bulk Loads”
The statistics are alread gathered during Bulk operation
select TABLE_NAME,NUM_ROWS,BLOCKS,AVG_ROW_LEN from dba_tables where table_name = 'TAB1_PARSE'; TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN -------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ----------- TAB1_PARSE 1000000 3228 18
But still histograms are not gathered (not to influence to much efficiency of bulk operation)
col TABLE_NAME format a40 select TABLE_NAME,COLUMN_NAME,HISTOGRAM from dba_tab_col_statistics where table_name = 'TAB1_PARSE';SQL> TABLE_NAME COLUMN_NAME HISTOGRAM ---------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------- TAB1_PARSE ID NONE TAB1_PARSE DESCR NONE
So let’s analyze table and gather histogram for skewed data
analyze table tab1_parse compute statistics for table for all indexes for all indexed columns; select TABLE_NAME,COLUMN_NAME,HISTOGRAM from dba_tab_col_statistics where table_name = 'TAB1_PARSE'; TABLE_NAME COLUMN_NAME HISTOGRAM ---------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------- TAB1_PARSE ID NONE TAB1_PARSE DESCR NONE TAB1_PARSE SEX FREQUENCY
To see what is bind variable peeking, and difference between just parsing (Explain Plan) and execution phase,we should play with plans and variables
set lines 130
set pagesize 0
explain plan for select avg(id) from tab1_parse where sex='M';
SELECT * FROM table(DBMS_XPLAN.DISPLAY);SQL> SQL>
Explained.
Plan hash value: 2769599385
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1086 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS STORAGE FULL| TAB1_PARSE | 700K| 5468K| 1086 (2)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("SEX"='M')
filter("SEX"='M')
As we can see, during parse phase optimizer has choosen Full Scan of Table.
Value of ‘M’ is non-selective so this decision seems to be correct.
explain plan for select avg(id) from tab1_parse where sex='N/A';
Explained.
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
Plan hash value: 211962169
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1_PARSE | 1 | 8 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TAB1_PARSE_SEX | 1 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("SEX"='N/A')
This time optimizer decided to use index for very selective value ‘N/A’.
Because value of column SEX was passed as literal value, then at the phase of parsing optimizer could
correctly asses selectivity and cardinality
To observe only what we want let’s disable all adaptive optimizations
alter session set optimizer_adaptive_features=FALSE;
set pages 0
set lines 130
var b_sex char(3);
exec :b_sex := 'M';
select /*+ GATHER_PLAN_STATISTICS */ avg(id) from tab1_parse where sex=:b_sex;
select * from table(dbms_xplan.display_cursor(format=>'allstats last +PEEKED_BINDS'));
SQL_ID cym864mdw78rj, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ avg(id) from tab1_parse where
sex=:b_sex
Plan hash value: 2769599385
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.12 | 3905 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.12 | 3905 |
|* 2 | TABLE ACCESS STORAGE FULL| TAB1_PARSE | 1 | 700K| 700K|00:00:00.09 | 3905 |
---------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (CHAR(30), CSID=873): 'M '
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("SEX"=:B_SEX)
filter("SEX"=:B_SEX)
This time to run the query we used bind variable with value ‘M’
Execution plan we displayed from executions from Cursor.
So change the value to very selective value ‘N/A’ and check plan
exec :b_sex := 'N/A';
select /*+ GATHER_PLAN_STATISTICS */ avg(id) from tab1_parse where sex=:b_sex;
1000000
select * from table(dbms_xplan.display_cursor(format=>'allstats last +PEEKED_BINDS'));
SQL_ID cym864mdw78rj, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ avg(id) from tab1_parse where
sex=:b_sex
Plan hash value: 2769599385
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.04 | 3905 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.04 | 3905 |
|* 2 | TABLE ACCESS STORAGE FULL| TAB1_PARSE | 1 | 700K| 1 |00:00:00.04 | 3905 |
---------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (CHAR(30), CSID=873): 'M '
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("SEX"=:B_SEX)
filter("SEX"=:B_SEX)
Execution Plan is the same ! Look at column “E-Rows” for this plan … Regarding to this plan optimizer expected 700K rows,and actuall rows “A-Rows” was 1.
So it is horrible mistake ! Why ? Since Oracle 9i there is a mechanism called “Bind Peeking”
So during first execution , after parse, value for bind variable is chosen , execution plan based on this value is created, and to make it possible to share Cursors , optimizer sticks to this plan !
Since Oracle 11G there was introduced feature called “Adaptive Cursor Sharing”.
Quite often, this is understood as a way to “improve” execution plan . It is not ! It is a feature that makes Cursor sensitive to selectivity comming from values of bind variables
The best way, will be to show it on examples. Since 11G in v$sql are two additional columns “IS_BIND_SENSITIVE” and “IS_BIND_AWARE”. There is also a column “IS_SHAREABLE” saying whether Cursor is shareable.
connect tstuser/tstuser
set lines 200
set pages 0
col SQL_TEXT format a40
alter session set optimizer_adaptive_features=TRUE;
alter system flush shared_pool;
var b_sex char(3);
exec :b_sex := 'M';
select /*+ GATHER_PLAN_STATISTICS */ avg(id) from tab1_parse where sex=:b_sex;
select * from table(dbms_xplan.display_cursor(format=>'allstats last +PEEKED_BINDS'));
SQL_ID cym864mdw78rj, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ avg(id) from tab1_parse where
sex=:b_sex
Plan hash value: 2769599385
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.12 | 3905 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.12 | 3905 |
|* 2 | TABLE ACCESS STORAGE FULL| TAB1_PARSE | 1 | 700K| 700K|00:00:00.08 | 3905 |
---------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (CHAR(30), CSID=873): 'M '
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("SEX"=:B_SEX)
filter("SEX"=:B_SEX)
Look at mentioned columns
Select sql_id,SQL_TEXT,CHILD_NUMBER, IS_BIND_AWARE,IS_BIND_SENSITIVE,IS_SHAREABLE from v$sql where SQL_TEXT like '%GATHER_%tab1_parse%';
SQL_ID SQL_TEXT CHILD_NUMBER I I I
------------- ---------------------------------------- ------------ - - -
cym864mdw78rj select /*+ GATHER_PLAN_STATISTICS */ avg 0 N Y Y
(id) from tab1_parse where sex=:b_sex
Optimizer already noticed that query uses bind variable, and on column in question there is histogram. So IS_BIND_SENSITIVE is set to “Y”
Now we change value which should cause this plan to be inefficient
exec :b_sex := 'N/A';
select /*+ GATHER_PLAN_STATISTICS */ avg(id) from tab1_parse where sex=:b_sex;
select * from table(dbms_xplan.display_cursor(format=>'allstats last +PEEKED_BINDS'));
SQL_ID cym864mdw78rj, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ avg(id) from tab1_parse where
sex=:b_sex
Plan hash value: 2769599385
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.04 | 3905 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.04 | 3905 |
|* 2 | TABLE ACCESS STORAGE FULL| TAB1_PARSE | 1 | 700K| 1 |00:00:00.04 | 3905 |
---------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (CHAR(30), CSID=873): 'M '
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("SEX"=:B_SEX)
filter("SEX"=:B_SEX)
Select sql_id,SQL_TEXT,CHILD_NUMBER, IS_BIND_AWARE,IS_BIND_SENSITIVE,IS_SHAREABLE from v$sql where SQL_TEXT like '%GATHER_%tab1_parse%';
SQL_ID SQL_TEXT CHILD_NUMBER I I I
------------- ---------------------------------------- ------------ - - -
cym864mdw78rj select /*+ GATHER_PLAN_STATISTICS */ avg 0 N Y Y
(id) from tab1_parse where sex=:b_sex
Although this value was changed, still it was executed with “old, inefficient” Cursor. “A-Rows” should huge discrepancy between actuall and expected values.
But Cursor is already marked as BIND_AWARE!
Run it again with the same value
select /*+ GATHER_PLAN_STATISTICS */ avg(id) from tab1_parse where sex=:b_sex;
SQL_ID SQL_TEXT CHILD_NUMBER I I I
------------- ---------------------------------------- ------------ - - -
cym864mdw78rj select /*+ GATHER_PLAN_STATISTICS */ avg 0 N Y N
(id) from tab1_parse where sex=:b_sex
cym864mdw78rj select /*+ GATHER_PLAN_STATISTICS */ avg 1 Y Y Y
(id) from tab1_parse where sex=:b_sex
And..new Cursor (CHILD_NUMBER 1) was created ! Already marked as BIND_AWARE and BIND_SENSITIVE. In the same time, to save space in Library Cache CHILD_NUMBER 0 is marked as NON-SHAREABLE, so it will be first candidate to age out.
And the plan was
select * from table(dbms_xplan.display_cursor('cym864mdw78rj',1,'allstats last +PEEKED_BINDS'));
SQL_ID cym864mdw78rj, child number 1
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ avg(id) from tab1_parse where
sex=:b_sex
Plan hash value: 211962169
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 4 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1_PARSE | 1 | 1 | 1 |00:00:00.01 | 4 |
|* 3 | INDEX RANGE SCAN | TAB1_PARSE_SEX | 1 | 1 | 1 |00:00:00.01 | 3 |
-----------------------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (CHAR(30), CSID=873): 'N/A'
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("SEX"=:B_SEX)
Expected rows are equal to actuall rows
Let’s set value for our variable which is also quite selective, and using Index Range Scan would be proper for it.
exec :b_sex:='N';
select /*+ GATHER_PLAN_STATISTICS */ avg(id) from tab1_parse where sex=:b_sex;
Select sql_id,SQL_TEXT,CHILD_NUMBER, IS_BIND_AWARE,IS_BIND_SENSITIVE,IS_SHAREABLE from v$sql where SQL_TEXT like '%GATHER_%tab1_parse%';
SQL_ID SQL_TEXT CHILD_NUMBER I I I
------------- ---------------------------------------- ------------ - - -
cym864mdw78rj select /*+ GATHER_PLAN_STATISTICS */ avg 0 N Y N
(id) from tab1_parse where sex=:b_sex
cym864mdw78rj select /*+ GATHER_PLAN_STATISTICS */ avg 1 Y Y N
(id) from tab1_parse where sex=:b_sex
cym864mdw78rj select /*+ GATHER_PLAN_STATISTICS */ avg 2 Y Y Y
(id) from tab1_parse where sex=:b_sex
select * from table(dbms_xplan.display_cursor('cym864mdw78rj',2,'allstats last +PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cym864mdw78rj, child number 2
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ avg(id) from tab1_parse where
sex=:b_sex
Plan hash value: 211962169
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 172 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 172 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1_PARSE | 1 | 999 | 999 |00:00:00.01 | 172 |
|* 3 | INDEX RANGE SCAN | TAB1_PARSE_SEX | 1 | 999 | 999 |00:00:00.01 | 6 |
-----------------------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (CHAR(30), CSID=873): 'N '
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("SEX"=:B_SEX)
New Child Cursor num 2 was created. Plan for this Cursor is the same as for Cursor Child num 1. This child is BIND_AWARE, BIND_SENSITIVE, SHAREABLE. And…CHILD NUMBER 1 is marked as non-shareable to save space.
So what happens ? Based on bind variable values , optimizer builds something what is called SELECTIVITY CUBES. They are set of values that bind variables can fall into before a new plan is required.
So similar bind values, with selectivity causing the same plan are in the same cubes. Cubes are created and modified during observing by optimizer, how query runs with BIND_SENSITIVE Cursors.
Let’s continue
exec :b_sex:='F';
select /*+ GATHER_PLAN_STATISTICS */ avg(id) from tab1_parse where sex=:b_sex;
Select sql_id,SQL_TEXT,CHILD_NUMBER, IS_BIND_AWARE,IS_BIND_SENSITIVE,IS_SHAREABLE from v$sql where SQL_TEXT like '%GATHER_%tab1_parse%';
SQL_ID SQL_TEXT CHILD_NUMBER I I I
------------- ---------------------------------------- ------------ - - -
cym864mdw78rj select /*+ GATHER_PLAN_STATISTICS */ avg 0 N Y N
(id) from tab1_parse where sex=:b_sex
cym864mdw78rj select /*+ GATHER_PLAN_STATISTICS */ avg 1 Y Y N
(id) from tab1_parse where sex=:b_sex
cym864mdw78rj select /*+ GATHER_PLAN_STATISTICS */ avg 2 Y Y Y
(id) from tab1_parse where sex=:b_sex
cym864mdw78rj select /*+ GATHER_PLAN_STATISTICS */ avg 3 Y Y Y
(id) from tab1_parse where sex=:b_sex
select * from table(dbms_xplan.display_cursor('cym864mdw78rj',3,'allstats last +PEEKED_BINDS'));
select /*+ GATHER_PLAN_STATISTICS */ avg(id) from tab1_parse where
sex=:b_sex
Plan hash value: 2769599385
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.08 | 3905 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.08 | 3905 |
|* 2 | TABLE ACCESS STORAGE FULL| TAB1_PARSE | 1 | 299K| 299K|00:00:00.07 | 3905 |
---------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (CHAR(30), CSID=873): 'F '
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("SEX"=:B_SEX)
filter("SEX"=:B_SEX)
So, for non-selective value ‘F’ 3rd Child Cursor was created which is BIND_AWRE,BIND_SENSITIVE, SHAREABLE, but Child Cursor num 1 with the same plan is marked as non-shareable. These two bind values ‘M’ and ‘F’ are in the same selectivity cube.
To describe the full circle, let’s see what will happen when we set again value ‘M’
exec :b_sex:='M';
select /*+ GATHER_PLAN_STATISTICS */ avg(id) from tab1_parse where sex=:b_sex;
Select sql_id,SQL_TEXT,CHILD_NUMBER, IS_BIND_AWARE,IS_BIND_SENSITIVE,IS_SHAREABLE from v$sql where SQL_TEXT like '%GATHER_%tab1_parse%';
SQL_ID SQL_TEXT CHILD_NUMBER I I I
------------- ---------------------------------------- ------------ - - -
cym864mdw78rj select /*+ GATHER_PLAN_STATISTICS */ avg 0 N Y N
(id) from tab1_parse where sex=:b_sex
cym864mdw78rj select /*+ GATHER_PLAN_STATISTICS */ avg 1 Y Y N
(id) from tab1_parse where sex=:b_sex
cym864mdw78rj select /*+ GATHER_PLAN_STATISTICS */ avg 2 Y Y Y
(id) from tab1_parse where sex=:b_sex
cym864mdw78rj select /*+ GATHER_PLAN_STATISTICS */ avg 3 Y Y N
(id) from tab1_parse where sex=:b_sex
cym864mdw78rj select /*+ GATHER_PLAN_STATISTICS */ avg 4 Y Y Y
(id) from tab1_parse where sex=:b_sex
select * from table(dbms_xplan.display_cursor('cym864mdw78rj',4,'allstats last +PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cym864mdw78rj, child number 4
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ avg(id) from tab1_parse where
sex=:b_sex
Plan hash value: 2769599385
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.10 | 3905 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.10 | 3905 |
|* 2 | TABLE ACCESS STORAGE FULL| TAB1_PARSE | 1 | 700K| 700K|00:00:00.07 | 3905 |
---------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (CHAR(30), CSID=873): 'M '
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("SEX"=:B_SEX)
filter("SEX"=:B_SEX)
Now we have 2 SHAREABLE Cursors for all four possible values of our bind variable. So values ‘M’ and ‘F’ are in the same selectivity cube, and ‘N’ and ‘N/A’ in another single cube.
Hope, these examples let you understand how Adaptive Cursor Sharing allows executions of SQL make sensitive to bind variable values.
Other optimizations like DYNAMIC PLANS modifies plan during first execution depending eg. on cardinality of row sources.
But these 12c Adaptive Optimization I will explain in 4th part.
