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
SELECT first_load_time, sql_text 

FROM v$sql
WHERE first_load_time > to_char(sysdate-1/(24*6), 'YYYY-MM-DD/HH24:MI:SS')
Keep 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.

No comments: