How to identify statements causing hard parses
I have seen rise in CPU utilization on my system (oracle10.2.0.5). Statspack refects very high hard parse activity. In a 1 hour time, it show 47000 for parse count. Normally I look at v$sql or v$sqlarea to look for similar statements - but there are only a few in the range of a hundred or more - and only 16000 rows in v$sqlarea. Is v$sqlare or v$sql getting flushed? How can I identify where the hard parsing is happening? Thanks
From statspack session....
parse count (failures) 3,577 1.0 0.0
parse count (hard) 47,266 13.2 0.3
parse count (total) 2,310,375 643.4 16.8
parse time cpu 78,765 21.9 0.6
parse time elapsed 121,814 33.9 0.9