By Franck Pachot

.
SQL Plan Baselines is a great feature for plan stability: you capture the plans that you accept. However, if the data model changes and the accepted plans cannot reproduce, the optimizer will come with a new plan. In 18c we have a note from DBMS_XPLAN when the optimization ‘failed to use SQL plan baseline for this statement’.

I create a table, with an index, and run a query on it using this index:


SQL> create table DEMO as select rownum n from xmltable('1 to 1000');
Table DEMO created.
 
SQL> create index DEMO_N on DEMO(n);
Index DEMO_N created.
 
SQL> select * from DEMO where n=1;
 
  N
  -
  1

The execution plan is correct, using the index:


SQL> select * from dbms_xplan.display_cursor();
 
PLAN_TABLE_OUTPUT
-----------------
SQL_ID  4mcr18aqntpkq, child number 0
-------------------------------------
select * from DEMO where n=1
 
Plan hash value: 217077817
 
---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| DEMO_N |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("N"=1)

I’m happy with this plan, and I capture it as an accepted SQL Plan Baseline for this statemement:


SQL> exec dbms_output.put_line( dbms_spm.load_plans_from_cursor_cache('4mcr18aqntpkq') )
PL/SQL procedure successfully completed.
 
SQL> select * from dba_sql_plan_baselines;
 
            SIGNATURE SQL_HANDLE             SQL_TEXT                       PLAN_NAME                        CREATOR   ORIGIN                          PARSING_SCHEMA_NAME   DESCRIPTION   VERSION      CREATED                           LAST_MODIFIED                     LAST_EXECUTED   LAST_VERIFIED   ENABLED   ACCEPTED   FIXED   REPRODUCED   AUTOPURGE   ADAPTIVE     OPTIMIZER_COST MODULE                   ACTION     EXECUTIONS   ELAPSED_TIME   CPU_TIME   BUFFER_GETS   DISK_READS   DIRECT_WRITES   ROWS_PROCESSED   FETCHES   END_OF_FETCH_COUNT
            --------- ----------             --------                       ---------                        -------   ------                          -------------------   -----------   -------      -------                           -------------                     -------------   -------------   -------   --------   -----   ----------   ---------   --------     -------------- ------                   ------     ----------   ------------   --------   -----------   ----------   -------------   --------------   -------   ------------------
  5689790730784434204 SQL_4ef632861ab7681c   select * from DEMO where n=1   SQL_PLAN_4xxjkhsdbfu0wd5d62705   DEMO      MANUAL-LOAD-FROM-CURSOR-CACHE   DEMO                                18.0.0.0.0   27-FEB-18 09.37.55.000000000 PM   27-FEB-18 09.37.55.000000000 PM                                   YES       YES        NO      YES          YES         NO                        1 java@VM181 (TNS V1-V3)                       3           5771       4824            82            1               0                3         6

When I run the sattement again, this plan is used and DBMS_XPLAN mentions the SQL Plan BAseline that was used:


SQL> select * from DEMO where n=1;
 
  N
  -
  1
 
SQL> select * from dbms_xplan.display_cursor();
 
PLAN_TABLE_OUTPUT
-----------------
SQL_ID  4mcr18aqntpkq, child number 0
-------------------------------------
select * from DEMO where n=1
 
Plan hash value: 217077817
 
---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| DEMO_N |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("N"=1)
 
Note
-----
   - SQL plan baseline SQL_PLAN_4xxjkhsdbfu0wd5d62705 used for this statement

Now, if I drop the index, the accepted plan cannot be used:


SQL> drop index DEMO_N;
Index DEMO_N dropped.
 
SQL> select * from DEMO where n=1;
 
  N
  -
  1
 
SQL> select * from dbms_xplan.display_cursor();
 
PLAN_TABLE_OUTPUT
-----------------
SQL_ID  4mcr18aqntpkq, child number 0
-------------------------------------
select * from DEMO where n=1
 
Plan hash value: 4000794843
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| DEMO |     1 |     4 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("N"=1)
 
Note
-----
   - Failed to use SQL plan baseline for this statement

So the new note in 18c explains that there is an SQL Plan Baseline that cannot be used. Unfortunately, there is no identification of the SQL Plan baselines.


SQL> select * from dba_sql_plan_baselines;
 
            SIGNATURE SQL_HANDLE             SQL_TEXT                       PLAN_NAME                        CREATOR   ORIGIN                          PARSING_SCHEMA_NAME   DESCRIPTION   VERSION      CREATED                           LAST_MODIFIED                     LAST_EXECUTED                     LAST_VERIFIED   ENABLED   ACCEPTED   FIXED   REPRODUCED   AUTOPURGE   ADAPTIVE     OPTIMIZER_COST MODULE                   ACTION     EXECUTIONS   ELAPSED_TIME   CPU_TIME   BUFFER_GETS   DISK_READS   DIRECT_WRITES   ROWS_PROCESSED   FETCHES   END_OF_FETCH_COUNT
            --------- ----------             --------                       ---------                        -------   ------                          -------------------   -----------   -------      -------                           -------------                     -------------                     -------------   -------   --------   -----   ----------   ---------   --------     -------------- ------                   ------     ----------   ------------   --------   -----------   ----------   -------------   --------------   -------   ------------------
  5689790730784434204 SQL_4ef632861ab7681c   select * from DEMO where n=1   SQL_PLAN_4xxjkhsdbfu0w838f84a8   DEMO      AUTO-CAPTURE                    DEMO                                18.0.0.0.0   27-FEB-18 09.37.56.000000000 PM   27-FEB-18 09.37.56.000000000 PM                                                     YES       NO         NO      YES          YES         NO                        2 java@VM181 (TNS V1-V3)                       0              0          0             0            0               0                0         0
  5689790730784434204 SQL_4ef632861ab7681c   select * from DEMO where n=1   SQL_PLAN_4xxjkhsdbfu0wd5d62705   DEMO      MANUAL-LOAD-FROM-CURSOR-CACHE   DEMO                                18.0.0.0.0   27-FEB-18 09.37.55.000000000 PM   27-FEB-18 09.37.55.000000000 PM   27-FEB-18 09.37.55.000000000 PM                   YES       YES        NO      YES          YES         NO                        1 java@VM181 (TNS V1-V3)                       3           5771       4824            82            1               0                3         6

So, because the accepted plan was not able to reproduce, because the index has been dropped, the new plan was captured but not accepted.

Note that if I re-create the index but with a different name, then the accepted SQL Plan cannot be used either:


SQL> create index DEMO_XXX on DEMO(n);
Index DEMO_XXX created.
 
SQL> select * from DEMO where n=1;
 
  N
  -
  1
 
SQL> select * from dbms_xplan.display_cursor();
 
PLAN_TABLE_OUTPUT
-----------------
SQL_ID  4mcr18aqntpkq, child number 0
-------------------------------------
select * from DEMO where n=1
 
Plan hash value: 1306684165
 
-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| DEMO_XXX |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("N"=1)
 
Note
-----
   - Failed to use SQL plan baseline for this statement

So, it is not a bad idea to monitor the SQL PLan Baseline which did not reproduce. We can get them from the ‘baseline_repro_fail’ mention in OTHER_XML:


SQL> select sql_id,other_xml from v$sql_plan where other_xml like '%baseline_repro_fail%';
 
SQL_ID          OTHER_XML                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
------          ---------                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
4mcr18aqntpkq   <other_xml><info type="db_version">18.0.0.0</info><info type="parse_schema"><![CDATA["DEMO"]]></info><info type="plan_hash_full">211349514</info><info type="plan_hash">1306684165</info><info type="plan_hash_2">211349514</info><info type="baseline_repro_fail" note="y">yes</info><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('18.1.0')]]></hint><hint><![CDATA[DB_VERSION('18.1.0')]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[INDEX(@"SEL$1" "DEMO"@"SEL$1" ("DEMO"."N"))]]></hint></outline_data></other_xml>

From the SQL_ID I can get the SIGNATURE used by SQL Plan Management:


SQL> select sql_id,exact_matching_signature from v$sql where sql_id='4mcr18aqntpkq';
 
SQL_ID            EXACT_MATCHING_SIGNATURE
------            ------------------------
4mcr18aqntpkq          5689790730784434204

And then the SQL Plan Baselines:


SQL> select * from dba_sql_plan_baselines where signature=5689790730784434204;
 
            SIGNATURE SQL_HANDLE             SQL_TEXT                       PLAN_NAME                        CREATOR   ORIGIN                          PARSING_SCHEMA_NAME   DESCRIPTION   VERSION      CREATED                           LAST_MODIFIED                     LAST_EXECUTED                     LAST_VERIFIED   ENABLED   ACCEPTED   FIXED   REPRODUCED   AUTOPURGE   ADAPTIVE     OPTIMIZER_COST MODULE                   ACTION     EXECUTIONS   ELAPSED_TIME   CPU_TIME   BUFFER_GETS   DISK_READS   DIRECT_WRITES   ROWS_PROCESSED   FETCHES   END_OF_FETCH_COUNT
            --------- ----------             --------                       ---------                        -------   ------                          -------------------   -----------   -------      -------                           -------------                     -------------                     -------------   -------   --------   -----   ----------   ---------   --------     -------------- ------                   ------     ----------   ------------   --------   -----------   ----------   -------------   --------------   -------   ------------------
  5689790730784434204 SQL_4ef632861ab7681c   select * from DEMO where n=1   SQL_PLAN_4xxjkhsdbfu0w0c98f00a   DEMO      AUTO-CAPTURE                    DEMO                                18.0.0.0.0   27-FEB-18 10.02.07.000000000 PM   27-FEB-18 10.02.07.000000000 PM                                                     YES       NO         NO      YES          YES         NO                        1 java@VM181 (TNS V1-V3)                       0              0          0             0            0               0                0         0
  5689790730784434204 SQL_4ef632861ab7681c   select * from DEMO where n=1   SQL_PLAN_4xxjkhsdbfu0w838f84a8   DEMO      AUTO-CAPTURE                    DEMO                                18.0.0.0.0   27-FEB-18 10.02.07.000000000 PM   27-FEB-18 10.02.07.000000000 PM                                                     YES       NO         NO      YES          YES         NO                        2 java@VM181 (TNS V1-V3)                       0              0          0             0            0               0                0         0
  5689790730784434204 SQL_4ef632861ab7681c   select * from DEMO where n=1   SQL_PLAN_4xxjkhsdbfu0wd5d62705   DEMO      MANUAL-LOAD-FROM-CURSOR-CACHE   DEMO                                18.0.0.0.0   27-FEB-18 10.02.06.000000000 PM   27-FEB-18 10.02.06.000000000 PM   27-FEB-18 10.02.06.000000000 PM                   YES       YES        NO      YES          YES         NO                        1 java@VM181 (TNS V1-V3)                       3           4634       4210            75            1               0                3         6

Now it is easy to look at the SPM baselines to understand why it did not reproduce:


SQL> select * from dbms_xplan.display_sql_plan_baseline('SQL_4ef632861ab7681c',format=>'basic');
 
PLAN_TABLE_OUTPUT
-----------------
 
--------------------------------------------------------------------------------
SQL handle: SQL_4ef632861ab7681c
SQL text: select * from DEMO where n=1
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_4xxjkhsdbfu0w0c98f00a         Plan id: 211349514
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------
 
Plan hash value: 1306684165
 
-------------------------------------
| Id  | Operation        | Name     |
-------------------------------------
|   0 | SELECT STATEMENT |          |
|   1 |  INDEX RANGE SCAN| DEMO_XXX |
-------------------------------------
 
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_4xxjkhsdbfu0w838f84a8         Plan id: 2207220904
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------
 
Plan hash value: 4000794843
 
----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| DEMO |
----------------------------------
 
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_4xxjkhsdbfu0wd5d62705         Plan id: 3587581701
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD-FROM-CURSOR-CACHE
Plan rows: From dictionary
--------------------------------------------------------------------------------
 
Plan hash value: 217077817
 
-----------------------------------
| Id  | Operation        | Name   |
-----------------------------------
|   0 | SELECT STATEMENT |        |
|   1 |  INDEX RANGE SCAN| DEMO_N |
-----------------------------------
 

Now we can see the unaccepted plan using index DEMO_XXX and the accepted one using index DEMO_N. If we add format=>’+outline’ we can even see that they index the same column. REady to accept the new plan and remove the old one.

In conclusion: check you V$SQL_PLAN.OTHER_XML for info type=”baseline_repro_fail” in 18c and you can do some housekeeping on SQL Plan baselines which do not reproduce. Because if you accepted a plan which cannot reproduce, you may have a problem, and better address this pro-actively.

Post Views: 1,200
Thumbnail [60x60]
by
Oracle Team