Failed to use SQL plan baseline for this statement
What is baseline? Baseline is a benchmark threshold for a job or sql or task etc.. for marking the performance base level.
It is essential for every database to have a baseline to determine the actual capability of a sql query to execute with in a given amount of time with available resources.
If there is no baseline, then DBA may have infinite never ending process for tuning a sql.
Example: If an adhoc query executes in 5 seconds, then the baseline for that query is 5 seconds. After couple of days, the same query take 1 minute to execute due to changes in the execution plan. In this case, baseline can be checked to get the good plan or initial plan which took 5 seconds and feed optimizer to accept and use the good plan.
During an upgrade from 11g to 12c or 12c to 19c or 19c to 21c, there may be situations where the execution plans may change in the upgraded version due to additional features and enhancements.
There may be cases where the baseline plan need to be exported from preupgrade version of database to newly upgraded database.
If the baseline is not reproducible, then it may throw the below error due to below main reasons,
Failed to use SQL plan baseline for this statement
- Object id used in the query changes
- Object is no longer available (Index invisible or dropped)
- Plan is not reproducible in new version due to invalid hints or unused hints in outline data
- Bugs
Enable auto baseline capture for the sql’s which are executed in the database.
SQL> show parameter baseline
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL> ALTER system set optimizer_capture_sql_plan_baselines=TRUE;
System altered.
Create a sample table and select the records with a where clause. Note that the columns are not indexed. This is a new query which execute for the first time in the database.
SQL> CREATE table dspm AS SELECT * from dba_source;
Table created
SQL> SELECT owner from dspm WHERE type='PROCEDURE' and owner not in ('SYS','SYSTEM');
OWNER
--------------------------------------------------------------------------------------------------------------------------------
OUTLN
OUTLN
OUTLN
OUTLN
OUTLN
OUTLN
OUTLN
OUTLN
OUTLN
DBSNMP
DBSNMP
DBSNMP
DBSNMP
Generate the execution plan for the previous query
SQL> set lines 200 pages 1000
SQL> SELECT * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b6kqbg298h1j1, child number 0
-------------------------------------
SELECT owner from dspm WHERE type='PROCEDURE' and owner not in
('SYS','SYSTEM')
Plan hash value: 3837617297
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1570 (100)| |
|* 1 | TABLE ACCESS FULL| DSPM | 5 | 370 | 1570 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("TYPE"='PROCEDURE' AND "OWNER"<>'SYS' AND
"OWNER"<>'SYSTEM'))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
24 rows selected.
Create a new index for the table column which is used in ‘where’ clause of the query and select the data.
SQL> CREATE index dtype on dspm(type);
Index created.
SQL> SELECT owner from dspm WHERE type='PROCEDURE' and owner not in ('SYS','SYSTEM');
OWNER
--------------------------------------------------------------------------------------------------------------------------------
OUTLN
OUTLN
OUTLN
OUTLN
Generate the new execution plan with newly created index.
SQL> SELECT * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b6kqbg298h1j1, child number 0
-------------------------------------
SELECT owner from dspm WHERE type='PROCEDURE' and owner not in
('SYS','SYSTEM')
Plan hash value: 2382681726
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1401 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DSPM | 5 | 370 | 1401 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DTYPE | 40538 | | 149 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("OWNER"<>'SYS' AND "OWNER"<>'SYSTEM'))
2 - access("TYPE"='PROCEDURE')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
25 rows selected.
There are two plans generated for sql ‘b6kqbg298h1j1’. Check the available baselines for this new query. The plan is enabled, accepted and not fixed. This means that, the plan will not be statically used for the query and plan may get changed if a better one is available.
SQL> col SQL_HANDLE for a15
SQL> col SQL_TEXT for a20
SQL> col PLAN_NAME for a20
SQL> col VERSION for a20
SQL> SELECT SIGNATURE,SQL_HANDLE,SQL_TEXT,PLAN_NAME,ENABLED,ACCEPTED,FIXED,ELAPSED_TIME,VERSION from dba_sql_plan_baselines;
SIGNATURE SQL_HANDLE SQL_TEXT PLAN_NAME ENA ACC FIX ELAPSED_TIME VERSION
---------- --------------- -------------------- -------------------- --- --- --- ------------ --------------------
7.0999E+18 SQL_6287e50c216 SELECT owner from ds SQL_PLAN_651z51hhqt3 YES YES NO 0 19.0.0.0.0
c8c87 pm WHERE type='PROCE 4787226bf8
DURE' and owner not
in ('SYS','SYSTEM')
Make the index invisible
SQL> ALTER index dtype unusable;
Index altered.
SQL> SELECT owner from dspm WHERE type='PROCEDURE' and owner not in ('SYS','SYSTEM');
SELECT owner from dspm WHERE type='PROCEDURE' and owner not in ('SYS','SYSTEM')
*
ERROR at line 1:
ORA-01502: index 'SYS.DTYPE' or partition of such index is in unusable state
SQL> ALTER index dtype invisible;
Index altered.
Again execute the same query and now keenly notice the ‘Note‘ section in the plan. The subject of this blog occurred as error saying that the baseline which is available in the database has the better plan 2382681726 but cannot be used for some reason and an alternative plan 3837617297 is used which was already used before creating an index. Now you should be knowing the reason, why the plan was not used as the index dtype was marked invisible to the optimizer.
The baseline plan 2382681726 stored in the data dictionary contains the good plan though optimizer cannot pick the plan because the plan contains index range scans and optimizer cannot do index scans without an index or invisible index.
SQL> SELECT owner from dspm WHERE type='PROCEDURE' and owner not in ('SYS','SYSTEM');
OWNER
--------------------------------------------------------------------------------------------------------------------------------
OUTLN
OUTLN
OUTLN
OUTLN
OUTLN
OUTLN
OUTLN
OUTLN
OUTLN
DBSNMP
SQL> SELECT * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b6kqbg298h1j1, child number 1
-------------------------------------
SELECT owner from dspm WHERE type='PROCEDURE' and owner not in
('SYS','SYSTEM')
Plan hash value: 3837617297
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1570 (100)| |
|* 1 | TABLE ACCESS FULL| DSPM | 5 | 370 | 1570 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("TYPE"='PROCEDURE' AND "OWNER"<>'SYS' AND
"OWNER"<>'SYSTEM'))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Failed to use SQL plan baseline for this statement <===
25 rows selected.
The exact reason can be identified by v$sql_plan or dba_hist_sqlplan if history. If you notice, there are two outputs for b6kqbg298h1j1 which has recursive depth of 1. Also, for phv 2382681726, there are two objects with object id 73446 and 73451 which are both table dspm and its index dtype but for phv 3837617297, there is only one object which is the table dspm itself. To find the full reason, there is a column called OTHER_XML in v$sql_plan which gives some additional information on the plan.
SQL> SELECT sql_id,plan_hash_value,object#,depth FROM v$sql_plan where plan_hash_value=3837617297;
SQL_ID PLAN_HASH_VALUE OBJECT# DEPTH
------------- --------------- ---------- ----------
b6kqbg298h1j1 3837617297 0
b6kqbg298h1j1 3837617297 73446 1
SQL> SELECT sql_id,plan_hash_value,object#,depth FROM v$sql_plan where sql_id='b6kqbg298h1j1';
SQL_ID PLAN_HASH_VALUE OBJECT# DEPTH
------------- --------------- ---------- ----------
b6kqbg298h1j1 3837617297 0
b6kqbg298h1j1 3837617297 73446 1
b6kqbg298h1j1 2382681726 0
b6kqbg298h1j1 2382681726 73446 1
b6kqbg298h1j1 2382681726 73451 2
6 rows selected.
SQL> SELECT other_xml FROM v$sql_plan where plan_hash_value=3837617297;
OTHER_XML
--------------------------------------------------------------------------------
<other_xml><info type="has_user_tab">yes</info><info type="db_version">19.0.0.0<
/info><info type="parse_schema"><![CDATA["SYS"]]></info><info type="dynamic_samp
ling" note="y">2</info><info type="plan_hash_full">3029714510</info><info type="
plan_hash">3837617297</info><info type="plan_hash_2">3029714510</info><info type
="baseline_repro_fail" note="y">yes</info><stats type="compilation"><stat name="
bg">68</stat></stats><qb_registry><q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><
t><![CDATA[DSPM]]></t><s><![CDATA[SEL$1]]></s></h></f></q></qb_registry><outline
_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZ
ER_FEATURES_ENABLE('19.1.0')]]></hint><hint><![CDATA[DB_VERSION('19.1.0')]]></hi
nt><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hi
nt><hint><![CDATA[FULL(@"SEL$1" "DSPM"@"SEL$1")]]></hint></outline_data></other_
xml>
Get the exact matching signature of the sql
SQL> select sql_id,exact_matching_signature from v$sql where sql_id='b6kqbg298h1j1';
SQL_ID EXACT_MATCHING_SIGNATURE
------------- --------------------------------------------------------------------------------
b6kqbg298h1j1 7099895177836006535
b6kqbg298h1j1 7099895177836006535
Substitute the signature in the baseline and get the sql handle details of the spm.
SQL> col SIGNATURE for 9999999999999999999
SQL> SELECT SIGNATURE,SQL_HANDLE,SQL_TEXT,PLAN_NAME,ENABLED,ACCEPTED,FIXED,ELAPSED_TIME,VERSION from dba_sql_plan_baselines WHERE signature=7099895177836006535;
SIGNATURE SQL_HANDLE SQL_TEXT PLAN_NAME ENA ACC FIX ELAPSED_TIME VERSION
-------------------- --------------- -------------------- -------------------- --- --- --- -------------- --------------------
7099895177836006535 SQL_6287e50c216 SELECT owner from ds SQL_PLAN_651z51hhqt3 YES YES NO 0 19.0.0.0.0
c8c87 pm WHERE type='PROCE 4787226bf8
DURE' and owner not
in ('SYS','SYSTEM')
7099895177836006535 SQL_6287e50c216 SELECT owner from ds SQL_PLAN_651z51hhqt3 YES NO NO 0 19.0.0.0.0
c8c87 pm WHERE type='PROCE 47b495c64e
DURE' and owner not
in ('SYS','SYSTEM')
Get the comparison between two different plans and the reason behind the baseline not being used.
SQL> SELECT * from table(dbms_xplan.display_sql_plan_baseline('SQL_6287e50c216c8c87',format=>'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_6287e50c216c8c87
SQL text: SELECT owner from dspm WHERE type='PROCEDURE' and owner not in
('SYS','SYSTEM')
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_651z51hhqt34787226bf8 Plan id: 2267180024
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Outline Data from SMB:
/*+
BEGIN_OUTLINE_DATA
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "DSPM"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "DSPM"@"SEL$1" ("DSPM"."TYPE"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
--------------------------------------------------------------------------------
Plan hash value: 2382681726
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 370 | 1401 (1)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DSPM | 5 | 370 | 1401 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DTYPE | 40538 | | 149 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DSPM@SEL$1
2 - SEL$1 / DSPM@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "DSPM"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "DSPM"@"SEL$1" ("DSPM"."TYPE"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"<>'SYS' AND "OWNER"<>'SYSTEM')
2 - access("TYPE"='PROCEDURE')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OWNER"[VARCHAR2,128]
2 - "DSPM".ROWID[ROWID,10]
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[DSPM]]></t><s><![CDATA[SEL$1
]]></s></h></f></q>
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_651z51hhqt347b495c64e Plan id: 3029714510
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Outline Data from SMB:
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "DSPM"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
--------------------------------------------------------------------------------
Plan hash value: 3837617297
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 370 | 1570 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DSPM | 5 | 370 | 1570 (1)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DSPM@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "DSPM"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TYPE"='PROCEDURE' AND "OWNER"<>'SYS' AND
"OWNER"<>'SYSTEM')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OWNER"[VARCHAR2,128]
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[DSPM]]></t><s><
![CDATA[SEL$1]]></s></h></f></q>
153 rows selected.
