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 ) ;
No comments:
Post a Comment