Database Tuning (MOSC)

MOSC Banner

How to identify statements causing hard parses

edited Apr 12, 2014 12:00PM in Database Tuning (MOSC) 1 commentAnswered


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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center