Maybe you, like me, have read documentation that says SQL Plan Baselines work together Adaptive Cursor Sharing and wondered what this means? This is something lower down the priority list that I’ve been meaning to take a closer look at for some time.
Executive Summary
ACS and baselines do work together.
But I know when I read the documentation, I came away with some questions that I wanted to look at. I wondered if they did more together than they do.
For example, did baselines preserve some of the ACS not-so-secret sauce such that if the ACS information aged or was flushed out of the cache, we didn’t have to repeat the multiple executions to get the bind awareness back?
And the answer to that is no. Which I’m glad Coskan has shown in his follow-up post.
1. The bind awareness will kick back in eventually.
2. The baseline mechanism will allow the optimizer to use the ACS feedback and consider both plans in the baseline.
3. But we need to repeat the executions to get back to the bind awareness i.e. three runs to have one bind aware plan, four runs to have the two bind aware plans that we preserved initially.
Is that a big deal?
We’ve had to do one extra execution each of the statement but if that initial execution with the “wrong” plan was a big enough performance problem, then the implications could be significant.
And the two mechanisms that I thought might be designed to work together to address it, don’t unless you can guarantee that the information will not age out of the cache.
More Detail
Let me show you what I looked at.
Recap on ACS
Adaptive Cursor Sharing refers to cursors being bind sensitive and then bind aware.
First, a quick recap on bind sensitivity from the Performance Tuning Guide:
A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable.
The database monitors the behavior of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.
The criteria used by the optimizer to decide whether a cursor is bind-sensitive include the following:
– The optimizer has peeked at the bind values to generate selectivity estimates.
– A histogram exists on the column containing the bind value
….
A bind-aware cursor is a bind-sensitive cursor eligible to use different plans for different bind values. After a cursor has been made bind-aware, the optimizer chooses plans for future executions based on the bind value and its selectivity estimate.
When a statement with a bind-sensitive cursor executes, the database decides whether to mark the cursor bind-aware. The decision depends on whether the cursor produces significantly different data access patterns for different bind values.
Setup
First up, here’s the table and data I’m going to be working with:
SQL> create table t1
2 (col1 number
3 ,col2 varchar2(50)
4 ,flag varchar2(2));
Table created.
SQL>
SQL> insert into t1
2 select rownum
3 , lpad('X',50,'X')
4 , case when rownum = 1
5 then 'Y1'
6 when rownum = 2
7 then 'Y2'
8 when mod(rownum,2) = 0
9 then 'N1'
10 else 'N2'
11 end
12 from dual
13 connect by rownum <= 100000;
100000 rows created.
SQL>
SQL> select flag, count(*) from t1 group by flag;
FL COUNT(*)
-- ----------
N2 49999
N1 49999
Y1 1
Y2 1
SQL> create index i1 on t1 (flag);
Index created.
SQL>
(The ‘N2’ and ‘Y2’ values in the test data are not relevant to this article – something else I was looking at…)
Show that bind sensitivity requires histogram (and index so that we have an alternative execution plan)
I’ve some skew, but no stats. No stats, means no histogram. No histogram should mean no bind sensitivity.
SQL> var n varchar2(2)
SQL> exec :n := 'N1'
PL/SQL procedure successfully completed.
SQL> select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL>
SQL> 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 | | | | 322 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 44032 | 1290K| 322 (3)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
Note
-----
- dynamic sampling used for this statement (level=4)
23 rows selected.
SQL> select sql_id
2 , child_number
3 , is_bind_aware
4 , is_bind_sensitive
5 , is_shareable
6 , to_char(exact_matching_signature) sig
7 , executions
8 , plan_hash_value
9 from v$sql
10 where sql_id = '731b98a8u0knf';
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------- ---------- ---------------
731b98a8u0knf 0 N N Y 1292784087274697613 1 3724264953
SQL>
So, not bind-aware, not bind-sensitive.
Show that if we gather histogram we get bind sensitivity
If we gather some stats, then repeat the previous test:
SQL> exec dbms_stats.gather_table_stats(USER,'T1',method_opt=>'FOR COLUMNS flag SIZE 5',no_invalidate=>FALSE);
PL/SQL procedure successfully completed.
SQL> select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL>
SQL> 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 | | | | 322 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 49257 | 1443K| 322 (3)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
19 rows selected.
SQL>
SQL> select sql_id
2 , child_number
3 , is_bind_aware
4 , is_bind_sensitive
5 , is_shareable
6 , to_char(exact_matching_signature) sig
7 , executions
8 , plan_hash_value
9 from v$sql
10 where sql_id = '731b98a8u0knf';
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------- ---------- ---------------
731b98a8u0knf 0 N Y Y 1292784087274697613 1 3724264953
SQL>
We have a cursor that IS bind sensitive but IS NOT bind aware.
Baselines, preserve us
If we stick that in a baseline:
SQL> declare
2 l_op pls_integer;
3 begin
4 l_op :=
5 dbms_spm.load_plans_from_cursor_cache('731b98a8u0knf');
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
And delete the stats:
SQL> exec dbms_stats.delete_table_stats(USER,'T1',no_invalidate=>FALSE);
PL/SQL procedure successfully completed.
SQL>
And repeat the query:
SQL> select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL>
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID 731b98a8u0knf, child number 0
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found
NOTE: cannot fetch plan for SQL_ID: 731b98a8u0knf, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
8 rows selected.
SQL> select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL>
SQL> 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 | | | | 322 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 44032 | 1290K| 322 (3)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
Note
-----
- dynamic sampling used for this statement (level=4)
- SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement
24 rows selected.
SQL>
SQL> select sql_id
2 , child_number
3 , is_bind_aware
4 , is_bind_sensitive
5 , is_shareable
6 , to_char(exact_matching_signature) sig
7 , executions
8 , plan_hash_value
9 from v$sql
10 where sql_id = '731b98a8u0knf';
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------- ---------- ---------------
731b98a8u0knf 0 N N Y 1292784087274697613 1 3724264953
SQL>
then we have a plan that is NOT bind sensitive
And if we regather stats and repeat, it is back to being bind sensitive:
SQL> exec dbms_stats.gather_table_stats(USER,'T1',method_opt=>'FOR COLUMNS flag SIZE 5',no_invalidate=>FALSE);
PL/SQL procedure successfully completed.
SQL> select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL>
SQL> 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 | | | | 322 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 51829 | 1518K| 322 (3)| 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.
SQL>
SQL> select sql_id
2 , child_number
3 , is_bind_aware
4 , is_bind_sensitive
5 , is_shareable
6 , to_char(exact_matching_signature) sig
7 , executions
8 , plan_hash_value
9 from v$sql
10 where sql_id = '731b98a8u0knf';
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------- ---------- ---------------
731b98a8u0knf 0 N Y Y 1292784087274697613 1 3724264953
SQL>
So, in summary this clarifies that bind sensitivity is not an attribute that is preserved in a SQL Plan Baseline.
How then do ACS and SQL Plan Baselines work together?
From reading the documentation, you might get the impression – or at least I did – that there’s more to the combination of baselines and ACS than there is.
Let’s get two BIND AWARE plans.
Continuing on from above, let me delete the baseline, flush and start again with just the table and the data.
We start with one query that selects a large proportion of the data with a FTS:
SQL> var n varchar2(2)
SQL> exec :n := 'N1'
PL/SQL procedure successfully completed.
SQL> select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL>
SQL> 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 | | | | 322 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 51829 | 1518K| 322 (3)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
19 rows selected.
SQL>
Then, let’s use a different bind that selects a much smaller proportion of the data, initially with a FTS as well because that’s what’s parsed and shareable:
SQL> exec :n := 'Y1'
PL/SQL procedure successfully completed.
SQL> select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL>
SQL> 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 | | | | 322 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 51829 | 1518K| 322 (3)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
19 rows selected.
SQL>
But after a couple of executions we get a more appropriate plan, thanks to ACS:
SQL> select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL>
SQL> 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 | 9 | 270 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 9 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:N)
20 rows selected.
SQL>
And we see in V$SQL that we have the original cursor marked as NOT SHAREABLE and a new cursor marked now as BIND AWARE.
SQL> select sql_id
2 , child_number
3 , is_bind_aware
4 , is_bind_sensitive
5 , is_shareable
6 , to_char(exact_matching_signature) sig
7 , executions
8 , plan_hash_value
9 from v$sql
10 where sql_id = '731b98a8u0knf';
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------- ---------- ---------------
731b98a8u0knf 0 N Y N 1292784087274697613 2 3724264953
731b98a8u0knf 1 Y Y Y 1292784087274697613 1 3625400295
SQL>
And if we execute the SQL with the initial bind that favoured a FTS, we do get that FTS as is appropriate but we have a new child cursor that is also bind aware:
SQL> exec :n := 'N1'
PL/SQL procedure successfully completed.
SQL> select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL>
SQL> 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 | | | | 322 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 51829 | 1518K| 322 (3)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
19 rows selected.
SQL>
SQL> select sql_id
2 , child_number
3 , is_bind_aware
4 , is_bind_sensitive
5 , is_shareable
6 , to_char(exact_matching_signature) sig
7 , executions
8 , plan_hash_value
9 from v$sql
10 where sql_id = '731b98a8u0knf';
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------- ---------- ---------------
731b98a8u0knf 0 N Y N 1292784087274697613 2 3724264953
731b98a8u0knf 1 Y Y Y 1292784087274697613 1 3625400295
731b98a8u0knf 2 Y Y Y 1292784087274697613 1 3724264953
SQL>
Stick the bind-aware plans in a baseline
Now, let’s baseline the two plans:
SQL> declare
2 l_op pls_integer;
3 begin
4 l_op :=
5 dbms_spm.load_plans_from_cursor_cache('731b98a8u0knf');
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
And validate that that was successful because DBMS_SQLTUNE.LOAD_PLANS_FROM_CURSOR_CACHE does not error if no plans were loaded:
SQL> select to_char(signature) signature, sql_handle, plan_name, enabled, accepted, reproduced
2 from dba_sql_plan_baselines
3 where signature = 1292784087274697613;
SIGNATURE SQL_HANDLE PLAN_NAME ENA ACC REP
------------------------ ------------------------ ----------------------------
1292784087274697613 SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd616acf47 YES YES YES
1292784087274697613 SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd8576eb1f YES YES YES
SQL>
Rinse and repeat
Now lets flush the shared pool and see what happens when we run those statements which are meant to be
bind sensitive.
Firstly, if we initially run with the bind for the FTS, that’s what we get. It’s no surprise:
SQL> select sql_id
2 , child_number
3 , is_bind_aware
4 , is_bind_sensitive
5 , is_shareable
6 , to_char(exact_matching_signature) sig
7 , executions
8 , plan_hash_value
9 from v$sql
10 where sql_id = '731b98a8u0knf';
no rows selected
SQL> exec :n := 'N1'
PL/SQL procedure successfully completed.
SQL> select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL>
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID 731b98a8u0knf, child number 0
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found
NOTE: cannot fetch plan for SQL_ID: 731b98a8u0knf, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
8 rows selected.
SQL> select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL>
SQL> 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 | | | | 322 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 51829 | 1518K| 322 (3)| 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.
SQL>
And if we follow-up with the index-favouring value:
SQL> exec :n := 'Y1'
PL/SQL procedure successfully completed.
SQL> select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL>
SQL> 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 | | | | 322 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T1 | 51829 | 1518K| 322 (3)| 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.
SQL>
SQL> select sql_id
2 , child_number
3 , is_bind_aware
4 , is_bind_sensitive
5 , is_shareable
6 , to_char(exact_matching_signature) sig
7 , executions
8 , plan_hash_value
9 from v$sql
10 where sql_id = '731b98a8u0knf';
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------- ---------- ---------------
731b98a8u0knf 0 N Y Y 1292784087274697613 2 3724264953
SQL>
We’re back where we started. We’re using one of our baseline plans but we’ve lost our bind-awareness.
What do you mean we’ve lost our bind-awareness?
Well, previously we did all that hard work with multiple executions to get our bind-awareness. The baselines have preserved and are enforcing our ACCEPTED plans but we have to repeat the multiple executions to get back the bind-awareness.
Maybe it helps if we remind ourselves what the optimizer does when baseline plans are present.
At hard parse time, the optimizer will peek at the binds and generate a best cost plan anyway, regardless of the presence of a baselined plan.
If the best cost plan matches one that’s in a baseline, then we get a note in an optimizer trace file along the lines of:
SPM: cost-based plan found in the plan baseline, planId = 2239163167
If we don’t have the best cost plan in the baseline then we get a line in the optimizer trace along the lines of:
SPM: planId's of plan baseline are: 1634389831
SPM: using qksan to reproduce, cost and select accepted plan, sig = 1292784087274697613
SPM: plan reproducibility round 1 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 1634389831
Bind List follows:
bind value pos=1 name=
bind(0x2ad9a1c7cd40): flags=0 typ=1 data=0x2ad9a285af88 len=2
bind_strval="Y2"
bind in binary format:
2AD9A285AF80 09E83259 [Y2..]
and a new plan is added to the baseline as UNACCEPTED and stored for future evolution whilst a baseline is then used to generate the parsed plan.
So, whilst we do have our two baselined plans, the initial hard parse peeks at the binds and gets ‘N1’, generates the best cost plan which matches one of the baselines – job done. (And if the initial hard parse had peeked and found value ‘Y1’ we would have had our baselined index plan).
The subsequent execution of the cursor with a different value falls into the standard shareable SQL scenario – the initial cursor was parsed with peeked binds, the values of which do not suit our subsequent execution until ACS kicks in as previously.
If we return to our sql statement above and re-execute with the same bind that should be using an index, we see the switch from baseline plan SQL_PLAN_13w748wknkcwd616acf47 to SQL_PLAN_13w748wknkcwd8576eb1f:
SQL> select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL>
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID 731b98a8u0knf, child number 1
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found
NOTE: cannot fetch plan for SQL_ID: 731b98a8u0knf, CHILD_NUMBER: 1
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
8 rows selected.
SQL>
SQL> select sql_id
2 , child_number
3 , is_bind_aware
4 , is_bind_sensitive
5 , is_shareable
6 , to_char(exact_matching_signature) sig
7 , executions
8 , plan_hash_value
9 from v$sql
10 where sql_id = '731b98a8u0knf';
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------- ---------- ---------------
731b98a8u0knf 0 N Y N 1292784087274697613 2 3724264953
SQL> select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL>
SQL> 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 | 9 | 270 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 9 | | 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.
SQL>
SQL> select sql_id
2 , child_number
3 , is_bind_aware
4 , is_bind_sensitive
5 , is_shareable
6 , to_char(exact_matching_signature) sig
7 , executions
8 , plan_hash_value
9 from v$sql
10 where sql_id = '731b98a8u0knf';
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ----------------------- ---------- ---------------
731b98a8u0knf 0 N Y N 1292784087274697613 2 3724264953
731b98a8u0knf 1 N Y Y 1292784087274697613 1 3625400295
SQL> exec :n := 'N1'
PL/SQL procedure successfully completed.
SQL>
Again, then marking of child 0 as NOT SHAREABLE, etc, etc.
So, what are you saying?
Well, not a lot really.
In summary, from the documentation, you may get the impression that there’s more to the combination of ACS and baselines than there really is.
ACS and baselines work alongside each other but somewhat independently.
ACS can provide SPM with bind-sensitive plans to evolve.
Whilst SPM can store the multiple plans that ACS requires.
But ACS attributes are not stored in SPM.
Even with multiple plans in the a baseline, if your ACS information is flushed or ages out of the cache, you’re going to have to repeat the relevant executions required to get that ACS information back. Baselines can’t act as a shortcut to bringing back in that ACS feedback.
BOTTOM LINE: if you’re using binds when you should be using literals, there’s no silver bullet
I’ve made no mention here of views:
- V$SQL_CS_HISTOGRAM
- V$SQL_CS_SELECTIVITY
- V$SQL_CS_STATISTICS
but these are worthy of further observation if you want to investigate ACS. However that was not the point of this post.
OraStory 
Recent Comments