ACS and baselines do work together? For example, did
baselines preserve some of the ACS – such that if the ACS information aged or
flushed out of the cache, we didn’t have to repeat the multiple execution, to
get the bind awareness back?
And the answer to that is no,
- The bind awareness will kick back in eventually
- The baseline mechanism will allow the optimizer to use ACS feedback and consider both the plans in the baseline
- But we need to have multiple execution to get the bind awareness back.
Let me show what I looked at.
rajesh@ORA11G> create table t
2 as
3 select a.* ,
4 decode(rownum,1,1,99) as flag
5 from all_objects a;
Table created.
rajesh@ORA11G>
rajesh@ORA11G> create index t_idx on t(flag);
Index created.
rajesh@ORA11G> begin
2 dbms_stats.gather_table_stats(
3 ownname=>user,
4 tabname=>'T',
5 estimate_percent=>100,
6 cascade=>true,
7 method_opt=>'for all indexed columns
size 254');
8 end;
9 /
PL/SQL procedure successfully completed.
rajesh@ORA11G>
So we have a massively skewed data on the column ‘flag’ with an index and histogram on it.
Now the very first run with most popular values in bind variable.
rajesh@ORA11G>
exec :n := 99;
PL/SQL
procedure successfully completed.
rajesh@ORA11G>
select max(object_id) from t
2
where flag = :n;
MAX(OBJECT_ID)
--------------
89145
1 row selected.
rajesh@ORA11G>
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID f4sssw14rkhf0, child
number 0
-------------------------------------
select
max(object_id) from t where flag = :n
Plan hash
value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | |
354 (100)| |
| 1 |
SORT AGGREGATE | |
1 | 16 | | |
|* 2 |
TABLE ACCESS FULL| T | 84620 | 1322K|
354 (2)| 00:00:05 |
---------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
19 rows
selected.
rajesh@ORA11G>
select sql_id,child_number,
2
is_bind_aware,
3
is_bind_sensitive,
4 is_shareable,
5
to_char(exact_matching_signature) sig,
6
executions, plan_hash_value
7 from
v$sql
8
where sql_id ='f4sssw14rkhf0';
SQL_ID CHILD_NUMBER I I I SIG
EXECUTIONS
-------------
------------ - - - ---------------------
----------
f4sssw14rkhf0 0 N Y Y
13707516052980376030 1
1 row selected.
rajesh@ORA11G>
This cursor is bind sensitive, but is NOT bind aware and it is still
shareable.
rajesh@ORA11G>
exec :n := 1;
PL/SQL
procedure successfully completed.
rajesh@ORA11G>
select max(object_id) from t
2
where flag = :n;
MAX(OBJECT_ID)
--------------
20
1 row selected.
rajesh@ORA11G>
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID f4sssw14rkhf0, child
number 0
-------------------------------------
select
max(object_id) from t where flag = :n
Plan hash
value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | |
354 (100)| |
| 1 |
SORT AGGREGATE | |
1 | 16 | | |
|* 2 |
TABLE ACCESS FULL| T | 84620 | 1322K|
354 (2)| 00:00:05 |
---------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
19 rows
selected.
rajesh@ORA11G>
select sql_id,child_number,
2
is_bind_aware,
3
is_bind_sensitive,
4 is_shareable,
5
to_char(exact_matching_signature) sig,
6
executions, plan_hash_value
7 from
v$sql
8
where sql_id ='f4sssw14rkhf0';
SQL_ID CHILD_NUMBER I I I SIG
EXECUTIONS
-------------
------------ - - - ---------------------
----------
f4sssw14rkhf0 0 N Y Y
13707516052980376030 2
1 row selected.
rajesh@ORA11G>
for the very first execution, optimizer made a mistake, This cursor is
bind sensitive, but is NOT bind aware and it is still shareable.
But after a repeat, we got an appropriate plan, thanks to ACS
rajesh@ORA11G>
exec :n := 1;
PL/SQL
procedure successfully completed.
rajesh@ORA11G>
select max(object_id) from t
2
where flag = :n;
MAX(OBJECT_ID)
--------------
20
1 row selected.
rajesh@ORA11G>
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID f4sssw14rkhf0, child
number 1
-------------------------------------
select
max(object_id) from t where flag = :n
Plan hash
value: 1789076273
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| | 2 (100)| |
| 1 |
SORT AGGREGATE | |
1 | 16 | | |
| 2 |
TABLE ACCESS BY INDEX ROWID| T
| 1 | 16 |
2 (0)| 00:00:01 |
|* 3 |
INDEX RANGE SCAN | T_IDX
| 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:N)
20 rows
selected.
rajesh@ORA11G>
select sql_id,child_number,
2
is_bind_aware,
3
is_bind_sensitive,
4
is_shareable,
5
to_char(exact_matching_signature) sig,
6
executions, plan_hash_value
7 from
v$sql
8
where sql_id ='f4sssw14rkhf0' ;
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS
-------------
------------ - - - ---------------------
----------
f4sssw14rkhf0 0 N Y N 13707516052980376030 2
f4sssw14rkhf0 1 Y Y Y 13707516052980376030 1
2 rows
selected.
rajesh@ORA11G>
So this new child cursor (child_number=1) is not only bind-sensitive,
but also bind aware and it is shareable. See how this pervious child cursor
(child_number=0) changed its is_shareable attribute from ‘Y’ to ‘N’, which is
non-shareable (not shared across different executions)
Now flipping the bind variable to most popular value, we get this.
rajesh@ORA11G>
exec :n := 99;
PL/SQL
procedure successfully completed.
rajesh@ORA11G>
select max(object_id) from t
2
where flag = :n;
MAX(OBJECT_ID)
--------------
89177
1 row selected.
rajesh@ORA11G>
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID f4sssw14rkhf0, child
number 2
-------------------------------------
select
max(object_id) from t where flag = :n
Plan hash
value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | |
354 (100)| |
| 1 |
SORT AGGREGATE | |
1 | 16 | | |
|* 2 |
TABLE ACCESS FULL| T | 84624 | 1322K|
354 (2)| 00:00:05 |
---------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
19 rows
selected.
rajesh@ORA11G>
select sql_id,child_number,
2
is_bind_aware,
3 is_bind_sensitive,
4
is_shareable,
5
to_char(exact_matching_signature) sig,
6
executions
7 from
v$sql
8
where sql_id ='f4sssw14rkhf0';
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS
-------------
------------ - - - -------------------- ----------
f4sssw14rkhf0 0 N Y N 13707516052980376030 2
f4sssw14rkhf0 1 Y Y Y 13707516052980376030 1
f4sssw14rkhf0 2 Y Y Y 13707516052980376030 1
3 rows
selected.
rajesh@ORA11G>
Now, let us baseline both the plans.
rajesh@ORA11G> set serveroutput on
rajesh@ORA11G> exec
dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id=>'f4sssw14rkhf0'));
2
PL/SQL procedure successfully completed.
rajesh@ORA11G> set serveroutput off
rajesh@ORA11G>
rajesh@ORA11G> select to_char(signature) sig,
2 sql_handle,plan_name,
3 enabled,accepted,reproduced
4 from dba_sql_plan_baselines ;
SIG
SQL_HANDLE
PLAN_NAME ENA ACC REP
-------------------- ---------------------
------------------------------ --- --- ---
13707516052980376030 SQL_be3ae13982dc21de SQL_PLAN_bwfr1761ds8fy17262d88 YES YES YES
13707516052980376030 SQL_be3ae13982dc21de SQL_PLAN_bwfr1761ds8fy3fdbb376 YES YES YES
2 rows selected.
rajesh@ORA11G>
rajesh@ORA11G>
Now let us flush the shared pool and see what happens when we run those
statements, which are meant to be bind sensitive.
rajesh@ORA11G>
rajesh@ORA11G> alter system flush shared_pool;
System altered.
rajesh@ORA11G> select sql_id,child_number,
2 is_bind_aware,
3 is_bind_sensitive,
4 is_shareable,
5 to_char(exact_matching_signature) sig,
6 executions
7 from v$sql
8 where sql_id ='f4sssw14rkhf0';
no rows selected
rajesh@ORA11G>
Firstly, if we run initially with the bind for the FTS, that’s what we
get, it’s no surprise.
rajesh@ORA11G> exec :n := 99;
PL/SQL procedure successfully completed.
rajesh@ORA11G> select max(object_id) from t
2 where flag = :n;
MAX(OBJECT_ID)
--------------
89187
1 row selected.
rajesh@ORA11G> select * from
table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID f4sssw14rkhf0,
child number 0
An uncaught error happened in prepare_sql_statement : ORA-01403:
no data found
NOTE: cannot fetch plan for SQL_ID: f4sssw14rkhf0, 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.
rajesh@ORA11G> select max(object_id) from t
2 where flag = :n;
MAX(OBJECT_ID)
--------------
89187
1 row selected.
rajesh@ORA11G> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID f4sssw14rkhf0, child number 0
-------------------------------------
select max(object_id) from t where flag = :n
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
| | 354 (100)| |
| 1 | SORT AGGREGATE |
| 1 | 16 | | |
|* 2 | TABLE ACCESS FULL| T | 84624 |
1322K| 354 (2)| 00:00:05 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
filter("FLAG"=:N)
Note
-----
- SQL plan
baseline SQL_PLAN_bwfr1761ds8fy3fdbb376 used for this statement
23 rows selected.
rajesh@ORA11G> select sql_id,child_number,
2 is_bind_aware,
3 is_bind_sensitive,
4 is_shareable,
5 to_char(exact_matching_signature) sig,
6 executions
7 from v$sql
8 where sql_id ='f4sssw14rkhf0';
SQL_ID CHILD_NUMBER
I I I SIG EXECUTIONS
------------- ------------ - - - -------------------- ----------
f4sssw14rkhf0 0
N Y Y 13707516052980376030 1
1 row selected.
rajesh@ORA11G>
And if we followed up with the index-favoring value,
rajesh@ORA11G>
rajesh@ORA11G> exec :n := 1;
PL/SQL procedure successfully completed.
rajesh@ORA11G> select max(object_id) from t
2 where flag = :n;
MAX(OBJECT_ID)
--------------
20
1 row selected.
rajesh@ORA11G> select * from
table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID f4sssw14rkhf0, child number 0
-------------------------------------
select max(object_id) from t where flag = :n
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
| | 354 (100)| |
| 1 | SORT AGGREGATE |
| 1 | 16 | | |
|* 2 | TABLE ACCESS FULL| T | 84624 | 1322K|
354 (2)| 00:00:05 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
filter("FLAG"=:N)
Note
-----
- SQL plan
baseline SQL_PLAN_bwfr1761ds8fy3fdbb376 used for this statement
23 rows selected.
rajesh@ORA11G> select sql_id,child_number,
2 is_bind_aware,
3 is_bind_sensitive,
4 is_shareable,
5 to_char(exact_matching_signature) sig,
6 executions
7 from v$sql
8 where sql_id ='f4sssw14rkhf0';
SQL_ID CHILD_NUMBER
I I I SIG EXECUTIONS
------------- ------------ - - - -------------------- ----------
f4sssw14rkhf0 0
N Y Y 13707516052980376030 2
1 row selected.
We are back with where we started, we are using one of our baseline
plans, but we lost our bind awareness, means we did all the hard work with
multiple executions to get our bind awareness. The baselines have preserved and
enforcing our ACCEPETED plans, but we have to repeat the multiple executions to
get back the bind-awareness.
May be it helps if we remind ourselves what the optimizer does when
baseline plans are present? At hard parse the optimizer will peak at the binds
and generate the best cost plan anyways, regardless of the presence of a
baseline plans.
If this best cost plan matches with the one in baseline then we make
use of them, else just add the new plan to baseline as UNACCEPTED and store
there for future evolution, and make use of the best accepted plan from the
plan baselines.
Since we have two accepted plans in baselines, the initial hard parse
peek at the binds and get “99”, generates the best cost plan, which matches
with one of the baselines – job done. (and if the initial hard parse had peeked
and found the value ‘1’ we would have had our base lined index plan)
The subsequent execution of the cursor with a different value fall into
the standard sharable 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.
rajesh@ORA11G>
rajesh@ORA11G> exec :n := 1;
PL/SQL procedure successfully completed.
rajesh@ORA11G> select max(object_id) from t
2 where flag = :n;
MAX(OBJECT_ID)
--------------
20
1 row selected.
rajesh@ORA11G> select * from
table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID f4sssw14rkhf0, child number 0
-------------------------------------
select max(object_id) from t where flag = :n
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | |
354 (100)| |
| 1 | SORT AGGREGATE |
| 1 | 16 | | |
|* 2 | TABLE ACCESS FULL| T | 84624 | 1322K|
354 (2)| 00:00:05 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
filter("FLAG"=:N)
Note
-----
- SQL plan
baseline SQL_PLAN_bwfr1761ds8fy3fdbb376 used for this statement
23 rows selected.
rajesh@ORA11G> select sql_id,child_number,
2 is_bind_aware,
3 is_bind_sensitive,
4 is_shareable,
5 to_char(exact_matching_signature) sig,
6 executions
7 from v$sql
8 where sql_id ='f4sssw14rkhf0';
SQL_ID CHILD_NUMBER
I I I SIG EXECUTIONS
------------- ------------ - - - -------------------- ----------
f4sssw14rkhf0 0
N Y Y 13707516052980376030 2
1 row selected.
rajesh@ORA11G> select max(object_id) from t
2 where flag = :n;
MAX(OBJECT_ID)
--------------
20
1 row selected.
rajesh@ORA11G> select * from
table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID f4sssw14rkhf0,
child number 1
An uncaught error happened in prepare_sql_statement : ORA-01403:
no data found
NOTE: cannot fetch plan for SQL_ID: f4sssw14rkhf0, 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.
rajesh@ORA11G> select sql_id,child_number,
2 is_bind_aware,
3 is_bind_sensitive,
4 is_shareable,
5 to_char(exact_matching_signature) sig,
6 executions
7 from v$sql
8 where sql_id ='f4sssw14rkhf0';
SQL_ID CHILD_NUMBER
I I I SIG EXECUTIONS
------------- ------------ - - - -------------------- ----------
f4sssw14rkhf0 0
N Y N 13707516052980376030 2
1 row selected.
rajesh@ORA11G> select max(object_id) from t
2 where flag = :n;
MAX(OBJECT_ID)
--------------
20
1 row selected.
rajesh@ORA11G> select * from
table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID f4sssw14rkhf0, child number 1
-------------------------------------
select max(object_id) from t where flag = :n
Plan hash value: 1789076273
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
| | 2 (100)| |
| 1 | SORT AGGREGATE | |
1 | 16 | | |
| 2 | TABLE ACCESS BY INDEX
ROWID| T | 1 |
16 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX | 1 |
| 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 -
access("FLAG"=:N)
Note
-----
- SQL plan
baseline SQL_PLAN_bwfr1761ds8fy17262d88 used for this statement
24 rows selected.
rajesh@ORA11G> select sql_id,child_number,
2 is_bind_aware,
3 is_bind_sensitive,
4 is_shareable,
5 to_char(exact_matching_signature) sig,
6 executions
7 from v$sql
8 where sql_id ='f4sssw14rkhf0';
SQL_ID CHILD_NUMBER
I I I SIG EXECUTIONS
------------- ------------ - - - -------------------- ----------
f4sssw14rkhf0 0
N Y N 13707516052980376030 2
f4sssw14rkhf0 1
N Y Y 13707516052980376030 1
2 rows selected.
rajesh@ORA11G>
Again, making child_number=0 as NOT SHARABLE, etc, etc.
rajesh@ORA11G>
rajesh@ORA11G>
rajesh@ORA11G> exec :n := 99;
PL/SQL procedure successfully completed.
rajesh@ORA11G> select max(object_id) from t
2 where flag = :n;
MAX(OBJECT_ID)
--------------
89187
1 row selected.
rajesh@ORA11G> select * from
table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID f4sssw14rkhf0, child number 1
-------------------------------------
select max(object_id) from t where flag = :n
Plan hash value: 1789076273
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
| | 2 (100)| |
| 1 | SORT AGGREGATE | |
1 | 16 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T |
1 | 16 | 2
(0)| 00:00:01 |
|* 3 | INDEX RANGE
SCAN | T_IDX | 1 |
| 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 -
access("FLAG"=:N)
Note
-----
- SQL plan
baseline SQL_PLAN_bwfr1761ds8fy17262d88 used for this statement
24 rows selected.
rajesh@ORA11G> select sql_id,child_number,
2 is_bind_aware,
3 is_bind_sensitive,
4 is_shareable,
5 to_char(exact_matching_signature) sig,
6 executions
7 from v$sql
8 where sql_id ='f4sssw14rkhf0';
SQL_ID CHILD_NUMBER
I I I SIG EXECUTIONS
------------- ------------ - - - -------------------- ----------
f4sssw14rkhf0 0
N Y N 13707516052980376030 2
f4sssw14rkhf0 1
N Y Y 13707516052980376030 2
2 rows selected.
rajesh@ORA11G> select max(object_id) from t
2 where flag = :n;
MAX(OBJECT_ID)
--------------
89187
1 row selected.
rajesh@ORA11G> select * from
table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID f4sssw14rkhf0, child number 2
-------------------------------------
select max(object_id) from t where flag = :n
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
| | 354 (100)| |
| 1 | SORT AGGREGATE |
| 1 | 16 | | |
|* 2 | TABLE ACCESS FULL|
T | 84624 | 1322K|
354 (2)| 00:00:05 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
filter("FLAG"=:N)
Note
-----
- SQL plan
baseline SQL_PLAN_bwfr1761ds8fy3fdbb376 used for this statement
23 rows selected.
rajesh@ORA11G> select sql_id,child_number,
2 is_bind_aware,
3 is_bind_sensitive,
4 is_shareable,
5 to_char(exact_matching_signature) sig,
6 executions
7 from v$sql
8 where sql_id ='f4sssw14rkhf0';
SQL_ID CHILD_NUMBER
I I I SIG EXECUTIONS
------------- ------------ - - - -------------------- ----------
f4sssw14rkhf0 0
N Y N 13707516052980376030 2
f4sssw14rkhf0 1
N Y N 13707516052980376030 2
f4sssw14rkhf0 2
Y Y Y 13707516052980376030 1
3 rows selected.
rajesh@ORA11G> exec :n := 1;
PL/SQL procedure successfully completed.
rajesh@ORA11G> select max(object_id) from t
2 where flag = :n;
MAX(OBJECT_ID)
--------------
20
1 row selected.
rajesh@ORA11G> select * from
table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID f4sssw14rkhf0, child number 3
-------------------------------------
select max(object_id) from t where flag = :n
Plan hash value: 1789076273
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
| | 2 (100)| |
| 1 | SORT AGGREGATE | |
1 | 16 | | |
| 2 | TABLE ACCESS BY
INDEX ROWID| T | 1 |
16 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX | 1 |
| 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 -
access("FLAG"=:N)
Note
-----
- SQL plan
baseline SQL_PLAN_bwfr1761ds8fy17262d88 used for this statement
24 rows selected.
rajesh@ORA11G> select sql_id,child_number,
2 is_bind_aware,
3 is_bind_sensitive,
4 is_shareable,
5 to_char(exact_matching_signature) sig,
6 executions
7 from v$sql
8 where sql_id ='f4sssw14rkhf0';
SQL_ID CHILD_NUMBER
I I I SIG EXECUTIONS
------------- ------------ - - - -------------------- ----------
f4sssw14rkhf0 0
N Y N 13707516052980376030 2
f4sssw14rkhf0 1
N Y N 13707516052980376030 2
f4sssw14rkhf0 2
Y Y Y 13707516052980376030 1
f4sssw14rkhf0 3 Y Y Y
13707516052980376030 1
4 rows selected.
rajesh@ORA11G>
In summary,
- ACS and baselines work alongside each other but somewhat independently
- ACS can provide SPM with bind-sensitive plans to evolve.
- SPM can store multiple plans that ACS requires
- But ACS attributes are not stored in SPM.
Even with multiple plans in the baselines, if your ACS information is
flushed or aged out of the cache, you are going to have repeated, relevant
executions to get that ACS information back, baselines cannot act as a shortcut
to bring back in that ACS feedback.
