BUFFER CACHE调整指标一般有那些?
FREE BUFFER INSPECTED:当在内存中找一个块来用时,发现找不到这个块,这个时候需要进行物理读把块从磁盘中读取到内存中,在读进来之前要找一个FREE BUFFER,但是这个FREE BUFFER可能一次找不到要找很多次才能找到,找到这个FREE BUFFER前检查了多少个BUFFER可以由这个指标反应出来。性能稍微差
FREE BUFFER WAITS:当在找寻一个块时,找不到可用块,这个时候就要等待DBWN把脏数据写回到磁盘,这样BUFFER CACHE中才有可用的块,如果DBWN没写完,在这样的情况下申请新块就会出现等待。性能最差
BUFFER BUSY WAITS:当在内存中寻找某一块,但是发现这个块已经被别的东西在使用,那当前申请这个块的操作就只有运行等待。性能差
如果查看这几个指标的值?
SQL> select * from v$sysstat where name like '%buffer%';
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ------------------------------ ---------- ---------- ---------- 69 DBWR checkpoint buffers writte 8 0 1208600358 n
70 DBWR thread checkpoint buffers 8 0 3905787588 written
71 DBWR tablespace checkpoint buf 8 0 2649259263 fers written
72 DBWR parallel query checkpoint 8 0 1768645316 buffers written
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ------------------------------ ---------- ---------- ----------
73 DBWR object drop buffers writt 8 0 658143835 en
76 DBWR revisited being-written b 8 0 2773697723 uffer
93 free buffer requested 8 5557 3411924934 94 dirty buffers inspected 8 0 1344569897 95 pinned buffers inspected 8 0 833456521 96 hot buffers moved to head of L 8 0 2030212224
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ------------------------------ ---------- ---------- ---------- RU
97 free buffer inspected 8 0 941676439 102 commit cleanout failures: buff 8 0 1626141486 er being written
110 switch current to new buffer 8 36 1312802324 135 redo buffer allocation retries 2 0 1446958922 320 buffer is pinned count 72 113962 1300470380 321 buffer is not pinned count 72 24932 2670431739 322 no buffer to keep pinned count 72 1 3528454686
17 rows selected.
SQL>
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ------------------------------ ---------- ---------- ---------- 69 DBWR checkpoint buffers writte 8 0 1208600358 n
70 DBWR thread checkpoint buffers 8 0 3905787588 written
71 DBWR tablespace checkpoint buf 8 0 2649259263 fers written
72 DBWR parallel query checkpoint 8 0 1768645316 buffers written
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ------------------------------ ---------- ---------- ----------
73 DBWR object drop buffers writt 8 0 658143835 en
76 DBWR revisited being-written b 8 0 2773697723 uffer
93 free buffer requested 8 5557 3411924934 94 dirty buffers inspected 8 0 1344569897 95 pinned buffers inspected 8 0 833456521 96 hot buffers moved to head of L 8 0 2030212224
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ------------------------------ ---------- ---------- ---------- RU
97 free buffer inspected 8 0 941676439 102 commit cleanout failures: buff 8 0 1626141486 er being written
110 switch current to new buffer 8 36 1312802324 135 redo buffer allocation retries 2 0 1446958922 320 buffer is pinned count 72 113962 1300470380 321 buffer is not pinned count 72 24932 2670431739 322 no buffer to keep pinned count 72 1 3528454686
17 rows selected.
SQL>
SQL> select event,total_waits
2 from v$system_event
3 where event like '%buffer%';
EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
latch: cache buffers chains 1
buffer busy waits 1
latch: cache buffers lru chain 1
SQL>
这里查询出BUFFER BUSY WAITS出现了一次,如何查询这一次在在争用那一个块呢?
SQL> col parameter1 format a10
SQL> col parameter2 format a10
SQL> col parameter3 format a10
SQL> select name,parameter1,parameter2,parameter3 from v$event_name
2 where name ='buffer busy waits';
NAME PARAMETER1 PARAMETER2 PARAMETER3
------------------------------ ---------- ---------- ----------
buffer busy waits
file# block# class#
SQL>
根据文件号和块号就可以查到那个块在被争用。
导致BUFFER BUSY WAITS出现的原因有那些?
DATA BLOCK:如果因为一个对象竞争对象中的同一块,检查查询语句是否有选择性。访问的对象不同但是都在同一块中也会导致
UNDO HEADER:这种情况应该采用自动UNDO管理表空间
UNDO BLOCK:这种情况应该增加块大小
FREE BUFFER WAITS出现原因?
DBWN工作效率低,或者说IO效率低下,这时候需要确保文件分布在不同磁盘上,换快磁盘。还有一种可能是BUFFER CACHE空间太小,这就要调整BUFFER CACHE大小。还有一种就是如果说BUFFER CACHE空间够大,增加DBWN的数量来避免出现FREE BUFFER WAITS.
如何衡量CATCH 的命中率?
一个方法是从SYSSTAT中看
SQL> select 1-(phy.value-lob.value-dir.value)
2 /ses.value "cache hit ratio"
3 from v$sysstat ses,v$sysstat lob,
4 v$sysstat dir,v$sysstat phy
5 where ses.name='session logical reads'
6 and dir.name='physical reads direct'
7 and lob.name='physical reads direct (lob)'
8 and phy.name='physical reads';
cache hit ratio
---------------
.890503408
SQL>
在11G中算法又不一样。。。计算命中率要看数据库版本。
另一个是从STATSPACK中读取
总的计算方法是:(1-物理读的次数)/总的申请块的次数=命中率
物理读:实际发生IO的次数,从磁盘读取文件到内存的次数
SESSION LOGIC READS:简单的说叫发出的总的请求次数
PHYSIC READ DIRECTOR:它是直接读,不会用到CACHE
PHYSIC READ DIRECTOR(LOB):这个操作也不会用到CACHE 所以要除开
数据访问方式影响命中率?
全表扫描
数据或者应用的设置不合理
命中率不是全部?
并不是说命中率越高越好,只是调优的一个指标。减少ORACLE的负荷是关键,因为都知道,他不干活的时候效率最高!在决定增加或减少BUFFER CACHE SIZE的时候需要想很多东西,查看V$DB_CACHE_ADVISE.增大了也不一定会提升性能,避免重复扫描,避免反复查询同样的SQL,ORACLE在对大表操作的时候,这些块被放到LRU末尾。排序的表的SIZE要考虑等等。也不要一贯去增加BUFFER CACHE大小
在增加BUFFER CACHE大小前应该做的工作?
系统中等待事件已经调节好了
SQL语句已经调节好了
SGA固定在内存,防止操作系统把它PAGE OUT写到交换区
确保上一次增加BUFFER CACHE是有效的
在看CATCH命中率是否低下
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15720542/viewspace-731851/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15720542/viewspace-731851/