Mar 12, 2013 1:34:00 PM by Quest Software
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):
With host variables in the SQL (reusable):Select address from cust where cust_name = 'FREEMAN';
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.
The library cache is actually a combination of two components:
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.
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: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
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:Select event, total_waits, time_waited From v$system_event Where event like '%latch free%'; EVENT TOTAL_WAITS TIME_WAITED ------------------------------ ----------- ----------- latch free 4 2
NAME GETS MISSES SLEEPS ------------------------------ ---------- ---------- ---------- shared pool 11610 0 0
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:
Written by Quest Software