Skip to Main Content
  • Questions
  • Ways to find why a SQL Plan Baseline is not being used

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, AnĂ­bal.

Asked: June 10, 2021 - 12:58 pm UTC

Last updated: November 19, 2021 - 12:58 am UTC

Version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Viewed 1000+ times

You Asked

Hi Chris/Connor. We have a production 12c Database (12.2.0.1) and have enabled a particular SQL Plan Baseline for a high-load query, but, for some reason, it's not being used. Baseline is ENABLED and FIXED, and REPRODUCED column reads YES, but I can't see a date on LAST_EXECUTED nor in LAST_REPRODUCED and neither can I see the plan name in the notes when executing DBMS_XPLAN.DISPLAY_CURSOR or so. The plan is the only one in the baseline and plan history. I have checked that tables and indexes named there exist and are valid (and visible for indexes), no remote tables are being referenced, and the SQL is being executed from the same schema specified in PARSING_SCHEMA_NAME of DBA_SQL_PLAN_BASELINES.

I want to know what options do I have to determine why the plan is not being executed (i.e. a data dictionary view that shows why a particular plan is not being used or so). Does the optimizer save somewhere its decision for not using a sql plan baseline? Thanks in advance for your help.


and Connor said...

Try this to get more info

- alter session set events 'trace [SQL_Compiler.*]';
- (run your query)
- select * from table(dbms_xplan.display_cursor());

In the trace file, there should be a section "SPM Plan Dump" - that often contains more information about why we did use a baseline.

If you get no joy from that, a good option is to get a SQL test case via

SQL> variable tc clob;
SQL> begin
  2    dbms_sqldiag.export_sql_testcase(
  3    directory=>'TEMP',
  4    sql_id=>'your-sql-id',
  5    testcase=>:tc);
  6  end;
  7  /

PL/SQL procedure successfully completed.


That gives a thorough analysis of the entire execution process and can be uploaded to support with an SR

Rating

  (3 ratings)

Comments

from Trace File Dump contents

Rajeshwaran Jeyabal, June 22, 2021 - 3:16 am UTC

...
In the trace file, there should be a section "SPM Plan Dump"
...

So here is my Test case from 18c database.
drop table emp purge;
create table emp as select * from scott.emp;
alter table emp add constraint emp_pk primary key(empno);

variable x number
exec :x := 7499;
set serveroutput off
select * from emp where empno = :x;
select * from table( dbms_xplan.display_cursor );

variable n number
begin 
 :n := dbms_spm.load_plans_from_cursor_cache( 
  sql_id =>'0cytxdnqq3h5w' ,
  plan_hash_value =>4024650034 );
end;
/
select * from emp where empno = :x; 
select * from table( dbms_xplan.display_cursor );

alter index emp_pk invisible;
select * from emp where empno = :x; 
select * from table( dbms_xplan.display_cursor );

demo@XEPDB1> alter session set events 'trace [SQL_Compiler.*]';

Session altered.

demo@XEPDB1> select * from emp where empno = :x;

     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-1981       1600        300         30

demo@XEPDB1> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  0cytxdnqq3h5w, child number 1
-------------------------------------
select * from emp where empno = :x

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"=:X)

Note
-----
   - Failed to use SQL plan baseline for this statement


22 rows selected.

So where will be the trace file generated,? dont see it available in "default Trace File location" listed from v$diag_info - so how does one know why the optimizer Failed to use SQL plan baseline for this statement - does it save the reason some where in the dictionary ?
Connor McDonald
June 25, 2021 - 3:27 am UTC

I did exactly your steps...

SQL> select value
  2  from   v$diag_info
  3  where  name = 'Default Trace File';

VALUE
----------------------------------------------------------
----------------
C:\ORACLE\diag\rdbms\db19\db19\trace\db19_ora_16244.trc



SQL> drop table emp purge;

Table dropped.

SQL> create table emp as select * from scott.emp;

Table created.

SQL> alter table emp add constraint emp_pk primary key(empno);

Table altered.

SQL> variable x number
SQL> exec :x := 7499;

PL/SQL procedure successfully completed.

SQL> set serveroutput off
SQL> select * from emp where empno = :x;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

SQL> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
----------------
SQL_ID  0cytxdnqq3h5w, child number 0
-------------------------------------
select * from emp where empno = :x

Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=:X)


19 rows selected.

SQL>
SQL> variable n number
SQL> begin
  2   :n := dbms_spm.load_plans_from_cursor_cache(
  3    sql_id =>'0cytxdnqq3h5w' ,
  4    plan_hash_value =>4024650034 );
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select * from emp where empno = :x;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

SQL> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
----------------
SQL_ID  0cytxdnqq3h5w, child number 1
-------------------------------------
select * from emp where empno = :x

Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=:X)

Note
-----
   - SQL plan baseline SQL_PLAN_3m6nau6140hmh447b331a used for this statement


23 rows selected.

SQL> alter session set events 'trace [SQL_Compiler.*]';

Session altered.

SQL> alter index emp_pk invisible;

Index altered.

SQL> select * from emp where empno = :x;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

SQL> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
----------------
SQL_ID  0cytxdnqq3h5w, child number 1
-------------------------------------
select * from emp where empno = :x

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    37 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"=:X)

Note
-----
   - Failed to use SQL plan baseline for this statement

SQL> select value
  2  from   v$diag_info
  3  where  name = 'Default Trace File';

VALUE
------------------------------------------------------------------------------------------------------------------
----------------
C:\ORACLE\diag\rdbms\db19\db19\trace\db19_ora_35180.trc

SQL>


========

------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
  parse_schema name        : MCDONAC
  plan_baseline signature  : 4150782639013380720
  plan_baseline plan_id    : 1148924698
  plan_baseline hintset    :
    hint num  1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS 
    hint num  2 len 35 text: OPTIMIZER_FEATURES_ENABLE('19.1.0') 
    hint num  3 len 20 text: DB_VERSION('19.1.0') 
    hint num  4 len  8 text: ALL_ROWS 
    hint num  5 len 22 text: OUTLINE_LEAF(@"SEL$1") 
    hint num  6 len 52 text: INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO")) 

SPM: baseline plan:
 
============
Plan Table
============
-----------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name    | Rows  | Bytes | Cost  | Time      |
-----------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |         |       |       |     1 |           |
| 1   |  TABLE ACCESS BY INDEX ROWID | EMP     |     1 |    37 |     1 |  00:00:01 |
| 2   |   INDEX UNIQUE SCAN          | EMP_PK  |     1 |       |     0 |           |
-----------------------------------------------+-----------------------------------+
Query Block Name / Object Alias (identified by operation id):
------------------------------------------------------------
 1 - SEL$1                / "EMP"@"SEL$1"
 2 - SEL$1                / "EMP"@"SEL$1"
------------------------------------------------------------
Predicate Information:
----------------------
2 - access("EMPNO"=:X)
Column Projection Information (identified by operation id):
-----------------------------------------------------------

SPM: generated non-matching plan:

----- Explain Plan Dump -----
----- Plan Table -----
 
============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |         |       |       |     3 |           |
| 1   |  TABLE ACCESS FULL | EMP     |     1 |    37 |     3 |  00:00:01 |
-------------------------------------+-----------------------------------+




Reproducing plan

Andrew Markiewicz, November 17, 2021 - 9:31 pm UTC

Connor,
I tried the SQL compiler trace event. Looking at the trace file I can see that the optimizer attempts to reproduce the plan in the baseline. It has two attempts and then determines it cannot reproduce the plan and so rejects the baseline.

Not sure what I am missing, but isn't the purpose of the baseline to inform the optimizer of the plan it should use? If the optimizer could parse and obtain the plan in the baseline, we would not need the baseline.
Can you clear up the process of "reproducing" the plan?

Thanks

SPM: kkopmCheckSmbUpdate (enter) xscP=0x7f0d08d998f0, ciP=0xc1dff6e0, dtCtx=0x7f0d0e365d50, smoInfo=(nil), sig=10432042122727988865, planId=3472127703
SPM: kkopmCheckSmbUpdate (enter) xscP=0x7f0d08d998f0, ciP=0xc1dff6e0, dtCtx=0x7f0d0e365d50, smoInfo=(nil), sig=10432042122727988865, planId=3472127703
SPM: statement found in SMB
SPM: finding a match for the generated plan, planId = 3472127703
SPM: setup to add new plan to existing plan baseline, sig = 10432042122727988865, planId = 3472127703
SPM: sql stmt=select
SPM: planId's of plan baseline are: 3083109073
SPM: using qksan to reproduce, cost and select accepted plan, sig = 10432042122727988865 cntRepro = 0
SPM: plan reproducibility round 1 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 3083109073
SPM: planId in plan baseline = 3083109073, planId of reproduced plan = 4109224122
------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
SPM: baseline plan:
SPM: generated non-matching plan:
------- END SPM Plan Dump -------
SPM: plan reproducibility round 2 (hinted OFE only)
SPM: using qksan to reproduce accepted plan, planId = 3083109073
SPM: planId in plan baseline = 3083109073, planId of reproduced plan = 1696282644
------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
SPM: baseline plan:
SPM: generated non-matching plan:
------- END SPM Plan Dump -------
SPM: couldn't reproduce any enabled+accepted plan so using the cost-based plan, planId = 3472127703
SPM: kkopmUnsupportedOrFakeBinds - pos=1 oac=0x11bbf3ea8 dty=1 oacflg=0x3 oacfl2=0x1000010 bnd=0xc0ab1b20 bndfir=(nil) bndflgs=0x20 name=1
SPM: using qksan to check for successful recursive parse
SPM: recursive parse succeeded, sig = 10432042122727988865, new planId = 3472127703
SPM: kkopmCheckSmbUpdate (enter) xscP=0x7f0d08c83130, ciP=0x11bbf35b8, dtCtx=0x7f0d0e365d50, smoInfo=(nil), sig=10432042122727988865, planId=3472127703
SPM: add new plan: sig = 10432042122727988865, planId = 3472127703
SPM: failed to auto-capture, sig = 10432042122727988865, planId = 3472127703
SPM: kkopmCheckSmbUpdate (enter) xscP=0x7f0d08c83130, ciP=0x11bbf35b8, dtCtx=0x7f0d0e365d50, smoInfo=(nil), sig=10432042122727988865, planId=3472127703


Connor McDonald
November 18, 2021 - 3:06 am UTC

SPM stores a plan that is our preference for use. It does not stop the standard parse.

So a query arrives, we parse it.

We now see if that plan we just came up with matches any existing (accepted) plans we've stored with SPM.

If it does, we go ahead and use it
If it does not, we store it as an un-accepted plan, and choose one of the accepted plans.

But what if the *accepted* plan we want to use can *not* be used.

eg You stored a plan that used an index, and the index is no longer present. We *want* to use an accepted plan, but we cant. We "failed to reproduce" the plan.

Reproducing plan 2

Andrew Markiewicz, November 18, 2021 - 3:35 pm UTC

Thanks for the response Connor.

The missing index makes sense as to why a plan could not be used.

When it parses the first time does it use the exact plan in the baseline as a guide and the parsing is just checking it? Or does it parse the SQL anew on its own and see if it can match the baseline plan?

If it's the former then the process makes sense.
When the baseline plan is rejected/not reproduced then there must be something that is inconsistent in the plan vs what is currently accessible to the optimizer (i.e. tables, indexes, view definitions, embedded hints(?), etc)

I ran the explain plan using the pretend match setting to see what might be causing the issue. The hints section is showing that some embedded hints (in views the query uses) are not accepted because of the ubiquitous IGNORE_OPTIM_EMBEDDED_HINTS hint.
I was able to originally get the baseline by generating the plan that accepted the NL_AJ hints. Wondering why the optimizer ignores them when trying to reproduce.

Full disclosure: I am on SE so SPM is limited.
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production.

alter session set "_sql_plan_management_control"=4;

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 37 (U - Unused (37))
---------------------------------------------------------------------------
 
   0 -  SEL$08FAAEB1
         U -  ANSI_REARCH(@"SEL$508290EB") / duplicate hint
 
   0 -  SEL$0B4C6533
         U -  ANSI_REARCH(@"SEL$595B15A4") / duplicate hint
 
   0 -  SEL$147ADC81
         U -  ANSI_REARCH(@"SEL$487D1ADF") / duplicate hint
 
   0 -  SEL$29AE05B6
         U -  ANSI_REARCH(@"SEL$D148698D") / duplicate hint
 
   0 -  SEL$39
         U -  NL_AJ / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
 
   0 -  SEL$392B692C
         U -  ANSI_REARCH(@"SEL$672C2C75") / duplicate hint
 
   0 -  SEL$3DD7C428
         U -  ANSI_REARCH(@"SEL$CF599CF4") / duplicate hint
 
   0 -  SEL$41
         U -  NL_AJ / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
 
   0 -  SEL$45C75E67
         U -  ANSI_REARCH(@"SEL$30AD4F61") / duplicate hint
 
   0 -  SEL$594EFAE2
         U -  ANSI_REARCH(@"SEL$D3E65D98") / duplicate hint
 
   0 -  SEL$5B5E66FB
         U -  ANSI_REARCH(@"SEL$E9A21A46") / duplicate hint
 
   0 -  SEL$632EACFE
         U -  ANSI_REARCH(@"SEL$456040EA") / duplicate hint
 
   0 -  SEL$63725446
         U -  ANSI_REARCH(@"SEL$096538EE") / duplicate hint
 
   0 -  SEL$756D1D86
         U -  ANSI_REARCH(@"SEL$477373A1") / duplicate hint
 
   0 -  SEL$7798E38D
         U -  ANSI_REARCH(@"SEL$B79B78E7") / duplicate hint
 
   0 -  SEL$7B2B21FE
         U -  ANSI_REARCH(@"SEL$2809FFFB") / duplicate hint
 
   0 -  SEL$7C856269
         U -  ANSI_REARCH(@"SEL$8FD3FEB1") / duplicate hint
 
   0 -  SEL$85772F3E
         U -  ANSI_REARCH(@"SEL$E73D19AC") / duplicate hint
 
   0 -  SEL$90FFC543
         U -  ANSI_REARCH(@"SEL$A33441D9") / duplicate hint
 
   0 -  SEL$961AFFD8
         U -  ANSI_REARCH(@"SEL$A2018766") / duplicate hint
 
   0 -  SEL$9E085BB8
         U -  ANSI_REARCH(@"SEL$7BF1F0EF") / duplicate hint
 
   0 -  SEL$9FD2D130
         U -  ANSI_REARCH(@"SEL$C9FA280B") / duplicate hint
 
   0 -  SEL$A0238BFB
         U -  ANSI_REARCH(@"SEL$EC4C49FA") / duplicate hint
 
   0 -  SEL$A210340B
         U -  ANSI_REARCH(@"SEL$4C9E5BE1") / duplicate hint
 
   0 -  SEL$A6AE2347
         U -  ANSI_REARCH(@"SEL$CF041723") / duplicate hint
 
   0 -  SEL$AEBBDBD1
         U -  ANSI_REARCH(@"SEL$94E151C1") / duplicate hint
 
   0 -  SEL$B9DC9CB3
         U -  ANSI_REARCH(@"SEL$9068A70A") / duplicate hint
 
   0 -  SEL$BF8B1ACF
         U -  ANSI_REARCH(@"SEL$5765889F") / duplicate hint
 
   0 -  SEL$DE1187AC
         U -  ANSI_REARCH(@"SEL$25CCDAA6") / duplicate hint
 
   0 -  SEL$E00CB90A
         U -  ANSI_REARCH(@"SEL$FFA93175") / duplicate hint
 
   0 -  SEL$E597B1CC
         U -  ANSI_REARCH(@"SEL$70FB0DD7") / duplicate hint
 
   0 -  SEL$EC7A253B
         U -  ANSI_REARCH(@"SEL$7661CCD7") / duplicate hint
 
   0 -  SEL$EF376757
         U -  ANSI_REARCH(@"SEL$B6238B29") / duplicate hint
 
   0 -  SEL$F2BA44E1
         U -  ANSI_REARCH(@"SEL$4229E3F1") / duplicate hint
 
   0 -  SEL$F89DB8CB
         U -  ANSI_REARCH(@"SEL$6AF57419") / duplicate hint
 
 165 -  SEL$37
         U -  NL_AJ / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
 
 174 -  SEL$43
         U -  NL_AJ / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
 
Note
-----
   - SQL plan baseline "SQL_PLAN_91jhg187ju7n1cef476d7 (pretend match)" used for this statement

Connor McDonald
November 19, 2021 - 12:58 am UTC

"Or does it parse the SQL anew on its own and see if it can match the baseline plan?"

We parse because SPM is not just about "Hey, you have liked this plan in the past, so thats all we care about"

It is also about "Hey, we've got this really cool new plan that we think is better. Dont panic we wont use it because you told us not to, but we'll hang onto it for you to look at later"

(or even automatically replace it after doing some performance tests)

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.