In an earlier article we discussed two very popular reasons, LOAD_OPTIMIZER_STATS and HASH_MATCH_FAILEDpushing Oracle to refuse sharing an existing execution plan and, thereby, hard parsing a new plan. While the first reason is closely related to the Adaptive Cursor Sharing feature the second one is still not easily defined despite the clear reproducible example we have provided. Shortly after the publication of the previous article I have participated into an interesting Oracle OTN discussion related to the HASH_MATCH_FAILED reason and which I believe is worth to be mentioned here for a sake of completeness. In this second part of the series we will examine two new reasons that are : ROLL_INVALID_MISMATCH and TOP_LEVEL_RPI_CURSOR.
ROLL_INVALID_MISMATCH
Oracle defines this reason as follows:
Marked for rolling invalidation and invalidation window exceeded
This definition is, to say the least, not very clear. Let’s make it understandable via a reproducible example:
QL> 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
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> select count(1) from t1 where v1 = 'Hundred';
COUNT(1)
----------
100
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID 0rq3mwbx047x9, child number 0
-------------------------------------
select count(1) from t1 where v1 = 'Hundred'
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"='Hundred')
I have created a heap table with a single column b-tree index and have executed a simple select against this table printing out its corresponding execution plan. Notice that, even though I haven’t gathered any statistics on the t1 table, dynamic sampling didn’t kick in. In fact this is a new 12c feature where statistics are automatically gathered on tables created via CTAS (create table as select) command as proofed via the following select:
SQL> col table_name format a20
SQL> select table_name,num_rows, last_analyzed
2 from all_tables
3 where table_name ='T1';
TABLE_NAME NUM_ROWS LAST_ANALYZED
-------------------- ---------- -----------------
T1 10000 20160612 15:31:46
Let’s now gather manually statistics on t1 table (and notice the value of the no_invalidate parameter)
SQL> begin
dbms_stats.gather_table_stats
(user
, 't1'
, method_opt => 'for all columns size 1'
, estimate_percent => dbms_stats.auto_sample_size
, no_invalidate => dbms_stats.auto_invalidate
);
end;
/
PL/SQL procedure successfully completed.
Here’s below how Oracle defines the no_invalidate parameter
The value controls the invalidation of dependent cursors of the tables for which statistics are being gathered. Does not invalidate the dependent cursors if set true. The procedure invalidates the dependent cursors immediately if set false. Use dbms_stats.auto_invalidate to have Oracle decide when to invalidate dependent cursors. This is the default.
Using the dbms_stats.auto_invalidate value I have signalled to Oracle that it has to manage a certain amount of time before invalidating cursors depending on the analyzed table (t1 here). This particular time interval during which dependent cursor will be invalidated is determined by the hidden parameter_optimizer_invalidation_period which defaults to 18000 seconds (5 hours). Typically you will use this parameter in order to avoid a ”hard parse” storm when lots of cursors are to be invalidated.
After a certain period of time, if we re-execute the same query above we will realise that Oracle has invalidated the existing child cursor number 0 and have complied a new one as shown below:
SQL> select count(1) from t1 where v1 = 'Hundred';
COUNT(1)
----------
100
SQL> select * from table(dbms_xplan.display_cursor);
--------------------
SQL_ID 0rq3mwbx047x9, child number 1
-------------------------------------
Plan hash value: 3724264953
-------------------------------------------
| 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"='Hundred')
Notice now the appearance of a new child cursor n°1 which clearly indicates that Oracle has decided to compile a new execution plan instead of sharing the existing child cursor n° 0. And if you want to know for what reason Oracle has decided to hard parse this new plan then simply use the following:
SQL> @nonshared 0rq3mwbx047x9 Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)... SQL_ID : 0rq3mwbx047x9 ADDRESS : 00007FF92C48D600 CHILD_ADDRESS : 00007FF92C48C150 CHILD_NUMBER : 0 REASON : <ChildNode><ChildNumber>0</ChildNumber><ID>34</ID> <reason>Rolling Invalidate Window Exceeded(3) </reason> <size>2x4</size><invalidation_window> 1465757431</invalidation_window> <ksugctm>1465795795</ksugctm></ChildNode> CON_ID : 1 ----------------- SQL_ID : 0rq3mwbx047x9 ADDRESS : 00007FF92C48D600 CHILD_ADDRESS : 00007FF9297A7D40 CHILD_NUMBER : 1 ROLL_INVALID_MISMATCH : Y REASON : CON_ID : 1 ----------------- PL/SQL procedure successfully completed.
Simply put the ROLL_INVALID_MISMATCH reason indicates that, after a fresh statistics, usingdbms_stats.auto_invalidate value for the parameter auto_invalidate, have been gathered on objects (table/index), the underlying cursor has been invalidated within a time interval determined by the hidden parameter _optimizer_invalidation_period, and thereby a new execution plan has been compiled.
TOP_LEVEL_RPI_CURSOR
Here’s below how Oracle defines this reason:
(Y|N) is top level RPI cursor
Again, Oracle has not been very precise, to say the least, in its explanation of this execution plan non-sharing reason. In order to give credit where credit is due, I have to confess that it is in this article by Brian Peasland where I have first clearly understood what this reason stands for. Let’s then re-explain it very briefly in this article.
First, we will create a stored procedure in which we will issue a simple select against the above mentioned t1 table.
SQL> create or replace procedure p_get_count
as
ln_cnt number;
begin
select count(1) into ln_cnt from t1 where v1 = 'Hundred';
end p_get_count;
/
Procedure created.
The above stored procedure contains a simple select count(1) statement. The second step of the demonstration consists of executing this simple select as a stand alone sql query as shown below:
SQL> SELECT COUNT(1) FROM T1 WHERE V1 = 'Hundred';
COUNT(1)
----------
100
SQL> select * from table(dbms_xplan.display_cursor);
--------------------
SQL_ID 0fkx94p1gvkx3, child number 0
-------------------------------------
SELECT COUNT(1) FROM T1 WHERE V1 = 'Hundred'
Plan hash value: 3724264953
-------------------------------------------
| 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"='Hundred')
Notice in passing that I have managed to write the simple select count query in capital letters. This is because, as explained by Brian Peasland, SQL statements executed inside stored procedures are rewritten in capital letters. And as fa as we want to compare two identical sql_id , I had better to use capital letters.
The final step in the demonstration is to execute the stored procedure as shown below:
SQL> exec p_get_count();
PL/SQL procedure successfully completed.
And finally here’s below the different execution plans we obtained and their non-sharing reason respectively:
SQL> select
2 sql_id
3 ,child_number
4 from gv$sql
5 where sql_id = '0fkx94p1gvkx3';
SQL_ID CHILD_NUMBER
------------- ------------
0fkx94p1gvkx3 0
0fkx94p1gvkx3 1
SQL> @nonshared 0fkx94p1gvkx3
Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)...
SQL_ID : 0fkx94p1gvkx3
ADDRESS : 00007FF9267CA340
CHILD_ADDRESS : 00007FF926506120
CHILD_NUMBER : 0
REASON : <ChildNode><ChildNumber>0</ChildNumber>
<ID>7</ID><reason>Top Level RPI Cursor(0)
</reason><size>2x4</size><ctxxyfl>0</ctxxyfl>
<ispri>1</ispri></ChildNode>
CON_ID : 1
-----------------
SQL_ID : 0fkx94p1gvkx3
ADDRESS : 00007FF9267CA340
CHILD_ADDRESS : 00007FF9264FB608
CHILD_NUMBER : 1
TOP_LEVEL_RPI_CURSOR : Y
REASON :
CON_ID : 1
-----------------
PL/SQL procedure successfully completed.
SUMMARY
While in the first part of the instalment we have explained the LOAD_OPTIMIZER_STATS and HASH_MATCH_FAILED execution plan non-sharing reasons in this article 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 particular value of dbms_stats.auto_invalidate for the no_invalidate parameter of the DBMS_STATSpackage. We have demonstrated in this case that the v$sql_shared_cursor will show the ROLL_INVALID_MISMATCH as a 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 statements are called from a different depth. Particularly when one is called from SQL while the other is used in a PL/SQL stored procedure.
Start the discussion at forums.toadworld.com