SELECT first_load_time, sql_textKeep in mind that not necessarily each hard parse is bad. Actually every statements needs at least one hard parse. But if you see on the other hand a lot SQL statements that are not using bind variables the problem at hand is obvious.
WHERE first_load_time > to_char(sysdate-1/(24*6), 'YYYY-MM-DD/HH24:MI:SS')
Friday, December 24, 2004
How to determine SQL statements that cause hard parses
From a tuning point of view hard parses can be quite limiting to the scalability of database and an application. If the number of hard parses is high this is a serious problem. To tackle the problem the first step is to determine which SQL statements are causing hard parses. With the column FIRST_LOAD_TIME of the view V$SQL this can be determined. The following example shows how this knowledge can be used. It reports all statement that have been hard parsed in the last ten minutes