Using the power of grep to find the PL/SQL procedures that are hard parsing from Alert Log.

{ Abhilash Kumar Bhattaram : Follow on LinkedIn }

There are tons of issues with PL/SQL which may not be in the DBA scope to fix , but identifying what's failing is definitely a thing DBA's need to pass on the Developers

Use the power of grep to identify what's failing
grep -A 5 essentially means to filter out the next 5 lines that to the search condition , this comes in very handy to identify Procs causing hard parses.

## Find the number of hard parses 
$ grep -rw /path/to/alert.log -e"too many parse errors" | wc -l
1785

## Find the procs that are Hard Parsing
$ grep -rw /path/to/alert.log -A 5 -e"----- PL/SQL Call Stack -----" | awk '{ print $1" "$2" "$3" "$4 }' | grep procedure | grep IIMS_UWR.MYPROC | sort | uniq | tail -10
.
.

0xbaf6b858 293 procedure MYUSER.MYPROC1
0xbb90b0c0 268 procedure MYUSER.MYPROC2
0xbb90b0c0 285 procedure MYUSER.MYPROC2
0xbb90b0c0 293 procedure MYUSER.MYPROC3
0xbbd6ab98 285 procedure MYUSER.MYPROC4
0xbbf9b5a8 285 procedure MYUSER.MYPROC5
0xbbf9b5a8 293 procedure MYUSER.MYPROC6
0xbbfee8a8 268 procedure MYUSER.MYPROC7
0xbbfee8a8 285 procedure MYUSER.MYPROC8

.
.

The output basically says at which Line Number the Proc is Hard Parsing , looks like the issue is between lines 268 and 293 where these alerts are being logged in the Alert Log.

NOTE : The Below SR comes in handy , it basically says the Apps Teams should fix the code.

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

#oracle #oci #oracledatabase #plsql #oraclecloud #oracledba #nabhaas