|
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; | 
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
楼主