# Memory Configuration and Use

Calculating the Buffer Cache Hit Ratio
```SELECT NAME, VALUE
FROM V\$SYSSTAT

```

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

```NAME                                                                  VALUE
---------------------------------------------------------------- ----------

```

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

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

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.

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

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
, invalidations
FROM V\$LIBRARYCACHE
ORDER BY namespace;

```

The output of this query could look like the following:

```NAMESPACE             PINS    PINHITS    RELOADS INVALIDATIONS
--------------- ---------- ---------- ---------- -------------
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:

```SUM(PINHITS)/SUM(PINS)
----------------------
.999466248
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:SELECT * FROM V\$SGASTAT
WHERE NAME = 'free memory'
AND POOL = 'shared pool';

The output will be similar to the following:

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

column pct_succ_gets format 999.9

SELECT parameter
, sum(gets)
, sum(getmisses)
, 100*sum(gets - getmisses) / sum(gets)  pct_succ_gets
FROM V\$ROWCACHE
WHERE gets > 0
GROUP BY parameter;

The output of this query will be similar to the following:PARAMETER              SUM(GETS) SUM(GETMISSES) PCT_SUCC_GETS      UPDATES
--------------------- ---------- -------------- ------------- ------------
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_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:SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V\$ROWCACHE;http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/memory.htm#48854 ```

• 博文量
98
• 访问量
181379