Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Cursor Cache and Excessive Parsing

Cursor Cache and Excessive Parsing

From: Matt <mccmx_at_hotmail.com>
Date: 25 Feb 2004 08:42:52 -0800
Message-ID: <cfee5bcf.0402250842.edd3ff1@posting.google.com>


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 Received on Wed Feb 25 2004 - 10:42:52 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US