Re: Cursor Cache and Excessive Parsing

On 25 Feb 2004 08:42:52 -0800, mccmx_at_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