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(*);
No comments:
Post a Comment