ROLL_INVALID_MISMATCH UNSHARED CURSORS ISSUE ORACLE
I recently came into an issue related to high version counts for a particular query where the reason is ROLL_INVALID_MISMATCH. Customer was frequently gathering statistics for the objects in the database making the old statistics to invalidate with a new child cursor.
Oracle invalidate the statistics for the objects in the database periodically from 10g to avoid unexpected surprises of CPU spikes due to hard parse of the new statistics which need to be used for the sql statements as part of optimizer execution plan generation.
Oracle has introduced automatic control over invalidation of the cursors for freshly created statistics with no_invalidate => DBMS_STATS.AUTO_INVALIDATE of DBMS_STATS package from 10g onwards. Until the threshold value for _optimizer_invalidation_period parameter is met, the old cursors are reused for the object statistics.
kish@x3z6zx9<^>col INVALIDATION_MODE for a30
kish@x3z6zx9<^>SELECT DBMS_STATS.get_prefs(pname=>'NO_INVALIDATE') Invalidation_mode FROM dual;
INVALIDATION_MODE
------------------------------
DBMS_STATS.AUTO_INVALIDATE
A sample shell script to simulate the ROLL_INVALID_MISMATCH issue. This script will generate a pool of child cursors over time and populate the shared pool.
[oracle@x3dbzx36 ~]$ cat test.sql
#!/bin/bash -x
export ORACLE_SID=x3x6zx6
export ORACLE_HOME=/apps01/base/product/11.2.0/dbhome
c=0
while [ $c -lt 1000 ];
do
$ORACLE_HOME/bin/sqlplus -S '/ as sysdba' <<EOF
spool ri.txt
set lines 200 pages 1000
set timing on
set echo on
ALTER system set "_optimizer_invalidation_period"=10;
!sleep 15
select to_char(last_analyzed,'DD-MM-YY HH24:MI:SS') last_analyzed from dba_tables where table_name='YTBL';
select count(*) from ytbl;
col child_number for 99999
col parse_calls for 99999
col executions for 9999999
col first_load_time for a20
col last_load_time for a20
SELECT child_number,parse_calls,executions,first_load_time,last_load_time,last_active_time
FROM v\$sql WHERE sql_id='8bsh7ckvjnx9x';
!sleep 15
SELECT ROLL_INVALID_MISMATCH from v\$sql_shared_cursor WHERE sql_id='8bsh7ckvjnx9x';
insert /*+append parallel(8)*/ into ytbl
select dbms_random.string('a','10'),
yid.nextval,
decode(round(dbms_random.value(0,10)),0,'Chennai',1,'Canberra',2,'Berlin',3,'London',4,'Paris',5,'Washington',6,'Lisbon',7,'Madrid',8,'Mumbai',9,'Moscow',10,'Rome'),
floor(dbms_random.value(1,1000000))
from (select level from dual connect by level <= 1),
(select level from dual connect by level <= 1),
(select level from dual connect by level <= 1);
commit;
EXEC dbms_stats.gather_table_stats('SYS','YTBL');
spool off
exit;
EOF
c=`expr $c + 1`
done
There are almost 1602 cursors which are created as child for parent cursor
kish@x3z6zx9<^>col RIM for a30
kish@x3z6zx9<^>col COUNT for 999999999
kish@x3z6zx9<^>SELECT rpad('ROLL_INVALID_MISMATCH : ',33)|| ROLL_INVALID_MISMATCH as RIM ,count(*) count FROM v$sql_shared_cursor GROUP BY ROLL_INVALID_MISMATCH;
RIM COUNT
------------------------------ ----------
ROLL_INVALID_MISMATCH : 1602
Y
ROLL_INVALID_MISMATCH : 3834
N
The output shows two parse calls for every 1 minute as the value for _optimizer_invalidation_period is 10 but the first cursor load time is same from parent cursor 0.
CHILD_NUMBER PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTI
------------ ----------- ---------- -------------------- -------------------- ---------
0 37 37 2023-10-31/10:56:41 2023-10-31/10:56:41 31-OCT-23
1 2 2 2023-10-31/10:56:41 2023-10-31/16:19:28 31-OCT-23
2 2 2 2023-10-31/10:56:41 2023-10-31/16:20:30 31-OCT-23
3 2 2 2023-10-31/10:56:41 2023-10-31/16:21:32 31-OCT-23
4 2 2 2023-10-31/10:56:41 2023-10-31/16:22:34 31-OCT-23
5 2 2 2023-10-31/10:56:41 2023-10-31/16:23:37 31-OCT-23
6 2 2 2023-10-31/10:56:41 2023-10-31/16:24:39 31-OCT-23
7 2 2 2023-10-31/10:56:41 2023-10-31/16:25:41 31-OCT-23
8 2 2 2023-10-31/10:56:41 2023-10-31/16:26:44 31-OCT-23
9 2 2 2023-10-31/10:56:41 2023-10-31/16:27:48 31-OCT-23
10 2 2 2023-10-31/10:56:41 2023-10-31/16:28:51 31-OCT-23
11 2 2 2023-10-31/10:56:41 2023-10-31/16:29:54 31-OCT-23
Solution:
- Make sure that the statistics are gathered in equal interval and not too frequently for the objects.
- Set _optimizer_invalidation_period parameter to a value which suits the data load period of the database.
- Another workaround can be setting _cursor_obsolete_threshold to lower value to forcefully obsolete the child cursors in the database after every new child spawn.
kish@x3z6zx9<^>set lines 200 pages 1000
col "Hidden_parameter" for a40
col "It's Value" for a20
col Desc for a40
select x.ksppinm as Hidden_parameter,
y.ksppstvl "It's Value",
x.ksppdesc "Desc"
from
x$ksppi x
inner join x$ksppcv y on (x.indx = y.indx)
and
x.ksppinm like '%_cursor_obsolete_threshold%'
order by x.ksppinm;kish@x3z6zx9<^>kish@x3z6zx9<^>kish@x3z6zx9<^>kish@x3z6zx9<^> 2 3 4 5 6 7 8 9
HIDDEN_PARAMETER It's Value Desc
---------------------------------------- -------------------- ----------------------------------------
_cursor_obsolete_threshold 1024 Number of cursors per parent before obso
letion.
--Default value of _cursor_obsolete_threshold per versions
11.2.0.3 :=100
11.2.0.4 :=1024
12.1.0.1 :=1024
12.1.0.2 :=1024
12.2.0.1 :=8192
18c :=8192
19c :=8192
21c :=8192
Setting this parameter to lower value may be obsoleted from shared pool which is the remaining 1024 – 200 = 824 cursors which were child versions of the parent stats version for ytbl.
kish@x3z6zx9<^>ALTER system set "_cursor_obsolete_threshold"=200 scope=spfile;
System altered.
