Infrastructure at your Service

Oracle Team

18c dbms_xplan note about failed SQL Plan Baseline

By February 27, 2018 Oracle No Comments

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.

 

Leave a Reply

Oracle Team
Oracle Team