I ended my last post about the interaction between ACS and SPM by the following observation
How could a creation of an extra index disturb the ACS behavior?
Well, it seems that there is a different combination which leads to this situation. Instead of jumping to a conclusion that might be wrong I prefer presenting my demo upon which I will make a proposition and let you (readers thanks in advance for that) criticizing what I tend to affirm.
For sake of simplicity, the following sql against v$sql will be referred to as is_bind_aware.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';
The model used for this demo can be found in Dominic Brook’s article and I will start from here
SQL > exec :n := 'N2'; --> FULL TABLE SCAN
SQL > select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL > select * from table(dbms_xplan.display_cursor);
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 | | | | 275 (100)| |
| 1 | SORT AGGREGATE | | 1 | 54 | | |
|* 2 | TABLE ACCESS FULL| T1 | 50110 | 2642K| 275 (2)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement
SQL > exec :n := 'Y2'; --> INDEX RANGE SCAN
SQL > select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL > select * from table(dbms_xplan.display_cursor);
SQL_ID 731b98a8u0knf, child number 3
-------------------------------------
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 | 54 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 9 | 486 | 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
After several executions of the above sql, using alternately index and full table scan bind variables, I ended up with the following situation:
SQL > @is_bind_aware.sql SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE ------------- ------------ - - - ------------------ ----------- ----------------- 731b98a8u0knf 0 N Y N 1292784087274697613 2 3724264953 731b98a8u0knf 1 Y Y N 1292784087274697613 1 3625400295 731b98a8u0knf 2 Y Y Y 1292784087274697613 1 3724264953 --> TABLE FULL SCAN 731b98a8u0knf 3 Y Y Y 1292784087274697613 1 3625400295 --> INDEX RANGE SCAN SQL > select * from v$sql_cs_statistics where sql_id = '731b98a8u0knf'; ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED -------- ---------- ------------- ------------ ------------------- - ---------- -------------- 22A4D04C 2443201166 731b98a8u0knf 3 3066078819 Y 1 3 22A4D04C 2443201166 731b98a8u0knf 2 3938583969 Y 1 50000 22A4D04C 2443201166 731b98a8u0knf 1 2780714206 Y 1 3 22A4D04C 2443201166 731b98a8u0knf 0 1896453392 Y 1 50000 SQL > select * from v$sql_cs_histogram where sql_id = '731b98a8u0knf'; ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT -------- ---------- ------------- ------------ ---------- ---------- 22A4D04C 2443201166 731b98a8u0knf 3 0 1 --> rows_processed < 1,000 22A4D04C 2443201166 731b98a8u0knf 3 1 0 22A4D04C 2443201166 731b98a8u0knf 3 2 0 22A4D04C 2443201166 731b98a8u0knf 2 0 0 22A4D04C 2443201166 731b98a8u0knf 2 1 1 --> 1,000 <rows_processed <1,000,000 22A4D04C 2443201166 731b98a8u0knf 2 2 0 22A4D04C 2443201166 731b98a8u0knf 1 0 1 22A4D04C 2443201166 731b98a8u0knf 1 1 0 22A4D04C 2443201166 731b98a8u0knf 1 2 0 22A4D04C 2443201166 731b98a8u0knf 0 0 1 22A4D04C 2443201166 731b98a8u0knf 0 1 1 22A4D04C 2443201166 731b98a8u0knf 0 2 0
Two child cursor(2 and 3) that are (a) shareable (b) bind sensitive and (c) bind aware so that ACS can associate each bind variable to it’s a corresponding child number and hence the execution plan that best fits each bind variable.
Up to this point, ACS is working very well in presence of a SPM baseline
SQL > select 2 to_char(signature) signature 3 , sql_handle 4 , plan_name 5 , enabled 6 , accepted 7 from dba_sql_plan_baselines 8 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
I have two enabled and accepted sql plan baseline (one, SQL_PLAN …eb1f, for the index range scan and the other one, SQL_PLAN … acf47, for the table full scan). Now, I will create an extra index(i2) in addition to the existing i1 index and I will continue my selects
SQL > create index i2 on t1(flag,col2) compress; Index created.
I will then first execute my query for FULL TABLE SCAN bind variable
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 > select * from table(dbms_xplan.display_cursor); SQL_ID 731b98a8u0knf, child number 2 An uncaught error happened in prepare_sql_statement : ORA-01403: no data found NOTE: cannot fetch plan for SQL_ID: 731b98a8u0knf, CHILD_NUMBER: 2 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)
This error is an indication that something went abnormally as already notified by the Oracle Optimizer blog.
SQL > select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL > select * from table(dbms_xplan.display_cursor);
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 | | | | 275 (100)| |
| 1 | SORT AGGREGATE | | 1 | 54 | | |
|* 2 | TABLE ACCESS FULL| T1 | 49872 | 2629K| 275 (2)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement
For the sake of clarity I will present below the results of is_bind_aware.sql before the creation of the I2 index and after its creation
SQL > @is_bind_aware.sql --> before the index creation SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE ------------- ------------ - - - ------------------ ----------- ----------------- 731b98a8u0knf 0 N Y N 1292784087274697613 2 3724264953 731b98a8u0knf 1 Y Y N 1292784087274697613 1 3625400295 731b98a8u0knf 2 Y Y Y 1292784087274697613 1 3724264953 --> TABLE FULL SCAN 731b98a8u0knf 3 Y Y Y 1292784087274697613 1 3625400295 --> INDEX i1 RANGE SCAN SQL > @is_bind_aware.sql --> after the index creation SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE ------------- ------------ - - - ---------------------------------------- ---------- --------------- 731b98a8u0knf 0 N Y N 1292784087274697613 2 3724264953 731b98a8u0knf 1 Y Y N 1292784087274697613 1 3625400295 731b98a8u0knf 2 N N Y 1292784087274697613 1 3724264953 --> TABLE FULL SCAN
Wow!!! Child cursor n° 3 has gone while child cursor n° 2, despite it is still shareable, becomes however not bind sensitive and not bind aware. And how this has influenced the ACS view?
SQL > select * from v$sql_cs_statistics where sql_id = '731b98a8u0knf'; --> before the index creation ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED -------- ---------- ------------- ------------ ------------------- - ---------- -------------- 22A4D04C 2443201166 731b98a8u0knf 3 3066078819 Y 1 3 22A4D04C 2443201166 731b98a8u0knf 2 3938583969 Y 1 50000 22A4D04C 2443201166 731b98a8u0knf 1 2780714206 Y 1 3 22A4D04C 2443201166 731b98a8u0knf 0 1896453392 Y 1 50000 SQL > select * from v$sql_cs_statistics where sql_id = '731b98a8u0knf'; --> after the index creation ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED -------- ---------- ------------- ------------ ------------------- - ---------- -------------- 22A4D04C 2443201166 731b98a8u0knf 1 2780714206 Y 1 3 22A4D04C 2443201166 731b98a8u0knf 0 1896453392 Y 1 50000
No trace of child cursor n ° 2 or n ° 3 in this view while the presence of child cursor n ° 0 and n ° 1 can be considered as obsolete because they represent a non shareable cursors.
Let’s continue executing the query this time using the INDEX RANGE SCAN bind variable
SQL > exec :n := 'Y1'
SQL > select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL > select * from table(dbms_xplan.display_cursor);
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 | | | | 275 (100)| |
| 1 | SORT AGGREGATE | | 1 | 54 | | |
|* 2 | TABLE ACCESS FULL| T1 | 49872 | 2629K| 275 (2)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement
Repeat the same sql several times to see if ACS will kick off and produce the INDEX RANGE SCAN plan (the one identified into the SPM baseline SQL_PLAN …eb1f)
SQL > select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL > /
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL > /
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
… Execute this several times
SQL > select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
--------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL >select * from table(dbms_xplan.display_cursor);
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 | | | | 275 (100)| |
| 1 | SORT AGGREGATE | | 1 | 54 | | |
|* 2 | TABLE ACCESS FULL| T1 | 49872 | 2629K| 275 (2)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)
Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement
No way for the CBO to produce the INDEX RANGE SCAN Plan so that the SPM will select it. And why the CBO is unable to produce the INDEX RANGE SCAN plan? There might be two answers to that question (a) either the ACS is working well but it is producing a plan that is not in the SPM and hence it is constrained or (b) the ACS is not working and the CBO is always sharing the existing FULL TABLE SCAN until a hard parse occurs. Let see first if the ACS is working well
SQL> @is_bind_aware SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE ------------- ------------ - - - -------------------- ----------- ----------------- 731b98a8u0knf 0 N Y N 1292784087274697613 2 3724264953 731b98a8u0knf 1 Y Y N 1292784087274697613 1 3625400295 731b98a8u0knf 2 N N Y 1292784087274697613 18 3724264953 SQL > select * from v$sql_cs_statistics where sql_id = '731b98a8u0knf'; ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED -------- ---------- ------------- ------------ ------------------- - ---------- -------------- 22A4D04C 2443201166 731b98a8u0knf 1 2780714206 Y 1 3 22A4D04C 2443201166 731b98a8u0knf 0 1896453392 Y 1 50000
ACS is not working!!!
What if I disable the use of sql baseline?
SQL > show parameter '%baseline%'
NAME TYPE VALUE
------------------------------------ ----------- -------------------------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL > alter session set optimizer_use_sql_plan_baselines = FALSE;
Session altered.
SQL > select count(*), max(col2) from t1 where flag = :n;
COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL > select * from table(dbms_xplan.display_cursor);
SQL_ID 731b98a8u0knf, child number 3
-------------------------------------
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 | 54 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 18 | 972 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 18 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:N)
SQL > @is_bind_aware
SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------------------------- ---------- ---------------
731b98a8u0knf 0 N Y N 1292784087274697613 2 3724264953
731b98a8u0knf 1 Y Y N 1292784087274697613 1 3625400295
731b98a8u0knf 2 N N Y 1292784087274697613 18 3724264953
731b98a8u0knf 3 N Y Y 1292784087274697613 1 3625400295
SQL > select * from v$sql_cs_statistics where sql_id = '731b98a8u0knf';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED
-------- ---------- ------------- ------------ ------------------- - ---------- --------------
22A4D04C 2443201166 731b98a8u0knf 3 2780714206 Y 1 3
22A4D04C 2443201166 731b98a8u0knf 1 2780714206 Y 1 3
22A4D04C 2443201166 731b98a8u0knf 0 1896453392 Y 1 50000
The new plan is not due to ACS because the corresponding child cursor n°3 is not yet bind aware. So this new plan is due to a hard parse. Let’s continue with the FULL TABLE SCAN bind variable
SQL > exec :n := 'N1' SQL > select count(*), max(col2) from t1 where flag = :n; COUNT(*) MAX(COL2) ---------- -------------------------------------------------- 49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX SQL > select * from table(dbms_xplan.display_cursor); SQL_ID 731b98a8u0knf, child number 3 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 | 54 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 18 | 972 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | I1 | 18 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- SQL > select count(*), max(col2) from t1 where flag = :n; COUNT(*) MAX(COL2) ---------- -------------------------------------------------- 49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX SQL > select * from table(dbms_xplan.display_cursor); SQL_ID 731b98a8u0knf, child number 4 ------------------------------------- select count(*), max(col2) from t1 where flag = :n Plan hash value: 2348726875 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 46 (100)| | | 1 | SORT AGGREGATE | | 1 | 54 | | | |* 2 | INDEX FAST FULL SCAN| I2 | 49872 | 2629K| 46 (5)| 00:00:01 | ------------------------------------------------------------------------------
It seems that ACS is back.
SQL >@is_bind_aware SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE ------------- ------------ - - - -------------------- ----------- ---------------- 731b98a8u0knf 0 N Y N 1292784087274697613 2 3724264953 731b98a8u0knf 1 Y Y N 1292784087274697613 1 3625400295 731b98a8u0knf 2 N N Y 1292784087274697613 18 3724264953 731b98a8u0knf 3 N Y Y 1292784087274697613 2 3625400295 731b98a8u0knf 4 Y Y Y 1292784087274697613 1 2348726875 --> INDEX FAST FULL SCAN SQL > select * from v$sql_cs_statistics where sql_id = '731b98a8u0knf'; ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED -------- ---------- ------------- ------------ ------------------- - ---------- -------------- 22A4D04C 2443201166 731b98a8u0knf 4 1896453392 Y 1 50000 -->INDEX FAST FULL S. 22A4D04C 2443201166 731b98a8u0knf 3 2780714206 Y 1 3 22A4D04C 2443201166 731b98a8u0knf 1 2780714206 Y 1 3 22A4D04C 2443201166 731b98a8u0knf 0 1896453392 Y 1 50000
ACS is in fact really back. After several executions I have the following ACS picture
SQL >@is_bind_aware SQL_ID CHILD_NUMBER I I I SIG EXECUTIONS PLAN_HASH_VALUE ------------- ------------ - - - ----------------------------------- ----- --------- 731b98a8u0knf 0 N Y N 1292784087274697613 2 3724264953 731b98a8u0knf 1 Y Y N 1292784087274697613 1 3625400295 731b98a8u0knf 2 N N Y 1292784087274697613 18 3724264953 731b98a8u0knf 3 N Y N 1292784087274697613 2 3625400295 731b98a8u0knf 4 Y Y Y 1292784087274697613 1 2348726875 --> INDEX i2 FAST FULL SCAN 731b98a8u0knf 5 Y Y Y 1292784087274697613 1 3625400295 --> INDEX i1 RANGE SCAN SQL > select * from v$sql_cs_statistics where sql_id = '731b98a8u0knf'; ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED -------- ---------- ------------- ------------ ------------------- - ---------- -------------- 22A4D04C 2443201166 731b98a8u0knf 5 3066078819 Y 1 3 --> INDEX i1 RANGE SCAN 22A4D04C 2443201166 731b98a8u0knf 4 1896453392 Y 1 50000 --> INDEX i2 FFS 22A4D04C 2443201166 731b98a8u0knf 3 2780714206 Y 1 3 22A4D04C 2443201166 731b98a8u0knf 1 2780714206 Y 1 3 22A4D04C 2443201166 731b98a8u0knf 0 1896453392 Y 1 50000 SQL > select * from v$sql_cs_histogram where sql_id = '731b98a8u0knf'; ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT -------- ---------- ------------- ------------ ---------- ---------- 22A4D04C 2443201166 731b98a8u0knf 5 0 1 ---> bucket id 0 incremented 22A4D04C 2443201166 731b98a8u0knf 5 1 0 22A4D04C 2443201166 731b98a8u0knf 5 2 0 22A4D04C 2443201166 731b98a8u0knf 4 0 0 22A4D04C 2443201166 731b98a8u0knf 4 1 1 ---> bucket id 1 incremented 22A4D04C 2443201166 731b98a8u0knf 4 2 0 22A4D04C 2443201166 731b98a8u0knf 3 0 1 22A4D04C 2443201166 731b98a8u0knf 3 1 1 22A4D04C 2443201166 731b98a8u0knf 3 2 0 22A4D04C 2443201166 731b98a8u0knf 1 0 1 22A4D04C 2443201166 731b98a8u0knf 1 1 0 22A4D04C 2443201166 731b98a8u0knf 1 2 0 22A4D04C 2443201166 731b98a8u0knf 0 0 1 22A4D04C 2443201166 731b98a8u0knf 0 1 1 22A4D04C 2443201166 731b98a8u0knf 0 2 0
The post is becoming long and may be annoying so I will stop here not without mentioning that I did played with the demo setting the optimizer_capture_sql_plan_baselines to TRUE/FALSE and observing the behavior of ACS through its corresponding views and it seems that adding an extra index generates a new sql plan baseline that is not into the SPM and influence a little bit the work of ACS without knowing the exact reason.
Mohamed Houri’s Oracle Notes 
> How could a creation of an extra index disturb the ACS behavior?
Before the index creation you have four child cursors only two of which are valid and shareable.
On index creation, children #2 and #3 are also invalidated.
This is expected, right?
When you execute the sql immediately after the sql creation, child #2 is revalidated and child#3 is cleaned up (but not #0 and #1).
The point being that you need the multiple executions to get the bind awareness. And if, for whatever reason, this information is lost (ages out or is invalidated for example sue to index creation) then you need to repeat the multiple executions to regain it.
Comment by Dom Brooks — April 4, 2013 @ 2:01 pm |
Hi Dominic,
Thanks for your comment. But see how many times I have executed the query with the index range bind variable value: 18 times without making the corresponding child cursor (n°2) at least bind sensitive .
And what looks strange for me is that when I disable the use of sql plan baseline, only 2 executions bring back the ACS at work
Then when I enabled the use of sql plan baseline again, ACS ceases to work correctly.
May be if I evolve the SQL Plan Baseline related to that newly created index, ACS will be working as expected with SPM.
I need to re-test this carefully again to be sure about my experiment
Comment by Houri — April 4, 2013 @ 2:44 pm |
Ok. I see where you’re coming from.
I’ve got a feeling this reply could be the length of post in itself… sorry.
So, a quick recap for my own sake.
Bind sensitivity (is_bind_sensitive) is just a flag that indicates that information exists that means a statement could be bind sensitive
i.e. can be as simple as using binds and peeking @ binds, stats seem to be needed but not necessarily histograms.
There doesn’t even need to be an index present to offer an alternative access plan.
Bind awareness is then a step on from bind sensitivity which can indicate (but not always – let’s not go there) that the optimizer has different cursors for different sets of binds.
But, once bind sensitivity and bind awareness has aged out, you’ve shown that the baseline can have an influence – albeit in a different way from my original post which was just intended to show that a baselined plan contains nothing about bind sensitivity, etc.
So, looking at your illustration:
Immediately after the index creation you have some invalid, non-shareable cursors and some invalid, shareable cursors.
What happens next depends on which bind you run with first and whether you have the two plans – FTS and Index RS – baselined.
The critical point is that the i2 index now means that for bind := ‘N1’, the optimizer favours an index FFS rather than the FTS previously.
Regardless of baselines, the first execution of whichever bind results in:
1. The “clean up” of the invalid, shareable child cursors.
If you DO NOT have the two baselined plans in place, the first execution of whichever bind results in:
2. A new child cursor which IS bind sensitive but not bind aware
If the execution is with bind := ‘N1’ then it should be the “new” index FFS plan, for ‘Y1’ the previous index RS plan.
If you DO have the two baselined plans in place, if you execute the SQL with bind := ‘Y1’ then:
2. A new child cursor which IS bind sensitive but not bind aware
If you DO have the two baselined plans in place, if you execute the SQL with bind := ‘N1’ then:
2. A new child cursor which IS NOT bind sensitive (and therefore not bind aware either)
The influence of the baseline here is that for bind N1, the optimizer cannot pick the best cost generated plan with index FFS of i2 because
it is not baselined so the optimizer has to use an alternative baselined plan.
That seems to be the critical factor that means that bind sensitivity is not picked up until the ‘Y1’ execution happens.
There are some interesting observations to be made when running and rerunning the tests.
I’ve rerun these tests so many times and sometimes the results are not consistent – I’m always hopeful that that’s because I’ve done something in a slightly different order or missed a step but I’m not 100% convinced.
Even the number of times you have to initially run the ‘Y1’ execution to get the initial bind sensitivity can change.
Interesting stuff.
Comment by Dom Brooks — April 4, 2013 @ 5:24 pm |
In order to make things simple, I dropped all the baselines, dropped the index i2, set the optimizer_use_sql_plan_baseline to FALSE and played with my query: ACS works perfectly alternating between FULL TABLE SCAN and INDEX RANGE SCAN. Then I created the i2 index and again ACS works perfectly alternating between INDEX FAST FULL SCAN and INDEX RANGE SCAN. I dropped again the index and ACS works also perfectly as expected.
In a second step, I set the optimizer_use_sql_plan_baseline to TRUE, and let the optimizer to capture the baseline (in absence of the index) and I have evolved the FTS and the IRS baselines to be ACCEPETED AND ENABLED: and ACS works perfectly with SPM alternating between TABLE FULL SCAN and INDEX RANGE SCAN baselines.
Finally I created the index, run a query to produce the INDEX FAST FULL SCAN plan which I have immediately set into the baseline as ENABLED and ACCEPTED: ACS works perfectly with SPM alternating between INDEX FAST FULL SCAN and INDEX RANGE SCAN baselines.
As I have expected when the IFFS baseline is enabled and accepted the underneath index i2 ceases to disturb the good work of ACS.
Best regards
FootNote : When I dropped the 3 baselines I got some trouble using the INDEX FAST FULL SCAN baseline with the following error
SQL> select count(*), max(col2) from t1 where flag = :n; select count(*), max(col2) from t1 where flag = :n * ERROR at line 1: ORA-00600: internal error code, arguments: [qsmoAlterPlans:4], [], [], [], [], [], [], [], [], [], [], [] SQL> select count(*), max(col2) from t1 where flag = :n; select count(*), max(col2) from t1 where flag = :n * ERROR at line 1: ORA-38141: SQL plan baseline SQL_PLAN_13w748wknkcwd495f4ddb does not existI was obliged to set the optimizer_capture_sql_plan_baseline to TRUE in order to capture the SQL_PLAN_13w748wknkcwd495f4ddb plan and to avoid this error. Thought that I didn’t do this for the other two baselines despite they have been dropped. This is another story
Comment by hourim — April 4, 2013 @ 7:26 pm |
[…] scan that are both shareable. Thanks to these two child cursors (until they are flushed out, or something disturbs their good working), the CBO will be alternating between the two executions plans giving […]
Pingback by Literal, bind variable and adaptive cursor sharing: simplify them please!!! | Mohamed Houri’s Oracle Notes — May 18, 2013 @ 11:00 am |