Hello everyone,

I am in the process of tuning a long running batch job. I run the
process while enabling a SQL TRACE (with alter session) and then run
my '*.trc' file through tkprof and sort it on elapsed time to
highlight my worst offenders.

On my first pass through the trace file I noticed that the parse
elapsed time for most of these statements is much higher than both the
execute and fetch phases combined.

So I scroll down to the bottom of the tkprof output to check the
summary of the whole session. Over 70% of the traced interval is
spent on parsing the SQL (the vast majority of which is CPU parse time
which rules out any latch contention).

So this could be caused by one of 2 things:

1. A very long parse phase for a few statements or
2. Repeated parsing of the same statements

It turned out that it was a combination of both of these.

To address number 1 above I lowered the optimizer_max_permutations to
2000 (from 80000) to reduce the amount of time Oracle spent parsing
the complex SQL. This helped to a small degree.

However the main part of the parse overhead is the repeated reparsing
of the same statements. In the SQL_TRACE output the parse and execute
count section for every statement are equal, meaning that it is
reparsing every single SQL on every execution.

So I enabled a session cursor cache (100). During the next run of the
process I observed a large increase in the 'cursor cache hits' in
v$sysstat but the process takes exectly the same length of time to
run, and the parse and execute statements are exactly equal again.

So my question (finally) is what are the conditions for a statement to
be added into the cursor cache. Because it is evident that I am not
getting the benefit of this cache despite what 'v$sysstat' is
reporting.

Any help on this would be greatly appreciated because I have been
pulling my hair out for 3 days on this problem...

Thanks in advance...

Matt

On 25 Feb 2004 08:42:52 -0800, mccmx@hotmail.com (Matt) wrote:

>Hello everyone,
>
>I am in the process of tuning a long running batch job. I run the
>process while enabling a SQL TRACE (with alter session) and then run
>my '*.trc' file through tkprof and sort it on elapsed time to
>highlight my worst offenders.
>
>On my first pass through the trace file I noticed that the parse
>elapsed time for most of these statements is much higher than both the
>execute and fetch phases combined.
>
>So I scroll down to the bottom of the tkprof output to check the
>summary of the whole session. Over 70% of the traced interval is
>spent on parsing the SQL (the vast majority of which is CPU parse time
>which rules out any latch contention).
>
>So this could be caused by one of 2 things:
>
>1. A very long parse phase for a few statements or
>2. Repeated parsing of the same statements
>
>It turned out that it was a combination of both of these.
>
>To address number 1 above I lowered the optimizer_max_permutations to
>2000 (from 80000) to reduce the amount of time Oracle spent parsing
>the complex SQL. This helped to a small degree.
>
>However the main part of the parse overhead is the repeated reparsing
>of the same statements. In the SQL_TRACE output the parse and execute
>count section for every statement are equal, meaning that it is
>reparsing every single SQL on every execution.
>
>So I enabled a session cursor cache (100). During the next run of the
>process I observed a large increase in the 'cursor cache hits' in
>v$sysstat but the process takes exectly the same length of time to
>run, and the parse and execute statements are exactly equal again.
>
>So my question (finally) is what are the conditions for a statement to
>be added into the cursor cache. Because it is evident that I am not
>getting the benefit of this cache despite what 'v$sysstat' is
>reporting.
>
>Any help on this would be greatly appreciated because I have been
>pulling my hair out for 3 days on this problem...
>
>Thanks in advance...
>
>Matt


You are starting from the wrong end. Your problem is caused by a
developer who doesn't how to program to avoid parses.
Find him/her, threaten him or sue him.
Seriously: You will never ever be able to solve excessive parsing by
database tuning.



--
Sybrand Bakker, Senior Oracle DBA

Are you saying that "session_cached_cursors" has no effect....?

The code isn't as bad as some I've dealt with in the past... at least
they are making use of bind variables.

In the absence of 'ideal code' I was hoping to squeeze some benefit
from using a cursor cache (which I thought was its intended purpose).

Also bear in mind that the 'recursive' SQL is also being repeatedly
reparsed (which can't be blamed on the developers).

For a process that is 75% parse time, surely there is something at the
back end I can do to improve the response time.

I've already identified that the large bulk of parse time is due to
the repeated reparsing of 'every' statement.

Thanks for your time

Matt

SESSION CACHED CURSORS will only help reduce the time spent on soft
parsing, not hard parsing.
In TKPROF putput, for each SQL, check the value for "Misses in library
cache during parse". Check if this is equal to the PARSE COUNT.
regards
Srivenu

Hi Matt,

"Matt" <mccmx@hotmail.com> wrote in message
news:cfee5bcf.0402250842.edd3ff1@posting.google.co m...

> However the main part of the parse overhead is the repeated reparsing
> of the same statements. In the SQL_TRACE output the parse and execute
> count section for every statement are equal, meaning that it is
> reparsing every single SQL on every execution.
> Matt

This means your statements are getting soft parsed everytime
(parse=executes). The best way to avoid soft parses is writting PL/SQL
procedure. If your batch job is written by any other method (not PL/SQL
store proc), it is difficlut to avoid soft parses. Make sure that you are
using bind variables in all the places, even though you generate the
statement dynamically in your batch job. By writting PL/SQL store proc, you
will able to achive ratio of 1 parse: n executes.

Also refer to asktom.oracle.com for more on this...

Sameer

Hi Matt

It looks like you have followed exactly the correct path in troubleshooting.
You don't say what this batch process is written in, but it might be that it
uses a technology that deliberately parses the same statement twice. I
believe JDBC does this by default and that perl dbi does as well. If so it
is likely that you can turn off the excessive parse calls when setting up
the db connections. The bottom line though is that it is going to be the
client that is issuing the parse calls and so the problem will need to be
fixed at the client end (I'm eliminating a ludicrously small shared pool
here).


--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Matt" <mccmx@hotmail.com> wrote in message
news:cfee5bcf.0402260016.1f6e8e4@posting.google.co m...
> Are you saying that "session_cached_cursors" has no effect....?
>
> The code isn't as bad as some I've dealt with in the past... at least
> they are making use of bind variables.
>
> In the absence of 'ideal code' I was hoping to squeeze some benefit
> from using a cursor cache (which I thought was its intended purpose).
>
> Also bear in mind that the 'recursive' SQL is also being repeatedly
> reparsed (which can't be blamed on the developers).
>
> For a process that is 75% parse time, surely there is something at the
> back end I can do to improve the response time.
>
> I've already identified that the large bulk of parse time is due to
> the repeated reparsing of 'every' statement.
>
> Thanks for your time
>
> Matt

Thanks for the feedback...

My shared pool is 100Mb..

It looks like I have been short sighted. I have assumed all along
that most of the reparsing was down to soft rather that than hard
parsing. But in fact there were over 3000 "misses in library cache
during parse".

Therefore the cursor cache will never help...

Now for the interesting part:

Why am I getting so many hard parses..? Well it turns out that the
BATCH job does repeated TRUNCATES on all of the tables. Since TRUNC
is a DDL statement, all of the cursors are invalidated.

So I can only conclude that there is nothing I can do at the back end
to improve the throughput of this process.

Thanks for your help anyway..

Matt

hey I expect they put the trunc's in there to make it 'faster'

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Matt" <mccmx@hotmail.com> wrote in message
news:cfee5bcf.0402260920.2d44110d@posting.google.c om...
> Thanks for the feedback...
>
> My shared pool is 100Mb..
>
> It looks like I have been short sighted. I have assumed all along
> that most of the reparsing was down to soft rather that than hard
> parsing. But in fact there were over 3000 "misses in library cache
> during parse".
>
> Therefore the cursor cache will never help...
>
> Now for the interesting part:
>
> Why am I getting so many hard parses..? Well it turns out that the
> BATCH job does repeated TRUNCATES on all of the tables. Since TRUNC
> is a DDL statement, all of the cursors are invalidated.
>
> So I can only conclude that there is nothing I can do at the back end
> to improve the throughput of this process.
>
> Thanks for your help anyway..
>
> Matt