15.4 How do I…Determine shared pool performance?
Problem
As part of my performance tuning strategy, I want to make sure that the hit ratio in the shared pool is acceptable. Because the shared pool contains the dictionary cache and the library cache, performance can suffer greatly if it is not large enough. How do I determine the hit ratio in the shared pool?
Technique
The shared pool is one of the three main memory structures in the system global area (SGA). It is comprised of the data dictionary cache and the library cache. The data dictionary cache buffers in memory data dictionary objects that contain data about tables, indexes, users, and all the rest of the database’s objects.
Every database action requires information from the data dictionary. If that information is not in the data dictionary cache section of memory, then Oracle8 must get it from disk. If this happens consistently, then database performance suffers.
The shared pool also contains the library or SQL cache. The library cache buffers previously executed queries, so they need not be reloaded and re-parsed if a user calls them again. If a SQL statement is executed repeatedly but cannot reside in the SQL cache because it is too small, performance improvements are possible.
The UTLESTAT report contains sections focusing on the library and dictionary caches. Listings 15.4 and 15.5 show excerpts from those sections of the report summarizing library and data dictionary cache performance, respectively.
Listing 15.4 The library cache performance section of the UTLESTAT report
SVRMGR> Rem Select Library cache statistics. The pin hit rate should be high.
SVRMGR> select namespace library,
2> gets,
3> round(decode(gethits,0,1,gethits)/decode(gets,0,1,gets),3)
4> gethitratio,
5> pins,
6> round(decode(pinhits,0,1,pinhits)/decode(pins,0,1,pins),3)
7> pinhitratio,
8> reloads, invalidations
9> from stats$lib;
LIBRARY
GETS
GETHITRATI
PINS
PINHITRATI
RELOADS
INVALIDATI
------------
------------
------------
------------
------------
------------
------------
BODY
0
1
0
1
0
0
CLUSTER
36
1
16
1
0
0
INDEX
0
1
0
1
0
0
OBJECT
0
1
0
1
0
0
PIPE
0
1
0
1
0
0
SQL AREA
30
.767
81
.802
2
0
TABLE/PROCED
63
.968
57
.93
0
0
TRIGGER
0
1
0
1
0
0
In Listing 15.4, the column PINS represents the number of executions of SQL statements that reside, in parsed form, in the library cache. The column RELOADS indicates the number of attempts to execute items that existed at one time in the library cache but have been aged out of the cache to make room for other items. The general rule is that the ratio of RELOADS to PINS should not exceed .01 (or 1%).
Listing 15.5 The data dictionary cache performance section of the UTLESTAT report
SVRMGR> Rem get_miss and scan_miss should be very low compared to the requests.
SVRMGR> Rem cur_usage is the number of entries in the cache that are being used.
SVRMGR> select * from stats$dc
2> where get_reqs != 0 or scan_reqs != 0 or mod_reqs != 0;
NAME
GET_REQS
GET_MISS
SCAN_REQ
SCAN_MIS
MOD_REQS
COUNT
CUR_USAG
--------
--------
--------
--------
--------
--------
--------
--------
dc_tablespaces
5
0
0
0
0
10
4
dc_users
9
0
0
0
2
4
1
dc_objects
47
2
0
0
0
118
111
dc_usernames
24
0
0
0
0
20
1
dc_object_ids
36
2
0
0
0
84
83
In Listing 15.5, the values in the column GET_REQS are the number of times Oracle requested data dictionary information on the object categories in the NAME column. The column GET_MISS is the number of requests that the dictionary cache could not accommodate because the requested item had aged out of the cache. In these cases, Oracle8 had to go to disk to satisfy the request. A good rule of thumb is that the ratio of GET_MISS to GET_REQ should not exceed .15 (or 15%).
The V$LIBRARYCACHE view shown in Table 15.2 stores performance data for the library cache. The V$ROWCACHE view shown in Table 15.3 stores performance data for the data dictionary cache.
Table 15.2 Some of the columns comprising the V$LIBRARYCACHE view
Column Column Description NAMESPACE Library cache namespace GETS The number of times a lock was requested for objects of this namespace GETHITS The number of times Oracle8 found an object’s handle in memory GETHITRATIO The ratio of GETHITS to GETS PINS Number of executions of an item in this namespace PINHITS The number of times all the meta data pieces of the library object were found in memory PINHITRATIO The ratio of PINHITS to PINS RELOADS Number of times an item from this namespace must be reread from disk INVALIDATIONS The total number of times objects in this namespace were marked invalid because of a dependent object modification Table 15.3 Some of the columns comprising the V$ROWCACHE view
Column Column Description CACHE# Row cache ID number COUNT Total number of entries in the cache GETS Total number of requests for information of this cache# GETMISSES Number of unsatisfied requests The library cache is working efficiently when the ratio of RELOADS to PINS is less than 1%. The data dictionary cache is optimized when the number of GETMISSES to GETS is less than 15%. If the cache performance does not satisfy these guidelines, then increase the value of the initialization parameter SHARED_POOL_SIZE.
Steps
1. Run SQL*Plus and connect as the WAITE user account. Use the START command to load and execute the CHP15_6.SQL script to query the V$LIBRARYCACHE view and report on library cache performance. The script and its output appear in Figure 15.4.
Lines 1 and 2 of the CHP15_6.SQL script compute the sums of all of the pins and reloads for all the object types maintained in the library cache. Line 3 calculates the miss ratio for the library cache. Remember that ideally this ratio will not exceed 1%.
2. While still connected as the WAITE user in SQL*Plus, use the START command to load and execute the CHP15_7.SQL script. The script and its output appears in Figure 15.5.
Line 1 of CHP15_7.SQL calculates the sum of all GETS and GETMISSES for the data dictionary cache. Line 2 computes the percentage of time the requested object did not reside in the cache. Ideally, this ratio will not exceed 15%.
How It Works
The miss percentages for the library cache and the data dictionary cache indicate the overall health of the shared pool. Step 1 highlights a SQL statement to determine the library cache miss ratio; Step 2 does the same for the data dictionary cache.
Comments
If the data dictionary cache miss percentage exceeds 15 percent, then you have no options for improvement except to increase the size of the shared pool by increasing the value of the SHARED_POOL_SIZE parameter in the initialization parameter file.
Other options to improve the performance of the library cache exist as well. Encourage users, for example, to share SQL statements whenever possible because even the subtlest difference in syntax, like a case change, can result in SQL statement reparsing. The use of packages, in addition, increases the library cache hit rate because whenever any user accesses any part of a package Oracle8 loads the entire package into the library cache.
Whether you use the UTLESTAT report or the queries from Steps 1 and 2 to assess the shared pool performance level, give the database sufficient time to calculate shared pool statistics. Otherwise, the numbers that either of these methods return may not be meaningful.