misses in librarycache during parse

Hi Freinds..

I have three questions.

1.)

One thing I don't understand is how one statement can have 2,3 or 4 hard parses.

I traced a session to identify sql performance issues.
One thing I noticed was that many statements had a misses in library cache during parse of 2, 3 and 4. I would expect this to be 1 (only reload once if text is not already there)

These are considered 'hard parses' i.e space in the shared pool has to be made available and cpu is consumed.

2.)
How can the amount of hard parses be reduced ?

I understand that setting session_cached_cursors and increasing shared pool would help, but I have limited resources and the reloads/pins ratio is below 1% anyway.

Can anyone gve me pointers on how the sql can be changed to reduce hard parses ?

3.)

When timed statistics are set to true, can cpu time and elapsed time be trusted ? Most of the statements which had many hard parses had a very low cpu time and elapsed time. Considering my reloads/pins ratio is low (0.3%) Does this mean that I can ignore these hard parses as a performance issue?

Thanks

Suresh

BTW, most of the problems with performance identified by the traced session were full table scans and massive buffer gets numbers. I am now attempting to establish whether hard parses are a performance problem.

Make sure your query uses the bind variables. Then if you cannot control how the query gets defined, then set

ALTER SYSTEM SET CURSOR_SHARING=FORCE;

make the changes in the init.ora file too
CURSOR_SHARING=FORCE

This would force cursor sharing but would increase the soft parses too.

Sam

[Edited by sambavan on 10-09-2001 at 05:03 PM]

Be care with this, we had some major bugs in Oracle 8.1.6.2 and ended up not being able to use it.