By Franck Pachot

.
I 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.

Cleanup

Whith all those adaptive features, you need to start clean if you want a reproductible testcase

SQL> -- drop tables
SQL> drop table DEMO1;
Table dropped.
SQL> drop table DEMO2;
Table dropped.
SQL> 
SQL> whenever sqlerror exit failure
SQL> 
SQL> -- drop all sql plan baselines
SQL> set serveroutput on long 100000 longc 100000
SQL> 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;
1
 
PL/SQL procedure successfully completed.
 
SQL> set serveroutput off
SQL> select 0/0 from dba_sql_plan_baselines;
no rows selected
SQL> 
SQL> -- flush shared pool
SQL> alter system flush shared_pool;
System altered.
SQL> select 0/0 from v$sql where sql_id='agw7bn072730a';
no rows selected

Create the tables

SQL> -- create two tables with few rows for L=1 and lot of rows for L=15
SQL> create table DEMO2 (id constraint PK1 primary key,l) as select rownum,floor(log(2,rownum)) from xmltable('1 to 100000');
Table created.
 
SQL> create table DEMO1 as select * from DEMO2;
Table created.

Run the query


SQL> -- run a join for the few rows case
SQL> alter session set statistics_level=all;
Session altered.
SQL> select count(DEMO2.l) from DEMO1 join DEMO2 using(id) where DEMO1.l=1;
 
COUNT(DEMO2.L)
--------------
             3

And here is the adaptive plan:

SQL> select * from table(dbms_xplan.display_cursor(null,null,format=>'allstats last +adaptive +outline'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  agw7bn072730a, child number 0
-------------------------------------
select count(DEMO2.l) from DEMO1 join DEMO2 using(id) where DEMO1.l=1
 
Plan hash value: 2870612662
 
-------------------------------------------------------------------------------------------------------------
|   Id  | Operation                      | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT               |       |      1 |        |      1 |00:00:00.01 |     203 |    183 |
|     1 |  SORT AGGREGATE                |       |      1 |      1 |      1 |00:00:00.01 |     203 |    183 |
|- *  2 |   HASH JOIN                    |       |      1 |   5882 |      3 |00:00:00.01 |     203 |    183 |
|     3 |    NESTED LOOPS                |       |      1 |   5882 |      3 |00:00:00.01 |     203 |    183 |
|     4 |     NESTED LOOPS               |       |      1 |        |      3 |00:00:00.01 |     200 |    183 |
|-    5 |      STATISTICS COLLECTOR      |       |      1 |        |      3 |00:00:00.01 |     195 |    179 |
|  *  6 |       TABLE ACCESS FULL        | DEMO1 |      1 |   5882 |      3 |00:00:00.01 |     195 |    179 |
|  *  7 |      INDEX UNIQUE SCAN         | PK1   |      3 |        |      3 |00:00:00.01 |       5 |      4 |
|     8 |     TABLE ACCESS BY INDEX ROWID| DEMO2 |      3 |      1 |      3 |00:00:00.01 |       3 |      0 |
|-    9 |    TABLE ACCESS FULL           | DEMO2 |      0 |    100K|      0 |00:00:00.01 |       0 |      0 |
-------------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$58A6D7F6" "DEMO2"@"SEL$1" ("DEMO2"."ID"))
      NLJ_BATCHING(@"SEL$58A6D7F6" "DEMO2"@"SEL$1")
      USE_NL(@"SEL$58A6D7F6" "DEMO2"@"SEL$1")
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$58A6D7F6")
      MERGE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      FULL(@"SEL$58A6D7F6" "DEMO1"@"SEL$1")
      LEADING(@"SEL$58A6D7F6" "DEMO1"@"SEL$1" "DEMO2"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("DEMO1"."ID"="DEMO2"."ID")
   6 - filter("DEMO1"."L"=1)
   7 - access("DEMO1"."ID"="DEMO2"."ID")
 
Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

It’s an adaptive plan, HASH JOIN was the initial choice but first execution activated the NESTED LOOP.

SQL Baseline Capture

SQL> alter session set optimizer_capture_sql_plan_baselines=true;
Session altered.
 
SQL> select count(DEMO2.l) from DEMO1 join DEMO2 using(id) where DEMO1.l=1;
 
COUNT(DEMO2.L)
--------------
             3
 
SQL> alter session set optimizer_capture_sql_plan_baselines=false;
Session altered.

Here is the SQL Baseline:

SQL> select sql_handle, plan_name, accepted, origin, created from dba_sql_plan_baselines;
 
SQL_HANDLE                     PLAN_NAME                                ACC ORIGIN                        CREATED
------------------------------ ---------------------------------------- --- ----------------------------- ---------------------------------------------------------------------------
SQL_4c1b404640b73a81           SQL_PLAN_4s6u08t0bffn1e47b6a4d           YES AUTO-CAPTURE                  28-MAY-16 09.13.04.000000 PM

and its plan:

SQL> select plan_table_output from dba_sql_plan_baselines,table(dbms_xplan.display_sql_plan_baseline(sql_handle, plan_name, format=>'+adaptive'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
SQL handle: SQL_4c1b404640b73a81
SQL text: select count(DEMO2.l) from DEMO1 join DEMO2 using(id) where DEMO1.l=1
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_4s6u08t0bffn1e47b6a4d         Plan id: 3833293389
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------
 
Plan hash value: 740165205
 
-------------------------------------------------------------------------------
|   Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|     0 | SELECT STATEMENT    |       |     1 |    16 |   108   (2)| 00:00:01 |
|     1 |  SORT AGGREGATE     |       |     1 |    16 |            |          |
|  *  2 |   HASH JOIN         |       |  5882 | 94112 |   108   (2)| 00:00:01 |
|  *  3 |    TABLE ACCESS FULL| DEMO1 |  5882 | 47056 |    54   (2)| 00:00:01 |
|     4 |    TABLE ACCESS FULL| DEMO2 |   100K|   781K|    54   (2)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("DEMO1"."ID"="DEMO2"."ID")
   3 - filter("DEMO1"."L"=1)
 
Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

Unfortunately, the baseline captured only the ‘initial’ plan with the HASH JOIN.
This is not what is documented in Maria Colgan paper:
SPM plan capture and Adaptive Plans: When automatic plan capture is enabled and a SQL statement that has an adaptive plan is executed, only the final plan used will be captured in the SQL plan baseline.

Run with baseline

SQL> alter session set statistics_level=all;
Session altered.
 
SQL> select count(DEMO2.l) from DEMO1 join DEMO2 using(id) where DEMO1.l=1;
 
COUNT(DEMO2.L)
--------------
             3
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,format=>'allstats last +adaptive'));
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  agw7bn072730a, child number 1
-------------------------------------
select count(DEMO2.l) from DEMO1 join DEMO2 using(id) where DEMO1.l=1
 
Plan hash value: 740165205
 
------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |      1 |        |      1 |00:00:00.01 |     390 |       |       |          |
|   1 |  SORT AGGREGATE     |       |      1 |      1 |      1 |00:00:00.01 |     390 |       |       |          |
|*  2 |   HASH JOIN         |       |      1 |   5882 |      3 |00:00:00.01 |     390 |  2545K|  2545K|  826K (0)|
|*  3 |    TABLE ACCESS FULL| DEMO1 |      1 |   5882 |      3 |00:00:00.01 |     195 |       |       |          |
|   4 |    TABLE ACCESS FULL| DEMO2 |      1 |    100K|    100K|00:00:00.01 |     195 |       |       |          |
------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("DEMO1"."ID"="DEMO2"."ID")
   3 - filter("DEMO1"."L"=1)
 
Note
-----
   - SQL plan baseline SQL_PLAN_4s6u08t0bffn1e47b6a4d used for this statement

This confirms that the SQL baseline forces the initial HASH JOIN plan. It’s a bug that should be fixed in 12.2 so for the moment, be very careful when you want to fix an adaptive plan with SQL Baselines: your goal is to stabilize once you have the optimal plan, but the result may be a regression to a bad plan.

Post Views: 388
Thumbnail [60x60]
by
Oracle Team