Toad World

Why my execution plan has not been shared – Part III

Avatar photo
Post author:Written By

In the previous installment of this series we looked at four reasons from gv$sql_shared_cursor view and explained the situations and criteria for which Oracle is forced to compile a new execution plan. We used reproducible examples to demonstrate these reasons and to make them easily understandable. In this third instalment we will decipher two supplementary reasons from the sixty four ones exposed in the gv$sql_shared_cursor which are BIND_MISMATCH and STB_OBJ_MISMATCH.

 

BIND_MISMATCH

Here’s below how Oracle defines this reason

(Y|N) The bind metadata does not match the existing child cursor

 

When Oracle compile a new execution plan for a parent cursor several information are stored in memory together with the underlying child cursor. This cursor metadata contains, among others, the current optimizer parameters, object statistics, objects identities, the name, the type and the length of the bind variables. The information about the length of the bind variable used during the execution plan compilation is particularly non-innocent. Indeed, anything that breaks the stored bind variable length metadata is going to invalidate the cursor and, thereby, provoke a hard parse of a new execution plan. Within the child cursor metadata Oracle has implemented the following five buffer sizes to store varchar2 bind variable lengths:

  • a buffer for bind variable with less than 32 characters

  • a buffer for bind variable with a length between 33 and 128 characters

  • a buffer for bind variable with a length between 129 and 2000 characters

  • a buffer for bind variable with a length between 2001 and 4000 characters

  • a buffer for bind variable with more than 4000 characters

Suppose that you have, first, executed a query using a string bind variable of 10 bytes length and then you rerun the same query but this time using a bind variable of 42 bytes length. During the second execution you used a bind variable length which traverses the first range of buffer size exposed above forcing, thereby, Oracle to hard parse a new execution plan. In order to illustrate this situation I will run the following query five times:

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
 

SQL> SELECT count(1) from t1 where v1 = :s1;
 
COUNT(1)
----------
100

When running the above query I will be using the same bind variable value :s1 but with a different length at each execution. I will start the experiment using a bind variable of a varchar2(10) length which is less than 32 bytes corresponding to the first buffer size category mentioned above:

SQL> create table t1 as
select
rownum n1
,trunc ((rownum-1)/5) n2
,case mod(rownum, 100)
when 0 then 'Hundred'
else 'NotHundred'
end v1
from dual
connect by level <= 1e4;
 
SQL> var s1 varchar2(10)
SQL> exec :s1 := 'Hundred'
 
SQL> SELECT count(1) from t1 where v1 = :s1;
 
COUNT(1)
----------
100
 
SQL> select * from table(dbms_xplan.display_cursor);
 
 
SQL_ID 1dgh1vt3brm4w, child number 0
-------------------------------------
SELECT count(1) from t1 where v1 = :s1
 
-------------------------------------------
| Id | Operation             | Name | Rows |
-------------------------------------------
|  0 | SELECT STATEMENT      |      |      |
|  1 |   SORT AGGREGATE      |      |    1 |
|* 2 |     TABLE ACCESS FULL |   T1 | 5000 |
-------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("V1"=:S1)

Now, I will use a :s1 bind variable length situated in the second buffer size category (>32 and < 128)

SQL> var s1 varchar2(33)
SQL> exec :s1 := 'Hundred'
 
SQL> SELECT count(1) from t1 where v1 = :s1;
 
COUNT(1)
----------
100
 
SQL> select * from table(dbms_xplan.display_cursor);
 
SQL_ID 1dgh1vt3brm4w, child number 1
-------------------------------------
SELECT count(1) from t1 where v1 = :s1
 
-------------------------------------------
| Id | Operation             | Name | Rows |
-------------------------------------------
|  0 | SELECT STATEMENT      |      |      |
|  1 |   SORT AGGREGATE      |      |    1 |
|* 2 |     TABLE ACCESS FULL |   T1 | 5000 |
-------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
2 – filter("V1"=:S1)

Notice how changing the bind variable length from 10 to 33 has forced Oracle to compile a new execution plan represented by the child number 1. If you want to know the reason for this hard parse then simply run the following script:

SQL> @nonshared 1dgh1vt3brm4w

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

SQL_ID : 1dgh1vt3brm4w
ADDRESS : 00007FF8CFDDB148
CHILD_ADDRESS : 00007FF8CFDFDBD8
CHILD_NUMBER : 0
REASON :<reason>Bind mismatch(22)</reason> <original_oacmxl>32</original_oacmxl> <upgradeable_new_oacmxl>128</upgradeable_new_oacmxl>
 

----------------
SQL_ID : 1dgh1vt3brm4w
ADDRESS : 00007FF8CFDDB148
CHILD_ADDRESS : 00007FF8D1EAA548
CHILD_NUMBER : 1
BIND_LENGTH_UPGRADEABLE : Y
REASON : <reason>Bind mismatch(22)</reason>
<original_oacmxl>128</original_oacmxl> <upgradeable_new_oacmxl>32</upgradeable_new_oacmxl>

Oracle is clearly showing that it has been forced to compile a new execution plan because the new length of the s1 bind variable (original_oacmxl =32 < 42 < upgradeable_new_oacmxl = 128) is not anymore situated in the same buffer size interval as the one stored in the metadata of the previous child cursor n°0 (10 < original_oacmxl =32).

Continuing the same experiment I will successively change the bind variable length so that I will cover all remaining buffer size intervals:

L> var s1 varchar2(129)

SQL> exec :s1 := 'Hundred'

SQL> SELECT count(1) from t1 where v1 = :s1;
 
SQL_ID 1dgh1vt3brm4w, child number 2
-------------------------------------
-------------------------------------------
| Id | Operation             | Name | Rows |
-------------------------------------------
|  0 | SELECT STATEMENT      |      |      |
|  1 |   SORT AGGREGATE      |      |    1 |
|* 2 |     TABLE ACCESS FULL |   T1 |    1 |
-------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("V1"=:S1)
 
SQL> var s1 varchar2(2001)
SQL> exec :s1 := 'Hundred'
 
SQL> SELECT count(1) from t1 where v1 = :s1;
 
 
SQL_ID 1dgh1vt3brm4w, child number 3
-------------------------------------
SELECT count(1) from t1 where v1 = :s1
 
-------------------------------------------
| Id | Operation             | Name | Rows |
-------------------------------------------
|  0 | SELECT STATEMENT      |      |      |
|  1 |   SORT AGGREGATE      |      |    1 |
|* 2 |     TABLE ACCESS FULL |   T1 | 5000 |
-------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("V1"=:S1)

SQL> var s1 varchar2(4001)
SQL> exec :s1 := 'Hundred'
 
SQL> SELECT count(1) from t1 where v1 = :s1;
 
SQL_ID 1dgh1vt3brm4w, child number 4
-------------------------------------
SELECT count(1) from t1 where v1 = :s1
 
-------------------------------------------
| Id | Operation             | Name | Rows |
-------------------------------------------
|  0 | SELECT STATEMENT      |      |      |
|  1 |   SORT AGGREGATE      |      |    1 |
|* 2 |     TABLE ACCESS FULL |   T1 | 5000 |
-------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("V1"=:S1)

As you can easily point it out, each time I have used a bind variable length that crossed the upper limit of the buffer size interval stored in the previous child cursor metadata, a new execution plan has been generated with he corresponding new buffer size category (original_oacmxl) updated accordingly as shown below:

SQL> @nonshared 1dgh1vt3brm4w

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

SQL_ID : 1dgh1vt3brm4w
ADDRESS : 00007FF8CFDDB148
CHILD_ADDRESS : 00007FF8CFDFDBD8
CHILD_NUMBER : 0
REASON :<reason>Bind mismatch(22)</reason> <original_oacmxl>32</original_oacmxl> <upgradeable_new_oacmxl>128</upgradeable_new_oacmxl>
-----------------
SQL_ID : 1dgh1vt3brm4w
ADDRESS : 00007FF8CFDDB148
CHILD_ADDRESS : 00007FF8D1EAA548
CHILD_NUMBER : 1
BIND_LENGTH_UPGRADEABLE : Y
REASON :<reason>Bind mismatch(22)</reason>
<original_oacmxl>128</original_oacmxl> <upgradeable_new_oacmxl>32</upgradeable_new_oacmxl>
 
-----------------
SQL_ID : 1dgh1vt3brm4w
ADDRESS : 00007FF8CFDDB148
CHILD_ADDRESS : 00007FF8CFCCB008
CHILD_NUMBER : 2
LANGUAGE_MISMATCH : Y
REASON :<reason>Bind mismatch(22)</reason>
<original_oacmxl>2000</original_oacmxl> <upgradeable_new_oacmxl>4000</upgradeable_new_oacmxl>
 

----------------
SQL_ID : 1dgh1vt3brm4w
ADDRESS : 00007FF8CFDDB148
CHILD_ADDRESS : 00007FF8CFD5AE20
CHILD_NUMBER : 3
BIND_LENGTH_UPGRADEABLE : Y
REASON :<reason>Bind mismatch(20)</reason><size>4x4</size>
<original_oacmxl>4000 2000</upgradeable_new_oacmxl>
-----------------
SQL_ID : 1dgh1vt3brm4w
ADDRESS : 00007FF8CFDDB148
CHILD_ADDRESS : 00007FF8CFD55290
CHILD_NUMBER : 4
BIND_MISMATCH : Y
LANGUAGE_MISMATCH : Y
REASON :<reason>Bind mismatch(20)</reason>
<original_oacmxl>4001</original_oacmxl> <new_oacmxl>4000</new_oacmxl>
 
-----------------
STB_OBJECT_MISMATCH

Here’s below how Oracle defines this reason

(Y|N) STB is an internal name for a SQL Management Object Mismatch. A SQL Management Object Mismatch means that either a SQL plan baseline, or a SQL profile, or a SQL patch has been created for your SQL statement between the executions. Because a cursor is a read-only entity, a hard parse is forced to be able to create a new cursor that contains information about the new SQL management object related to this SQL statement.

 

That is one of the best and clear definitions of the g$vsql_shared_cursor reasons I have ever read so far. It explains that when you create a SQL plan baseline or a SQL profile for a SQL statement you want to guaranty its stability, a hard parse is internally forced by Oracle in order to create a new execution plan pre-empting this SQL query from using an unknown and a non-accepted execution plan.

Here's below a simple illustration of this reason:

SQL> select count(1) from t1 where n1 < 15;

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

SQL_ID d1ztzuajkcz3k, child number 0
-------------------------------------
select count(1) from t1 where n1 < 15
 
Plan hash value: 3724264953
---------------------------------------------------
| Id | Operation             | Name | Rows | Bytes |
---------------------------------------------------
|  0 | SELECT STATEMENT      |      |       |      |
|  1 |   SORT AGGREGATE      |      |     1 |   13 |
|* 2 |     TABLE ACCESS FULL |   T1 |    14 |  182 |
---------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 – filter("N1"<15)
 
Note

----
- dynamic statistics used: dynamic sampling (level=2)

Suppose that the above FULL TABLE scan execution path is the plan you want Oracle to choose for any subsequent execution; then one technique we can use to do the trick is fixing a SPM plan baseline. In order to avoid changing the optimizer_capture_sql_plan_baselines parameter with the risk of mixing two non-shared reasons, we will fix the FULL TABLE scan plan directly from the cursor cache using the following anonymous PL/SQL block

SQL> declare
rs pls_integer;
begin
rs := dbms_spm.load_plans_from_cursor_cache('d1ztzuajkcz3k');
end;
/
 
SQL> select count(1) from t1 where n1 < 15;
 
SQL> select * from table(dbms_xplan.display_cursor);
 
SQL_ID d1ztzuajkcz3k, child number 1
-------------------------------------
select count(1) from t1 where n1 < 15
 
Plan hash value: 3724264953
---------------------------------------------------
| Id | Operation             | Name | Rows | Bytes |
---------------------------------------------------
|  0 | SELECT STATEMENT      |      |      |       |
|  1 |   SORT AGGREGATE      |      |    1 |    13 |
|* 2 |     TABLE ACCESS FULL |   T1 |   14 |   182 |
---------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 – filter("N1"<15)
 
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL plan baseline SQL_PLAN_f2xq10npqdx1x616acf47 used for this statement

As you can see via the above Note, we succeeded to create a SQL plan baseline with which we will pre-empt Oracle from using any other different execution plan.

And finally, and as expected, below is shown the corresponding non-sharing reason stored in the gv$sql_shared_cursor view:

SQL> @nonshared d1ztzuajkcz3k

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

SQL_ID : d1ztzuajkcz3k
ADDRESS : 00007FFE7FFAF1B8
CHILD_ADDRESS : 00007FFE81168298
CHILD_NUMBER : 0
REASON : <reason>SQL Tune Base Object Different(3)</reason>
CON_ID : 1
-----------------
SQL_ID : d1ztzuajkcz3k
ADDRESS : 00007FFE7FFAF1B8
CHILD_ADDRESS : 00007FFE7FBC9B28
CHILD_NUMBER : 1
REASON :
CON_ID : 1
-----------------

Interestingly, when conducting this experiment in several Oracle releases I have realized that the STB_OBJECT_MISMATCH column is not filled up in the 12.1.0.1.0 version of gv$sql_shared_cursor while it is filled up in the 12.1.0.2.0 as shown below respectively:

SQL> select
        sql_id
        ,child_number
        ,stb_object_mismatch
    from
    gv$sql_shared_cursor
    where sql_id = 'd1ztzuajkcz3k';
 
SQL_ID      CHILD_NUMBER   S
------------- ------------ -
d1ztzuajkcz3k 0            N
d1ztzuajkcz3k 1            N
 

SQL> select
        sql_id
        ,child_number
        ,stb_object_mismatch
     from
       gv$sql_shared_cursor
    where
      sql_id = 'd1ztzuajkcz3k';
 
SQL_ID        CHILD_NUMBER S
------------- ------------ -
d1ztzuajkcz3k 0            N
d1ztzuajkcz3k 1            Y

 

SUMMARY

While in the first part of the installment we have explained the LOAD_OPTIMIZER_STATS and the HASH_MATCH_FAILEDexecution plan non-sharing reasons, in the second part we examined why Oracle has to hard parse a new execution plan when the underlying cursor has been invalidated following a fresh statistics gathering using the default value of the no_invalidate parameter of the dbms_stats package. We have demonstrated in this case that the gv$sql_shared_cursor will show the ROLL_INVALID_MISMATCH as the reason for this execution plan hard parsing. We have then explained the not so obvious TOP_RPI_LEVEL non-sharing reason which kicks in when two identical SQL statement are called from a different depth. Particularly when one is called from SQL while the other is called from a PL/SQL stored procedure.

In this article (Part III) we explained the BIND_MISMATCH and the STB_OBJECT_MISMATCH. While the former represents one of the recurrent reasons I have very often observed in SQL code generated from Java applications because of bind variable length variations, the later pops-up whenever a SQL Plan Baseline is created to fix a stable and validated execution plan. With these three articles we covered six reasons among sixty four ones exposed in gv$sql_shared_cursor. Stay tuned several parts will follow.

Tags:

Start the discussion at forums.toadworld.com