Toad World Blog

Why my execution plan has not been shared – Part I

Apr 29, 2016 11:00:00 PM by Mohamed Houri

For most people troubleshooting SQL query performance problems is a necessity rather than a preference. Very often they are using a trial and error strategy to overcome those frustrating situations. Unfortunately the trial and error method is not reliable. This is particularly true because of the burgeoning number of possible performance root causes. When a query deviates from its normal usual accepted response time this is very probably due to a change in its execution plan. Hopefully Oracle has implemented an internal piece of code that tell us the reason for which an existing execution plan has not been shared and thereby a new one has been hard parsed. This article examines few of the most common non-sharing execution plan reasons, outlines their appearance criteria and demonstrates their occurrence via reproducible examples.

 

Tanel Poder script (nonshared.sql)

As stated above, Oracle externalises the non-sharing reasons of an execution plan into a dedicated view namedv$sql_shared_cursor having the following description:

SQL> describe v$sql_shared_cursor
Name                            Null?        Type
------------------------------- -------- ---------------
1 SQL_ID                                 VARCHAR2(13)
2 ADDRESS                                RAW(8)
3 CHILD_ADDRESS                          RAW(8)
4 CHILD_NUMBER                           NUMBER
5 UNBOUND_CURSOR                         VARCHAR2(1)
6 SQL_TYPE_MISMATCH                      VARCHAR2(1)
7 OPTIMIZER_MISMATCH                     VARCHAR2(1)
8 OUTLINE_MISMATCH                       VARCHAR2(1)
9 STATS_ROW_MISMATCH                     VARCHAR2(1)
10 LITERAL_MISMATCH                      VARCHAR2(1)
11 FORCE_HARD_PARSE                      VARCHAR2(1)
12 EXPLAIN_PLAN_CURSOR                   VARCHAR2(1)
13 BUFFERED_DML_MISMATCH                 VARCHAR2(1)
14 PDML_ENV_MISMATCH                     VARCHAR2(1)
15 INST_DRTLD_MISMATCH                   VARCHAR2(1)
16 SLAVE_QC_MISMATCH                     VARCHAR2(1)
17 TYPECHECK_MISMATCH                    VARCHAR2(1)
18 AUTH_CHECK_MISMATCH                   VARCHAR2(1)
19 BIND_MISMATCH                         VARCHAR2(1)
20 DESCRIBE_MISMATCH                     VARCHAR2(1)
21 LANGUAGE_MISMATCH                     VARCHAR2(1)
22 TRANSLATION_MISMATCH                  VARCHAR2(1)
23 BIND_EQUIV_FAILURE                    VARCHAR2(1)
24 INSUFF_PRIVS                          VARCHAR2(1)
25 INSUFF_PRIVS_REM                      VARCHAR2(1)
26 REMOTE_TRANS_MISMATCH                 VARCHAR2(1)
27 LOGMINER_SESSION_MISMATCH             VARCHAR2(1)
28 INCOMP_LTRL_MISMATCH                  VARCHAR2(1)
29 OVERLAP_TIME_MISMATCH                 VARCHAR2(1)
30 EDITION_MISMATCH                      VARCHAR2(1)
31 MV_QUERY_GEN_MISMATCH                 VARCHAR2(1)
32 USER_BIND_PEEK_MISMATCH               VARCHAR2(1)
33 TYPCHK_DEP_MISMATCH                   VARCHAR2(1)
34 NO_TRIGGER_MISMATCH                   VARCHAR2(1)
35 FLASHBACK_CURSOR                      VARCHAR2(1)
36 ANYDATA_TRANSFORMATION                VARCHAR2(1)
37 PDDL_ENV_MISMATCH                     VARCHAR2(1)
38 TOP_LEVEL_RPI_CURSOR                  VARCHAR2(1)
39 DIFFERENT_LONG_LENGTH                 VARCHAR2(1)
40 LOGICAL_STANDBY_APPLY                 VARCHAR2(1)
41 DIFF_CALL_DURN                        VARCHAR2(1)
42 BIND_UACS_DIFF                        VARCHAR2(1)
43 PLSQL_CMP_SWITCHS_DIFF                VARCHAR2(1)
44 CURSOR_PARTS_MISMATCH                 VARCHAR2(1)
45 STB_OBJECT_MISMATCH                   VARCHAR2(1)
46 CROSSEDITION_TRIGGER_MISMATCH         VARCHAR2(1)
47 PQ_SLAVE_MISMATCH                     VARCHAR2(1)
48 TOP_LEVEL_DDL_MISMATCH                VARCHAR2(1)
49 MULTI_PX_MISMATCH                     VARCHAR2(1)
50 BIND_PEEKED_PQ_MISMATCH               VARCHAR2(1)
51 MV_REWRITE_MISMATCH                   VARCHAR2(1)
52 ROLL_INVALID_MISMATCH                 VARCHAR2(1)
53 OPTIMIZER_MODE_MISMATCH               VARCHAR2(1)
54 PX_MISMATCH                           VARCHAR2(1)
55 MV_STALEOBJ_MISMATCH                  VARCHAR2(1)
56 FLASHBACK_TABLE_MISMATCH              VARCHAR2(1)
57 LITREP_COMP_MISMATCH                  VARCHAR2(1)
58 PLSQL_DEBUG                           VARCHAR2(1)
59 LOAD_OPTIMIZER_STATS                  VARCHAR2(1)
60 ACL_MISMATCH                          VARCHAR2(1)
61 FLASHBACK_ARCHIVE_MISMATCH            VARCHAR2(1)
62 LOCK_USER_SCHEMA_FAILED               VARCHAR2(1)
63 REMOTE_MAPPING_MISMATCH               VARCHAR2(1)
64 LOAD_RUNTIME_HEAP_FAILED              VARCHAR2(1)
65 HASH_MATCH_FAILED                     VARCHAR2(1)
66 PURGED_CURSOR                         VARCHAR2(1)
67 BIND_LENGTH_UPGRADEABLE               VARCHAR2(1)
68 USE_FEEDBACK_STATS                    VARCHAR2(1)
69 REASON                                CLOB
70 CON_ID                                NUMBER

This view contains 64 possible reasons for an execution plan (aka CHILD_NUMBER) to do not be shared. The first article of the instalment series examines a couple of reasons which I have seen kicking in very often in running systems. Typically, when an execution plan can not been shared its corresponding non-sharing VARCHAR2(1) column value will be set to 'Y'. This view has been engineered so that it is not easy to find the non-shared reason with a simple select statement. Hopefully Tanel Poder has developed a SQL script (nonshared.sql) which we will be using with great success all over this article.

Having described the v$sql_shared_cursor view let's now embark on the explanation of few very common reasons starting by the LOAD_OPTIMIZER_STATS reason

 

LOAD_OPTIMIZER_STATS

Oracle defines this reason as follows:

(Y|N) A hard parse is forced in order to initialize extended cursor sharing

 

The extended cursor sharing is a feature introduced by Oracle starting from release 11g. Simply put, this feature aims to compile, for a bind aware cursor, an optimal execution plan for each query execution. Extensive details about this feature can be found in Chapter 4 of the upcoming book I have co-authored. A reproducible example being worth a thousand words lets then see, below, how to produce such a kind of execution plan non-sharing reason:

SQL> select banner from v$version where rownum=1;


BANNER
------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
 
SQL> create table t1 as
  2  select
  3  rownum n1
  4  ,trunc ((rownum-1)/5) n2
  5  ,case mod(rownum, 100)
  6  when 0 then 'Hundred'
  7  else 'NotHundred'
  8  end v1
  9  from dual
 10  connect by level <= 1e4;
 
Table created.
 
SQL> create index t1_ind on t1(v1);
 
Index created.
 
SQL> begin
       dbms_stats.gather_table_stats(user
                                    , 't1'
                                    , method_opt => 'for all columns size skewonly'
                                    );
    end;
    / 

The model consists of a heap table having a single column b-tree index and a column not evenly distributed as shown below:

SQL> desc t1
           Name                            Null?    Type
           ------------------------------- -------- ---------------
    1      N1                                       NUMBER
    2      N2                                       NUMBER
    3      V1                                       VARCHAR2(10)
 
 
SQL> select v1, count(1)
  2  from t1
  3  group by v1;
 
V1           COUNT(1)
---------- ----------
Hundred           100
NotHundred       9900

Collecting statistics on t1 table  will obviously compute a Frequency histogram for the v1 column as shown in the following:

SQL> begin

  2  dbms_stats.gather_table_stats
  3  (user
  4  ,'t1'
  5  ,method_opt => 'for all columns size skewonly'
  6  );
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 

SQL> select column_name, histogram
  2  from user_tab_col_statistics
  3  where table_name = 'T1'
  4  and column_name = 'V1';
 
COLUMN_NAM HISTOGRAM
---------- ---------------
V1         FREQUENCY

At this stage of the investigation we are now ready, via the following piece of code, to give life to the LOAD_OPTIMIZER_STATSreason:

SQL> var v1 varchar2(10)

SQL> exec :v1 := 'Hundred'
 
SQL> select count(1) from t1 where v1 = :v1;
 
COUNT(1)
----------
100
 
SQL> select * from table(dbms_xplan.display_cursor);
 
SQL_ID d2h2phry5d881, child number 0
-------------------------------------
--------------------------------------------
| Id | Operation        | Name   | Rows    |
--------------------------------------------
| 0  | SELECT STATEMENT |        |         |
| 1  | SORT AGGREGATE   |        | 1       |
|* 2 | INDEX RANGE SCAN | T1_IND | 100     |
--------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
2 – access("V1"=:V1)

As it can be noticed trough the above execution plan the first select against t1 table has been honored via an index range scan path materialized by a child cursor number 0. Let's now change the bind variable value so that a new execution plan will be hard parsed as shown below (we will have to run the above query two times before seeing the extended cursor sharing kicking as explained with great details in the above mentioned book):

SQL> exec :v1 := 'NotHundred'

SQL> select count(1) from t1 where v1 = :v1;
 
COUNT(1)
----------
9900
 
SQL> select count(1) from t1 where v1 = :v1;
 
COUNT(1)
----------
9900
 
SQL> select * from table(dbms_xplan.display_cursor);
 
SQL_ID d2h2phry5d881, child number 1
------------------------------------------------
| Id | Operation            | Name   | Rows    |
------------------------------------------------
| 0  | SELECT STATEMENT     |        |         |
| 1  | SORT AGGREGATE       |        | 1       |
|* 2 | INDEX FAST FULL SCAN | T1_IND | 9900    |
------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
2 – filter("V1"=:V1)

Spot now how a new optimal execution plan has been compiled so that is better suits the new bind variable. And, finally, here's below the corresponding non-sharing reason stored by Oracle into the v$sql_shared_cursor view:

SQL> @nonshared d2h2phry5d881

Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)...
 
SQL_ID                  : d2h2phry5d881
ADDRESS                 : 00007FFB7B4B2188
CHILD_ADDRESS           : 00007FFB7B4B0CD8
CHILD_NUMBER            : 0
LOAD_OPTIMIZER_STATS    : Y
REASON                  : <ChildNode><ChildNumber>0</ChildNumber><ID>40</ID><reason>Bindmismatch(25)</reason><size>0x0</size>
                          <details>extended_cursor_sharing</details></ChildNode>
CON_ID                  : 1
 
-----------------
SQL_ID                  : d2h2phry5d881
ADDRESS                 : 00007FFB7B4B2188
CHILD_ADDRESS           : 00007FFB7B487268
CHILD_NUMBER            : 1
REASON                  :
CON_ID                  : 1
----------------- 

Hopefully with the above demonstration if you come to meet a switch of an execution plan due to theLOAD_OPTIMIZER_STATS reason then you will clearly know why Oracle has decided to hard parse your query.

 

HASH_MATCH_FAILED

Oracle defines this reason as follows:

(Y|N) No existing child cursors have the unsafe literal bind hash values required by the current cursor

 

To say the least this definition is not obvious at all. Let's try first reproducing it and then hope to be able to reverse engineering a clear definition for this reason.

QL> alter system flush shared_pool;

 
SQL> exec :v1 := 'Hundred'
 
SQL> select count(1) from t1 where v1 = :v1;
 
COUNT(1)
----------
100
 
SQL> select * from table(dbms_xplan.display_cursor);
 
SQL_ID d2h2phry5d881, child number 0
Plan hash value: 2603166377
--------------------------------------------
| Id | Operation        | Name   | Rows    |
--------------------------------------------
| 0  | SELECT STATEMENT |        |         |
| 1  | SORT AGGREGATE   |        | 1       |
|* 2 | INDEX RANGE SCAN | T1_IND | 100     |
--------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("V1"=:V1)

Let's suppose that we don't want any-more the above query to run via an index range scan path and that a full table scan path is what we do prefer. We can use a SQL Plan Baseline to achieve this desire as shown in the following:

SQL> select /*+ full(t1) */ count(1) from t1 where v1 = :v1;

 
COUNT(1)
----------
100
 
SQL> select * from table(dbms_xplan.display_cursor);
 
SQL_ID 34z8wv6bsyu6u, child number 0
 
Plan hash value: 3724264953
-------------------------------------------
| Id  | Operation        | Name | Rows    |
-------------------------------------------
| 0  | SELECT STATEMENT  |      |         |
| 1  | SORT AGGREGATE    |      | 1       |
|* 2 | TABLE ACCESS FULL | T1   | 100     |
-------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
2 – filter("V1"=:V1)

Since we have a full table scan execution plan in memory we can attach it to the original query so that it will always be run via this full table scan execution plan (Transferspm.sql is at the bottom of this article):

SQL> @Transferspm

Enter value for original_sql_id: d2h2phry5d881
Enter value for modified_sql_id: 34z8wv6bsyu6u
Enter value for plan_hash_value: 3724264953
 
PL/SQL procedure successfully completed.

With this set-up in place if we execute the initial query using an index range scan bind variable value we will see that Oracle will force this query to use the SPM full table scan baselined plan as demonstrated in the followings:

SQL> select count(1) from t1 where v1 = :v1;

 
COUNT(1)
----------
100
 
SQL> select * from table(dbms_xplan.display_cursor);
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID d2h2phry5d881, child number 0
 
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found
NOTE: cannot fetch plan for SQL_ID: d2h2phry5d881, CHILD_NUMBER: 0
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)

The above error indicates that Oracle has come up with an execution plan having a Phv2 that is not equal to the PlanId of the SPM baselined plan and that it has inserted this new plan into the SPM Baseline for future evolution. Re-executing the same query will show now that the SPM plan is, indeed, used:

SQL> select count(1) from t1 where v1 = :v1;

 
SQL_ID d2h2phry5d881, child number 0
------------------------------------
select count(1) from t1 where v1 = :v1

Plan hash value: 3724264953
-------------------------------------------
| Id | Operation        | Name | Rows     |
-------------------------------------------
| 0 | SELECT STATEMENT  |      |          |
| 1 | SORT AGGREGATE    |      | 1        |
|* 2 | TABLE ACCESS FULL| T1   | 100      |
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("V1"=:V1)

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

And finally using Tanel Poder script here's below the corresponding non-sharing reason:

SQL> @nonshared d2h2phry5d881

Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)...

SQL_ID              : d2h2phry5d881
ADDRESS             : 00007FFB809C75C8
CHILD_ADDRESS       : 00007FFB7EC96358
CHILD_NUMBER        : 0
HASH_MATCH_FAILED   : Y
REASON              :
CON_ID              : 1
-----------------

This situation is not quite usual. We can notice that the initial query (sql_id d2h2phry5d881) has been forced to use a different execution plan which has been compiled for a different sql_id (34z8wv6bsyu6u). And this fairly likely what theHASH_MATCH_FAILED reason means: when a parent cursor is forced to run via an execution plan coming from a different or modified sql_id.

 

Summary

A large part of a SQL tuning strategy resides in understanding the reason that pushes the Oracle optimizer compiling a new execution plan. We saw in this first Part of the series how, using Tanel Poder SQL script, we can easily get the non-sharing reason from the dedicated v$sql_shared_cursor view. We have then explained two very popular hard parsing reason and . The next part of the instalment will examine three other reasons.

*Transferspm.sql

declare
      v_sql_text clob;
      ln_plans pls_integer;
 
begin
      select replace(sql_fulltext, chr(00), ' ')
      into v_sql_text
      from gv$sqlarea
      where sql_id = trim('&original_sql_id')
      and rownum = 1;
 
     -- create sql_plan_baseline for original sql using plan from modified sql
     ln_plans := dbms_spm.load_plans_from_cursor_cache (
                    sql_id          => trim('&modified_sql_id'),
                    plan_hash_value => to_number(trim('&plan_hash_value')),
                    sql_text        => v_sql_text);
     dbms_output.put_line('Plans Loaded: '||ln_plans);
end;
/

Tags: Oracle

Mohamed Houri

Written by Mohamed Houri