Mohamed Houri’s Oracle Notes

March 11, 2018

Which execution plan will be used?

Filed under: Sql Plan Managment — hourim @ 4:08 pm

Following a question raised by Franck Pachot on Twitter, I was surprised that 43% of the answers were false. The question was terribly simple : an SQL Plan Baseline contains two execution plans:

  • Plan 1: is enabled but not accepted
  • Plan 2: is not enabled but accepted

If the Cost Based Optimiser comes up with Plan 1, which execution plan will the query finally be allowed to use?

There are very simple rules to know when it comes to the interaction between the CBO and the presence of SPM baselined plans:

  • Not enabled SPM plans will not be used regardless of their acceptance status
  • Enabled SPM plans will not be used if they are not accepted

From Franck’s question we understand that SPM Plan 2 is not enabled; so we will rule it out from the correct answer possibilities. We can see as well that SPM Plan 1 is not accepted which makes it unusable by the CBO. Since both SPM execution plans are not usable because of their enabled or accepted properties the CBO will use the plan it will come up with : Plan 1 in this case.

Here below the demonstration (the model can be found here):

alter session set cursor_sharing= force;
alter session set optimizer_capture_sql_plan_baselines=true;

select count(1) from t_acs where n2 = 1e6;
select count(1) from t_acs where n2 = 1e6;

alter session set optimizer_capture_sql_plan_baselines=false;

select  sql_handle, plan_name, enabled, accepted
from dba_sql_plan_baselines
where signature = '7542536418437113777';

SQL_HANDLE             PLAN_NAME                      ENA ACC
---------------------- ------------------------------ --- ---
SQL_68ac78e9166427b1   SQL_PLAN_6jb3sx4b689xj3069e6f9 YES YES

DECLARE
   l_p  PLS_INTEGER;
BEGIN
  l_p := DBMS_SPM.alter_sql_plan_baseline(
            sql_handle      => 'SQL_68ac78e9166427b1',
            plan_name       => 'SQL_PLAN_6jb3sx4b689xj3069e6f9',
            attribute_name  => 'enabled',
            attribute_value => 'NO');
END;
/

select  sql_handle, plan_name, enabled, accepted
from dba_sql_plan_baselines
where signature = '7542536418437113777';

SQL_HANDLE                     PLAN_NAME                      ENA ACC
------------------------------ ------------------------------ --- ---
SQL_68ac78e9166427b1           SQL_PLAN_6jb3sx4b689xj3069e6f9 NO  YES

We have our Plan 2: Accepted but not Enabled.

Next I will generate Plan 1: Enabled but not Accepted

select count(1) from t_acs where n2 = 1;

SQL> select count(1) from t_acs where n2 = 1;

  COUNT(1)
----------
         1

SQL> select * from table(dbms_xplan.display_cursor)


SQL_ID  7ck8k47bnqpnv, child number 1

An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

NOTE: cannot fetch plan for SQL_ID: 7ck8k47bnqpnv, CHILD_NUMBER: 1
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

Whenever I see this error I know that it has been raised because the CBO comes up with an execution plan that doesn’t match one of the SPM baselined plans:

select  sql_handle, plan_name, enabled, accepted
from dba_sql_plan_baselines
where signature = '7542536418437113777';

SQL_HANDLE                     PLAN_NAME                      ENA ACC
------------------------------ ------------------------------ --- ---
SQL_68ac78e9166427b1           SQL_PLAN_6jb3sx4b689xj3069e6f9 NO  YES
SQL_68ac78e9166427b1           SQL_PLAN_6jb3sx4b689xjf5f8c88e YES NO

Here we go: we have an SPM baseline with two execution plans exactly as asked by Franck

select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_68ac78e9166427b1'));
 
--------------------------------------------------------------------------------
SQL handle: SQL_68ac78e9166427b1
SQL text: select count(:"SYS_B_0") from t_acs where n2 = :"SYS_B_1"
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_6jb3sx4b689xjf5f8c88e         Plan id: 4126722190
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------
 
Plan hash value: 1687207741
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |            |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T_ACS_IDX1 |     1 |     3 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("N2"=:SYS_B_1)
 
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_6jb3sx4b689xj3069e6f9         Plan id: 812246777
Enabled: NO      Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------
 
Plan hash value: 535703726
----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     3 |   628   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T_ACS |  1099K|  3219K|   628   (3)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("N2"=:SYS_B_1)

Next I will execute the above query with a bind variable value that will favor the index range scan plan which is present in the SPM baseline, is enabled but not accepted:

SQL> @53on
alter session set events '10053 trace name context forever, level 1';
alter session set "_optimizer_trace"=all;

select count(1) from t_acs where n2 = 100;

SQL_ID  7ck8k47bnqpnv, child number 1

Plan hash value: 1687207741
--------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes |
--------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |
|   1 |  SORT AGGREGATE   |            |     1 |     3 |
|*  2 |   INDEX RANGE SCAN| T_ACS_IDX1 |   100 |   300 |
--------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N2"=:SYS_B_1)

SQL> @53off
alter session set events '10053 trace name context off';

The absence of the Note at the bottom of the above execution plan indicates that the CBO didn’t use any execution plan from the SPM baseline but it uses rather the execution plan it comes up with during the compilation phase. In the corresponding 10053 trace file we can read the following lines:

SPM: statement found in SMB

SPM: finding a match for the generated plan, planId = 4126722190
SPM: plan baseline non-existant or empty (no accepted plans) so using cost-based plan, planId = 4126722190

The developer at Oracle could not be clearer than this : non-existant or empty (no accepted plans) so using cost-based plan

Advertisements

1 Comment »

  1. Hello Mohamed,
    Thank you , I did not know the problem between display cursor and spm, is it a bug ?
    Have a good day

    Comment by tgascard — March 13, 2018 @ 8:32 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: