Re: Cursor Cache and Excessive Parsing

From: Sameer Deshpande <sameer_deshpande_at_hotmail.com>
Date: Thu, 26 Feb 2004 10:24:37 +0100
Message-ID: <c1ke1u$ttr$01$1@news.t-online.com>

Hi Matt,

"Matt" <mccmx_at_hotmail.com> wrote in message news:cfee5bcf.0402250842.edd3ff1_at_posting.google.com...

> 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 Received on Thu Feb 26 2004 - 03:24:37 CST