2013-1-6 - 1-7 11gR2 "Performance Tuning Guide" page 114 - 141

 

 

Buffer Pool Data in V$DB_CACHE_ADVICE -- 在v$视图中查询BUFFER POOL的相关数据
You can use V$DB_CACHE_ADVICE to size all pools configured on a database instance.
Make the initial cache size estimate, run the representative workload, then simply
query the V$DB_CACHE_ADVICE view for the pool you want to use.
For example, to query data from the KEEP pool:
SELECT SIZE_FOR_ESTIMATE, BUFFERS_FOR_ESTIMATE, ESTD_PHYSICAL_READ_FACTOR,
ESTD_PHYSICAL_READS
FROM V$DB_CACHE_ADVICE
WHERE NAME = 'KEEP'
AND BLOCK_SIZE = (SELECT VALUE FROM V$PARAMETER WHERE NAME =
'db_block_size')
AND ADVICE_STATUS = 'ON';

Buffer Pool Hit Ratios -- BUFFER池的命中率
The data in V$SYSSTAT reflects the logical and physical reads for all buffer pools
within one set of statistics. To determine the hit ratio for the buffer pools individually,
query the V$BUFFER_POOL_STATISTICS view. This view maintains statistics for
each pool on the number of logical reads and writes.
The buffer pool hit ratio can be determined using the following formula:
1 - (physical_reads/(db_block_gets + consistent_gets))
The ratio can be calculated with the following query:
SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
FROM V$BUFFER_POOL_STATISTICS;

 

Determining Which Segments Have Many Buffers in the Pool -- 查询段在BUFFER池中的占用率

Method 1
The following query counts the number of blocks for all segments that reside in the
buffer cache at that point in time. Depending on buffer cache size, this might require a
lot of sort space.
COLUMN OBJECT_NAME FORMAT A40
COLUMN NUMBER_OF_BLOCKS FORMAT 999,999,999,999
SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
FROM DBA_OBJECTS o, V$BH bh
WHERE o.DATA_OBJECT_ID = bh.OBJD
AND o.OWNER != 'SYS'
GROUP BY o.OBJECT_NAME
ORDER BY COUNT(*);
OBJECT_NAME NUMBER_OF_BLOCKS
---------------------------------------- ----------------
OA_PREF_UNIQ_KEY 1
SYS_C002651 1
..
DS_PERSON 78
OM_EXT_HEADER 701
OM_SHELL 1,765
OM_HEADER 5,826
OM_INSTANCE 12,644

Method 2 -- 仅针对 SINGLE SEGMENT
Use the following steps to determine the percentage of the cache used by an individual
object at a given point in time:
1. Find the Oracle Database internal object number of the segment by entering the
following query:
SELECT DATA_OBJECT_ID, OBJECT_TYPE
FROM DBA_OBJECTS
WHERE OBJECT_NAME = UPPER('segment_name');
Because two objects can have the same name (if they are different types of objects),
use the OBJECT_TYPE column to identify the object of interest.
2. Find the number of buffers in the buffer cache for SEGMENT_NAME:
SELECT COUNT(*) BUFFERS
FROM V$BH
WHERE OBJD = data_object_id_value;
where data_object_id_value is from step 1.
3. Find the number of buffers in the instance:
SELECT NAME, BLOCK_SIZE, SUM(BUFFERS)
FROM V$BUFFER_POOL
GROUP BY NAME, BLOCK_SIZE
HAVING SUM(BUFFERS) 0;
4. Calculate the ratio of buffers to total buffers to obtain the percentage of the cache
currently used by SEGMENT_NAME:
% cache used by segment_name = [buffers(Step2)/total buffers(Step3)]