Re: Soft parse or Hard parse

You may not be right. In your example
it is possible for v$sql to hold a very
large number of child cursors (possibly
limited to 1023, but I'm not sure about
that).

There is a fair chance, though, that your assumption about soft parses is correct - check the LOADS, INVALIDATIONS,
VERSION_COUNT, and LOAD_VERSIONS
columns for very small numbers to confirm this.

You are, however, still doing lots of parse calls which could be avoided, or could be made cheaper. However you only have
a problem to address if you are losing
CPU or elapsed time to parsing (e.g.
parse time (CPU) in v$sesstat, or
time lost of 'latch free' in v$session_event with lots of sleeps on library cache and shared pool latches.

If you need to improve performance,
especially if heading for high concurrency, you can look at session_cached_cursors
as a holding point, and then look to modifying the code to 'parse once, execute many'.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now available One-day tutorials:
  Cost Based Optimisation
  Trouble-shooting and Tuning
  Indexing Strategies

(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______April 8th
____UK_______April 22nd

____Denmark May 21-23rd

____USA_(FL)_May 2nd


Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____UK_(Manchester)_May
____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"charlie cs" <cs3526(no-spam)@yahoo.com> wrote in message
news:6aFca.57801$gi1.41465_at_nwrdny02.gnilink.net...


> John Weeg in his artical "are we parsing too much" provided a sql to

> identified the sql statements that went through too much parse.

>

> It is something like:

>

> select parse_calls, executions, sql_text from v$sqlarea

> where parse_calls >= executions

>

> I found that in my database, there are quite some sql statement like

that


>

> parse_call     executions            sql_text

> 19900             19900                select * from A where col1=:x

> ...........

>

> Because they are from v$sqlarea, so they are all soft parses, and I

should


> not pay too much attention to it, am I right?

>

> And they already use bind values, there is not too much I can do

about it,


> am I right?

>

> Thanks for your help.

>

>