parse count (failures) – How do I find queries causing failures ?

In my last blog on Hard Parses and failures posted on September 3, 2011, I missed to write about the ways to find the Queries causing “parse count (failures)”. I received few mails from some of readers wanting to know on how to get these. Since these queries fail, these are not stored in the Shared Pool and therefore, it is not possible to get these from v$ views. Secondly, since these are not stored in the Shared Pool, everytime these queries are executed, these are hard parsed, thus causing unwanted contention on Library Cache latches.

A possible way to get these is 10046 traces. I used these traces to get the Queries that were causing these failures to occur. Yesterday, I was working at another customer site, where I could see some failures happening. These are again around 25% to 30% of the total Hard Parses. Therefore, if we eliminate these, total hard parses / second would come down. Following query helps me check, whether the statistics related to hard parses are alarming.

set linesize 132
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select sysdate, name, value from v$sysstat where name like 'parse%';
exec dbms_lock.sleep(10);
select sysdate, name, value from v$sysstat where name like 'parse%';

I execute a query against v$sysstat, wait for 10 seconds and re-execute the same. Therefore, the incremental figure is for 10 seconds duration. From this, it is very easy to compute the parses/second ratio. Following is the output of the query taken yesterday :

SQL> @hard_parse

Session altered.

Elapsed: 00:00:00.00

SYSDATE             NAME                                                                  VALUE
------------------- ---------------------------------------------------------------- ----------
20-09-2011 10:28:14 parse time cpu                                                       117213
20-09-2011 10:28:14 parse time elapsed                                                   390923
20-09-2011 10:28:14 parse count (total)                                                54294681
20-09-2011 10:28:14 parse count (hard)                                                   766713
20-09-2011 10:28:14 parse count (failures)                                               177555
20-09-2011 10:28:14 parse count (describe)                                                  204

6 rows selected.

Elapsed: 00:00:00.03

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.00

SYSDATE             NAME                                                                  VALUE
------------------- ---------------------------------------------------------------- ----------
20-09-2011 10:28:24 parse time cpu                                                       117216
20-09-2011 10:28:24 parse time elapsed                                                   390969
20-09-2011 10:28:24 parse count (total)                                                54303954
20-09-2011 10:28:24 parse count (hard)                                                   767011
20-09-2011 10:28:24 parse count (failures)                                               177637
20-09-2011 10:28:24 parse count (describe)                                                  204

6 rows selected.

Elapsed: 00:00:00.02
SQL> select 767011-766713, 177637-177555 from dual;

767011-766713 177637-177555
------------- -------------
          298            82

In this case, the Hard Parses are 29.8 per second and failures are 8.2, which is around 28% of the total hard parses. To check for the queries causing these, I generated enabled 10046 trace at system level, during less concurrency time (non-peak hours), and issued grep -i “PARSE ERROR” *.trc. This gave me the name of the trace files and from these traces, it was very easy to figure out the queries that were failing. In the trace file, search for PARSE ERROR and you will get the query, alongwith the reason for its failure, which is ora-942 (table or view does not exists), as can be seen in err column.

grep -i "PARSE ERROR" *.trc
RAC1_ora_1966688.trc:PARSE ERROR #1:len=157 dep=0 uid=38 oct=3 lid=38 tim=2611876001420 err=942
RAC1_ora_1966688.trc:PARSE ERROR #1:len=157 dep=0 uid=38 oct=3 lid=38 tim=2611876029670 err=942
RAC1_ora_1966688.trc:PARSE ERROR #5:len=95 dep=1 uid=0 oct=3 lid=0 tim=2611876430154 err=942

Advertisement