{"id":1446,"date":"2016-11-18T10:56:18","date_gmt":"2016-11-18T08:56:18","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=1446"},"modified":"2020-08-18T16:19:54","modified_gmt":"2020-08-18T14:19:54","slug":"adaptive-plans-baselines","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/adaptive-plans-baselines\/","title":{"rendered":"How Adaptive Plans work with SQL Plan Baselines?"},"content":{"rendered":"<p><strong>Disclaimer<\/strong>: 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&#8230; but that&#8217;s blogger&#8217;s life \ud83d\ude42<\/p>\n<p>Wednesday I have got a nice question after my presentation about Adaptive Features at the DOAG16 conference:<\/p>\n<p><strong><em>What happens when you load an adaptive plan in a SQL Plan Baseline?<\/em><\/strong><br \/>\n<strong><em>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?<\/em><\/strong><\/p>\n<p>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. <strong>Tim Hall<\/strong> already did <a href=\"https:\/\/oracle-base.com\/articles\/12c\/adaptive-plans-12cr1\">an excellent test case to create and alter an adaptive plan in his blog<\/a>, so I have reused massively most of its code. Thanks Tim :-).<\/p>\n<p>I will not post all the code (please find it in Tim&#8217;s post), I will go straight to the plans.<\/p>\n<p><strong>First<\/strong>: I have an adaptive plan that resolves to NESTED LOOPS:<\/p>\n<pre class=\"lang:plsql decode:true\">SQL&gt; SELECT \/*+ GATHER_PLAN_STATISTICS *\/ \r\n  2    a.data AS tab1_data,\r\n  4    b.data AS tab2_data\r\n  5  FROM   tab1 a\r\n  6         JOIN tab2 b ON b.tab1_id = a.id\r\n  7  WHERE  a.code = 'ONE';\r\n\r\n...\r\n \r\n30 rows selected.\r\n\r\nSQL&gt; SET LINESIZE 200 PAGESIZE 100\r\nSQL&gt; SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format =&gt; 'adaptive'));\r\n\r\nPLAN_TABLE_OUTPUT\r\n----------------------------------------------------------------------------------------------------------\r\nSQL_ID  4r3harjun4dvz, child number 0\r\n-------------------------------------\r\nSELECT a.data AS tab1_data,        b.data AS tab2_data FROM   tab1 a\r\n    JOIN tab2 b ON b.tab1_id = a.id WHERE  a.code = 'ONE'\r\n\r\nPlan hash value: 2672205743\r\n\r\n-----------------------------------------------------------------------------------------------------------\r\n|   Id  | Operation                               | Name          | Rows  | Bytes | Cost (%CPU)| Time     |\r\n-----------------------------------------------------------------------------------------------------------\r\n|     0 | SELECT STATEMENT                        |               |       |       |     3 (100)|          |\r\n|- *  1 |  HASH JOIN                              |               |    25 |   425 |     3   (0)| 00:00:01 |\r\n|     2 |   NESTED LOOPS                          |               |    25 |   425 |     3   (0)| 00:00:01 |\r\n|     3 |    NESTED LOOPS                         |               |    25 |   425 |     3   (0)| 00:00:01 |\r\n|-    4 |     STATISTICS COLLECTOR                |               |       |       |            |          |\r\n|     5 |      TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |     1 |    11 |     2   (0)| 00:00:01 |\r\n|  *  6 |       INDEX RANGE SCAN                  | TAB1_CODE     |     1 |       |     1   (0)| 00:00:01 |\r\n|  *  7 |     INDEX RANGE SCAN                    | TAB2_TAB1_FKI |    25 |       |     0   (0)|          |\r\n|     8 |    TABLE ACCESS BY INDEX ROWID          | TAB2          |    25 |   150 |     1   (0)| 00:00:01 |\r\n|-    9 |   TABLE ACCESS FULL                     | TAB2          |    25 |   150 |     1   (0)| 00:00:01 |\r\n-----------------------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   1 - access(\"B\".\"TAB1_ID\"=\"A\".\"ID\")\r\n   6 - access(\"A\".\"CODE\"='ONE')\r\n   7 - access(\"B\".\"TAB1_ID\"=\"A\".\"ID\")\r\n\r\nNote\r\n-----\r\n   - this is an adaptive plan (rows marked '-' are inactive)\r\n\r\n\r\n33 rows selected.<\/pre>\n<p><strong>Second<\/strong>: I load the plan (lazy way: using baseline capture at session level)<\/p>\n<pre class=\"lang:plsql decode:true\">SQL&gt; ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;\r\n\r\nSession altered.\r\n\r\nSQL&gt; SELECT \/*+ GATHER_PLAN_STATISTICS *\/\r\n  2         a.data AS tab1_data,\r\n  3         b.data AS tab2_data\r\n  4  FROM   tab1 a\r\n  5         JOIN tab2 b ON b.tab1_id = a.id\r\n  6  WHERE  a.code = 'ONE';\r\n\r\n TAB1_DATA  TAB2_DATA\r\n---------- ----------\r\n...\r\n\r\n30 rows selected.\r\n\r\nSQL&gt; r \r\n  1  SELECT \/*+ GATHER_PLAN_STATISTICS *\/\r\n  2         a.data AS tab1_data,\r\n  3         b.data AS tab2_data\r\n  4  FROM   tab1 a\r\n  5         JOIN tab2 b ON b.tab1_id = a.id\r\n  6* WHERE  a.code = 'ONE'\r\n\r\n TAB1_DATA  TAB2_DATA\r\n---------- ----------\r\n...\r\n\r\n30 rows selected.\r\n\r\nSQL&gt; ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;\r\n\r\nSession altered.\r\n\r\nSQL&gt; select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;\r\n\r\nSQL_HANDLE           PLAN_NAME                          SQL_TEXT                                   ENA ACC FIX\r\n-------------------- ---------------------------------- ------------------------------------------ --- --- ---\r\nSQL_6c4c6680810dd01a SQL_PLAN_6sm36h20hvn0u55a25f73     SELECT \/*+ GATHER_PLAN_STATISTICS *\/       YES YES NO\r\n                                                                       a.data AS tab1_data,\r\n                                                                       b.data A<\/pre>\n<p><strong>Third<\/strong>: re-run the statement and check the plan<\/p>\n<pre class=\"lang:plsql decode:true\">SQL&gt; SELECT \/*+ GATHER_PLAN_STATISTICS *\/\r\n  2         a.data AS tab1_data,\r\n  3         b.data AS tab2_data\r\n  4  FROM   tab1 a\r\n  5         JOIN tab2 b ON b.tab1_id = a.id\r\n  6  WHERE  a.code = 'ONE';\r\n\r\n TAB1_DATA  TAB2_DATA\r\n---------- ----------\r\n...\r\n\r\n30 rows selected.\r\n\r\nSQL&gt; select * from table(dbms_xplan.display_cursor(format=&gt;'+adaptive'));\r\n\r\nPLAN_TABLE_OUTPUT\r\n-----------------------------------------------------------------------------------------------------\r\nSQL_ID  1km5kczcgr0fr, child number 3\r\n-------------------------------------\r\nSELECT \/*+ GATHER_PLAN_STATISTICS *\/        a.data AS tab1_data,\r\nb.data AS tab2_data FROM   tab1 a        JOIN tab2 b ON b.tab1_id =\r\na.id WHERE  a.code = 'ONE'\r\n\r\nPlan hash value: 2672205743\r\n\r\n-------------------------------------------------------------------------------------------------------\r\n| Id  | Operation                             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |\r\n-------------------------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT                      |               |       |       |     3 (100)|          |\r\n|   1 |  NESTED LOOPS                         |               |    25 |   425 |     3   (0)| 00:00:01 |\r\n|   2 |   NESTED LOOPS                        |               |    25 |   425 |     3   (0)| 00:00:01 |\r\n|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |     1 |    11 |     2   (0)| 00:00:01 |\r\n|*  4 |     INDEX RANGE SCAN                  | TAB1_CODE     |     1 |       |     1   (0)| 00:00:01 |\r\n|*  5 |    INDEX RANGE SCAN                   | TAB2_TAB1_FKI |    25 |       |     0   (0)|          |\r\n|   6 |   TABLE ACCESS BY INDEX ROWID         | TAB2          |    25 |   150 |     1   (0)| 00:00:01 |\r\n-------------------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   4 - access(\"A\".\"CODE\"='ONE')\r\n   5 - access(\"B\".\"TAB1_ID\"=\"A\".\"ID\")\r\n\r\nNote\r\n-----\r\n   - SQL plan baseline SQL_PLAN_6sm36h20hvn0u55a25f73 used for this statement\r\n\r\n\r\n30 rows selected.<\/pre>\n<p>It does not look adaptive, but I can also check from the function DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE:<\/p>\n<pre class=\"lang:plsql decode:true\">SQL&gt; select * from table (DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_6c4c6680810dd01a', format=&gt;'+adaptive'));\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------\r\n--------------------------------------------------------------------------------\r\nSQL handle: SQL_6c4c6680810dd01a\r\nSQL text: SELECT \/*+ GATHER_PLAN_STATISTICS *\/        a.data AS tab1_data,\r\n            b.data AS tab2_data FROM   tab1 a        JOIN tab2 b ON b.tab1_id =\r\n          a.id WHERE  a.code = 'ONE'\r\n--------------------------------------------------------------------------------\r\n\r\n--------------------------------------------------------------------------------\r\nPlan name: SQL_PLAN_6sm36h20hvn0u55a25f73         Plan id: 1436704627\r\nEnabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE\r\nPlan rows: From dictionary\r\n--------------------------------------------------------------------------------\r\n\r\nPlan hash value: 2672205743\r\n\r\n---------------------------------------------------------------------------------------------------------\r\n|   Id  | Operation                             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |\r\n---------------------------------------------------------------------------------------------------------\r\n|     0 | SELECT STATEMENT                      |               |    25 |   425 |     3   (0)| 00:00:01 |\r\n|     1 |  NESTED LOOPS                         |               |    25 |   425 |     3   (0)| 00:00:01 |\r\n|     2 |   NESTED LOOPS                        |               |    25 |   425 |     3   (0)| 00:00:01 |\r\n|     3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |     1 |    11 |     2   (0)| 00:00:01 |\r\n|  *  4 |     INDEX RANGE SCAN                  | TAB1_CODE     |     1 |       |     1   (0)| 00:00:01 |\r\n|  *  5 |    INDEX RANGE SCAN                   | TAB2_TAB1_FKI |    25 |       |     0   (0)| 00:00:01 |\r\n|     6 |   TABLE ACCESS BY INDEX ROWID         | TAB2          |    25 |   150 |     1   (0)| 00:00:01 |\r\n---------------------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   4 - access(\"A\".\"CODE\"='ONE')\r\n   5 - access(\"B\".\"TAB1_ID\"=\"A\".\"ID\")\r\n\r\nNote\r\n-----\r\n   - this is an adaptive plan (rows marked '-' are inactive)\r\n\r\n37 rows selected.\r\n<\/pre>\n<p>Again, despite in the Note section it says it is adaptive, it does not look like an adaptive plan.<\/p>\n<p>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&#8217;s example):<\/p>\n<pre class=\"lang:plsql decode:true\">SQL&gt; INSERT \/*+ APPEND *\/ INTO tab1\r\n  2   SELECT tab1_seq.nextval,\r\n  2         'ONE',\r\n  4         level\r\n  5  FROM   dual\r\n  6  CONNECT BY level &lt;= 10000;\r\n\r\n  10000 rows created.\r\n\r\nSQL&gt; COMMIT;\r\n\r\nCommit complete.\r\n\r\nSQL&gt; alter session set optimizer_use_sql_plan_baselines=false;\r\n\r\nSession altered.\r\n\r\nSQL&gt; SELECT \/*+ GATHER_PLAN_STATISTICS *\/\r\n  2         a.data AS tab1_data,\r\n  3         b.data AS tab2_data\r\n  4  FROM   tab1 a\r\n  5         JOIN tab2 b ON b.tab1_id = a.id\r\n  6  WHERE  a.code = 'ONE';\r\n\r\n TAB1_DATA  TAB2_DATA\r\n---------- ----------\r\n...\r\n\r\n30 rows selected.\r\n\r\n\r\nSQL&gt; SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format =&gt; 'allstats last adaptive'));\r\n\r\nPLAN_TABLE_OUTPUT\r\n------------------------------------------------------------------------------------------------------------------------------\r\nSQL_ID  1km5kczcgr0fr, child number 0\r\n-------------------------------------\r\nSELECT \/*+ GATHER_PLAN_STATISTICS *\/        a.data AS tab1_data,\r\nb.data AS tab2_data FROM   tab1 a        JOIN tab2 b ON b.tab1_id =\r\na.id WHERE  a.code = 'ONE'\r\n\r\nPlan hash value: 1599395313\r\n\r\n------------------------------------------------------------------------------------------------------------------------------------------------\r\n|   Id  | Operation                               | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |\r\n------------------------------------------------------------------------------------------------------------------------------------------------\r\n|     0 | SELECT STATEMENT                        |               |      1 |        |     30 |00:00:00.01 |     142 |       |       |          |\r\n|  *  1 |  HASH JOIN                              |               |      1 |     25 |     30 |00:00:00.01 |     142 |  2261K|  2261K| 2180K (0)|\r\n|-    2 |   NESTED LOOPS                          |               |      1 |     25 |  20001 |00:00:00.01 |     124 |       |       |          |\r\n|-    3 |    NESTED LOOPS                         |               |      1 |     25 |  20001 |00:00:00.01 |     124 |       |       |          |\r\n|-    4 |     STATISTICS COLLECTOR                |               |      1 |        |  20001 |00:00:00.01 |     124 |       |       |          |\r\n|     5 |      TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |      1 |      1 |  20001 |00:00:00.01 |     124 |       |       |          |\r\n|  *  6 |       INDEX RANGE SCAN                  | TAB1_CODE     |      1 |      1 |  20001 |00:00:00.01 |      74 |       |       |          |\r\n|- *  7 |     INDEX RANGE SCAN                    | TAB2_TAB1_FKI |      0 |     25 |      0 |00:00:00.01 |       0 |       |       |          |\r\n|-    8 |    TABLE ACCESS BY INDEX ROWID          | TAB2          |      0 |     25 |      0 |00:00:00.01 |       0 |       |       |          |\r\n|     9 |   TABLE ACCESS FULL                     | TAB2          |      1 |     25 |    100 |00:00:00.01 |      18 |       |       |          |\r\n------------------------------------------------------------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   1 - access(\"B\".\"TAB1_ID\"=\"A\".\"ID\")\r\n   6 - access(\"A\".\"CODE\"='ONE')\r\n   7 - access(\"B\".\"TAB1_ID\"=\"A\".\"ID\")\r\n\r\nNote\r\n-----\r\n   - this is an adaptive plan (rows marked '-' are inactive)\r\n\r\n\r\n34 rows selected.\r\n\r\nSQL&gt; alter session set optimizer_use_sql_plan_baselines=true;\r\n\r\nSession altered.\r\n\r\nSQL&gt; SELECT \/*+ GATHER_PLAN_STATISTICS *\/\r\n  2         a.data AS tab1_data,\r\n  3         b.data AS tab2_data\r\n  4  FROM   tab1 a\r\n  5         JOIN tab2 b ON b.tab1_id = a.id\r\n  6  WHERE  a.code = 'ONE';\r\n\r\n TAB1_DATA  TAB2_DATA\r\n---------- ----------\r\n...\r\n\r\n30 rows selected.\r\n\r\nSQL&gt; SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format =&gt; 'allstats last adaptive'));\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nSQL_ID  1km5kczcgr0fr, child number 2\r\n-------------------------------------\r\nSELECT \/*+ GATHER_PLAN_STATISTICS *\/        a.data AS tab1_data,\r\nb.data AS tab2_data FROM   tab1 a        JOIN tab2 b ON b.tab1_id =\r\na.id WHERE  a.code = 'ONE'\r\n\r\nPlan hash value: 2672205743\r\n\r\n-----------------------------------------------------------------------------------------------------------------\r\n| Id  | Operation                             | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |\r\n-----------------------------------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT                      |               |      1 |        |     30 |00:00:00.01 |   30889 |\r\n|   1 |  NESTED LOOPS                         |               |      1 |     25 |     30 |00:00:00.01 |   30889 |\r\n|   2 |   NESTED LOOPS                        |               |      1 |     25 |     30 |00:00:00.01 |   30886 |\r\n|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |      1 |      1 |  20001 |00:00:00.01 |     125 |\r\n|*  4 |     INDEX RANGE SCAN                  | TAB1_CODE     |      1 |      1 |  20001 |00:00:00.01 |      75 |\r\n|*  5 |    INDEX RANGE SCAN                   | TAB2_TAB1_FKI |  20001 |     25 |     30 |00:00:00.02 |   30761 |\r\n|   6 |   TABLE ACCESS BY INDEX ROWID         | TAB2          |     30 |     25 |     30 |00:00:00.01 |       3 |\r\n-----------------------------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   4 - access(\"A\".\"CODE\"='ONE')\r\n   5 - access(\"B\".\"TAB1_ID\"=\"A\".\"ID\")\r\n\r\nNote\r\n-----\r\n   - SQL plan baseline SQL_PLAN_6sm36h20hvn0u55a25f73 used for this statement\r\n\r\n\r\n30 rows selected.<\/pre>\n<p>After changing the rows:<\/p>\n<ul>\n<li>when I do not use the baseline, the plan resolves to HASH JOIN<\/li>\n<li>when I use it, the baseline forces to NESTED LOOPS.<\/li>\n<\/ul>\n<p>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:<\/p>\n<pre class=\"lang:plsql decode:true \">SQL&gt;  ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;\r\n\r\nSession altered.\r\n\r\nSELECT \/*+ GATHER_PLAN_STATISTICS *\/\r\n  2         a.data AS tab1_data,\r\n  3         b.data AS tab2_data\r\n  4  FROM   tab1 a\r\n  5         JOIN tab2 b ON b.tab1_id = a.id\r\n  6  WHERE  a.code = 'ONE';\r\n\r\n TAB1_DATA  TAB2_DATA\r\n---------- ----------\r\n...\r\n\r\n30 rows selected.\r\n\r\nSQL&gt; r\r\n  1  SELECT \/*+ GATHER_PLAN_STATISTICS *\/\r\n  2         a.data AS tab1_data,\r\n  3         b.data AS tab2_data\r\n  4  FROM   tab1 a\r\n  5         JOIN tab2 b ON b.tab1_id = a.id\r\n  6* WHERE  a.code = 'ONE'\r\n\r\n TAB1_DATA  TAB2_DATA\r\n---------- ----------\r\n...\r\n\r\n30 rows selected.\r\n\r\nSQL&gt;  ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;\r\n\r\nSession altered.\r\n\r\nSQL&gt; select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;\r\n\r\nSQL_HANDLE           PLAN_NAME                      SQL_TEXT                                                                         ENABLED   ACCEPTED  FIXED\r\n-------------------- ------------------------------ -------------------------------------------------------------------------------- --------- --------- ---------\r\nSQL_6c4c6680810dd01a SQL_PLAN_6sm36h20hvn0u55a25f73 SELECT \/*+ GATHER_PLAN_STATISTICS *\/                                             YES       YES       NO\r\n                                                           a.data AS tab1_data,\r\n                                                           b.data A\r\n<\/pre>\n<p>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&#8217;s try to drop the existing one and redo the test:<\/p>\n<pre class=\"lang:plsql decode:true\">SQL&gt; connect \/ as sysdba\r\nConnected\r\nSQL&gt; DECLARE\r\n  2   v_dropped_plans number;\r\n  3 BEGIN\r\n  4   v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (\r\n  5      sql_handle =&gt; 'SQL_6c4c6680810dd01a'\r\n  6 );\r\n  7 END;\r\n  8 \/\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL&gt; select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;\r\n\r\nno rows selected\r\n\r\nSQL&gt; conn ludo\/ludo\r\nConnected.\r\nSQL&gt;  ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;\r\n\r\nSession altered.\r\n\r\nSELECT \/*+ GATHER_PLAN_STATISTICS *\/\r\n  2         a.data AS tab1_data,\r\n  3         b.data AS tab2_data\r\n  4  FROM   tab1 a\r\n  5         JOIN tab2 b ON b.tab1_id = a.id\r\n  6  WHERE  a.code = 'ONE';\r\n\r\n TAB1_DATA  TAB2_DATA\r\n---------- ----------\r\n...\r\n\r\n30 rows selected.\r\n\r\nSQL&gt; r\r\n  1  SELECT \/*+ GATHER_PLAN_STATISTICS *\/\r\n  2         a.data AS tab1_data,\r\n  3         b.data AS tab2_data\r\n  4  FROM   tab1 a\r\n  5         JOIN tab2 b ON b.tab1_id = a.id\r\n  6* WHERE  a.code = 'ONE'\r\n\r\n TAB1_DATA  TAB2_DATA\r\n---------- ----------\r\n...\r\n\r\n30 rows selected.\r\n\r\nSQL&gt;  ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;\r\n\r\nSession altered.\r\n\r\nSQL&gt; select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;\r\n\r\nSQL_HANDLE           PLAN_NAME                      SQL_TEXT                                                                         ENABLED   ACCEPTED  FIXED\r\n-------------------- ------------------------------ -------------------------------------------------------------------------------- --------- --------- ---------\r\nSQL_6c4c6680810dd01a SQL_PLAN_6sm36h20hvn0u55a25f73 SELECT \/*+ GATHER_PLAN_STATISTICS *\/                                             YES       YES       NO\r\n                                                           a.data AS tab1_data,\r\n                                                           b.data A\r\n\r\nSQL&gt; select * from table (DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_6c4c6680810dd01a', format=&gt;'+adaptive'));\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------\r\n--------------------------------------------------------------------------------\r\nSQL handle: SQL_6c4c6680810dd01a\r\nSQL text: SELECT \/*+ GATHER_PLAN_STATISTICS *\/        a.data AS tab1_data,\r\n            b.data AS tab2_data FROM   tab1 a        JOIN tab2 b ON b.tab1_id =\r\n          a.id WHERE  a.code = 'ONE'\r\n--------------------------------------------------------------------------------\r\n\r\n--------------------------------------------------------------------------------\r\nPlan name: SQL_PLAN_6sm36h20hvn0u55a25f73         Plan id: 1436704627\r\nEnabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE\r\nPlan rows: From dictionary\r\n--------------------------------------------------------------------------------\r\n\r\nPlan hash value: 2672205743\r\n\r\n---------------------------------------------------------------------------------------------------------\r\n|   Id  | Operation                             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |\r\n---------------------------------------------------------------------------------------------------------\r\n|     0 | SELECT STATEMENT                      |               |    25 |   425 |     3   (0)| 00:00:01 |\r\n|     1 |  NESTED LOOPS                         |               |    25 |   425 |     3   (0)| 00:00:01 |\r\n|     2 |   NESTED LOOPS                        |               |    25 |   425 |     3   (0)| 00:00:01 |\r\n|     3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |     1 |    11 |     2   (0)| 00:00:01 |\r\n|  *  4 |     INDEX RANGE SCAN                  | TAB1_CODE     |     1 |       |     1   (0)| 00:00:01 |\r\n|  *  5 |    INDEX RANGE SCAN                   | TAB2_TAB1_FKI |    25 |       |     0   (0)| 00:00:01 |\r\n|     6 |   TABLE ACCESS BY INDEX ROWID         | TAB2          |    25 |   150 |     1   (0)| 00:00:01 |\r\n---------------------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   4 - access(\"A\".\"CODE\"='ONE')\r\n   5 - access(\"B\".\"TAB1_ID\"=\"A\".\"ID\")\r\n\r\nNote\r\n-----\r\n   - this is an adaptive plan (rows marked '-' are inactive)\r\n\r\n37 rows selected.\r\n<\/pre>\n<p>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 <strong>the original plan is loaded as SQL Plan Baseline.<\/strong><\/p>\n<p>References:<\/p>\n<ul>\n<li><a href=\"https:\/\/oracle-base.com\/articles\/12c\/adaptive-plans-12cr1\">https:\/\/oracle-base.com\/articles\/12c\/adaptive-plans-12cr1<\/a><\/li>\n<li><a href=\"http:\/\/www.oracle.com\/technetwork\/articles\/database\/create-sql-plan-baseline-2237506.html\">http:\/\/www.oracle.com\/technetwork\/articles\/database\/create-sql-plan-baseline-2237506.html<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>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&#8230; but that&#8217;s blogger&#8217;s life \ud83d\ude42 Wednesday I have got a nice question after &hellip; <a href=\"https:\/\/www.ludovicocaldara.net\/dba\/adaptive-plans-baselines\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[326,3,52,6,132],"tags":[],"class_list":["post-1446","post","type-post","status-publish","format-standard","hentry","category-oracle","category-oracledb","category-12c","category-perf","category-triblog"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1446","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=1446"}],"version-history":[{"count":6,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1446\/revisions"}],"predecessor-version":[{"id":1452,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1446\/revisions\/1452"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=1446"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=1446"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=1446"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}