Tuesday, April 21, 2009

V$BUFFER_POOL_STATISTICS

V$BUFFER_POOL_STATISTICS displays statistics about all buffer pools available for the instance.

Column

Datatype

Description

ID

NUMBER

Buffer pool identifier number

NAME

VARCHAR2(20)

Name of the buffer pool

SET_MSIZE

NUMBER

Buffer pool maximum set size

CNUM_REPL

NUMBER

Number of buffers on replacement list

CNUM_WRITE

NUMBER

Number of buffers on write list

CNUM_SET

NUMBER

Number of buffers in set

BUF_GOT

NUMBER

Number of buffers gotten by the set

SUM_WRITE

NUMBER

Number of buffers written by the set

SUM_SCAN

NUMBER

Number of buffers scanned in the set

FREE_BUFFER_WAIT

NUMBER

Free buffer wait statistic

WRITE_COMPLETE_WAIT

NUMBER

Write complete wait statistic

BUFFER_BUSY_WAIT

NUMBER

Buffer busy wait statistic

FREE_BUFFER_INSPECTED

NUMBER

Free buffer inspected statistic

DIRTY_BUFFERS_INSPECTED

NUMBER

Dirty buffers inspected statistic

DB_BLOCK_CHANGE

NUMBER

Database blocks changed statistic

DB_BLOCK_GETS

NUMBER

Database blocks gotten statistic

CONSISTENT_GETS

NUMBER

Consistent gets statistic

PHYSICAL_READS

NUMBER

Physical reads statistic

PHYSICAL_WRITES

NUMBER

Physical writes statistic

Note:

1. V$BUFFER_POOL_STATISTICS view can be used to determine the hit ratio for the buffer pools. 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;

2. To determining which segments have many buffers in the pool

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(*);

Oracle data dictionary views

Oracle dynamic performance views