Today I saw a post from my friend Dominic Brooks (orastory) whom I work together at the same place for different environments (Wish he is working on the environments I look after together we could have made a great performance team :(). His post is about how Adaptive Cursor Sharing and SQL Plan Baselines work together . Post looks long but easy to read and enlightening about the issue with one small addition/correction/observation needed. I think he covered well enough but finished his tests a bit soon. I will not repeat what he has done but I need to cover the additional observation/correction part. After the introduction, half way through wrote a section “How then do ACS and SQL Plan Baselines work together?” (wish he uses some marking for subtopics – thats why we have bold and headings Dom). At the end of that section he says “We’re using one of our baseline plans but we’ve lost our bind-awareness.” which is not complete truth according to my tests on 11.2.0.1.
I assume table is created, index is created, stats are gathered, two sql_plan_baseline is generated and cursor is flushed as below
HR@ORACOS> select sql_id
, child_number
, is_bind_aware
, is_bind_sensitive
, is_shareable
, to_char(exact_matching_signature) sig
, executions
, plan_hash_value,
sql_plan_baseline
from v$sql
2 3 4 5 6 7 8 9 10 11 where sql_id = '731b98a8u0knf';
no rows selected
HR@ORACOS> select to_char(signature) signature, sql_handle, plan_name, enabled, accepted
from dba_sql_plan_baselines
2 3 where signature = 1292784087274697613;
SIGNATURE SQL_HANDLE PLAN_NAME ENA ACC
---------------------------------------- ------------------------------ ------------------------------ --- ---
1292784087274697613 SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd616acf47 YES YES
1292784087274697613 SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd8576eb1f YES YES
First Run — Full table scan —To make the post short I named v$sql query as sqlstat
var n varchar2(2)
exec :n := 'N1'
select count(*), max(col2) from t1 where flag = :n;
HR@ORACOS>
PL/SQL procedure successfully completed.
HR@ORACOS>
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
HR@ORACOS> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 203 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 49298 | 1444K| 203 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement
23 rows selected.
HR@ORACOS> @sqlstat
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf 0 N Y Y 1292784087274697613 1 3724264953 SQL_PLAN_13w748wknkcwd616acf47
Statement is not bind aware which is expected but is bind sensitive and sharable and using baseline
Second Run — Index Scan
exec :n := 'Y1'
select count(*), max(col2) from t1 where flag = :n;
PL/SQL procedure successfully completed.
HR@ORACOS>
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
HR@ORACOS> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 203 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 49298 | 1444K| 203 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement
23 rows selected.
HR@ORACOS> @sqlstat
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf 0 N Y Y 1292784087274697613 2 3724264953 SQL_PLAN_13w748wknkcwd616acf47
Statement is not bind aware which is “normal” as Optimizer group already wrote about in their optimizer blog and cursor is still sharable.
Optimizer group said
"Oracle monitors the behavior of the queries, and determined that the different bind values caused the data volumes manipulated by the query to be significantly different. Based on this difference, Oracle "adapts" its behavior so that the same plan is not always shared for this query. Hence on the next execution, a new plan is generated based on the current bind value."
Lets see if it is like that when baselines are in place
Third Run — Index Scan
exec :n := 'Y1'
select count(*), max(col2) from t1 where flag = :n;
PL/SQL procedure successfully completed.
HR@ORACOS>
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
HR@ORACOS> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 731b98a8u0knf, child number 1
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3625400295
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 30 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd8576eb1f used for this statement
24 rows selected.
HR@ORACOS> @sqlstat
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf 0 N Y Y 1292784087274697613 2 3724264953 SQL_PLAN_13w748wknkcwd616acf47
731b98a8u0knf 1 Y Y Y 1292784087274697613 1 3625400295 SQL_PLAN_13w748wknkcwd8576eb1f
Now on the second run of index favouring bind, new cursor is generated and bind aware also using the index scan baseline which is correct behaviour according to the optimizer groups statement.
Fourth Run — Table Scan
exec :n := 'N1'
select count(*), max(col2) from t1 where flag = :n;
PL/SQL procedure successfully completed.
HR@ORACOS>
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
HR@ORACOS> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 731b98a8u0knf, child number 2
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 203 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 49298 | 1444K| 203 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement
23 rows selected.
HR@ORACOS> @sqlstat
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf 0 N Y N 1292784087274697613 2 3724264953 SQL_PLAN_13w748wknkcwd616acf47
731b98a8u0knf 1 Y Y Y 1292784087274697613 1 3625400295 SQL_PLAN_13w748wknkcwd8576eb1f
731b98a8u0knf 2 Y Y Y 1292784087274697613 1 3724264953 SQL_PLAN_13w748wknkcwd616acf47
Now on the second run of table scan again new cursor is generated and again bind aware also using the right full table scan baseline. Please also look at the first cursor which is not sharable anymore.
Fifth Run — Index Scan
exec :n := 'Y1'
select count(*), max(col2) from t1 where flag = :n;
PL/SQL procedure successfully completed.
HR@ORACOS>
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
HR@ORACOS> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 731b98a8u0knf, child number 1
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3625400295
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 30 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd8576eb1f used for this statement
24 rows selected.
HR@ORACOS> @sqlstat
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf 0 N Y N 1292784087274697613 2 3724264953 SQL_PLAN_13w748wknkcwd616acf47
731b98a8u0knf 1 Y Y Y 1292784087274697613 2 3625400295 SQL_PLAN_13w748wknkcwd8576eb1f
731b98a8u0knf 2 Y Y Y 1292784087274697613 1 3724264953 SQL_PLAN_13w748wknkcwd616acf47
ORacle uses correct child cursor which is first child and hasn’t got any of is bind awareness or sensitivity changed
Sixth Run — Table Scan
HR@ORACOS>
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
HR@ORACOS> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 731b98a8u0knf, child number 2
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 203 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 49298 | 1444K| 203 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement
23 rows selected.
HR@ORACOS> @sqlstat
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf 0 N Y N 1292784087274697613 2 3724264953 SQL_PLAN_13w748wknkcwd616acf47
731b98a8u0knf 1 Y Y Y 1292784087274697613 2 3625400295 SQL_PLAN_13w748wknkcwd8576eb1f
731b98a8u0knf 2 Y Y Y 1292784087274697613 2 3724264953 SQL_PLAN_13w748wknkcwd616acf47
ORacle uses correct child cursor which is second child and hasn’t got any of is bind awareness or sensitivity changed
Bonus Run with different Bind N2 which should again favour full scan
exec :n := 'N2'
select count(*), max(col2) from t1 where flag = :n;
select * from table(dbms_xplan.display_cursor);
@sqlstat
PL/SQL procedure successfully completed.
HR@ORACOS>
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
HR@ORACOS>
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 731b98a8u0knf, child number 2
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 203 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 49298 | 1444K| 203 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement
23 rows selected.
HR@ORACOS> HR@ORACOS>
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf 0 N Y N 1292784087274697613 2 3724264953 SQL_PLAN_13w748wknkcwd616acf47
731b98a8u0knf 1 Y Y Y 1292784087274697613 2 3625400295 SQL_PLAN_13w748wknkcwd8576eb1f
731b98a8u0knf 2 Y Y Y 1292784087274697613 3 3724264953 SQL_PLAN_13w748wknkcwd616acf47
Oracle is doing the right thing by using the child no 1 with right baseline
Bonus Run with different Bind Y2 which should again favour Index Scan
exec :n := 'Y2'
select count(*), max(col2) from t1 where flag = :n;
select * from table(dbms_xplan.display_cursor);
HR@ORACOS>
PL/SQL procedure successfully completed.
HR@ORACOS>
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
HR@ORACOS>
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 731b98a8u0knf, child number 1
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3625400295
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 30 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd8576eb1f used for this statement
24 rows selected.
HR@ORACOS> @sqlstat
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE SQL_PLAN_BASELINE
------------- ------------ - - - ---------------------------------------- ---------- --------------- ------------------------------
731b98a8u0knf 0 N Y N 1292784087274697613 2 3724264953 SQL_PLAN_13w748wknkcwd616acf47
731b98a8u0knf 1 Y Y Y 1292784087274697613 3 3625400295 SQL_PLAN_13w748wknkcwd8576eb1f
731b98a8u0knf 2 Y Y Y 1292784087274697613 3 3724264953 SQL_PLAN_13w748wknkcwd616acf47
Oracle is doing the right thing by using the second child no 2 with right baseline.
Long story short I personally think they work perfectly fine together but I also wish if Oracle gives option to hold this runtime monitoring info in SYSAUX for env where people can accommodate more data in SYSAUX. This will save a lot of time for the initial loads.
To do a fair comparison I needed to do the tests on 11.2.0.1 but Don’t have 11.2.0.2 right now but I’m assuming it should be the same (I think I lost my appetite of installing new oracle version just for fun of it 🙂 )
Coskan's Approach to Oracle 