Oracle performance tuning study notes (E) - buffer Cache tuning B

2011-09-16  来源:本站原创  分类:Database  人气:110 

Dynamic view of diagnostic tools
v $ buffer_pool_statistics
v $ buffer_pool
v $ db_cache_advice
v $ sysstat
v $ sesstat
v $ system_event
v $ session_wait
v $ bh
v $ cache
OEM's tuning to reduce the use of performance indicator information in the following three indicators
Free buffer Inspected (check): Sp check if there spare LRU buffer, the waiting time.
Free buffer wait (wait): DBWR write data, sp wait.
Buffer busy waits: to use the same number of sp in a bock, sp wait.

select name, value from v $ sysstat
where name = 'free buffer inspected';

select event, total_waits
from v $ system_event
where event in
('Free buffer waits', 'buffer busy waits');
Find the block busy
select * from v $ event_name
where name = 'buffer busy waits'
buffer Cache type:
The default buffer cache:
Keep buffer cache:
Recycle buffer cache:
nk buffer cache:

competitive reasons to wait for data block buffer:
sql query use the index on the left the same cause inaccurate.
v $ session_wait provides file and data blocks frequently wait.
Non-automatic undo tablespace management, need to increase the rollback block.

Free buffer wait reasons
DBWn keep up with dirty data increase.
System IO is busy. For disk
IO wait for resources such as locks for the disk, adjust the file distribution
buffer cache is small. increase the buffer cache
large buffer cache, DBWN handle busy. open multiple DBWn
Cache hit rate calculation
Oracle 9I
select 1 - (phy.value-lob.value-dir.value) / ses.value "Cache Hit Ratio"
from v $ sysstat ses, v $ sysstat lob, v $ sysstat dir, v $ sysstat phy
where = 'session logical reads'
and = 'physical reads direct' - read directly from disk
and = 'physical reads direct (lob)' - do not get the buffer cache
and = 'physical reads'; - the total number of disk reads

Oracle 11g
consistent gets from cache: from the buffer cache to get the number of times the block
db block gets from cache: the current block from the buffer cache in the number of requests.
physical reads cache: total data block buffer cache from the disk to the number of times.
select name, value
from v $ sysstat
where name in ('db block gets from cache', 'consistent gets from cache',
'Physical reads cache');

Hit rate: 1 - (('physical reads cache')/(' db block gets from cache' + 'consistent gets from cache'))
Hit the factors affecting data access way affect the
1 full table scan
(2) data or application design problems
3 large table of random access
4. Uneven distribution of the cache hit rate hit ratio is not everything
In the hit rate adjustment, the need to view v $ db_cache_advice and hit rate.
Read many times larger when the table or index will reduce the hit rate can be used repeatedly in the middle tier data cache, or application. Oracle full-table scan information on the LRU end of the block, so may be out as soon as possible .
Hit rate is the tuning part.
Hit rate does not determine whether the data needs tuning.