Re: bad ratio in parse_calls to executions of SQL

Oracle does two types of parses - Hard Parse and Soft Parse. If a new SQL statement is issued which does not exist in the shared pool then this has to be parsed fully. Memory has to be allocated in shared pool, the statement has to be checked syntactically, semantically etc,. This is hard parse which is expensive.

  If a SQL statement is issued which is already in shared pool and it can use the existing version of that statement, then it is called   as a soft parse. As far as an application is concerned, parsing is done in both cases.

  This can be noticed in SQL trace. If there is a miss in the library cache, then Hard parse will be done.

  Set the sql_trace on and using tkprof utility get the report and analyze the SQL statements.

"The Views expressed here are my own and not necessarily those of Oracle Corporation"

"Leonid Berezin" <lberezin_at_net2phone.com> wrote in message news:38623DCF.357853CC_at_net2phone.com...
> Platform: Oracle 7.3.4 on Solaris
> I have a few queries that executes very often (1-2 millions times) and
> they are using bind variables. When I look into v$sqlarea I see that
> parse_calls for that statements is 2/3 of executions. The statements in
> question are executed both from database triggers and from the VB front
> end. Is there any recommendation on how to reduce the number of parses.
>
> Thank's
>
> Leonid
>
Received on Thu Dec 23 1999 - 23:01:44 CST