Spotlight on Oracle 10.4 - Getting Started Guide

Reduce Parse Overhead

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

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:

• Checking that the SQL statement is syntactically valid (that is, that the SQL conforms to the rules of the SQL language, and that all keywords and operators are valid and correctly used).
• Checking that the SQL is semantically valid. This means that all references to database objects (such as tables and columns) are valid.
• Checking security (that is, that the user has permission to perform the specified SQL operations on the objects involved).
• Determining an execution plan for the SQL statement. The execution plan describes the series of steps that Oracle performs in order to access and update the data involved.

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 literals, rather than bind variables, are used as query parameters, the SQL must be re-parsed on every execution. You should use bind variables whenever possible, unless there is a pressing reason for using column histograms.
• Some development tools or techniques result in SQL cursors being discarded after execution. If a cursor is discarded, then the parse is required before the statement can be re-executed.

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:

• Stores the hard parse time for each individual cursor.
• Introduces latching mechanisms called mutexes, which help reduce contention on the shared pool and library cache. The new mutex waits display in Spotlight as part of the latch wait time.

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:

• Use the SQL & Application Workload | Parse Activity Page charts to determine if parsing is a problem.
• Use the SQL & Application Workload | Parse Activity | SQL Page to identify if one application is causing the problem. If so:
  • Consider changing the application to use bind variables.
  • If you cannot change the application, consider changing the setting of the Oracle CURSOR_SHARING parameter. Oracle Parameters Page
• Use the SQL & Application Workload | Parse Activity | Sessions Page to identify if one application is causing the problem. If so, consider using the Sessions | Top Sessions Page to kill the session.