Disclaimer: after writing this post (but before publishing it) I have seen that other people already blogged about it, so I am ashamed of publishing it anyway… but that’s blogger’s life 🙂
Wednesday I have got a nice question after my presentation about Adaptive Features at the DOAG16 conference:
What happens when you load an adaptive plan in a SQL Plan Baseline?
Does it load only the final plan or does it load the whole plan including the inactive operations? Will the plan be evaluated again using the inflection point?
I have decided to do some tests in order to give the best possible answer. I did not spend the time to rethink about producing an adaptive plan. Tim Hall already did an excellent test case to create and alter an adaptive plan in his blog, so I have reused massively most of its code. Thanks Tim :-).
I will not post all the code (please find it in Tim’s post), I will go straight to the plans.
First: I have an adaptive plan that resolves to NESTED LOOPS:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ 2 a.data AS tab1_data, 4 b.data AS tab2_data 5 FROM tab1 a 6 JOIN tab2 b ON b.tab1_id = a.id 7 WHERE a.code = 'ONE'; ... 30 rows selected. SQL> SET LINESIZE 200 PAGESIZE 100 SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------- SQL_ID 4r3harjun4dvz, child number 0 ------------------------------------- SELECT a.data AS tab1_data, b.data AS tab2_data FROM tab1 a JOIN tab2 b ON b.tab1_id = a.id WHERE a.code = 'ONE' Plan hash value: 2672205743 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |- * 1 | HASH JOIN | | 25 | 425 | 3 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 | |- 4 | STATISTICS COLLECTOR | | | | | | | 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 11 | 2 (0)| 00:00:01 | | * 6 | INDEX RANGE SCAN | TAB1_CODE | 1 | | 1 (0)| 00:00:01 | | * 7 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 25 | | 0 (0)| | | 8 | TABLE ACCESS BY INDEX ROWID | TAB2 | 25 | 150 | 1 (0)| 00:00:01 | |- 9 | TABLE ACCESS FULL | TAB2 | 25 | 150 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("B"."TAB1_ID"="A"."ID") 6 - access("A"."CODE"='ONE') 7 - access("B"."TAB1_ID"="A"."ID") Note ----- - this is an adaptive plan (rows marked '-' are inactive) 33 rows selected. |
Second: I load the plan (lazy way: using baseline capture at session level)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE; Session altered. SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ 2 a.data AS tab1_data, 3 b.data AS tab2_data 4 FROM tab1 a 5 JOIN tab2 b ON b.tab1_id = a.id 6 WHERE a.code = 'ONE'; TAB1_DATA TAB2_DATA ---------- ---------- ... 30 rows selected. SQL> r 1 SELECT /*+ GATHER_PLAN_STATISTICS */ 2 a.data AS tab1_data, 3 b.data AS tab2_data 4 FROM tab1 a 5 JOIN tab2 b ON b.tab1_id = a.id 6* WHERE a.code = 'ONE' TAB1_DATA TAB2_DATA ---------- ---------- ... 30 rows selected. SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE; Session altered. SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME SQL_TEXT ENA ACC FIX -------------------- ---------------------------------- ------------------------------------------ --- --- --- SQL_6c4c6680810dd01a SQL_PLAN_6sm36h20hvn0u55a25f73 SELECT /*+ GATHER_PLAN_STATISTICS */ YES YES NO a.data AS tab1_data, b.data A |
Third: re-run the statement and check the plan
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ 2 a.data AS tab1_data, 3 b.data AS tab2_data 4 FROM tab1 a 5 JOIN tab2 b ON b.tab1_id = a.id 6 WHERE a.code = 'ONE'; TAB1_DATA TAB2_DATA ---------- ---------- ... 30 rows selected. SQL> select * from table(dbms_xplan.display_cursor(format=>'+adaptive')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------- SQL_ID 1km5kczcgr0fr, child number 3 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ a.data AS tab1_data, b.data AS tab2_data FROM tab1 a JOIN tab2 b ON b.tab1_id = a.id WHERE a.code = 'ONE' Plan hash value: 2672205743 ------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 11 | 2 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | TAB1_CODE | 1 | | 1 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 25 | | 0 (0)| | | 6 | TABLE ACCESS BY INDEX ROWID | TAB2 | 25 | 150 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("A"."CODE"='ONE') 5 - access("B"."TAB1_ID"="A"."ID") Note ----- - SQL plan baseline SQL_PLAN_6sm36h20hvn0u55a25f73 used for this statement 30 rows selected. |
It does not look adaptive, but I can also check from the function DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
SQL> select * from table (DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_6c4c6680810dd01a', format=>'+adaptive')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SQL handle: SQL_6c4c6680810dd01a SQL text: SELECT /*+ GATHER_PLAN_STATISTICS */ a.data AS tab1_data, b.data AS tab2_data FROM tab1 a JOIN tab2 b ON b.tab1_id = a.id WHERE a.code = 'ONE' -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_6sm36h20hvn0u55a25f73 Plan id: 1436704627 Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE Plan rows: From dictionary -------------------------------------------------------------------------------- Plan hash value: 2672205743 --------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 25 | 425 | 3 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 11 | 2 (0)| 00:00:01 | | * 4 | INDEX RANGE SCAN | TAB1_CODE | 1 | | 1 (0)| 00:00:01 | | * 5 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 25 | | 0 (0)| 00:00:01 | | 6 | TABLE ACCESS BY INDEX ROWID | TAB2 | 25 | 150 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("A"."CODE"='ONE') 5 - access("B"."TAB1_ID"="A"."ID") Note ----- - this is an adaptive plan (rows marked '-' are inactive) 37 rows selected. |
Again, despite in the Note section it says it is adaptive, it does not look like an adaptive plan.
Can I trust this information? Of course I did not and tried to check the plan with and without baseline after changing the rows to force a plan switch to HJ (again taking Tim’s example):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 |
SQL> INSERT /*+ APPEND */ INTO tab1 2 SELECT tab1_seq.nextval, 2 'ONE', 4 level 5 FROM dual 6 CONNECT BY level <= 10000; 10000 rows created. SQL> COMMIT; Commit complete. SQL> alter session set optimizer_use_sql_plan_baselines=false; Session altered. SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ 2 a.data AS tab1_data, 3 b.data AS tab2_data 4 FROM tab1 a 5 JOIN tab2 b ON b.tab1_id = a.id 6 WHERE a.code = 'ONE'; TAB1_DATA TAB2_DATA ---------- ---------- ... 30 rows selected. SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------ SQL_ID 1km5kczcgr0fr, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ a.data AS tab1_data, b.data AS tab2_data FROM tab1 a JOIN tab2 b ON b.tab1_id = a.id WHERE a.code = 'ONE' Plan hash value: 1599395313 ------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 30 |00:00:00.01 | 142 | | | | | * 1 | HASH JOIN | | 1 | 25 | 30 |00:00:00.01 | 142 | 2261K| 2261K| 2180K (0)| |- 2 | NESTED LOOPS | | 1 | 25 | 20001 |00:00:00.01 | 124 | | | | |- 3 | NESTED LOOPS | | 1 | 25 | 20001 |00:00:00.01 | 124 | | | | |- 4 | STATISTICS COLLECTOR | | 1 | | 20001 |00:00:00.01 | 124 | | | | | 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 1 | 20001 |00:00:00.01 | 124 | | | | | * 6 | INDEX RANGE SCAN | TAB1_CODE | 1 | 1 | 20001 |00:00:00.01 | 74 | | | | |- * 7 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 0 | 25 | 0 |00:00:00.01 | 0 | | | | |- 8 | TABLE ACCESS BY INDEX ROWID | TAB2 | 0 | 25 | 0 |00:00:00.01 | 0 | | | | | 9 | TABLE ACCESS FULL | TAB2 | 1 | 25 | 100 |00:00:00.01 | 18 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("B"."TAB1_ID"="A"."ID") 6 - access("A"."CODE"='ONE') 7 - access("B"."TAB1_ID"="A"."ID") Note ----- - this is an adaptive plan (rows marked '-' are inactive) 34 rows selected. SQL> alter session set optimizer_use_sql_plan_baselines=true; Session altered. SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ 2 a.data AS tab1_data, 3 b.data AS tab2_data 4 FROM tab1 a 5 JOIN tab2 b ON b.tab1_id = a.id 6 WHERE a.code = 'ONE'; TAB1_DATA TAB2_DATA ---------- ---------- ... 30 rows selected. SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 1km5kczcgr0fr, child number 2 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ a.data AS tab1_data, b.data AS tab2_data FROM tab1 a JOIN tab2 b ON b.tab1_id = a.id WHERE a.code = 'ONE' Plan hash value: 2672205743 ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 30 |00:00:00.01 | 30889 | | 1 | NESTED LOOPS | | 1 | 25 | 30 |00:00:00.01 | 30889 | | 2 | NESTED LOOPS | | 1 | 25 | 30 |00:00:00.01 | 30886 | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 1 | 20001 |00:00:00.01 | 125 | |* 4 | INDEX RANGE SCAN | TAB1_CODE | 1 | 1 | 20001 |00:00:00.01 | 75 | |* 5 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 20001 | 25 | 30 |00:00:00.02 | 30761 | | 6 | TABLE ACCESS BY INDEX ROWID | TAB2 | 30 | 25 | 30 |00:00:00.01 | 3 | ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("A"."CODE"='ONE') 5 - access("B"."TAB1_ID"="A"."ID") Note ----- - SQL plan baseline SQL_PLAN_6sm36h20hvn0u55a25f73 used for this statement 30 rows selected. |
After changing the rows:
- when I do not use the baseline, the plan resolves to HASH JOIN
- when I use it, the baseline forces to NESTED LOOPS.
So the plan in the baseline is not adaptive and it forces to what has been loaded. Is it the final plan or the original one? I have to capture it again to see if a new baseline appears:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE; Session altered. SELECT /*+ GATHER_PLAN_STATISTICS */ 2 a.data AS tab1_data, 3 b.data AS tab2_data 4 FROM tab1 a 5 JOIN tab2 b ON b.tab1_id = a.id 6 WHERE a.code = 'ONE'; TAB1_DATA TAB2_DATA ---------- ---------- ... 30 rows selected. SQL> r 1 SELECT /*+ GATHER_PLAN_STATISTICS */ 2 a.data AS tab1_data, 3 b.data AS tab2_data 4 FROM tab1 a 5 JOIN tab2 b ON b.tab1_id = a.id 6* WHERE a.code = 'ONE' TAB1_DATA TAB2_DATA ---------- ---------- ... 30 rows selected. SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE; Session altered. SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACCEPTED FIXED -------------------- ------------------------------ -------------------------------------------------------------------------------- --------- --------- --------- SQL_6c4c6680810dd01a SQL_PLAN_6sm36h20hvn0u55a25f73 SELECT /*+ GATHER_PLAN_STATISTICS */ YES YES NO a.data AS tab1_data, b.data A |
A new baseline does not appear, so it looks that the original plan is considered by the capture process and not the resolved one! To be 100% sure, let’s try to drop the existing one and redo the test:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 |
SQL> connect / as sysdba Connected SQL> DECLARE 2 v_dropped_plans number; 3 BEGIN 4 v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE ( 5 sql_handle => 'SQL_6c4c6680810dd01a' 6 ); 7 END; 8 / PL/SQL procedure successfully completed. SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines; no rows selected SQL> conn ludo/ludo Connected. SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE; Session altered. SELECT /*+ GATHER_PLAN_STATISTICS */ 2 a.data AS tab1_data, 3 b.data AS tab2_data 4 FROM tab1 a 5 JOIN tab2 b ON b.tab1_id = a.id 6 WHERE a.code = 'ONE'; TAB1_DATA TAB2_DATA ---------- ---------- ... 30 rows selected. SQL> r 1 SELECT /*+ GATHER_PLAN_STATISTICS */ 2 a.data AS tab1_data, 3 b.data AS tab2_data 4 FROM tab1 a 5 JOIN tab2 b ON b.tab1_id = a.id 6* WHERE a.code = 'ONE' TAB1_DATA TAB2_DATA ---------- ---------- ... 30 rows selected. SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE; Session altered. SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACCEPTED FIXED -------------------- ------------------------------ -------------------------------------------------------------------------------- --------- --------- --------- SQL_6c4c6680810dd01a SQL_PLAN_6sm36h20hvn0u55a25f73 SELECT /*+ GATHER_PLAN_STATISTICS */ YES YES NO a.data AS tab1_data, b.data A SQL> select * from table (DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_6c4c6680810dd01a', format=>'+adaptive')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SQL handle: SQL_6c4c6680810dd01a SQL text: SELECT /*+ GATHER_PLAN_STATISTICS */ a.data AS tab1_data, b.data AS tab2_data FROM tab1 a JOIN tab2 b ON b.tab1_id = a.id WHERE a.code = 'ONE' -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_6sm36h20hvn0u55a25f73 Plan id: 1436704627 Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE Plan rows: From dictionary -------------------------------------------------------------------------------- Plan hash value: 2672205743 --------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 25 | 425 | 3 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 11 | 2 (0)| 00:00:01 | | * 4 | INDEX RANGE SCAN | TAB1_CODE | 1 | | 1 (0)| 00:00:01 | | * 5 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 25 | | 0 (0)| 00:00:01 | | 6 | TABLE ACCESS BY INDEX ROWID | TAB2 | 25 | 150 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("A"."CODE"='ONE') 5 - access("B"."TAB1_ID"="A"."ID") Note ----- - this is an adaptive plan (rows marked '-' are inactive) 37 rows selected. |
So, despite the fact that I have an adaptive plan that switches from NL to HJ, only the NESTED LOOPS operations are captured in the baseline, I can infer the only the original plan is loaded as SQL Plan Baseline.
References:
- https://oracle-base.com/articles/12c/adaptive-plans-12cr1
- http://www.oracle.com/technetwork/articles/database/create-sql-plan-baseline-2237506.html
The following two tabs change content below.
Latest posts by Ludovico (see all)
- New views in Oracle Data Guard 23c - January 3, 2024
- New in Data Guard 21c and 23c: Automatic preparation of the primary - December 22, 2023
- Does FLASHBACK QUERY work across incarnations or after a Data Guard failover? - December 13, 2023
So to conclude the Adaptive plan is not added in the SQL Plan Baseline?
Hi Sankalp, No, the plan loaded in the SPM will not be adaptive.
Salut Ludo
J’espère que vous allez très bien.
Thanks for this post. Indeed the automatic capture of sql plan baseline captures only the default plan.
This can be confirmed by getting the stored outline of captured SPM baseline where you can see that the NL join is used.
select
substr(extractvalue(value(d), ‘/hint’), 1, 100) as outline_hints
from
xmltable(‘/*/outline_data/hint’
passing (
select
xmltype(other_xml) as xmlval
from
sys.sqlobj$plan
where
signature in (select
signature
from dba_sql_plan_baselines
where plan_name = ‘SQL_PLAN_f7xzs75fcmngv55a25f73’
)
and other_xml is not null
)
) d;
OUTLINE_HINTS
———————————————————-
NLJ_BATCHING(@”SEL$58A6D7F6″ “B”@”SEL$1″)
USE_NL(@”SEL$58A6D7F6” “B”@”SEL$1″)
LEADING(@”SEL$58A6D7F6” “A”@”SEL$1” “B”@”SEL$1″)
INDEX(@”SEL$58A6D7F6” “B”@”SEL$1” (“TAB2″.”TAB1_ID”))
BATCH_TABLE_ACCESS_BY_ROWID(@”SEL$58A6D7F6″ “A”@”SEL$1″)
INDEX_RS_ASC(@”SEL$58A6D7F6” “A”@”SEL$1” (“TAB1″.”CODE”))
OUTLINE(@”SEL$1″)
OUTLINE(@”SEL$2″)
MERGE(@”SEL$1″)
OUTLINE_LEAF(@”SEL$58A6D7F6″)
ALL_ROWS
DB_VERSION(‘12.1.0.1’)
OPTIMIZER_FEATURES_ENABLE(‘12.1.0.1’)
IGNORE_OPTIM_EMBEDDED_HINTS
There is also a column named adaptive in dba_sql_plan_baselines which indicates that the SPM plan is not adaptive
The 10053 trace file shows also interesting information:
SPM: cost-based plan found in the plan baseline, planId = 1436704627
SPM: cost-based plan successfully matched, planId = 1436704627
SPM: match found but the plan is dynamic
Oracle consider the first plan (NL) as being one of the CBO generated plan even though it is not the final one used during query execution (HJ). In this case Oracle considers that the CBO default (NL) plan matches the SPM(NL) plan and, thereby,
the CBO (or the SPM plan no matter here because they are identical in the eyes of the CBO) is used.
When the CBO come up with an execution plan that is not in the SPM baseline it will insert this CBO plan in the SPM baseline for future evolution. You can easily see that, in the case where Oracle resolved to a dynamic HASH Join execution plan and end up by using the SPM (NL) plan, the CBO dynamic HASH join plan has not been inserted into the SPM baseline. This is another proof that Oracle considers both plans (default and the alternative one) to have been produced by the CBO and uses one of them provided they matches the planId (phv2) of the SPM plan.
Anyway thanks for this information : only the default plan is captured automatically into the SPM baseline when the plan is adaptive
Best regards
Mohamed Houri
Merci pour le commentaire, Mohamed 🙂
Pingback: How Adaptive Plans work with SQL Plan Baselines? - Ludovico Caldara - Blogs - triBLOG