首页 > Linux操作系统 > Linux操作系统 > Memory Configuration and Use

Memory Configuration and Use

原创 Linux操作系统 作者:v_fantasy 时间:2009-02-09 22:32:52 0 删除 编辑
Calculating the Buffer Cache Hit Ratio
WHERE NAME IN ('session logical reads','physical reads',
               'physical reads direct','physical reads direct (lob)');

The output of this query will look similar to the following:

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session logical reads                                             464905358
physical reads                                                     10380487
physical reads direct                                                 86850
physical reads direct (lob)                                               0

Calculate the hit ratio for the buffer cache with the following formula:

Hit Ratio = 1 - ((physical reads - physical reads direct - physical reads direct (lob)) / 
(db block gets + consistent gets - physical reads direct - physical reads direct (lob)) 

Based on the sample statistics in the example, the buffer cache hit ratio is equal to .978 or 97.8%.

Statistic Description

session logical reads

The total number of requests to access a block, whether in memory or on disk.

physical reads

The total number of requests to access a data block that resulted in access to datafiles on disk. The block could have been read into the cache or read into local memory by a direct read.

physical reads direct

The number of blocks read, bypassing the buffer cache, excluding direct reads for large objects (LOBs).

physical reads direct (lob)

The number of blocks read while reading LOBs, bypassing the buffer cache.

To examine each namespace individually, use the following query:

SELECT namespace
     , pins
     , pinhits
     , reloads
     , invalidations
 ORDER BY namespace;

The output of this query could look like the following:

--------------- ---------- ---------- ---------- -------------
BODY                  8870       8819          0             0
CLUSTER                393        380          0             0
INDEX                   29          0          0             0
OBJECT                   0          0          0             0
PIPE                 55265      55263          0             0
SQL AREA          21536413   21520516      11204             2
TABLE/PROCEDURE   10775684   10774401          0             0
TRIGGER               1852       1844          0             0

To calculate the library cache hit ratio, use the following formula:

Library Cache Hit Ratio = sum(pinhits) / sum(pins)

Using the library cache hit ratio formula, the cache hit ratio is the following:


Examining the returned data leads to the following observations:

  • For the SQL AREA namespace, there were 21,536,413 executions.
  • 11,204 of the executions resulted in a library cache miss, requiring Oracle to implicitly reparse a statement or block or reload an object definition because it aged out of the library cache (that is, a RELOAD).
  • SQL statements were invalidated two times, again causing library cache misses.
  • The hit percentage is about 99.94%. This means that only .06% of executions resulted in reparsing.

The amount of free memory in the shared pool is reported in V$SGASTAT. Report the current value from this view using the following query:

 WHERE NAME = 'free memory'
   AND POOL = 'shared pool';

The output will be similar to the following:

POOL        NAME                            BYTES
----------- -------------------------- ----------
shared pool free memory                   4928280

If free memory is always available in the shared pool, then increasing the size of the pool offers little or no benefit. However, just because the shared pool is full does not necessarily mean there is a problem. It may be indicative of a well-configured system.

Shared Pool Advisory Statistics

The amount of memory available for the library cache can drastically affect the parse rate of an Oracle instance. With Oracle9i, Release 2 (9.2) or higher, the shared pool advisory statistics provide a database administrator with information about library cache memory and predict how changes in the size of the shared pool can affect the parse rate.

The shared pool advisory statistics track the library cache's use of shared pool memory and predict how the library cache will behave in shared pools of different sizes. Two fixed views provide the information to determine how much memory the library cache is using, how much is currently pinned, how much is on the shared pool's LRU list, as well as how much time might be lost or gained by changing the size of the shared pool.

The following views of the shared pool advisory statistics are available. These views display any data when shared pool advisory is on. These statistics reset when the advisory is turned off.


This view displays information about estimated parse time savings in different sizes of shared pool. The sizes range from 50% to 200% of current shared pool size, in equal intervals. The value of the interval depends on current shared pool size.

Parse time saved refers to the amount of time saved by keeping library cache memory objects in the shared pool, as opposed to having to reload these object.


This view displays information about memory allocated to library cache memory objects in different namespaces. A memory object is an internal grouping of memory for efficient management. A library cache object may consist of one or more memory objects.

Shared Pool: Dictionary Cache Statistics

Typically, if the shared pool is adequately sized for the library cache, it will also be adequate for the dictionary cache data.

Misses on the data dictionary cache are to be expected in some cases. On instance startup, the data dictionary cache contains no data. Therefore, any SQL statement issued is likely to result in cache misses. As more data is read into the cache, the likelihood of cache misses decreases. Eventually, the database reaches a steady state, in which the most frequently used dictionary data is in the cache. At this point, very few cache misses occur.

Each row in the V$ROWCACHE view contains statistics for a single type of data dictionary item. These statistics reflect all data dictionary activity since the most recent instance startup. The columns in the V$ROWCACHE view that reflect the use and effectiveness of the data dictionary cache are listed in Table 14-2.

Table 14-2  V$ROWCACHE Columns



Identifies a particular data dictionary item. For each row, the value in this column is the item prefixed by dc_. For example, in the row that contains statistics for file descriptions, this column has the value dc_files.


Shows the total number of requests for information on the corresponding item. For example, in the row that contains statistics for file descriptions, this column has the total number of requests for file description data.


Shows the number of data requests which were not satisfied by the cache, requiring an I/O.


Shows the number of times data in the dictionary cache was updated.

Use the following query to monitor the statistics in the V$ROWCACHE view over a period of time while your application is running. The derived column PCT_SUCC_GETS can be considered the item-specific hit ratio:

column parameter format a21
column pct_succ_gets format 999.9
column updates format 999,999,999

SELECT parameter
     , sum(gets)
     , sum(getmisses)
     , 100*sum(gets - getmisses) / sum(gets)  pct_succ_gets
     , sum(modifications)                     updates
 WHERE gets > 0
 GROUP BY parameter;

The output of this query will be similar to the following:

--------------------- ---------- -------------- ------------- ------------
dc_database_links             81              1          98.8            0
dc_free_extents            44876          20301          54.8       40,453
dc_global_oids                42              9          78.6            0
dc_histogram_defs           9419            651          93.1            0
dc_object_ids              29854            239          99.2           52
dc_objects                 33600            590          98.2           53
dc_profiles                19001              1         100.0            0
dc_rollback_segments       47244             16         100.0           19
dc_segments               100467          19042          81.0       40,272
dc_sequence_grants           119             16          86.6            0
dc_sequences               26973             16          99.9       26,811
dc_synonyms                 6617            168          97.5            0
dc_tablespace_quotas         120              7          94.2           51
dc_tablespaces            581248             10         100.0            0
dc_used_extents            51418          20249          60.6       42,811
dc_user_grants             76082             18         100.0            0
dc_usernames              216860             12         100.0            0
dc_users                  376895             22         100.0            0

Examining the data returned by the sample query leads to these observations:

  • There are large numbers of misses and updates for used extents, free extents, and segments. This implies that the instance had a significant amount of dynamic space extension.
  • Based on the percentage of successful gets, and comparing that statistic with the actual number of gets, the shared pool is large enough to store dictionary cache data adequately.

It is also possible to calculate an overall dictionary cache hit ratio using the following formula; however, summing up the data over all the caches will lose the finer granularity of data:


来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

上一篇: Memory Architecture
下一篇: oracle10g AWR
请登录后发表评论 登录


  • 博文量
  • 访问量