Page 779
maximum number of bytes of your largest objects simultaneously loaded. Set SHARED_POOL_ RESERVED_MIN_ALLOC to the minimum number of bytes an object can be in order to use your reserved area specified by SHARED_POOL_RESERVED_SIZE. To determine the size of a particular object you want to include in your reserved area, use the following:
SQL> SELECT SUBSTR(NAME,1,25) "NAME", SHARABLE_MEM 2> FROM V$DB_OBJECT_CACHE 2> WHERE NAME='<object_name>';
Also, to determine the size you need to set SHARED_POOL_RESERVED_SIZE, use this:
SQL> SELECT SUM(SHARABLE_MEM) 2> FROM V$DB_OBJECT_CACHE 3> WHERE SHARABLE_MEM > <SHARED_POOL_RESERVED_MIN_ALLOC>;
Hence, to execute the previous query, you must have some rough idea as to what classifies, at a minimum, as a "large object." So, you want to take the following steps:
Also, you may set CURSOR_SPACE_FOR_TIME to TRUE to prevent SQL areas associated with cursors from aging out of the library cache before they have been closed by a program.
CAUTION |
|
Aside from the many application-based memory issues and tuning methods that you have just learned, you can look at what utlbstat.sql/utlestat.sql (report.txt) offers. In particular, you can look at the first section in report.txt, Library cache statistics. Specifically, ensure that the GETHITRATIO for the SQL AREA LIBRARY is in the 90s, preferably the high 90s. If you didn't run utlbstat.sql/utlestat.sql, you can also use:
SQL> SELECT GETHITRATIO 2> FROM V$LIBRARYCACHE 3> WHERE NAMESPACE='SQL AREA';
However, this method is a running average since the last instance startup and includes ramp- up times. Ramp up is the initialization process any system goes through when it first starts up. The statistics gathered during this period are misleading and not useful because they will largely reflect the physical reads necessary to fill all the various Oracle buffers and caches.
Page 780
Hence, sometimes this is also called caching up. As you learned earlier, you want to sample statistics during your peak load times. When a system is running at peak activity for some time, that system is said to be in equilibrium. If you run utlbstat.sql after peak activity has been reached and then run utlestat.sql just before peak activity ceases, you have a valid sample. Hence, selecting from the V$ dynamic performance views can be misleading, especially if you sample them early in the instance lifetime. The longer the instance remains up, the better the V$ views' statistics are because the peak and normal activities outweigh the initial startup period more and more. To be fair, the same can be said of utlbstat.sql/utlestat.sql.
NOTE |
Guideline: If the GETHITRATIO is less than .90, then apply the guidelines for improving the performance of the library cache. This typically means application code may be rewritten more efficiently.n |
Again, from the first section of report.txt, the Library cache statistics, calculate the RELOADS/PINS ratio for the SQL AREA LIBRARY. If the RELOADS/PINS ratio is greater than .01, increase the size (in bytes) of the total shared pool by setting the init.ora parameter, SHARED_POOL_SIZE. Remember, this sets the entire shared pool, including not just the library cache but also the data dictionary cache.
NOTE |
Guideline: If the RELOADS / PINS ratio is greater than .01, then increase the size (in bytes) of the total shared pool, by setting the init.ora parameter, SHARED_POOL_SIZE. Remember, this sets the entire shared pool, including not just the library cache but also the data dictionary cache.n |
From the Latch statistics section of report.txt, ensure that the HIT_RATIO for LATCH_NAME = `library cache' is in the high 90s. Other measures for measuring high latch contention, which may be an indication of poor library cache (and total shared pool) performance, are the following:
From the System wide wait events section of report.txt, check the (wait) Count column for Event Name = `latch free'.
Also run the following query:
SQL> SELECT COUNT(*) "LIBRARY_LATCH_WAITS" 2> FROM V$SESSION_WAIT W, V$LATCH L 3> WHERE W.WAIT_TIME = 0 4> AND W.EVENT='latch free' 5> AND W.P2 = L.LATCH# 6> AND L.NAME LIKE `library%';
NOTE Guideline: If any of the following are true:
l The HIT_RATIO for LATCH_NAME = `library cache'is < .98
l The (wait) Count for Event Name='latch free' is > 10
l The LIBRARY_LATCH_WAITS is > 2apply the guidelines for improving the performance of the library cache, and if necessary, increase the SHARED_POOL_SIZE setting.n
Page 781
The data dictionary cache portion of the shared pool, as you might expect, holds the caching structure for the Oracle data dictionary. The SHARED_POOL_SIZE parameter is the only way to indirectly size the data dictionary cache itself. The following are the objects held in the SYS and SYSTEM schemes (the SYSTEM tablespace):
Sizing, diagnosing, and tuning the library cache so that it performs well should have the side effect of helping the performance of the data dictionary cache because they coexist in the shared pool. They are not separately configurable.
There are a couple of ways to measure data dictionary cache performance. One is to query the V$ROWCACHE view:
SQL> SELECT SUM(GETMISSES)/SUM(GETS) "DC_MISS_RATIO" 2> FROM V$ROWCACHE;
The other way is to use the data dictionary section of report.txt. Compute the sum of all the GET_MISS and divide that by the sum of all the GET_REQS to get a similar DC_MISS_RATIO. If either of these two methods yields a DC_MISS_RATIO > .15, increase the SHARED_POOL_SIZE (and retest).
NOTE |
Guideline: If either of these two methods yield a DC_MISS_RATIO > .15, increase the SHARED_POOL_SIZE (and retest).n |
You should also briefly consider the MultiThreaded Server (MTS) and the allocation of server memory versus client (user) memory. Just as there is an SGA, there is a User Global Area (UGA), which contains user session information, sort areas, and private SQL areas. Normally, the default Oracle RDBMS instance (referred to as the dedicated server) results in a one-to-one mapping of user processes to server processes. With MTS, the UGA is moved up into the shared pool. The remaining process-specific memory is retained in the Process Global Area (PGA) and holds information that cannot be shared. In this way, the total amount of memory required in using MTS is not really more than the dedicated server, just redistributed. However, you will have to increase SHARED_POOL_SIZE. To help size the UGA that will be relocated to the SGA, run the following query:
SQL> SELECT SUM(VALUE) 2> FROM V$SESSTAT SE, V$STATNAME SN 3> WHERE SN.NAME = `max session memory' 4> AND SE.STATISTIC# = SN.STATISTIC#;