I have three questions.
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.
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 ?
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?
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.
Once you have eliminated all of the impossible,
whatever remains however improbable,
must be true.
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
This would force cursor sharing but would increase the soft parses too.
[Edited by sambavan on 10-09-2001 at 05:03 PM]
Life is a journey, not a destination!
Be care with this, we had some major bugs in Oracle 18.104.22.168 and ended up not being able to use it.
Click Here to Expand Forum to Full Width