10035 trace NOT 10053 trace

How to find where you parse errors are on large systems, If you’re asked for a 10035 trace file, it probably isn’t a mistake.

I came across an interesting case last week. Excessive parse time can sometimes be a problem, especially with complex SQL, in which case you may see “Parse CPU to Parse Elapsed” percentages which are quite low.

This  ratio is derived from the  CPU time spent parsing, divided by the elapsed time for the parse.

If you’re not getting a ratio near 100% then this means there is competition for CPU parsing time. If you have one parse to do and plenty of CPU to spare, the ratio will be 100%. If there are millions of statements being parsed at the same time, you’ll see a lower ratio. So I was somewhat surprised when I saw a ratio of 0.0%

Further investigation revealed that the “failed parse elapsed time” was not zero and far higher that it should be. One possible scenario for such numbers is an application which is detecting parsing errors and re-submitting SQL. This might be a good guess or there might be another explanation, but how do you prove such a theory?

10035 trace (yes 10035 NOT 10053) collects parsing errors and dumps them in the alert log if they occur. I did a little experiment, with this SQL

drop table test;
select colx from test;

Naturally I’m going to get some errors. There is no table test and even if there was it would be dropped. The problem with these kinds of errors is they can slip by unnoticed. Grid Control shows nothing

10035 not 10053
(C) SteliosCharalambides.com

Unless the parse errors are truly stupendous in nature there is nothing to see. The AWR report can give a clue but you have to be looking very carefully. Here’s an AWR report I collected when I did LOTS of parse errors

(C) SteliosCharalambides.com
(C) SteliosCharalambides.com

The Time model output shows 0.34% for “failed parse elapsed time”. So to capture this information I set up a script

(C) SteliosCharalambides.com
(C) SteliosCharalambides.com

After running the script we can see in the alert log the following entries

Figure_4

So next time somebody asks for a 10035 trace don’t assume they are number dyslexic, just check what it is they are after and remember to look in the right place for the results.

2 thoughts on “10035 trace NOT 10053 trace”

  1. Hello Stelios,

    this is the first time I’ve heard about 10035 trace.
    It’s nice to know that you can enable additional tracing to collect parsing errors.

    Thanks for sharing!

    Regards,
    Marko

  2. Great info!

    We have had issues where some complex SQL at times took greater than 30 seconds to hard parse. Our JDBC timeout is set to 30 seconds. So when the user submits a SQL the first time and it doesn’t finish in 30 seconds, the request gets cancelled automatically. Unfortunately, the SQL wasn’t parsed the first time in 30 seconds. So they submit a new request and the same cycle repeats.

    2 questions:
    1) Is there a way to find how much time is spent in the initial hard parsing of a SQL? Unfortunately, there is no such column in V$SQL view.

    2) Is there an equivalent 10046 trace event for tracing the hard parsing of a SQL?

Leave a Reply

Your email address will not be published. Required fields are marked *