Parse Failures During Syntax Check of Query (Doc ID 2400919.1)

Last updated on JULY 18, 2022

Applies to:

Symptoms

• Executing this procedure from multiple sessions, it reproduces syntax check failures during parsing:
  

  create or replace PROCEDURE LOAD7(LOOPS IN NUMBER )
  is
  begin
  for i in 1..LOOPS
  loop
  execute immediate 'select 1 from A ';
  end loop;
  end;

  SQL> BEGIN load7(100000000); END;

  *
  ERROR at line 1:
  ORA-00942: table or view does not exist
  ORA-06512: at "SYS.LOAD7", line 6
  ORA-06512: at line 1

• AWR for parse failure period:

  Snap Id Snap Time Sessions Cursors/Session
  Begin Snap: 18 19-May-18 07:51:43 31 1.0
  End Snap: 19 19-May-18 07:53:43 30 1.0
  Elapsed: 1.99 (mins)
  DB Time: 0.05 (mins)

  Top 10 Foreground Events by Total Wait Time

  Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
  DB CPU 1.5 54.7
  db file sequential read 821 .8 0.97 28.7 User I/O
  direct path write 9 0 2.52 .8 User I/O

  Statistic Name Time (s) % of DB Time % of Total CPU Time
  sql execute elapsed time 2.47 88.70
  DB CPU 1.53 54.69 77.58
  parse time elapsed 0.69 24.90
  hard parse elapsed time 0.64 22.90>>>>>>>>>>>>>>>>
  PL/SQL execution elapsed time 0.11 3.84
  connection management call elapsed time 0.08 2.91
  failed parse elapsed time 0.06 2.00 >>>>>>>>>>>>>>>> syntax check failures

  ElapTime Exec ElapTime/Exec %Total %CPU %IO SQL Id SQL Module SQL Text
  0.09        231  0.00               3.12 25.30 0.00 g84yypp3j0 sqlplus (TNS V1-V3) BEGIN load7(100000000); END;

  ParseCalls Executions % Total Parses SQL Id SQL Module SQL Text
  231        231            18.03 g84yypp3j0 sqlplus(TNS V1-V3) BEGIN load7(100000000); END;

  
  

  
  
  

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.