Analyzing Oracle SGA Shared Pool (Library Cache)
Posted: September 28th, 2008 | Author: TnT Admin | Filed under: Analyze | Tags: Oracle | 5 Comments »
Starting with the Oracle System Global Area, we are going to touch on the basics of Shared Pool – Library Cache followed by Data Dictionary Cache (which will be in another article). The Shared Pools’ Library Cache is the area Oracle caches the SQL and PL/SQL statements that have been recently issued by application users. PL/SQL statements can be in the form of procedures, functions, packages, triggers, anonymous PL/SQL blocks, or Java classes.
The primary indicator of the performance of the Shared Pool (both Library Cache and Data Dictionary Cache) is the Cache Hit Ratio. High cache-hit ratios indicate that the application users are frequently finding the SQL and PL/SQL statements they are issuing already in memory. Oracle recommends maximizing the Library Cache before the Database Buffer Cache because the Library Cache has a greater impact on performance thanan under performing cache hit ratio for the Database Buffer Cache. The performance of the Library Cache is measure by calculating its hit ratio. Through this article, we wil be showing how to use three tools, namely, V$LIBRARYCACHE, STATSPACK and OEM Performance Manager to analyze the hit ratio.
The following will be the counters associating with Library Cache.
- V$LIBRARYCACHE: GETHITRATIO column
- V$LIBRARYCACHE: GETPINRATIO column
- V$LIBRARYCACHE: INVALIDATION column
- V$LIBRARYCACHE: RELOAD ratio
- STATSPACK > Instance Efficiency Percentages (Target 100%): Library Hit %
- STATSPACK > Library Cache Activity: Invalidations: Pct Miss
- STATSPACK > Library Cache Activity: Reloads
- OEM > Performance Manager > Database Instance Category > Library Cache Hit %
The actual threshold for the Library Cache is as followed:
- The higher the GETHITRATIO, the better is the application is performing. According to Oracle, well-tuned OLTP systems can expect to have GETHITRATIO of 90% or higher for the SQL Area portion of the Library Cache. DSS and data warehouse applications often have lower hit ratios because of the ad hoc nature of the queries against them.
- Oracle recommends that well-tuned OLTP systems strive for PINHITRATIO that exceeds 90% for the SQL Area portion. DSS and data warehouse applications are generally lower.
- Low reload ratio for PINS is good. Oracle considers well-tuned systems to be those with Reload Ratios of less than 1 percent.
- High values for INVALIDATIONS mean additional overhead for the application. Therefore, performing activities that might cause INVALIDATIONS should be weighed against the expected benefit of those activities.
- For STATSPACK, look at Library Hit % for the percentage of Library Cache hit-ratio. The Namespace, SQL AREA should have a 90% hit-ratio for the Library Cache.
Queries to be used for Library Cache:
- SELECT NAMESPACE,GETHITRATIO,PINHITRATIO FROM V$LIBRARYCACHE;
- SELECT SUM(RELOADS)/SUM(PINS) “Reload Ratio” FROM V$LIBRARYCACHE;
The explanation of the counters are discussed after the table and will be recommended to read through them if you are unsure what they mean.
[1] V$LIBRARYCACHE
A library cache miss can occur at either the Parse or Execute phases of SQL statement processing. The cache hit ratio related to the Parse Phase is shown in the GETHITRATIO column of V$LIBRARYCACHE while the Execute Phase is shown in the PINHITRATIO column of V$LIBRARYCACHE. Other than GETHITRATIO and PINHITRATIO, the RELOADS and INVALIDATIONS columns of V$LIBRARYCACHE are also examined to uncovered behaviour of the Library Cache.
GETHITRATIO
Oracle uses the term “get” to refer to a type of lock, called a “Parse Lock”, that is taken out on an object during the Parse Phase for the statement that references that object. Each time a statement is parsed, the value for GETS in the V$LIBRARYCACHE view is incremented by 1. On the other hand, the column GETHIT stores the number of times that the SQL and PL/SQL statements issued by the application found a parsed copy of themselves already in memory. When this occurs, there is no parsing of the statement required and the user’s server process just executes the copy already in memory instead. The ratio of parsed statements (GETS) to those that did not required parsing (GETHITS) is calculated in the GETHITRATIO column of V$LIBRARYCACHE.
PINHITRATIO
PINs like GETS, are also related to locking. However, while GETS are associated with locks that occur at Parse time, PINS are related to locks that occur at Execution time. These locks are the short-term locks used when accessing an object. Therefore, each library cache GET also requires an associated PIN, in either Shared or Exclusive mode, before accessing the statement’s reference objects. Each time a statement is executed, the value for PINS is increments by 1. The PINHITRATIO column in V$LIBRARYCACHE indicates how frequently executed statements found the associated parsed SQL already in the Library Cache.
RELOADS
RELOADS column shows the number of times that an executed statement had to be re-parsed because the library cache had aged out or invalidated the parsed version of the statement. Examining the RELOAD column individually will not tell much. Reload activity can be monitored by comparing the number of statement that have been executed (PINS) to the number of those statements that required a reload (RELOADS), calculated based on the following SQL:
INVALIDATIONS
INVALIDATIONS occur when a cached SQL statement is marked as invalid and forced to parse even though it was already in the Library Cache. Cached statements are marked as invalid whenever the objects they referenced are modified in some way. For example, recompiling a view that was used by previously cached SQL statements will cause the those cached statements to be marked as invalid. Therefore, any subsequent statements that use this view will need to be parsed even though an exact copy of that statement may already be cached. High values for INVALIDATIONS mean additional overhead for the application. Therefore, performing activities that might cause invalidations should be weighed against the expected benefit of those activities.
[2] STATSPACK
The same cache hit-ratio information can be found in the results of STATSPACK. Library Cached performance statistics are included in the STATSPACK section headed Instance Efficiency Percentages (Target 100%). Look at Library Hit % for the percentage of Library Cache hit-ratio. Also, it is possible to look at the STATSPACK for INVALIDATIONS and RELOADS, under Library Cache Activity. Observe Pct Miss which conversely means that percentage that hits when minus off 100%.
[3] OEM Performance Manager
From OEM, access Performance Manager > Database Instance Category > Library Cache Hit %. This gives a runtime view of the Library Cache hit-ratio which eliminates the need to query V$LIBRARYCACHE. However, as this is a runtime view, you won’t be able to conduct analysing work after a load test. Therefore, for analysing work after a load test, it is advisable to use Custom Query of Oracle Monitoring in LoadRunner to query V$LIBRARYCACHE from a load testing perspective.
(Source: OCP Oracle 9i Performance Tuning Study Guide)
This shows reader a clear picture.
Thanks for the compliment!
the explanation is really good and very useful
thats really good
The way is has explained is very helpful….