SELECT NAME, VALUE
FROM V$SYSSTAT
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
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:
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
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.
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.
V$SHARED_POOL_ADVICEThis 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.
V$LIBRARY_CACHE_MEMORYThis 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.
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| Column | Description |
|---|---|
PARAMETER | 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. |
GETS | 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. |
GETMISSES | Shows the number of data requests which were not satisfied by the cache, requiring an I/O. |
MODIFICATIONS | 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
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_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:
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
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/16158219/viewspace-545690/ ,如需转载,请注明出处,否则将追究法律责任。