Oracle Identifying Shared Pool Contention

Shared pool latch contention can occur when the shared pool is sized too large to be effectively managed. Starting with Oracle version 8.1.6, Oracle implemented improved shared pool management architecture that was supposed to help aleviate many of the issues that caused shared pool contention to occur. Unfortunately, in fixing the problem they also causes a number of other problems, many of which didn’t get fixed until several patch sets later. Many of these problems are corrected in Oracle9i.

What Causes Shared Pool Contention?

When the Oracle shared pool is stressed, it generally will show up in various statistics which we will look at shortly. Perhaps the most common cause of Oracle shared pool problems is that applications do not utilize reusable SQL.

When a SQL statement is executed, it goes through at least two different steps. These two steps are:

The parse phase – When the time comes to parse a SQL statement, Oracle first checks to see if the parsed representation of the statement already exists in the library cache. If not, Oracle will allocate a shared SQL area within the library cache and then parse the SQL statement.

The execution phase – At execution time, Oracle checks to see if a parsed representation of the SQL statement already exists in the library cache. If not, Oracle will reparse and execute the statement. During the execution phase, the query plan is run and the data is retrieved from Oracle.
It is with the first step, the parse phase, that we are concerned with as regards the shared pool.

During parsing, Oracle's parsing algorithm tries to reduce the overall overhead of the parsing of a SQL statement by not reparsing identical SQL statements if at all possible. Problems arise when frequently executed SQL statements are developed. These statements are often very similar in nature, often differing only by a single literal value. The problem is that this literal value does change, and since it changes, the statement has changed and has to be reparsed. Here is an example of such a case:

A SQL Statement with literals in the SQL (not reusable):

Select
   address 
from
   cust
where
   cust_name = 'FREEMAN';

With host variables in the SQL (reusable):

Select
   address 
from
   cust
where
   cust_name = :var1;

Note that the second statement, with the addition of the host variable, makes the SQL statement reusable. The end result is a reduction in the time spent in the library cache, in overall parsing times and overall times that latches are held. This improves the overall throughput and performance of the SQL statement (and in many cases can make a significant difference).

Note that in many cases DBA’s might be tempted to flush the shared pool to "reset" the shared pool. This is a hack at best, and at worst a terrible thing to do to a perfectly good database. Instead, you should correct the errant SQL statements/application code, so that they use the database in an appropriate manner using reusable SQL code.

If changing the SQL code is not practical in the short term, Oracle8i and Oracle9i offers the cursor sharing option which is enabled by setting the cursor_sharing parameter in the Oracle database parameter file. When enabled, cursor sharing will cause the database to replace literal values, when possible, with system generated bind variables. This replacement is done before the parse phase occurs. Thus when the shared pool is searched, it will be searched using a SQL statement with bind variables in use, not literal values.

Be careful when enabling cursor sharing. While it can be a big boost to performance in certain cases, it can also be problematic in other cases. Test the feature well before you implement it in a production system.

Library Cache Usage Measurement

The library cache is actually a combination of two components:

• The shared SQL area
   
• The PL/SQL area

The library cache is also a sub-component of a larger area of memory called the shared pool, which is part of the SGA.

We can determine if there is contention for shared pool resources via the library cache miss ratio. This value represents the ratio of the sum of library cache reloads to the sum of pins. In general, if the library cache ratio is something less than 99 percent, you should consider adding to the shared_pool_size. Library cache misses occur during the compilation of SQL statements. Note that hit ratios can be determined for all dictionary objects that are loaded. These include table/procedures, triggers, indexes, package bodies, and clusters. If any of the hit ratios fall below 75 percent, you should add to the shared_pool_size.

The table V$LIBRARYCACHE is the internal Oracle virtual performance view that keeps information about library cache activity. This table has three columns of interest to us: namespace, pins, and reloads. The namespace column indicates whether the measurement is for the SQL area, a table or procedure, a package body, or a trigger. The pins column counts the number of times an item in the library cache is executed. The reloads column counts the number of times the parsed representation did not exist in the library cache, forcing Oracle to allocate the private SQL areas in order to parse and execute the statement. Let’s look at a query that provides the cache miss ratio.

SQL> Select sum(pins) "Executions", sum(reloads) "Cache Misses",
 2   ((( sum(reloads) / sum(pins) ))) "Library Cache Miss Ratio"
 3   FROM v$librarycache;

Executions Cache Misses Library Cache Miss Ratio
---------- ------------ ------------------------
      3279            9               .002744739

Another way to tell if there is contention is to look at the latch free wait statistics in the v$system_event view. If this event has a large amount of wait time associated with it, then the shared pool might well be having problems that need to be addressed. Here is a query against v$system_event that looks for the latch free wait statistic:

Select event, total_waits, time_waited
From v$system_event
Where event like '%latch free%';

EVENT                          TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
latch free                               4           2

Also, shared pool latch sleeps in the v$latch view, can be indicative of problems with the shared pool. Here is an example of the use of this view to determine if shared pool latch sleeps are occurring:

NAME                                 GETS     MISSES     SLEEPS
------------------------------ ---------- ---------- ----------
shared pool                         11610          0          0

Solving Shared Pool Contention

So, we have discovered shared pool problems, the question is what to do about it. As we have already mentioned, rewriting SQL to be reusable is typically a first approach. There are some other suggestions we can provide that you might want to consider:

• Pin often used PL/SQL objects or SQL cursor objects in the shared pool. You can use the DBMS_SHARED_POOL.KEEP package to instruct Oracle to maintain these specific packages and cursors in the shared pool.
   
• Fully qualify tables and objects with the schema owner name. This will eliminate some reparsing requirements. 
   
• Increase the shared pool size when shared cursors are being flushed out of the shared pool. Check the reload column of the v$library_cache table to see whether cursors have been thrown out.
   
• Decrease the shared pool size when the application does not use bind variables and when cursors are not shared and reused. When cursors are not shared, a smaller shared pool will be more efficient as a larger shared pool causes Oracle to perform more searches to find free memory for new cursors.
   
• When the application embeds literals in SQL (instead of using bind variables), consider setting the parameter cursor_sharing = force (first available in 8.1.6). When this parameter is set, Oracle replaces embedded literal values with bind variables prior to parsing the statement. This has the effect of avoiding additional hard parses for SQL statements that differ only by literal values.
   
• Add cursors to stored procedures/packages. Stored procedures/packages can be pinned in the shared pool, which prevents them from being flushed and subsequently reparsed.
   
• Use the same bind variable length and array size in SQL statements. When an SQL statement with different bind variable lengths is used throughout an application, it cannot be shared.