too many parse errors error=12872 ORA-12842 Cursor Invalidated During Parallel Execution

Issue:


Sql job getting stucked on sql id 21j18k2raxgpc when a package is executed in Oracle 12c database ..

Alert log errors ..

WARNING: too many parse errors, count=1700 SQL hash=0xaf2lbe
PARSE ERROR: ospid=79414, error=12872 for statement:

Observing "cursor pin" , "PGA memory operation" wait events for the waiting sql when below session query is executed  ...

col sql_text for a80
set pages 200
set line 900
col PROGRAM for a20
con inst_id for 99
col MACHINE for a20
col CPU 9999999999
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
col event for a40
select distinct *
from (select /*+ rule */s.inst_id,
sa.sql_id,
sa.CPU_TIME "CPU",
round(s.last_call_et,0) ,
s.sid "SID",
s.serial# "SERIAL",
s.program "PROGRAM",
s.machine "MACHINE",
sa.SQL_TEXT "SQL_TEXT",
vp.spid,
sw.event,
s.logon_time,
s.username,
s.status,s.osuser
from gv$sqlarea sa, gv$session s, gv$process vp, gv$session_wait sw
where sa.address = s.sql_address  
and sw.sid = s.sid and s.serial# <> 1 and s.status='ACTIVE'  
and s.paddr = vp.addr and sw.event not like '%client%'  
order by round(s.last_call_et,0) desc);

As workaround when flush shared pool is performed sql package execution terminates with ORA-12842 ..

alter system flush shared pool;


ORA-12842: Cursor Invalidated During Parallel Execution (Doc ID 1322894.1)


Solution:

The SQL is getting parsed errors either due to the memory bug or improper statistics of the underlying table or fixed objects ..

BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;
/

begin
dbms_stats.gather_table_stats
( ownname => 'MIS',
tabname => 'TAB_1',
cascade => TRUE
);
end;

/

Gather the statistics using above shown commands , Re-run the job to check if it fixes the issue else follow tuning advisory steps as below 

I) Create Tuning Task

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id =>'21j18k2raxgpc',
scope => 'COMPREHENSIVE',
time_limit => 600,
task_name => 'sql_21j18k2raxgpc',
description => 'Task to tune a query');
END;
/


II) Execute the tuning task.


Begin
dbms_sqltune.Execute_tuning_task (task_name => 'sql_21j18k2raxgpc');
end;
/


III) Check the status of the task using following query


select * from dba_advisor_log where task_name like '%21j18k2raxgpc%';


iV) View the Recommendation

set long 100000
set linesize 5000
set pages 4000
select dbms_sqltune.report_tuning_task('sql_21j18k2raxgpc') from dual;


Possible Solution would be to accept the sql profile ..

execute dbms_sqltune.accept_sql_profile(task_name => 'sql_21j18k2raxgpc', task_owner => 'SYS', replace => TRUE);