Note: (Prior to Oracle 10.2) Spotlight's ability to diagnose SQL parsing was limited. For (Oracle 10.2 and later) the SQL & Application Workload | Parse Activity Page can provide additional information on parse ratios and parse wait times, and on the SQL statements and user sessions responsible for high parse rates.
Parsing is the process of preparing your SQL statement for execution. This process is comparable to the process a language compiler or interpreter must undertake in order to translate high-level statements into machine code. The parsing process includes the following phases:
Parsing can be an expensive operation. Its overhead is often masked by the greater overhead of high I/O requirements. However, eliminating unnecessary parsing is always desirable.
The parse/execute ratio reflects the ratio of parse calls to execute calls. Because parsing is an expensive operation, it is better to parse statements once and then execute them many times. High parse ratios (greater than 20%) can result from the following circumstances:
If an application is discarding cursors, it may be possible to relieve some of the parse overhead by creating a session cursor cache. You can do this by using the SESSION_CACHED_CURSORS configuration parameter. This setting allows Oracle to maintain a cache of SQL statements in the session memory. If a session requests a parse of a statement that it has already parsed, then it might be found in the cache and re-parsing is avoided.
Parsing (Oracle 10.2 and later)
Oracle stores timing information on events such as active database time and parse time, enabling database administrators to see how much of the time spent executing SQL statements is spent in parsing. This information can be analyzed at a system level, and also by session.
Administrators can also identify where the use of bind variables may have allowed cursors to be shared. (To see where an application can be improved, and also view the benefits of sharing cursors, see the SQL & Application Workload | Parse Activity | SQL Page)
(Oracle 11g and later) Oracle also:
Using Spotlight to reduce parse overhead
When Spotlight raises the Total Parse Wait Time Ratio Alarm, or when you otherwise suspect that parse times may be too high you can: