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;
/
Start the discussion at forums.toadworld.com