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.