What Is This PARSE ERROR: ospid=31513, error=936 for statement:?
For every 100 parse errors in the database, an entry is recorded in the alertlog with “PARSE ERROR: ospid=<nnnnn>, error=<nnn> for statement: ” format.
Simulate the error by forcing syntax error with 10000 loops. For every 100 errors consolidated, 10000 / 100 = 100 entries are recorded in alertlog.
[oracle@xhydra trace]$ cat ~/select.sh
#!/bin/bash -x
export ORACLE_SID=sqldb
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
c=0
while [ $c -lt 10000 ];
do
$ORACLE_HOME/bin/sqlplus -S '/ as sysdba' <<EOF
whenever sqlerror continue;
select count(*)* from dspm;
exit;
EOF
c=`expr $c + 1`
done
The entry contains the error message which can point to cause of the issue including the sql text with sql id.
It also provide the application information with the username and action etc..
[oracle@xhydra trace]$ tail -100f alert_sqldb.log |grep -A5 'PARSE ERROR'
PARSE ERROR: ospid=30863, error=936 for statement:
2023-10-30T21:28:31.006154+05:30
select count(*)* from dspm
Additional information: hd=0x79bfa4f8 phd=0x79bfa920 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 sqlid=bg9pxaf3r8akn
...Current username=SYS
...Application: sqlplus@xhydra (TNS V1-V3) Action:
--
PARSE ERROR: ospid=31513, error=936 for statement:
2023-10-30T21:30:40.623706+05:30
select count(*)* from dspm
Additional information: hd=0x79bfa4f8 phd=0x79bfa920 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 sqlid=bg9pxaf3r8akn
...Current username=SYS
...Application: sqlplus@xhydra (TNS V1-V3) Action:
--
PARSE ERROR: ospid=32079, error=936 for statement:
2023-10-30T21:32:28.160547+05:30
select count(*)* from dspm
Additional information: hd=0x79bfa4f8 phd=0x79bfa920 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 sqlid=bg9pxaf3r8akn
...Current username=SYS
...Application: sqlplus@xhydra (TNS V1-V3) Action:
--
PARSE ERROR: ospid=32663, error=936 for statement:
2023-10-30T21:34:18.773368+05:30
select count(*)* from dspm
Additional information: hd=0x79bfa4f8 phd=0x79bfa920 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 sqlid=bg9pxaf3r8akn
...Current username=SYS
...Application: sqlplus@xhydra (TNS V1-V3) Action:
--
PARSE ERROR: ospid=33248, error=936 for statement:
2023-10-30T21:36:08.751102+05:30
select count(*)* from dspm
Additional information: hd=0x79bfa4f8 phd=0x79bfa920 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 sqlid=bg9pxaf3r8akn
...Current username=SYS
...Application: sqlplus@xhydra (TNS V1-V3) Action:
--
PARSE ERROR: ospid=33754, error=936 for statement:
2023-10-30T21:37:59.365650+05:30
select count(*)* from dspm
Additional information: hd=0x79bfa4f8 phd=0x79bfa920 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 sqlid=bg9pxaf3r8akn
...Current username=SYS
...Application: sqlplus@xhydra (TNS V1-V3) Action:
--
PARSE ERROR: ospid=34305, error=936 for statement:
2023-10-30T21:39:44.152553+05:30
select count(*)* from dspm
Additional information: hd=0x79bfa4f8 phd=0x79bfa920 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 sqlid=bg9pxaf3r8akn
...Current username=SYS
...Application: sqlplus@xhydra (TNS V1-V3) Action:
--
PARSE ERROR: ospid=34877, error=936 for statement:
2023-10-30T21:41:28.786950+05:30
select count(*)* from dspm
Additional information: hd=0x79bfa4f8 phd=0x79bfa920 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 sqlid=bg9pxaf3r8akn
...Current username=SYS
...Application: sqlplus@xhydra (TNS V1-V3) Action:
--
PARSE ERROR: ospid=35437, error=936 for statement:
2023-10-30T21:43:12.664262+05:30
select count(*)* from dspm
Additional information: hd=0x79bfa4f8 phd=0x79bfa920 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 sqlid=bg9pxaf3r8akn
...Current username=SYS
...Application: sqlplus@xhydra (TNS V1-V3) Action:
In AWR report, we notice high “connection management call elapsed time” and also “failed parse elapsed time” with less significant value. Whenever, parse errors happen in the database, then these symptoms always show in AWR report.
There are also high parse count failures out of total parse count which is not good.
Also, if the parse failures are significant, then instance efficiency ratio drop for Execute to parse and Parse CPU
Solution:
- There is no action that can be taken from DBA rather to inform application team to fix the code with correct syntax or permissions on the object depending on the type of error.
[oracle@xhydra trace]$ oerr ora 936
00936, 00000, "missing expression"
// *Cause:
// *Action:
2. Error stack should help
<error barrier> at 0x7ffee3f36a30 placed dbkda.c@296
ORA-00936: missing expression
<error barrier> at 0x7ffee3f39af0 placed prsde.c@1119
----- SQL Statement (None) -----
Current SQL information unavailable - no cursor.
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst1()+95 call kgdsdst() 7FFEE3F35E90 000000002
7FFEE3F301C0 ? 7FFEE3F302D8 ?
000000000 000000082 ?
ksedst()+58 call ksedst1() 000000000 000000001
7FFEE3F301C0 ? 7FFEE3F302D8 ?
000000000 ? 000000082 ?
dbkedDefDump()+2308 call ksedst() 000000000 000000001 ?
0 7FFEE3F301C0 ? 7FFEE3F302D8 ?
000000000 ? 000000082 ?
ksedmp()+577 call dbkedDefDump() 00000000C 000000000
7FFEE3F301C0 ? 7FFEE3F302D8 ?
000000000 ? 000000082 ?
dbkdaKsdActDriver() call ksedmp() 00000000C 000000000 ?
+2484 7FFEE3F301C0 ? 7FFEE3F302D8 ?
000000000 ? 000000082 ?
dbgdaExecuteAction( call dbkdaKsdActDriver() 7F67548AF6D0 7FFEE3F38320
)+354 7FFEE3F301C0 ? 7FFEE3F302D8 ?
000000000 ? 000000082 ?
dbgdaRunAction()+76 call dbgdaExecuteAction( 7F67548AF6D0 0140B5E00
2 ) 04EC1CBB0 7FFEE3F38320
000000001 000000082 ?
dbgdRunActions()+83 call dbgdaRunAction() 7F67548AF6D0 7FFEE3F385A0
7F67548AF6D0 000000000
7FFEE3F385A8 000000082 ?
dbgdProcessEventAct call dbgdRunActions() 7F67548AF6D0 ? 7FFEE3F385A0 ?
========== FRAME [17] (qcpiapr()+1615 -> kgesecl0()) ==========
defined by frame pointers 0x7ffee3f39410 and 0x7ffee3f39230
CALL TYPE: call ERROR SIGNALED: yes COMPONENT: (null)
There is a hidden parameter “_kks_parse_error_warning” which has a default value of 100 threshold for which the number of times that the warning message should print in alertlog. If this value is increased, then oracle does not print the message too frequently. But, this may also hide parse failures which is very important for the DBA to troubleshoot any performance problems of parsing issues.
SQL> 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 ‘%kks%’
order by x.ksppinm;SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9
HIDDEN_PARAMETER It’s Value Desc
—————————————- ——————– —————————————-
_kks_cached_parse_errors 0 KKS cached parse errors
_kks_obsolete_dump_threshold 1 Number of parent cursor obsoletions befo
re dumping cursor
_kks_parse_error_warning 100 Parse error warning
The failed parse time can be queried from time model statistics tables.
SQL> SELECT stat_name,value from v$sys_time_model where stat_name like '%fail%';
STAT_NAME VALUE
---------------------------------------------------------------- ----------
failed parse elapsed time 89025886
failed parse (out of shared memory) elapsed time 0
SQL> SELECT stat_name,sum(value) from dba_hist_sys_time_model where stat_name like '%fail%' group by stat_name;
STAT_NAME SUM(VALUE)
---------------------------------------------------------------- ----------
failed parse (out of shared memory) elapsed time 0
failed parse elapsed time 646813
Alternatively, 10035 trace can also help to identify the parse failures.
SQL> ALTER system set events '10035 trace name context forever,level 12';
System altered.
Summary:
- Although these parse errors looks harmless, these errors can be ignored if it is less in numbers
- If the parse errors are significant, then this indicates a potential performance problem of unnecessary parsing which lead to shared pool processing
- These sessions can be killed after confirmation from application team because of useless parses