美创科技技术社区

注册

 

发新话题 回复该主题

CACHE BUFFER [复制链接]

1#

db cache buffer 有3种,分别为DEFAULT,KEEP,RECYCLE。

各个buffer的命中率:  
SQL> SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS, 1 – (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio" FROM V$BUFFER_POOL_STATISTICS where name=’DEFAULT’;

NAME                 PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS  Hit Ratio  
——————– ————– ————- ————— ———-    
DEFAULT                       85221        754037         9695179 .991844269

V$DB_CACHE_ADVICE:一个参考视图,根据cache size的大小估算其他属性的值。  
SELECT SIZE_FOR_ESTIMATE, BUFFERS_FOR_ESTIMATE, ESTD_PHYSICAL_READ_FACTOR, ESTD_PHYSICAL_READS FROM V$DB_CACHE_ADVICE WHERE NAME=’DEFAULT’ AND BLOCK_SIZE=(SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘db_block_size’) AND ADVICE_STATUS=’ON’;

SIZE_FOR_ESTIMATE:假设的cache size(M)  
BUFFERS_FOR_ESTIMATE:cache size(以buffer数量为计)    
ESTD_PHYSICAL_READ_FACTOR:预估的物理读与真实的物理读的比率,如果没有物理读发生,则为NULL    
ESTD_PHYSICAL_READS:预估的物理读

查看一个对象占用的buffer:  
SQL> select count(*) buffers from v$bh where objd=(select data_object_id from dba_objects where owner=’ZHOUL’ and object_name=’TEST’ and object_type=’TABLE’);

   BUFFERS  
———-    
         0    
————data_object_id表示对象所属段得id(不是对象ID)

查看一些对象占用的buffer:  
COLUMN OBJECT_NAME FORMAT A40    
COLUMN NUMBER_OF_BLOCKS FORMAT 999,999,999,999

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(*);

查看总的buffer数量  
SELECT NAME, BLOCK_SIZE, SUM(BUFFERS) FROM V$BUFFER_POOL GROUP BY NAME, BLOCK_SIZE HAVING SUM(BUFFERS) > 0;

利用KEEP\RECYCLE BUFFER提高访问的性能,有2个大概的想法:  
1、对于大的,不经常访问的对象,建议为其指定 RECYCLE BUFFER    
2、对于频繁访问的对象,考虑为其指定KEEP BUFFER    
如下语法指定AAA表放于KEEP BUFFER:    
SQL> ALTER TABLE test.test STORAGE(BUFFER_POOL KEEP);

Table altered.

分享 转发
TOP
发新话题 回复该主题