Oracle too many parse errors PARSE ERROR

DB Alert log Filled With WARNING'S 'too many parse errors PARSE ERROR: ORA-00923' (Doc ID 2649163.1)

WARNING: too many parse errors, count

To find Sql id !!!




SQL ending up in the shared pool 

$ grep -c "SQL hash" alert_<DBName>.log
2027
$ grep -c "hash=0xbbcb647d" alert_<DBName>.log
2027 



test 


from https://jonathanlewis.wordpress.com/2017/10/06/12c-parse/

declare
        m1 number;
begin
        for i in 1..1000000 loop
        begin
                execute immediate 'select count(*) frm dual' into m1;
                dbms_output.put_line(m1);
        exception
                when others then null;
        end;
        end loop;
end;
/



from alert log !!!!

Additional information: hd=0x1002f46e0 phd=0x10cd45dc0 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
2022-12-14T04:42:30.206000-05:00
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x1175e2778         6  anonymous block  <<<<<<<<-------------------Check This 
WARNING: too many parse errors, count=1010000 SQL hash=0x19a22496
PARSE ERROR: ospid=32580, error=923 for statement:
2022-12-14T04:42:30.219167-05:00
select count(*) frm dual
Additional information: hd=0x1002f46e0 phd=0x10cd45dc0 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
2022-12-14T04:42:30.219363-05:00
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
  

0x1175e2778         6  anonymous block





set linesize 300 
col sql_text for a50 wrap
 select address,child_address, sql_text from V$sql where child_address like '%2778%';




set linesize 300
col sql_text for a50 wrap
select sql_id,address,child_address, sql_text from gv$sql where child_address like '%2778%';  --<<<< --- 0x1175e2778         6  anonymous block


SQL_ID        ADDRESS          CHILD_ADDRESS    SQL_TEXT
------------- ---------------- ---------------- --------------------------------------------------
2r9g466tw2h18 000000010CD45798 00000001175E2778 declare         m1 number; begin         for i in
                                                1..1000000 loop         begin                 exec
                                                ute immediate 'select count(*) frm dual' into m1;
                                                                dbms_output.put_line(m1);
                                                exception                 when others then null;
                                                       end;         end loop; end;



===

from 0x1175e2778         6  anonymous block 




set linesize 300
col sql_text for a50 wrap
select sql_id,address,child_address, sql_text from gv$sql where child_address like '%1175E2778%';


SQL_ID        ADDRESS          CHILD_ADDRESS    SQL_TEXT
------------- ---------------- ---------------- --------------------------------------------------
2r9g466tw2h18 000000010CD45798 00000001175E2778 declare         m1 number; begin         for i in
                                                1..1000000 loop         begin                 exec
                                                ute immediate 'select count(*) frm dual' into m1;
                                                                dbms_output.put_line(m1);
                                                exception                 when others then null;
                                                       end;         end loop; end;





set linesize 150 pagesize 300
col PLAN_TABLE_OUTPUT for a125

SELECT t.* FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t 
WHERE 1=1
--and sql_text LIKE '%XXX%'
and sql_id = '&sql_id'
;





alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
set pages 200  line 900
col sql_text for a80 
col PROGRAM for a20
con inst_id for 99
col MACHINE for a20
col CPU 9999999999
col kill for a17
col event for a40
select distinct * from (select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,s.con_id,sa.sql_id,vp.spid,
sa.CPU_TIME "CPU",
round(s.last_call_et,0) ,
s.program 		"PROGRAM",
s.machine 		"MACHINE",
sw.event,s.logon_time,s.username,
s.status,s.osuser ,
sa.SQL_TEXT 		"SQL_TEXT"
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
)
;