.
\nI encountered recently an issue with Adaptive Plan and SPM. Documentations says that it works perfectly together but I remembered a presentation from Nelson Calero at UKOUG TECH15 mentioning strange behavior. I reproduced the issue and share the test case here as you may encounter it in 12.1 leading to regressions when you capture SQL Plan Baselines.
\n<\/p>\n
Whith all those adaptive features, you need to start clean if you want a reproductible testcase<\/p>\n
SQL> -- drop tables\nSQL> drop table DEMO1;\nTable dropped.\nSQL> drop table DEMO2;\nTable dropped.\nSQL> \nSQL> whenever sqlerror exit failure\nSQL> \nSQL> -- drop all sql plan baselines\nSQL> set serveroutput on long 100000 longc 100000\nSQL> exec for i in (select sql_handle, plan_name, accepted, origin, created from dba_sql_plan_baselines) loop dbms_output.put_line(''||dbms_spm.drop_sql_plan_baseline(sql_handle=>i.sql_handle,plan_name=>i.plan_name)); end loop;\n1\n \nPL\/SQL procedure successfully completed.\n \nSQL> set serveroutput off\nSQL> select 0\/0 from dba_sql_plan_baselines;\nno rows selected\nSQL> \nSQL> -- flush shared pool\nSQL> alter system flush shared_pool;\nSystem altered.\nSQL> select 0\/0 from v$sql where sql_id='agw7bn072730a';\nno rows selected<\/code><\/pre>\nCreate the tables<\/h3>\nSQL> -- create two tables with few rows for L=1 and lot of rows for L=15\nSQL> create table DEMO2 (id constraint PK1 primary key,l) as select rownum,floor(log(2,rownum)) from xmltable('1 to 100000');\nTable created.\n \nSQL> create table DEMO1 as select * from DEMO2;\nTable created.<\/code><\/pre>\nRun the query<\/h3>\n\nSQL> -- run a join for the few rows case\nSQL> alter session set statistics_level=all;\nSession altered.\nSQL> select count(DEMO2.l) from DEMO1 join DEMO2 using(id) where DEMO1.l=1;\n \nCOUNT(DEMO2.L)\n--------------\n 3<\/code><\/pre>\nAnd here is the adaptive plan:<\/p>\n
SQL> select * from table(dbms_xplan.display_cursor(null,null,format=>'allstats last +adaptive +outline'));\n \nPLAN_TABLE_OUTPUT\n--------------------------------------------------------------------------------------------------------------\nSQL_ID agw7bn072730a, child number 0\n-------------------------------------\nselect count(DEMO2.l) from DEMO1 join DEMO2 using(id) where DEMO1.l=1\n \nPlan hash value: 2870612662\n \n-------------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |\n-------------------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 203 | 183 |\n| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 203 | 183 |\n|- * 2 | HASH JOIN | | 1 | 5882 | 3 |00:00:00.01 | 203 | 183 |\n| 3 | NESTED LOOPS | | 1 | 5882 | 3 |00:00:00.01 | 203 | 183 |\n| 4 | NESTED LOOPS | | 1 | | 3 |00:00:00.01 | 200 | 183 |\n|- 5 | STATISTICS COLLECTOR | | 1 | | 3 |00:00:00.01 | 195 | 179 |\n| * 6 | TABLE ACCESS FULL | DEMO1 | 1 | 5882 | 3 |00:00:00.01 | 195 | 179 |\n| * 7 | INDEX UNIQUE SCAN | PK1 | 3 | | 3 |00:00:00.01 | 5 | 4 |\n| 8 | TABLE ACCESS BY INDEX ROWID| DEMO2 | 3 | 1 | 3 |00:00:00.01 | 3 | 0 |\n|- 9 | TABLE ACCESS FULL | DEMO2 | 0 | 100K| 0 |00:00:00.01 | 0 | 0 |\n-------------------------------------------------------------------------------------------------------------\n \nOutline Data\n-------------\n \n \/*+\n BEGIN_OUTLINE_DATA\n INDEX(@\"SEL$58A6D7F6\" \"DEMO2\"@\"SEL$1\" (\"DEMO2\".\"ID\"))\n NLJ_BATCHING(@\"SEL$58A6D7F6\" \"DEMO2\"@\"SEL$1\")\n USE_NL(@\"SEL$58A6D7F6\" \"DEMO2\"@\"SEL$1\")\n IGNORE_OPTIM_EMBEDDED_HINTS\n OPTIMIZER_FEATURES_ENABLE('12.2.0.1')\n DB_VERSION('12.2.0.1')\n ALL_ROWS\n OUTLINE_LEAF(@\"SEL$58A6D7F6\")\n MERGE(@\"SEL$1\")\n OUTLINE(@\"SEL$2\")\n OUTLINE(@\"SEL$1\")\n FULL(@\"SEL$58A6D7F6\" \"DEMO1\"@\"SEL$1\")\n LEADING(@\"SEL$58A6D7F6\" \"DEMO1\"@\"SEL$1\" \"DEMO2\"@\"SEL$1\")\n END_OUTLINE_DATA\n *\/\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n 2 - access(\"DEMO1\".\"ID\"=\"DEMO2\".\"ID\")\n 6 - filter(\"DEMO1\".\"L\"=1)\n 7 - access(\"DEMO1\".\"ID\"=\"DEMO2\".\"ID\")\n \nNote\n-----\n - this is an adaptive plan (rows marked '-' are inactive)\n<\/code><\/pre>\nIt’s an adaptive plan, HASH JOIN was the initial choice but first execution activated the NESTED LOOP.<\/p>\n
SQL Baseline Capture<\/h3>\nSQL> alter session set optimizer_capture_sql_plan_baselines=true;\nSession altered.\n \nSQL> select count(DEMO2.l) from DEMO1 join DEMO2 using(id) where DEMO1.l=1;\n \nCOUNT(DEMO2.L)\n--------------\n 3\n \nSQL> alter session set optimizer_capture_sql_plan_baselines=false;\nSession altered.<\/code><\/pre>\nHere is the SQL Baseline:<\/p>\n
SQL> select sql_handle, plan_name, accepted, origin, created from dba_sql_plan_baselines;\n \nSQL_HANDLE PLAN_NAME ACC ORIGIN CREATED\n------------------------------ ---------------------------------------- --- ----------------------------- ---------------------------------------------------------------------------\nSQL_4c1b404640b73a81 SQL_PLAN_4s6u08t0bffn1e47b6a4d YES AUTO-CAPTURE 28-MAY-16 09.13.04.000000 PM<\/code><\/pre>\nand its plan:<\/p>\n
SQL> select plan_table_output from dba_sql_plan_baselines,table(dbms_xplan.display_sql_plan_baseline(sql_handle, plan_name, format=>'+adaptive'));\n \nPLAN_TABLE_OUTPUT\n--------------------------------------------------------------------------------\n \n--------------------------------------------------------------------------------\nSQL handle: SQL_4c1b404640b73a81\nSQL text: select count(DEMO2.l) from DEMO1 join DEMO2 using(id) where DEMO1.l=1\n--------------------------------------------------------------------------------\n \n--------------------------------------------------------------------------------\nPlan name: SQL_PLAN_4s6u08t0bffn1e47b6a4d Plan id: 3833293389\nEnabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE\nPlan rows: From dictionary\n--------------------------------------------------------------------------------\n \nPlan hash value: 740165205\n \n-------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n-------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | 16 | 108 (2)| 00:00:01 |\n| 1 | SORT AGGREGATE | | 1 | 16 | | |\n| * 2 | HASH JOIN | | 5882 | 94112 | 108 (2)| 00:00:01 |\n| * 3 | TABLE ACCESS FULL| DEMO1 | 5882 | 47056 | 54 (2)| 00:00:01 |\n| 4 | TABLE ACCESS FULL| DEMO2 | 100K| 781K| 54 (2)| 00:00:01 |\n-------------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n 2 - access(\"DEMO1\".\"ID\"=\"DEMO2\".\"ID\")\n 3 - filter(\"DEMO1\".\"L\"=1)\n \nNote\n-----\n - this is an adaptive plan (rows marked '-' are inactive)<\/code><\/pre>\n