Re: What is parse -vs- hard parse?

It's not true that "soft parse means that the application is using bind variables". Soft parse means the SQL statement is still "parse"d by the SQL engine and a search in library cache returns the parsed form successfully. I quote the word "parse" because I believe all Oracle does in this step is simply calculating the hash value of the statement and find the SQL statement in library cache based on the hash value. If it does not find it, a hard parse is performed to generate the ready-to- execute code.

From http://www.ixora.com.au/q+a/0008/29164243.htm:

• session cursor cache hits = no library cache access required
• shared cursor cache hits = library cache hits = soft parses
• shared cursor cache misses = library cache misses = hard parses

This tells us that, if you set session_cached_cursors to a non-zero number (which you should), the library cache is not hit at all. The parsed form of the SQL may be in your UGA, which is a very good thing!

In any case, using bind variables is good, unless you use column histograms.

Yong Huang
yong321_at_yahoo.com

In article <91r2gu$i7b$1_at_nnrp1.deja.com>,   jdarrah_co_at_my-deja.com wrote:
> Soft parse means that the application is using bind variables. A soft
> parse occurres when two users request either a) the exact same
> statement or b) the statements differ only in their bind variables.
> When a soft parse occures only the permissions are checked. The
> execution plan is resued and the syntax of the sql is not so a soft
> parse is much less cpu intensive than a hard parse. Your soft to hard
> parse ratio looks good.
>
> In article <91qulm$eo0$1_at_nnrp1.deja.com>,
> H2oStyle <sdempsey_at_my-deja.com> wrote:
> > I'm working with a system running Ora 8.1.5.00 Ent. Ed.
> > bstat/estat reports:
> > 108,572 total parses and
> > 3,572 hard parses
> > for 224632 executions of SQL statements
> >
> > I'm clear on what hard parses are, but what are the others?
> > Or rather what is going on during a non hard parse?
> >
> > And is there something to infer from the ratio of these "soft"
 parses
> > to executes(96.6%)? FYI, the application is not using bind vars,
 yet.
> > I read somewhere that Oracle reparses every sql statement that has
> > literals in the where clause. If that's true, I wonder if they
> > meant "soft" vs hard parse?
> >
> > Thanks in advance,
> > Scott

Sent via Deja.com
http://www.deja.com/ Received on Wed Dec 20 2000 - 14:52:13 CST