Buffer Cache专题

Buffer Cache专题

1.Buffer Cache的重要参数配置

select component,current_size,min_size FROM v$sga_dynamic_components;

2.使用advice来确认buffer cache的大小

SELECT size_for_estimate "Cache Size (MB)",size_factor,buffers_for_estimate "Buffers",

estd_physical_read_factor est_read_factor,

estd_physical_reads estd_phy_red,estd_physical_read_time est_phy_red_t

FROM  v$db_cache_advice

WHERE NAME='DEFAULT' AND block_size=(SELECT VALUE FROM v$parameter WHERE NAME='db_block_size');

3.一个对象占用buffer的具体情况

select

o.object_name,

decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',

6,'irec',7,'write',8,'pi') state,

count(*) blocks

from x$bh b, dba_objects o

where b.obj = o.data_object_id

--and o.object_name = ' T2'

group by o.object_name, state

order by blocks desc;

4.对象使用pool的具体情况（考虑了各种池子的情况）

select

o.object_name,

decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',

6,'irec',7,'write',8,'pi') state,

count(*) blocks

from x$bh b, dba_objects o

where b.obj = o.data_object_id and state <> 0

group by o.object_name, state

order by blocks asc;

5.寻找热块

SELECT

   obj      object,

   dbarfil  file#,

   dbablk   block#,

   tch      touches

FROM

   x$bh

WHERE

   tch > 10

ORDER BY

   tch asc;

6.整个数据库所有文件中block的总和

select

   sum(blocks)

from

   dba_data_files;

7.空闲空间的比例，最好控制在10%以内

select decode(state,0, 'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3, 'BEING USED', state) "BLOCK STATUS",count(*) 

from  x$bh 

group by decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3, 'BEING USED', state); 

8.最浪费内存的前10个语句占所有语句的比例，建议控制在5%以内

select sum(pct_bufgets) "Percent" 

from (select rank() over ( order by buffer_gets desc ) as 

rank_bufgets,to_char(100 * ratio_to_report(buffer_gets) over (),'999.99') pct_bufgets from v$sqlarea ) 

where rank_bufgets < 11;

9.找出消耗物理IO资源最大的的SQL语句

select disk_reads, substr(sql_text,1,4000) from v$sqlarea  order by disk_reads asc; 

select BUFFER_GETS, substr(sql_text,1,4000) from v$sqlarea  order by BUFFER_GETS asc; 

10.9、清空buffer cache

alter system flush buffer_cache;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29831645/viewspace-1693513/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29831645/viewspace-1693513/