This question is tormenting me since several months. So here it is my answer.
create table t1
as
with generator as (
select --+ materialize
rownum id
from dual
connect by
level <= 10000)
select
rownum id,
trunc(dbms_random.value(1,1000)) n1,
lpad(rownum,10,'0') small_vc,
rpad('x',100) padding
from
generator v1,
generator v2
where
rownum <= 1000000;
create index t1_n1 on t1(id, n1);
create table t2
as
with generator as (
select --+ materialize
rownum id
from dual
connect by
level <= 10000)
select
rownum id,
trunc(dbms_random.value(10001,20001)) x1,
lpad(rownum,10,'0') small_vc,
rpad('x',100) padding
from
generator v1,
generator v2
where
rownum <= 1000000;
create index t2_i1 on t2(x1);
exec dbms_stats.gather_table_stats(user, 't2', method_opt => 'FOR ALL COLUMNS SIZE 1');
exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'FOR ALL COLUMNS SIZE 1');
Now that we are done with the model, let’s execute a query under two different optimizer modes the classical all_rows mode andthe unsuitable first_rows mode
var n1 number exec :n1 := 17335 -- all_rows select * from t1 where id in (select id from t2 where x1 = :n1) order by id ; -- first_rows alter session set optimizer_mode = FIRST_ROWS; select * from t1 where id in (select id from t2 where x1 = :n1) order by id ;
Each of the above identical queries has been honored via a different execution plan due to the optimizer mode change. In order to complete my investigation setup, I will load the above two plans from the cursor cache into a SPM baseline
DECLARE l_plans_loaded PLS_INTEGER; BEGIN l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '7bfbmwdq70vzp'); END; /
I can now verify that I have successfully added two enabled and accepted SPM plans to be used by the above sql query
select signature, plan_name, enabled, accepted,reproduced, optimizer_cost from dba_sql_plan_baselines; SIGNATURE PLAN_NAME ENA ACC REP OPTIMIZER_COST ---------- ------------------------------ --- --- --- -------------- 1.7442E+19 SQL_PLAN_g43cagumm9pj8886a3342 YES YES YES 100315268 --> first_rows suboptimal plan 1.7442E+19 SQL_PLAN_g43cagumm9pj8c2e7a81f YES YES YES 203 --> all_rows optimal plan
And now, we are ready for the experiments
ALL_ROWS
show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- --------------------
optimizer_mode string ALL_ROWS
select * from t1 where id in (select id from t2 where x1 = :n1) order by id ;
94 rows selected.
Elapsed: 00:00:00.06 --> spot the execution time for the optimal plan
Execution Plan
----------------------------------------------------------
Plan hash value: 250297636
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 13100 | 203 (2)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 100 | 13100 | 202 (1)| 00:00:01 |
| 3 | SORT UNIQUE | | 100 | 1000 | 100 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 100 | 1000 | 100 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T2_I1 | 100 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 121 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("X1"=:N1)
6 - access("ID"="ID")Note
-----
- SQL plan baseline "SQL_PLAN_g43cagumm9pj8c2e7a81f" used for this statement
FIRST_ROWS
alter session set optimizer_mode = first_rows;
select * from t1 where id in (select id from t2 where x1 = :n1) order by id ;
94 rows selected.
Elapsed: 00:01:20.04
Execution Plan
----------------------------------------------------------
Plan hash value: 1518369540
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 13100 | 100M (1)| 73:27:12 |
| 1 | NESTED LOOPS SEMI | | 100 | 13100 | 100M (1)| 73:27:12 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1000K| 115M| 5070 (2)| 00:00:14 |
| 3 | INDEX FULL SCAN | T1_N1 | 1000K| | 722 (6)| 00:00:02 |
|* 4 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 10 | 100 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T2_I1 | 100 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID"="ID")
5 - access("X1"=:N1)
Note
-----
- SQL plan baseline "SQL_PLAN_g43cagumm9pj8886a3342" used for this statement
Under all_rows mode the CBO produces a plan that exists into the SPM baseline. This is why it has been used. Under the unsuitable first_rows mode the CBO produces a suboptimal plan which exists into the SPM baseline and this is why it has also been used.
So far so good.
To push the investigations further, I need to disturb this smooth environment. The serious stuff start only when:
- The CBO generates a plan that is not into the SPM baseline
- The SPM baseline contains more than one accepted and enabled plan for the current executed query
In this case the SPM plans enter in competition. The one with the better cost will be used. For that, the CBO needs to
- Reproduce each plan
- Compute their cost
- And choose the best one
But the question is: will the CBO use the current optimizer mode to re-cost the SPM plans generated with different optimizer modes? Or will it use the optimizer mode used during the SPM plan capture?
Let’s see
In order to disturb the above clean situation, I will add an extra index so that the CBO will produce a plan that doesn’t exist in the SPM baseline
SQL> create index ind_t1_extra on t1(id); SQL> show parameter optimizer_mode NAME TYPE VALUE ------------------------------------ ----------- ----------- optimizer_mode string FIRST_ROWS
And now I will execute my query under the unsuitable first_rows mode
SQL> select * from t1 where id in (select id from t2 where x1 = :n1) order by id ;
94 rows selected.
Elapsed: 00:00:00.06
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));
SQL_ID 7bfbmwdq70vzp, child number 1
-------------------------------------
select * from t1 where id in (select id from t2 where x1 = :n1) order
by id
Plan hash value: 250297636
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 203 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 100 | 13100 | 202 (1)| 00:00:01 |
| 3 | SORT UNIQUE | | 100 | 1000 | 100 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 100 | 1000 | 100 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T2_I1 | 100 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 121 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
4 - SEL$5DA710D3 / T2@SEL$2
5 - SEL$5DA710D3 / T2@SEL$2
6 - SEL$5DA710D3 / T1@SEL$1
7 - SEL$5DA710D3 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS ------------------------------> ALL_ROWS
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$5DA710D3" "T2"@"SEL$2" ("T2"."X1"))
INDEX(@"SEL$5DA710D3" "T1"@"SEL$1" ("T1"."ID" "T1"."N1"))
LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1")
USE_NL(@"SEL$5DA710D3" "T1"@"SEL$1")
NLJ_BATCHING(@"SEL$5DA710D3" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :N1 (NUMBER): 17335
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("X1"=:N1)
6 - access("ID"="ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22], "T1"."N1"[NUMBER,22], "T1"."SMALL_VC"[VARCHAR2,40],
"T1"."PADDING"[VARCHAR2,400]
2 - "T1".ROWID[ROWID,10], "ID"[NUMBER,22], "T1"."N1"[NUMBER,22]
3 - (#keys=1) "ID"[NUMBER,22]
4 - "ID"[NUMBER,22]
5 - "T2".ROWID[ROWID,10]
6 - "T1".ROWID[ROWID,10], "ID"[NUMBER,22], "T1"."N1"[NUMBER,22]
7 - "T1"."SMALL_VC"[VARCHAR2,40], "T1"."PADDING"[VARCHAR2,400]
Note
-----
- SQL plan baseline SQL_PLAN_g43cagumm9pj8c2e7a81f used for this statement ----> SPM of ALL_ROWS mode
Spot how, despite I’ve executed my query under the first_rows mode, I ended up by using the SPM plan that corresponds to the all_rows mode. This is confirmed via the outline, the Note and the execution time shown above.
In this particular case, thanks to the new extra index, the CBO comes up with a new plan that is not into the SPM baseline as shown via the following select:
SQL> select signature, plan_name, enabled, accepted,reproduced, optimizer_cost from dba_sql_plan_baselines; SIGNATURE PLAN_NAME ENA ACC REP OPTIMIZER_COST ---------- ------------------------------ --- --- --- -------------- 1.7442E+19 SQL_PLAN_g43cagumm9pj8886a3342 YES YES YES 100315268 -- first_rows mode 1.7442E+19 SQL_PLAN_g43cagumm9pj8c1c6dd02 YES NO YES 100315135 -- first_rows mode with new index 1.7442E+19 SQL_PLAN_g43cagumm9pj8c2e7a81f YES YES YES 203 -- all_rows mode
The new CBO plan has been added to the SPM baseline with the ACCPETED attributed put to NO for eventual future evolution. If you want to see the new “Non Accepted” execution plan generated by the CBO then you can get it using:
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name => 'SQL_PLAN_g43cagumm9pj8c1c6dd02'));
--------------------------------------------------------------------------------
SQL handle: SQL_f20d8a7ea734d628
SQL text: select * from t1 where id in (select id from t2 where x1 = :n1) order by id
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_g43cagumm9pj8c1c6dd02 Plan id: 3251035394
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------
Plan hash value: 2352314251
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 13100 | 102M (1)| 01:06:26 |
| 1 | NESTED LOOPS SEMI | | 100 | 13100 | 102M (1)| 01:06:26 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1000K| 115M| 19786 (1)| 00:00:01 |
| 3 | INDEX FULL SCAN | IND_T1_EXTRA | 1000K| | 2234 (1)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 10 | 102 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T2_I1 | 100 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID"="ID")
5 - access("X1"=:N1)
The second interesting test to do is to execute the same query this time under all_rows mode:
SQL> alter session set optimizer_mode=all_rows;
SQL> select * from t1 where id in (select id from t2 where x1 = :n1) order by id ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID 7bfbmwdq70vzp, child number 0
-------------------------------------
select * from t1 where id in (select id from t2 where x1 = :n1) order by id
Plan hash value: 250297636
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 253 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 100 | 13100 | 253 (0)| 00:00:01 |
| 3 | SORT UNIQUE | | 100 | 1000 | 103 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 100 | 1000 | 103 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T2_I1 | 100 | | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T1_N1 | 1 | | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 121 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("X1"=:N1)
6 - access("ID"="ID")
Note
-----
- SQL plan baseline SQL_PLAN_g43cagumm9pj8c2e7a81f used for this statement ----> SPM of ALL_ROWS mode
Where again it is the SPM plan of the all_rows mode that has been used.
Under the all_rows mode the CBO came up with a new plan which has also been consrtained by the SPM plan having the best cost reproduced using the optimizer mode stored against this SPM baseline and not using the current environnment optimizer mode.
The new generated CBO plan under all_rows mode is shown here below:
SQL> select signature, plan_name, enabled, accepted,reproduced, optimizer_cost from dba_sql_plan_baselines; SIGNATURE PLAN_NAME ENA ACC REP OPTIMIZER_COST ---------- ------------------------------ --- --- --- -------------- 1.7442E+19 SQL_PLAN_g43cagumm9pj8886a3342 YES YES YES 100315268 -- first_rows mode 1.7442E+19 SQL_PLAN_g43cagumm9pj8c1c6dd02 YES NO YES 100315135 -- first_rows mode with new index 1.7442E+19 SQL_PLAN_g43cagumm9pj8c2e7a81f YES YES YES 203 -- all_rows mode 1,7442E+19 SQL_PLAN_g43cagumm9pj8ff8eddc2 YES NO YES 253 -- all_rows mode with new index
With a corresponding execution plan shown also here below:
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name => 'SQL_PLAN_g43cagumm9pj8ff8eddc2'));
--------------------------------------------------------------------------------
SQL handle: SQL_f20d8a7ea734d628
SQL text: select * from t1 where id in (select id from t2 where x1 = :n1) order by id
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_g43cagumm9pj8ff8eddc2 Plan id: 4287552962
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------
Plan hash value: 2225889631
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 13100 | 253 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 100 | 13100 | 253 (0)| 00:00:01 |
| 3 | SORT UNIQUE | | 100 | 1000 | 103 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 100 | 1000 | 103 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T2_I1 | 100 | | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IND_T1_EXTRA | 1 | | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 121 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("X1"=:N1)
6 - access("ID"="ID")
It is time now to summarize
When the CBO comes up with a plan that exists into the SPM plan baseline then this plan is used without regards to the optimizer environment be it the current environment or the one used during the SPM plan capture
When the CBO comes up with a plan that doesn’t exists into a SPM baseline while this baseline contains more than one plan that can likely honor the CBO query, those SPM plans enter in a cost competition. This plan reproduction and cost reevaluation will be done using the optimizer mode stored against each baseline plan. It will not use the query current optimizer mode to reproduce the SPM plan. This is why we have seen and prove by example that when you execute the query under first_rows mode it is the SPM plan of the all_rows mode that has been used.
In the following picture I tried to simplify the view by showing each plan name with its corresponding plan_id.
SQL> select plan_name, enabled, accepted,reproduced, optimizer_cost from dba_sql_plan_baselines; PLAN_NAME ENA ACC REP OPTIMIZER_COST PLAN_ID --------------------------------------------- --- --- --- -------------- ---------- SQL_PLAN_g43cagumm9pj8886a3342 YES YES YES 102039969 2288661314 -- first rows mode without new index SQL_PLAN_g43cagumm9pj8c1c6dd02 YES NO YES 102039425 3251035394 -- first rows mode with new index SQL_PLAN_g43cagumm9pj8c2e7a81f YES YES YES 253 3269961759 -- all rows mode without index SQL_PLAN_g43cagumm9pj8ff8eddc2 YES NO YES 253 4287552962 -- all rows mode with new index
As such I can easily explain the 10053 trace file I have obtained when trying to execute my query under the first_rows mode which of course has been constrained with the SPM plan of the all_rows mode (plan_id 3269961759)
The below CBO trace file corresponds to the above query executed under the unsuitable first_rows mode which comes up with a plan having the plan_id = 3251035394 and that has been constrained with the all_rows SPM plan having plan_id = 3269961759
SPM: statement found in SMB --> signaling the presence of accepted SPM plans SPM: setup to add new plan to existing plan baseline, sig = 17441749208451569192, planId = 3251035394 SPM: sql stmt=select * from t1 where id in (select id from t2 where x1 = :n1) order by id
The above lines indicates that the CBO comes up with a new plan (planId = 3251035394) that is going to be added into the SPM plan baseline
While the next lines indicates that the CBO is aware of the presence of two SPM plans that are enabled and accepted the first one for the first_rows mode and the second planid for the all_rows mode
SPM: planId's of plan baseline are: 2288661314 3269961759
In the next step the CBO will try to reproduce and to re cost the two plans starting by the first one
SPM: using qksan to reproduce, cost and select accepted plan, sig = 17441749208451569192 SPM: plan reproducibility round 1 (plan outline + session OFE) SPM: using qksan to reproduce accepted plan, planId = 2288661314
Taking into account the following information
*************************************** PARAMETERS USED BY THE OPTIMIZER ******************************** ************************************ PARAMETERS WITH ALTERED VALUES ****************************** Compilation Environment Dump optimizer_mode_hinted = true optimizer_features_hinted = 12.1.0.1 optimizer_mode = first_rows is_recur_flags = 8
Which is clearly indicating that it is using the first_rows mode to reproduce the SPM first_rows mode. What is not clear is whether this is due to the current optimizer mode or to the optimizer mode stored against the SPM plan. But things will be clear later when the CBO will try to reproduce the all_rows plan
SPM: planId in plan baseline = 2288661314, planId of reproduced plan = 2288661314 SPM: best cost so far = 102039969.31, current accepted plan cost = 102039969.31
This is the CBO saying that it has succeeded to reproduce the first_rows mode plan.
Now it will start reproducing the all_rows mode (planid = 3269961759)
SPM: plan reproducibility round 1 (plan outline + session OFE) SPM: using qksan to reproduce accepted plan, planId = 3269961759
But spot below that the compilation environment is not showing any more that the optimizer_mode to be used is the first_rows mode. Don’t forget that I have executed the query under the first_rows mode. The below CBO trace file is clearly showing that the CBO is using the all_rows mode to reproduce the all_rows SPM plan confirming that the CBO will use the optimizer mode stored against the SPM baseline when reproducing their plans.
*************************************** PARAMETERS USED BY THE OPTIMIZER ******************************** ************************************* PARAMETERS WITH ALTERED VALUES ****************************** Compilation Environment Dump optimizer_mode_hinted = true optimizer_features_hinted = 12.1.0.1 is_recur_flags = 8 ************************************* PARAMETERS WITH DEFAULT VALUES ****************************** optimizer_features_enable = 12.1.0.1 optimizer_mode = all_rows
And finally the CBO saying that it has also reproduced the all_rows mode
SPM: planId in plan baseline = 3269961759, planId of reproduced plan = 3269961759 SPM: best cost so far = 253.11, current accepted plan cost = 253.11
And that, as far as this plan is having the best cost, it will be used
SPM: re-parsing to generate selected accepted plan, planId = 3269961759
Bottom line: when the CBO is trying to reproduce a plan stored into a SPM baseline it will use the optimizer mode stored against this SPM plan and not the current environment optimizer mode. Be aware that this is not a conclusion to be extended without tests for the other optimizer parameters particularly for the hidden ones
Mohamed Houri’s Oracle Notes 
[…] wrote a blog article which aims mainly to show what optimizer parameter the CBO will use to reproduce aSPM baseline […]
Pingback by NLS_SORT and SPM reproducibility | Mohamed Houri’s Oracle Notes — March 6, 2014 @ 9:40 am |
[…] for a change in an optimizer parameter (optimizer mode) […]
Pingback by SPM reproducibility: changing the _optimizer_skip_scan_enabled value | Mohamed Houri’s Oracle Notes — March 17, 2014 @ 9:36 am |