This article is about demonstrating Auto-Capture baseline behavior, which I was surprised that many people are not aware.
Auto-Capture of baseline behaves in following way
Case 1) optimizer_capture_sql_plan_baselines parameter is TRUE
If you have optimizer_capture_sql_plan_baselines parameter set to TRUE, baselines for the SQLs will get captured automatically if SQL query runs 2 or more times. I believe all of us know about this feature
Case 2) optimizer_capture_sql_plan_baselines parameter is FALSE
If optimizer_capture_sql_plan_baselines parameter is set to FALSE, many people think that no new baseline will be captured in the database. This is WRONG. Even when this parameter is set to FALSE, baselines will be captured for following case
*** If a query has one or more baselines with ENABLED=YES ***
Lets take an example
SQL>create table T as select * from dba_objects;
Table created.
SQL>explain plan for
2 select count(1) from T where data_object_id between 1000 and 2000;
Explained.
SQL>@display
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2966233522
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | TABLE ACCESS FULL| T |
-----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DATA_OBJECT_ID"<=2000 AND "DATA_OBJECT_ID">=1000)
Note
-----
- rule based optimizer used (consider using cbo)
18 rows selected.
SQL>select count(1) from T where data_object_id between 1000 and 2000;
COUNT(1)
----------
0
SQL>/
COUNT(1)
----------
0
SQL>select sql_id from v$sql where sql_text like 'select count(1) from T where data_object_id between 1000 and%';
SQL_ID
-------------
7fbxxz894w0mg
SQL>@baseline
Enter sql ID:- 7fbxxz894w0mg
SQL_TEXT
----------------------------------------------------------------------------------------------------
select count(1) from T where data_object_id between 1000 and 2000
SQL_HANDLE PLAN_NAME CREATOR ORIGIN LAST_MODIFIED ENA ACC FIX
------------------------------ ------------------------------ --------------- -------------- ------------------------------ --- --- ---
SYS_SQL_46f1e9bfbc331557 SYS_SQL_PLAN_bc3315573fdbb376 ORACLE_DBA AUTO-CAPTURE 26-JUN-12 11.30.08.000000 AM YES YES NO
SQL_HANDLE PLAN_NAME PLAN_HASH_VALUE ENABLED ACCEPTED FIXED
------------------------- ---------------------------- -------------- ------- ------- -------
SYS_SQL_46f1e9bfbc331557 SYS_SQL_PLAN_bc3315573fdbb376 2966233522 YES YES NO
PL/SQL procedure successfully completed.
SQL>show parameters capture
optimizer_capture_sql_plan_baselines boolean TRUE
SQL>alter system set optimizer_capture_sql_plan_baselines=false;
System altered.
SQL>create index T_IDX on T(data_object_id);
Index created.
SQL>select count(1) from T where data_object_id between 1000 and 2000;
COUNT(1)
----------
0
SQL>/
COUNT(1)
----------
0
SQL>@baseline
Enter sql ID:- 7fbxxz894w0mg
SQL_TEXT
----------------------------------------------------------------------------------------------------
select count(1) from T where data_object_id between 1000 and 2000
SQL_HANDLE PLAN_NAME CREATOR ORIGIN LAST_MODIFIED ENA ACC FIX
------------------------------ ------------------------------ --------------- -------------- ------------------------------ --- --- ---
SYS_SQL_46f1e9bfbc331557 SYS_SQL_PLAN_bc3315573fdbb376 ORACLE_DBA AUTO-CAPTURE 26-JUN-12 11.30.08.000000 AM YES YES NO
SYS_SQL_46f1e9bfbc331557 SYS_SQL_PLAN_bc331557ded8ae2f ORACLE_DBA AUTO-CAPTURE 26-JUN-12 11.31.20.000000 AM YES NO NO
SQL_HANDLE PLAN_NAME PLAN_HASH_VALUE ENABLED ACCEPTED FIXED
------------------------- ---------------------------- -------------- ------- ------- -------
SYS_SQL_46f1e9bfbc331557 SYS_SQL_PLAN_bc3315573fdbb376 2966233522 YES YES NO
SYS_SQL_46f1e9bfbc331557 SYS_SQL_PLAN_bc331557ded8ae2f 293504097 YES NO NO
PL/SQL procedure successfully completed.
SQL>
This proves that even when optimizer_capture_sql_plan_baselines parameter is set to false we can have baselines captured for our old SQL.
If we think about this feature, this is perfect.
Imagine you are rolling out a new module and new queries are going to run against new tables.
At first, initially you will not be having enough data in new tables and your queries will go with FTS (Full Table Scan) and will not use index, even if you create them. This is because based on value of optimizer_index_cost_adj parameter optimizer might make a decision to get data from table rather than going to index and than table.
If you have optimizer_capture_sql_plan_baselines set to TRUE, this is going to create baselines on those queries (which will be automatically accepted as first baselines are automatically accepted).
At later point when data grows and you really want the indexes to get used, your query will still be doing FTS (because of the auto-capture baseline in ACCEPTED state). So even if new baselines are getting captured, they are not getting used.
So you have to manually (or automatically) evolve those baselines to correct the plans.
Instead, my approach would be to enable optimizer_capture_sql_plan_baselines parameter when your database is really baked and you have enough data available. This way it will create baselines which will be first time right.
If your database is still not baked enough and you are looking for plan stability, I would suggest getting baselines from other similar prod database which is baked for long time.
Once you have baselines in place for almost all queries turn off optimizer_capture_sql_plan_baselines parameter. This will prevent baseline capture for new queries, at the same time this doesn’t stop new baselines from getting captured for old SQLs.
Hope this helps !!
Persistent Storage Solutions 