How Parsing in Oracle Causes Contention for Shared Pool and Library Cache Latches

Contentions for the shared pool and library cache latches are mainly due to intense hard parsing. A hard parse applies to new cursors and cursors that are aged out and must be re-executed. Excessive hard parsing is common among applications that primarily use SQL statements with literal values. A hard parse is a very expensive operation, and a child library cache latch must be held for the duration of the parse.

• Discover the magnitude of hard parsing in your database using the following query. The number of soft parses can be determined by subtracting the hard parse from the total parse.

 NOTE

 A parse failure is related to the “ORA-00942: table or view does not exist” error or out of shared memory.

• Discover the current sessions that perform a lot of hard parses:

The V$SESS_TIME_MODEL view in Oracle Database 10g shows where the hard parses are coming from by providing the elapsed time statistics on hard and failed parses. Following is a sample from the V$SESS_TIME_MODEL view for a particular session:

The hard parse statistics in the preceding output can be grouped as such:

1. parse time elapsed

            2. hard parse elapsed time

            3. hard parse (sharing criteria) elapsed time

            4. hard parse (bind mismatch) elapsed time

            2. failed parse elapsed time

            3. failed parse (out of shared memory) elapsed time

• Identify literal SQL statements that are good candidates for bind variables. The following query searches the V$SQLAREA view for statements that are identical in the first 40 characters and lists them when four or more instances of the statements exist. The logic assumes the first 40 characters of most of your application’s literal statements are the same. Obviously, longer strings (for example, substr(sql_text, 1,100)) and higher occurrences (for example, count(*) > 50) will yield a shorter report. Once you have identified the literal statements, you can advise the application developers on which statements to convert to use bind variables.

In Oracle9i Database, you can query the V$SQL view for SQL statements that share the same execution plan, as the statements may be identical except for the literal values. They are the candidates for bind variables.

 

 

 

You should see significant reduction in the contention for the shared pool and library cache latches when you convert literal SQL statements to use bind variables. The conversion is best done in the application. The workaround is to set the initialization parameter CURSOR_SHARING to FORCE. This allows statements that differ in literal values but are otherwise identical to share a cursor and therefore reduce latch contention, memory usage, and hard parse.

CAUTION

The CURSOR_SHARING feature has bugs in the earlier releases of Oracle8i Database. It is not recommended in environments with materialized views because it may cause prolonged library cache pin waits. Also, setting the CURSOR_SHARING to FORCE may cause the optimizer to generate unexpected execution plans because the optimizer does not know the values of the bind variables. This may positively or negatively impact the database performance. Starting in Oracle9i Database, the optimizer will peek at the bind variable values in the session’s PGA before producing an execution plan. This behavior is controlled by the parameter _OPTIM_PEEK_USER_BINDS. However, this applies to statements that require hard parsing only, which means the execution plan is based on the first value that is bound to the variable.

Whenever a SQL statement arrives, Oracle checks to see if the statement is already in the library cache. If it is, the statement can be executed with little overhead; this process is known as a soft parse. While hard parses are bad, soft parses are not good either. The library cache latch is acquired during a soft parse operation. Oracle still has to check the syntax and semantics of the statement, unless the statement is cached in the session’s cursor cache. You can reduce the library cache latch hold time by properly setting the SESSION_CACHED_CURSORS parameter. (See Oracle Metalink notes #30804.1 and #62143.1 for more information.) However, the best approach is to reduce the number of soft parses, which can only be done through the application. The idea is to parse once, execute many instead of parse once, execute once. You can find the offending statements by querying the V$SQLAREA view for statements with high numbers of PARSE_CALLS.