by http://webgeektutorials.blogspot.com

Monday, June 4, 2012

Oracle DBA Script : Calculating DB_Block Buffer Efficiency

This script uses the new way of calculating DB_Block Buffer Efficiency.
Notable Points : ( Performance Tuning )
Logical Read: consistent gets + db block gets
Hit-Ratio: (logical reads - physical reads) / (logical reads)
Our Aim: OLTP >= 95%, DSS/Batch >= 85%
Solution: enlarge block buffers, tune SQL, check appropriateness of indexes

Code:
select name, ((consistent_gets + db_block_gets) - physical_reads) /
(consistent_gets + db_block_gets) * 100 "Hit Ratio%"
from v$buffer_pool_statistics
where physical_reads > 0;

No comments:

Post a Comment