{"id":1454,"date":"2016-11-18T12:30:59","date_gmt":"2016-11-18T10:30:59","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=1454"},"modified":"2020-08-18T16:19:43","modified_gmt":"2020-08-18T14:19:43","slug":"loading-resolved-adaptive-plans-in-the-sql-plan-management","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/loading-resolved-adaptive-plans-in-the-sql-plan-management\/","title":{"rendered":"Loading resolved Adaptive Plans in the SQL Plan Management"},"content":{"rendered":"<p>In <a href=\"https:\/\/www.ludovicocaldara.net\/dba\/adaptive-plans-baselines\/\">my previous pos<\/a>t, I have shown that loading Adaptive Plans in the SQL Plan Baseline leads to using the original plan. Well, actually, this is true when you capture them via the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter.<\/p>\n<p>Thanks to a<a href=\"https:\/\/twitter.com\/ChandlerDBA\/status\/799541587588026368\"> tweet by Neil Chandler<\/a>, I&#8217;ve realized that it was a good idea to show also the case when the plan is loaded manually.<\/p>\n<p>When the adaptive plan switches to the alternative plan, the plan_hash_value also changes, and can be loaded manually in the baseline with DBMS_SPM.<\/p>\n<p>Let&#8217;s reset everything and retry quickly to:<\/p>\n<ul>\n<li>Capture the plan automatically (this will lead to the original plan)<\/li>\n<li>Load the plan manually (I will specify to load the alternative plan, if resolved)<\/li>\n<li>Drop the plan captured automatically<\/li>\n<li>Use the newly accepted baseline<\/li>\n<\/ul>\n<pre class=\"lang:plsql decode:true\">SQL&gt; connect \/ as sysdba\r\nConnected.\r\n\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    DBMS_OUTPUT.PUT_LINE('dropped ' || v_dropped_plans || ' plans');\r\n  8  END;\r\n  9  \/\r\n\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; alter system flush shared_pool;\r\n\r\nSystem altered.\r\n\r\nSQL&gt; select sql_id, plan_hash_value, child_number from v$sql where sql_id='1km5kczcgr0fr';\r\n\r\nno rows selected\r\n\r\nSQL&gt; connect 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\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\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_id, plan_hash_value, child_number from v$sql where sql_id='1km5kczcgr0fr';\r\n\r\nSQL_ID                                  PLAN_HASH_VALUE CHILD_NUMBER\r\n--------------------------------------- --------------- ------------\r\n1km5kczcgr0fr                                2672205743            1\r\n\r\nSELECT \/*+ GATHER_PLAN_STATISTICS *\/\r\n       a.data AS tab1_data,\r\n  2    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\n30 rows selected.\r\n\r\nSQL&gt; select sql_id, plan_hash_value, child_number from v$sql where sql_id='1km5kczcgr0fr';\r\n\r\nSQL_ID                                  PLAN_HASH_VALUE CHILD_NUMBER\r\n--------------------------------------- --------------- ------------\r\n1km5kczcgr0fr                                2672205743            1\r\n1km5kczcgr0fr                                2672205743            2\r\n\r\nSQL&gt; ALTER SESSION SET OPTIMIZER_USE_SQL_PLAN_BASELINES = FALSE;\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\n30 rows selected.\r\n\r\nSQL&gt; select sql_id, plan_hash_value, child_number from v$sql where sql_id='1km5kczcgr0fr';\r\n\r\nSQL_ID                                  PLAN_HASH_VALUE CHILD_NUMBER\r\n--------------------------------------- --------------- ------------\r\n1km5kczcgr0fr                                1599395313            0\r\n1km5kczcgr0fr                                2672205743            1\r\n1km5kczcgr0fr                                2672205743            2\r\n\r\n\r\nSQL&gt; connect \/ as sysdba\r\nConnected.\r\nSQL&gt; VARIABLE cnt NUMBER\r\nSQL&gt; EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id =&gt; '1km5kczcgr0fr',plan_hash_value =&gt; '1599395313');\r\n\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\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_6c4c6680810dd01a      SQL_PLAN_6sm36h20hvn0ud64ac9be      SELECT \/*+ GATHER_PLAN_STATISTICS *\/   YES       YES       NO\r\n                                                                     a.data AS tab1_data,\r\n                                                                     b.data A\r\n\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\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\n--------------------------------------------------------------------------------\r\nPlan name: SQL_PLAN_6sm36h20hvn0ud64ac9be         Plan id: 3595225534\r\nEnabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD\r\nPlan rows: From dictionary\r\n--------------------------------------------------------------------------------\r\n\r\nPlan hash value: 1599395313\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 |   TABLE ACCESS BY INDEX ROWID BATCHED| TAB1      |     1 |    11 |     2   (0)| 00:00:01 |\r\n|  *  3 |    INDEX RANGE SCAN                  | TAB1_CODE |     1 |       |     1   (0)| 00:00:01 |\r\n|     4 |   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   3 - access(\"A\".\"CODE\"='ONE')\r\n\r\nNote\r\n-----\r\n   - this is an adaptive plan (rows marked '-' are inactive)\r\n\r\n65 rows selected.\r\n\r\n\r\nSQL&gt; VARIABLE cnt NUMBER\r\nSQL&gt;  EXECUTE :cnt := DBMS_SPM.DROP_SQL_PLAN_BASELINE( SQL_HANDLE =&gt; 'SQL_6c4c6680810dd01a',plan_name =&gt; 'SQL_PLAN_6sm36h20hvn0u55a25f73');\r\n\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\nSQL_HANDLE                PLAN_NAME                           SQL_TEXT                               ENABLED   ACCEPTED  FIXED\r\n------------------------- ----------------------------------- -------------------------------------- --------- --------- ---------\r\nSQL_6c4c6680810dd01a      SQL_PLAN_6sm36h20hvn0ud64ac9be      SELECT \/*+ GATHER_PLAN_STATISTICS *\/   YES       YES       NO\r\n                                                                     a.data AS tab1_data,\r\n                                                                     b.data A\r\n\r\nSQL&gt; conn ludo\/ludo\r\nConnected.\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\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: 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 |        |     24 |00:00:00.01 |      71 |       |       |          |\r\n|*  1 |  HASH JOIN                           |           |      1 |     25 |     24 |00:00:00.01 |      71 |  1888K|  1888K| 1921K (0)|\r\n|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TAB1      |      1 |      1 |  10001 |00:00:00.01 |      62 |       |       |          |\r\n|*  3 |    INDEX RANGE SCAN                  | TAB1_CODE |      1 |      1 |  10001 |00:00:00.01 |      37 |       |       |          |\r\n|   4 |   TABLE ACCESS FULL                  | TAB2      |      1 |    100 |    100 |00:00:00.01 |       9 |       |       |          |\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   3 - access(\"A\".\"CODE\"='ONE')\r\n\r\nNote\r\n-----\r\n   - SQL plan baseline SQL_PLAN_6sm36h20hvn0ud64ac9be used for this statement\r\n\r\n\r\n28 rows selected.<\/pre>\n<p>To recap:<\/p>\n<ul>\n<li>The capture process will always load the original plan<\/li>\n<li>It is possible to decide to load manually the original one or the alternative one (if resolved)<\/li>\n<li>Using automatic capture is a bad idea<\/li>\n<\/ul>\n<p>HTH<\/p>\n<p>&#8212;<\/p>\n<p>Ludo<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In my previous post, I have shown that loading Adaptive Plans in the SQL Plan Baseline leads to using the original plan. Well, actually, this is true when you capture them via the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter. Thanks to a tweet by &hellip; <a href=\"https:\/\/www.ludovicocaldara.net\/dba\/loading-resolved-adaptive-plans-in-the-sql-plan-management\/\">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-1454","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\/1454","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=1454"}],"version-history":[{"count":1,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1454\/revisions"}],"predecessor-version":[{"id":1455,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1454\/revisions\/1455"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=1454"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=1454"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=1454"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}