Q: Why this work? A: After you make changes to production and the users or app servers come in, it's a good idea to catch such errors as table, view, synonym, or column not found, preferably before the users report them to you. If the audit_trail is enabled, you can "audit not exists" to detect the parse failures due to non-existing tables or views, but that doesn't record failures due to missing or incorrect columns or synonyms. Another way is to detect increasing values for session statistic named 'parse count (failures)', which gets incremented on any type of parse failures, although it's not easy to find the SQL that failed to parse. My parse_failure.pl automates this statistic check. You run it as if it was "tail -f ". Q: What errors cause parse failures? A: Generally, as long as the SQL has passed the client side syntax check and is sent to the server, a parse failure occurs if it can't pass either syntax check or semantic check at the SQL layer of the Oracle database. Test in 9.2.0.1: SQL> select * from v$mystat where statistic# = 234; <-- parse count (failures) in 9i SID STATISTIC# VALUE ---------- ---------- ---------- 11 234 3 SQL> select * from asdsa; select * from asdsa * ERROR at line 1: ORA-00942: table or view does not exist <-- Yes SQL> select * from v$mystat where statistic# = 234; SID STATISTIC# VALUE ---------- ---------- ---------- 11 234 4 SQL> select asdasd from t; select asdasd from t * ERROR at line 1: ORA-00904: "ASDASD": invalid identifier <-- Yes (If neither column nor table exists, ORA-942 occurs because syntax check goes backward from statement end.) SQL> select * from v$mystat where statistic# = 234; SID STATISTIC# VALUE ---------- ---------- ---------- 11 234 5 SQL> select *asdasd from t; select *asdasd from t * ERROR at line 1: ORA-00923: FROM keyword not found where expected <-- Yes SQL> select * from v$mystat where statistic# = 234; SID STATISTIC# VALUE ---------- ---------- ---------- 11 234 6 SQL> asasas SP2-0042: unknown command "asasas" - rest of line ignored. <-- No; didn't pass client side syntax check SQL> select * from v$mystat where statistic# = 234; SID STATISTIC# VALUE ---------- ---------- ---------- 11 234 6 SQL> select; select * ERROR at line 1: ORA-00936: missing expression <-- Yes SQL> select * from v$mystat where statistic# = 234; SID STATISTIC# VALUE ---------- ---------- ---------- 11 234 7 SQL> desc junk ERROR: ORA-04043: object junk does not exist <-- No; although this check is done on server, it hasn't reached SQL layer SQL> select * from v$mystat where statistic# = 234; SID STATISTIC# VALUE ---------- ---------- ---------- 11 234 7 SQL> sho user USER is "YONG" SQL> create table u.junk (x number) tablespace tools; Table created. SQL> create synonym ujunk for u.junk; Synonym created. SQL> desc ujunk Name Null? Type ----------------- -------- ------ X NUMBER SQL> drop table u.junk; Table dropped. SQL> desc ujunk ERROR: ORA-04043: object "U"."JUNK" does not exist SQL> select * from v$mystat where statistic# = 234; SID STATISTIC# VALUE ---------- ---------- ---------- 11 234 7 SQL> select * from junk; select * from junk * ERROR at line 1: ORA-00942: table or view does not exist <-- Yes SQL> select * from v$mystat where statistic# = 234; SID STATISTIC# VALUE ---------- ---------- ---------- 11 234 8 SQL> update; update * ERROR at line 1: ORA-00903: invalid table name SQL> select * from v$mystat where statistic# = 234; SID STATISTIC# VALUE ---------- ---------- ---------- 11 234 9 SQL> delete ; delete * ERROR at line 1: ORA-00903: invalid table name SQL> select * from v$mystat where statistic# = 234; SID STATISTIC# VALUE ---------- ---------- ---------- 11 234 10 SQL> insert; insert * ERROR at line 1: ORA-00925: missing INTO keyword SQL> select * from v$mystat where statistic# = 234; SID STATISTIC# VALUE ---------- ---------- ---------- 11 234 11 SQL> insert into; insert into * ERROR at line 1: ORA-00903: invalid table name SQL> select * from v$mystat where statistic# = 234; SID STATISTIC# VALUE ---------- ---------- ---------- 11 234 12 Summary: These errors cause parse failures (text in brackets is mine): ORA-00942: table or view does not exist [including missing synonym] ORA-00904: "%s": invalid identifier [%s is a column] ORA-00923: FROM keyword not found where expected ORA-00936: missing expression [in our case, it's only 'select'] ORA-00903: invalid table name ['update', 'delete' or 'insert into' not followed by table name] ORA-00925: missing INTO keyword ['insert' not followed by 'into'] Q: If I see the parse failures for a session, how do I find the failed SQLs? A: "Audit not exists" records the SQL that failed to find the missing table or view (but not missing columns). The best way to capture the SQLs is to set event 10035 (Write parse failures to alert log file, level 1 would be enough), not 10046 (SQL trace), because the latter does not capture all SQLs failing parsing. Ref: How to Identify Hard Parse Failures [Article ID 1353015.1] /* outdated: My parse_failure.pl has some difficulty. You can try the -S option with local connection. But not all SQLs that failed parsing are caught. The -S option is based on the assumption that the SQL with command type 0 in x$kglcursor was partially parsed and failed. But that's neither documented nor extensively tested. A more practical way is, if you see a session frequently generate parse failures, enable SQL trace on the session (exec dbms_system.set_ev(,,10046,4,0)) and find the bad SQLs clearly logged in the trace file. You can view all the error lines and the first lines of the SQLs with this command: perl -nle 'if (/^PARSE ERROR/) {print; print $_=<>;}' tracefile or in continuous fashion: tail -f tracefile | perl -nle 'if (/^PARSE ERROR/) {print; print $_=<>;}' Change ' to " on Windows (and install tail.exe and have perl.exe in %path%). If you want to catch a specific error such as ORA-942, you can enable event '942 trace name errorstack' or simply '942 trace name context' (the latter only records the bad table or view or synonym, not the full SQL). */ Warning: Please see ../freeware/ossw.html and use the tool ossw instead of parse_failure.pl! The following is kept for historical purposes. Now the script, first, the non-RAC version: #!perl #parse_failure.pl: Show session parse failure statistics, useful after table rename, synonym change, column add, etc #2007-05,-06: Created the script #2007-08: Added -S functionality, insert newline even without -v, changed default sampling rate to 5sec #2008-09: Added default user #On Windows, if you have Oracle installed, try including Perl's path, e.g., #set path=%path%;C:\oracle\product\10.2.0\db_1\perl\5.8.3\bin\MSWin32-x86-multi-thread #If that doesn't work, install ActiveState Perl from www.activestate.com, run `ppm install DBI'. #Then run the command below. Check Usage for their meanings of options. ^C to stop. #perl parse_failure.pl -u username -p password -v -d3 -s connectionstring #On UNIX/Linux, change the #! line to use perl under $ORACLE_HOME/perl/bin, and run #export PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/lib/site_perl/5.10.0 #export LD_LIBRARY_PATH=$ORACLE_HOME/lib32 (if 10g or lower) #export LD_LIBRARY_PATH=$ORACLE_HOME/lib (if 64-bit 11g) #(check paths first; lib32 may be lib). Then run the command below. ^C to stop. #./parse_failure.pl -u username -p password -v -d3 -s connectionstring $defusr = "perfstat"; #Please change to a user with create session priv and select_catalog_role. $defpass = "perfstat"; ##### No need to modify below this line. But hacking is welcome. ##### $| = 1; use Getopt::Std; getopts('hs:u:p:d:vn:S'); #if ($#ARGV == -1 or defined $opt_h or (!defined $opt_s and !defined $opt_S)) if (defined $opt_h or (!defined $opt_s and !defined $opt_S)) { print "Usage: parse_failure.pl [-h] -s TNSAlias [-u User -p Password] [-d Delay] [-v] [-n LowerLimit] [-S] -h: Help -s: (required unless -S is specified) Connect identifier -u: Alternate user instead of $defusr to login as -p: Alternate user's password -d: Number of seconds delay between calls (default 5) -v: Also show sessions whose parse failures are not changing (output marked 'NoChange!') -n: Only show sessions whose parse failures exceed n -S: (-s and -v cannot be specified, -u and -v will be ignored) Show SQL possibly causing the latest parse failure. Must run parse_failure.pl locally since it connects sys as sysdba Common usage examples: parse_failure.pl -s mydb parse_failure.pl -s dbconn -v parse_failure.pl -S #must run on the DB server locally Output is like: 1234-33:5:1:appserver where 1234 is SID, 33 serial#, 5 parse failures so far, 1 increment of parse failures over last sampling, appserver client machine If -S is specified, you may see hash=2528439425; module=sqlplus\@dbhostname (TNS V1-V3) Likely:select * from nosuch That's the SQL likely causing the parse failure. If the first word is Unlikely, the culprit SQL was not captured and may be prev or prev prev ... SQL.\n"; exit; } $opt_d = 5 if !defined $opt_d; $opt_n = 0 if !defined $opt_n; print "Output is like: 1234-33:5:1:hr-v3besextm001 where 1234 is SID, 33 serial#, 5 parse failures so far, 1 increment of parse failures over last sampling, hr-v3besextm001 client machine\n\n"; use DBI; if (defined $opt_S) { die "-s and -v cannot be specified if -S is specified!" if defined $opt_s or defined $opt_v; $dbh = DBI->connect("dbi:Oracle:", "/", "", {ora_session_mode=>2,RaiseError=>1}); } elsif (defined $opt_u) { die "Alternate user and password must be defined together!" if !defined $opt_p; $dbh = DBI->connect("dbi:Oracle:$opt_s", $opt_u, $opt_p, {RaiseError=>1}); } else { $dbh = DBI->connect("dbi:Oracle:$opt_s", $defusr, $defpass, {RaiseError=>1}); } die "Cannot do $dbh->connect: $DBI::errstr!" if !defined $dbh; $SIG{INT} = sub {exit}; #must be set to handle ^C and must be after DBI->connect $sql = "select statistic# from v\$statname where name='parse count (failures)'"; $sth = $dbh->prepare($sql); $sth->execute(); $statnum = $sth->fetchrow_array; #statistic# for 'parse count (failures)' if (!defined $opt_S) { $sql = "select a.sid||'-'||a.serial\#, b.value, a.machine from v\$session a, v\$sesstat b where b.statistic\#=$statnum and b.value>$opt_n and a.sid=b.sid"; $sth = $dbh->prepare($sql); } else #x$kglcursor.kglobt02 (cmd type) being 0 means something wrong with parsing { $sql = "select a.sid||'-'||a.serial\#, b.value, a.machine, a.sql_hash_value, kglobts0 module, decode(kglobt02,0,'Likely:','Unlikely:')||kglnaobj sqltxt from v\$session a, v\$sesstat b, x\$kglcursor c where b.statistic\#=$statnum and b.value>$opt_n and a.sid=b.sid and a.sql_hash_value=c.kglnahsh"; $sth = $dbh->prepare($sql); } while (1) { $sth->execute(); while (@row = $sth->fetchrow_array) { #@row[0]: SID-serial#; [1]: v$sesstat.value; [2]: v$session.machine. #If -S, [3]: v$session.sql_hash_value; [4]: x$kglcursor.kglobts0 (module) #[5]: x$kglcursor.kglnaobj: '(Likely|Unlikely):' i.e. Possibly culprit SQL #SID-serial# combo, not SID alone, is key because SID may be the same for diff sessions. #print join("\t", @row), " **********\n"; #debug $sidsr=$row[0]; $sidval=$row[1]; $sidmac=$row[2]; if (defined $opt_S) { $hash=$row[3]; $mod=$row[4]; $sqltxt=$row[5]; } if (defined $HoA{$sidsr}[0]) #It's undefined in first iteration. { $dltval{$sidsr} = $sidval - $HoA{$sidsr}[0]; #Delta sesstat value if ($dltval{$sidsr} > 0) { print "$sidsr:$sidval:$dltval{$sidsr}:$sidmac\n"; #e.g.: 1234-33:5:1:hr-v3besextm001 print "hash=$hash; module=$mod\n$sqltxt\n" if defined $opt_S; } elsif (defined $opt_v) #print their values even if they don't change between samplings { print "NoChange! $sidsr:$sidval:0:$sidmac\n"; } } if (!defined $opt_S) { $HoA{$sidsr} = [ ($sidval, $sidmac) ]; #$sidsr is key to this hash of array } else { $HoA{$sidsr} = [ ($sidval, $sidmac, $hash, $mod, $sqltxt) ]; } $allsidsr{$sidsr} = 1; #create this hash for all SID-serial#'s } foreach (keys %HoA) { delete $HoA{$_} if ! exists $allsidsr{$sidsr}; #This SID-serial# has disappeared. } #print "\n" if defined $opt_v; #Append one more \n for clarity and reminds of verbose mode print "\n"; sleep $opt_d; } And here's the RAC version: #!perl #parse_failure_RAC.pl: RAC version of Oracle parse_failure.pl, which shows session parse failure #statistics, useful after table rename, synonym change, column add, etc #2007-05,-06: Created the script #2007-08: Added -S functionality, insert newline even without -v, changed default sampling rate to 5sec #2008-09: Added default user, made RAC-aware #On Windows, if you have Oracle installed, try including Perl's path, e.g., #set path=%path%;C:\oracle\product\10.2.0\db_1\perl\5.8.3\bin\MSWin32-x86-multi-thread #If that doesn't work, install ActiveState Perl from www.activestate.com, run `ppm install DBI'. #Then run the command below. Check Usage for their meanings of options. ^C to stop. #perl parse_failure_RAC.pl -u username -p password -v -d3 -s connectionstring #On UNIX/Linux, change the #! line to use perl under $ORACLE_HOME/perl/bin, and run #export PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/lib/site_perl/5.10.0 #export LD_LIBRARY_PATH=$ORACLE_HOME/lib32 (if 10g or lower) #export LD_LIBRARY_PATH=$ORACLE_HOME/lib (if 64-bit 11g) #(check paths first; lib32 may be lib). Then run the command below. ^C to stop. #./parse_failure_RAC.pl -u username -p password -v -d3 -s connectionstring $defusr = "perfstat"; #Please change to a user with create session priv and select_catalog_role. $defpass = "perfstat"; ##### No need to modify below this line. But hacking is welcome. ##### $| = 1; use Getopt::Std; getopts('hs:u:p:d:vn:S'); #if ($#ARGV == -1 or defined $opt_h or (!defined $opt_s and !defined $opt_S)) if (defined $opt_h or (!defined $opt_s and !defined $opt_S)) { print "Usage: parse_failure.pl [-h] -s TNSAlias [-u User -p Password] [-d Delay] [-v] [-n LowerLimit] [-S] -h: Help -s: (required unless -S is specified) Connect identifier -u: Alternate user instead of $defusr to login as -p: Alternate user's password -d: Number of seconds delay between calls (default 5) -v: Also show sessions whose parse failures are not changing (output marked 'NoChange!') -n: Only show sessions whose parse failures exceed n -S: (-s and -v cannot be specified, -u and -v will be ignored) Show SQL possibly causing the latest parse failure. Must run parse_failure.pl locally since it connects sys as sysdba Common usage examples: parse_failure.pl -s mydb parse_failure.pl -s dbconn -v parse_failure.pl -S #must run on the DB server locally Output is like: 2-1234-33:5:1:appserver where 2 is instance ID, 1234 SID, 33 serial#, 5 parse failures so far, 1 increment of parse failures over last sampling, appserver client machine If -S is specified, you may see hash=2528439425; module=sqlplus\@dbhostname (TNS V1-V3) Likely:select * from nosuch That's the SQL likely causing the parse failure. If the first word is Unlikely, the culprit SQL was not captured and may be prev or prev prev ... SQL.\n"; exit; } $opt_d = 5 if !defined $opt_d; $opt_n = 0 if !defined $opt_n; print "Output is like: 2-1234-33:5:1:hr-v3besextm001 where 2 is instance ID, 1234 SID, 33 serial#, 5 parse failures so far, 1 increment of parse failures over last sampling, hr-v3besextm001 client machine\n\n"; use DBI; if (defined $opt_S) { die "-s and -v cannot be specified if -S is specified!" if defined $opt_s or defined $opt_v; $dbh = DBI->connect("dbi:Oracle:", "/", "", {ora_session_mode=>2,RaiseError=>1}); } elsif (defined $opt_u) { die "Alternate user and password must be defined together!" if !defined $opt_p; $dbh = DBI->connect("dbi:Oracle:$opt_s", $opt_u, $opt_p, {RaiseError=>1}); } else { $dbh = DBI->connect("dbi:Oracle:$opt_s", $defusr, $defpass, {RaiseError=>1}); } die "Cannot do $dbh->connect: $DBI::errstr!" if !defined $dbh; $SIG{INT} = sub {exit}; #must be set to handle ^C and must be after DBI->connect $sql = "select statistic# from v\$statname where name='parse count (failures)'"; $sth = $dbh->prepare($sql); $sth->execute(); $statnum = $sth->fetchrow_array; #statistic# for 'parse count (failures)' if (!defined $opt_S) { $sql = "select a.inst_id||'-'||a.sid||'-'||a.serial\#, b.value, a.machine from gv\$session a, gv\$sesstat b where b.statistic\#=$statnum and b.value>$opt_n and a.inst_id=b.inst_id and a.sid=b.sid"; $sth = $dbh->prepare($sql); } else #Connect to local instance only, as / as sysdba; #x$kglcursor.kglobt02 (cmd type) being 0 means something wrong with parsing { $sql = "select a.sid||'-'||a.serial\#, b.value, a.machine, a.sql_hash_value, kglobts0 module, decode(kglobt02,0,'Likely:','Unlikely:')||kglnaobj sqltxt from v\$session a, v\$sesstat b, x\$kglcursor c where b.statistic\#=$statnum and b.value>$opt_n and a.sid=b.sid and a.sql_hash_value=c.kglnahsh"; $sth = $dbh->prepare($sql); } while (1) { $sth->execute(); while (@row = $sth->fetchrow_array) { #@row[0]: inst_id-SID-serial#; [1]: v$sesstat.value; [2]: v$session.machine. #If -S, [3]: v$session.sql_hash_value; [4]: x$kglcursor.kglobts0 (module) #[5]: x$kglcursor.kglnaobj: '(Likely|Unlikely):' i.e. Possibly culprit SQL #inst_id-SID-serial# combo, not inst_id and SID alone, is the key to the hash because #SID may be the same for different sessions. #print join("\t", @row), " **********\n"; #debug $sidsr=$row[0]; $sidval=$row[1]; $sidmac=$row[2]; if (defined $opt_S) { $hash=$row[3]; $mod=$row[4]; $sqltxt=$row[5]; } if (defined $HoA{$sidsr}[0]) #It's undefined in first iteration. { $dltval{$sidsr} = $sidval - $HoA{$sidsr}[0]; #Delta sesstat value if ($dltval{$sidsr} > 0) { print "$sidsr:$sidval:$dltval{$sidsr}:$sidmac\n"; #e.g.: 1234-33:5:1:hr-v3besextm001 print "hash=$hash; module=$mod\n$sqltxt\n" if defined $opt_S; } elsif (defined $opt_v) #print their values even if they don't change between samplings { print "NoChange! $sidsr:$sidval:0:$sidmac\n"; } } if (!defined $opt_S) { $HoA{$sidsr} = [ ($sidval, $sidmac) ]; #$sidsr is key to this hash of array } else { $HoA{$sidsr} = [ ($sidval, $sidmac, $hash, $mod, $sqltxt) ]; } $allsidsr{$sidsr} = 1; #create this hash for all SID-serial#'s } foreach (keys %HoA) { delete $HoA{$_} if ! exists $allsidsr{$sidsr}; #This SID-serial# has disappeared. } #print "\n" if defined $opt_v; #Append one more \n for clarity and reminds of verbose mode print "\n"; sleep $opt_d; }