As mentioned in the previous three articles of the series there are sixty-four reasons for which the Oracle Optimizer might decide to hard parse a new execution plan instead of sharing the existing one. Out of these sixty-four reasons, we have already examined and explained six: load_optimizer_stats and hash_match_failed in part I, roll_invalid_mismatch and top_level_rpi_cursor in part II, and bind_mismatch and stb_obj_mismatch in part III. The current article outlines two new supplementary reasons: pq_slave_mismatch and optimizer_mismatch. The former is not very popular in real-life applications. It occurs when a parallel slave(PX) of a parallelized query refuses to share the execution plan of its Query Coordinator (QC). The latter is, however, very popular. It kicks in whenever an optimizer property,stored in the child cursor metadata,is modified. Oracle has foreseen thirteen sub-reasons for an optimizer parameter mismatch, which we will try to examine in this article.
PQ_SLAVE_MISMATCH
Oracle defines this reason as follows:
(Y|N) Top-level slave decides not to share cursor
To say the least, Oracle has not made a big effort to define this reason with a great precision. As always, the best way to make its proper opinion about this reason for non-sharing is to figure out how to force it. Here’s how I have managed to produce this reason in Oracle version 12.1.0.1.0; first the model and then the query:
create table t1
as
with generator as (
select --+ materialize
rownum id
from dual
connect by
level <= 1000
)
select
trunc((rownum-1)/2) product_id
,mod(rownum-1,2) flag1
,mod(rownum-1,3) flag2
,rownum n1
,lpad(rownum,30) v1
from
generator v1
,generator v2
where
rownum <= 1e6;
create table t2
as select
level id
,date '2012-06-07' + mod((level-1)*5,10) + interval '5' minute start_date
,rpad('xx',10) padding
from dual
connect by level <= 1e6;
begin
dbms_stats.gather_table_stats(user, 't1');
dbms_stats.gather_table_stats(user, 't2');
end;
/
The above script creates two simple heap tables and gathers their statistics without histogram. Let’s run the following query, get its execution plan, and check its execution statistics:
with got_my_dates as
(select /*+ materialize */
id
,padding
,start_date
from t2
where start_date > to_date('07/06/2012','dd/mm/yyyy')
and start_date <= to_date('10/06/2012','dd/mm/yyyy')
)
select
/*+ parallel(4) */
t1.*
,cte.padding
,cte.start_date
from
t1
,got_my_dates cte
where
t1.product_id = cte.id
and t1.product_id <= 1e3;
SQL_ID c5y75njp34rmf, child number 0
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | TQ |IN-OUT|
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 998K| Q1,00 | P->S |
| 4 | LOAD AS SELECT | | | Q1,00 | PCWP |
| 5 | PX BLOCK ITERATOR | | 998K| Q1,00 | PCWC |
|* 6 | TABLE ACCESS FULL | T2 | 998K| Q1,00 | PCWP |
| 7 | PX COORDINATOR | | | | |
| 8 | PX SEND QC (RANDOM) | :TQ20001 | 1723 | Q2,01 | P->S |
|* 9 | HASH JOIN | | 1723 | Q2,01 | PCWP |
| 10 | JOIN FILTER CREATE | :BF0000 | 1723 | Q2,01 | PCWP |
| 11 | PX RECEIVE | | 1723 | Q2,01 | PCWP |
| 12 | PX SEND BROADCAST | :TQ20000 | 1723 | Q2,00 | P->P |
| 13 | PX BLOCK ITERATOR | | 1723 | Q2,00 | PCWC |
|* 14 | TABLE ACCESS FULL | T1 | 1723 | Q2,00 | PCWP |
|* 15 | VIEW | | 998K| Q2,01 | PCWP |
| 16 | JOIN FILTER USE | :BF0000 | 998K| Q2,01 | PCWP |
| 17 | PX BLOCK ITERATOR | | 998K| Q2,01 | PCWC |
|* 18 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6628_1E139EE | 998K| Q2,01 | PCWP |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
filter(("START_DATE">TO_DATE(' 2012-06-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "START_DATE"<=TO_DATE(' 2012-06-1000:00:00', 'syyyy-mm-dd hh24:mi:ss')))
9 - access("T1"."PRODUCT_ID"="CTE"."ID")
14 - access(:Z>=:Z AND :Z<=:Z)
filter("T1"."PRODUCT_ID"<=1000)
15 - filter("CTE"."ID"<=1000)
18 - access(:Z>=:Z AND :Z<=:Z)
filter(SYS_OP_BLOOM_FILTER(:BF0000,"C0"))
Note
-----
– Degree of Parallelism is 4 because of hint
As you can see, this query has been run parallely with a Degree of Parallelism of 4 as indicated by the Note at the bottom of its execution plan. But interestingly, it produced two execution plans (child number 0 and 1) at its very first execution, as shown below:
SQL> @gv$sql Enter value for sql_id: c5y75njp34rmf SQL_ID CHILD_NUMBER FIRST_LOAD_TIME LAST_LOAD_TIME EXECUTIONS END_FETCH ------------- ------------ ------------------- ------------------- ---------- ---------- c5y75njp34rmf 0 2017-04-17/10:10:55 2017-04-17/10:10:55 1 1 c5y75njp34rmf 1 2017-04-17/10:10:55 2017-04-17/10:10:55 0 0
According to the end_of_fetch_count column, it is the child number 0 that has been effectively used to run this query. Let’s see now why Oracle has decided to compile two execution plans, using Tanel Poder’s nonshared script.
SQL> @nonshared c5y75njp34rmf
Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)…
SQL_ID : c5y75njp34rmf ADDRESS : 00007FFC9157FAF0 CHILD_ADDRESS : 00007FFC9157E640 CHILD_NUMBER : 0 REASON : <ChildNode><ChildNumber>0</ChildNumber><ID>9</ID> <reason>PQSlavemismatch(5)</reason><size>2x4</size> <ctxpq_StmtId_pqctx>3455170</ctxpq_StmtId_pqctx> <fxu_kxfxutqidb>3389067</fxu_kxfxutqidb></ChildNode> CON_ID : 1 ----------------- SQL_ID : c5y75njp34rmf ADDRESS : 00007FFC9157FAF0 CHILD_ADDRESS : 00007FFC9152B2C8 CHILD_NUMBER : 1 PQ_SLAVE_MISMATCH : Y REASON : <ChildNode><ChildNumber>1</ChildNumber><ID>9</ID> <reason>PQSlavemismatch(5)</reason><size>2x4</size> <ctxpq_StmtId_pqctx>3389067</ctxpq_StmtId_pqctx> <fxu_kxfxutqidb>3455170</fxu_kxfxutqidb></ChildNode> CON_ID : 1 -----------------
As you can see, we have finally simulated this reason. Simply put, we can say that it happens inevitably with a parallel query and during which one of the parallel server refuses to share the execution plan of its query coordinator. In the model that I have engineered in this article it seems that this non-sharing reason is due to the materialization of the CTE (Common Table Expression, aka the WITH clause) which I have forced with the hint /*+ materialize */. Bear in mind, however, that I have been playing with the same model into different Oracle releases without always being able to reproduce this pq_slave_mismatch reason.
OPTIMIZER_MISMATCH
Oracle defines this reason as follows:
(Y|N) The optimizer environment does not match the existing child cursor
When Oracle compiles a new execution plan (child cursor) for a parent cursor it will store information that identifies as fully as possible the environment characterizing the compilation and the execution of this execution plan. The optimizer parameters represent an important part of this stored cursor metadata. When the same parent cursor is launched again, its existing child cursor will be shared and reused provided all of its meta-ata optimizer parameters remain intact. If, however, Oracle realizes that one or many values of those stored optimizer parameters change, it will then invalidate the existing child cursor and hard parse a new one. It seems, too, that Oracle has implemented thirteen optimizer mismatch situations:
- Optimizermismatch(1)
- Optimizermismatch(2)
- Optimizermismatch(3)
- Optimizermismatch(4) → points to a px_mismatch for serial plan (not clear)
- Optimizermismatch(5)
- Optimizermismatch(6)
- Optimizermismatch(7)
- Optimizermismatch(8)
- Optimizermismatch(9)
- Optimizermismatch(10) → optimizer mode (first_rows, all_rows) mismatch
- Optimizermismatch(11)
- Optimizermismatch(12) → optimizer parameter mismatch
- Optimizermismatch(13) → cardinality feedback
The best and easiest way to decipher the meaning of those 13 situations is to run the following query in different running systems and get details of the corresponding non-sharing reason using a Tanel Poder script:
SQL> select distinct sql_id from gv$sql_shared_cursor where reason like '%Optimizer mismatch%' order by sql_id; SQL_ID ------------- 00ajuu5902429 00zqy3yd0r3p3 04kug40zbu4dm 0b0wj2ykgnnzg 0fr8zhn4ymu3v 0gx3b09qrx9f5 0m78skf1mudnb 0v3dvmc22qnam 10s3r3f17ccu3 SQL> @nonshared <sql_id>
Proceeding as such I realized that Optimizermismatch(13) is linked with cardinality feedback, 12 with a difference in the optimizer parameters, 10 with a difference in the optimizer mode, 4 with a not yet very clear px_mismatch linked with serial queries, and so on, as shown below:
SQL> @nonshared 00ajuu5902429
Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)…
SQL_ID : 00ajuu5902429 ADDRESS : 00007FFC9F7DC5D8 CHILD_ADDRESS : 00007FFC9F7DB128 CHILD_NUMBER : 0 REASON : <ChildNode><ChildNumber>0</ChildNumber><ID>3</ID> <reason>Optimizermismatch(10)</reason> <size>3x4</size><optimizer_mode_hinted_cursor>0 </optimizer_mode_hinted_cursor> <optimizer_mode_cursor>4 </optimizer_mode_cursor> <optimizer_mode_current>1</optimizer_mode_current> </ChildNode> CON_ID : 1 ----------------- SQL_ID : 00ajuu5902429 ADDRESS : 00007FFC9F7DC5D8 CHILD_ADDRESS : 00007FFC97D79000 CHILD_NUMBER : 1 OPTIMIZER_MODE_MISMATCH : Y REASON : <ChildNode><ChildNumber>1</ChildNumber><ID>3</ID> <reason>Optimizermismatch(10)</reason> <size>3x4</size> <optimizer_mode_hinted_cursor>0 </optimizer_mode_hinted_cursor> <optimizer_mode_cursor>1</optimizer_mode_cursor <optimizer_mode_current>4</optimizer_mode_current> </ChildNode> CON_ID : 1 ----------------- SQL> @nonshared gd90ygn1j4026
Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)…
SQL_ID : gd90ygn1j4026 ADDRESS : 00007FFCA113DC38 CHILD_ADDRESS : 00007FFCA113C788 CHILD_NUMBER : 0 REASON : <ChildNode><ChildNumber>0</ChildNumber><ID>3</ID> <reason>Optimizermismatch(12)</reason> <size>2x312</size> <_parallel_syspls_obey_force> true false </_parallel_syspls_obey_force></ChildNode> CON_ID : 1 ----------------- SQL_ID : gd90ygn1j4026 ADDRESS : 00007FFCA113DC38 CHILD_ADDRESS : 00007FFC9AF3DDD0 CHILD_NUMBER : 1 OPTIMIZER_MISMATCH : Y REASON : <ChildNode><ChildNumber>1</ChildNumber><ID>3</ID> <reason>Optimizermismatch(12)</reason> <size>2x312</size> <_parallel_syspls_obey_force> false true </_parallel_syspls_obey_force></ChildNode> CON_ID : 1 -----------------
What we should essentially remember here is that whatever the value x in Optimizermismatch(x), it inevitably indicates that a mismatch has been found in the optimizer parameters stored in the existing child cursor metadata. This optimizer parameter difference will almost always force Oracle to create a new child cursor with new corresponding metadata and store the non-sharing reason into the dedicated gv$sql_shared_cursor view.
SUMMARY
In this article we examined two new reasons from gv$sql_shared_cursor view: pq_slave_mismatch and optimizer_mismatch. Generally, watch out for the former when you are running a parallel query. We have shown an example of this non-sharing reason occurring when a parallel slave refuses to share the execution plan of its query coordinator because of the materialization of a subquery factoring. The latter is a very common reason in real-life applications. It happens whenever Oracle optimizer fails to find a hundred percent matching metadata of the current child cursor with that of the existing one(s).
Start the discussion at forums.toadworld.com