Cry about...
Oracle How To ...

Identify significant reparsing of SQL

The shared-pool contains (amongst other things) previously parsed SQL, and this allows Oracle to avoid re-parsing SQL unnecessarily.

The following SQL identifies those SQL statements that have needed to be re-parsed numerous times:

select executions, t.sql_text
from v$sqlarea a, v$sqltext t
where parse_calls >1 and parse_calls = executions and a.address=t.address and executions > 10000
order by executions desc;

If this returns a large number of rows then increase the number of ‘executions’ required. If it returns no rows then perhaps decrease the number of executions required.

If there is SQL that is being repeatedly reparsed then consider increasing the value of SHARED_POOL_SIZE.

This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.