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 <some magic parse failure log>".


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(<sid>,<serial#>,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):<SQL text>' 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):<SQL text>' 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;
}