|
Analyzing Oracle SGA Database Buffer Cache |
|
|
|
|
Written by TnT Admin
|
|
Wednesday, 08 October 2008 21:43 |
|
Page 1 of 4 Like the Shared Pool (Library Cache and Data Dictionary Cache), the performance of the Database Buffer Cache is determine with the cache hit-ratio. Cache hits occur whenever a user process finds that a data buffer needed by their SQL statement is already cached in memory; Consequently, cache misses occur when the user process does not find the requested data to already be cached in memory - causing the data to be read form disk instead. High cache hit ratios indicate that your application users are frequently finding that the data buffers they need are already in memory thus reducing the need (and delay) to read from disk (remember that disk reads are much slower than memory).
On the other hand, there are also non-hit ratio measures of Database Buffer Cache effectiveness such as Free Buffer Inspected Waits, Buffer Busy Wait events and Free Buffer Wait events. To find out about the hit-ratio for the Database Buffer Cache, we can use V$SYSSTAT, V$SYSTEM_EVENT and STATSPACK.
The counters that we should be watching are as followed: - V$SYSSTAT: Buffer Cache Hit Ratio
- V$SYSSTAT & V$SYSTEM_EVENT: Free Buffer Inspected
- STATSPACK > Instance Efficiency Percentages (Target 100%): Buffer Hit %
- STATSPACK > Instance Activity Stats for DB: <instance>: Free Buffer Inspected
- STATSPACK > Buffer Pool Statistics for DB: <instance>: Buffer Busy Wait
What we want to see of the counters are: - Buffer Cache Hit Ratio should be higher than 90% for OLTP system according to Oracle.
The following queries will be used to determine the hit-ratio: - SELECT 1 - ((physical.value - direct.value -lobs.value) / logical.value) "Buffer Cache Hit Ratio" FROM V$SYSSTAT physical, V$SYSSTAT direct, V$SYSSTAT lobs, V$SYSSTAT logical WHERE physical.name = 'physical reads' AND direct.name = 'physical reads direct' AND lobs.name = 'physical reads direct (lob)' AND logical.name = 'session logical reads';
- SELECT name, value FROM V$SYSSTAT WHERE name IN ('free buffer inspected') UNION SELECT event, total_waits FROM V$SYSTEM_EVENT
WHERE event in ('free buffer waits', 'buffer busy waits');
To know more about the counters, read on.
|
|
Last Updated ( Thursday, 18 September 2008 00:49 )
|