1. 檢視當前 SGA 動態分配的大小
select component,current_size,min_size from v$sga_dynamic_components;

2. 修改 buffer cache 大小
alter system set db_cache_size=200M scope=both;

3. 指導
DB_CACHE_SIZE = SGA_MAX_SIZE/2～ SGA_MAX_SIZE*2/3
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');

4. 一個物件佔用 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='T'
group by o.object_name, state order by blocks asc;

select object_name,DBARFIL,DBABLK from x$bh a,dba_objects b where a.obj=b.object_id and object_name='T';

5. 物件使用各個池的情況
select
    decode(wbpd.bp_id,1,'keep',2,'recycle',3,'default',4,'2k pool',5,'4k pool',6,'8k pool',7,'16k pool',8,'32k pool','unknown') pool,
    bh.owner,
    bh.object_name,
    count(1) numOfBuffers
from
    x$kcbwds wds,
    x$kcbwbpd wbpd,
    (
        select set_ds, x.addr, o.name object_name, u.name owner
        from sys.obj$ o, sys.user$ u, x$bh x
        where o.owner# = u.user#
            and o.dataobj# = x.obj
            and x.state != 0
            and o.owner# != 0
    ) bh
where
    wds.set_id >= wbpd.bp_lo_sid and
    wds.set_id <= wbpd.bp_hi_sid and
    wbpd.bp_size != 0 and
    wds.addr = bh.set_ds
group by
    decode(wbpd.bp_id,1,'keep',2,'recycle',3,'default',4,'2k pool',5,'4k pool',6,'8k pool',7,'16k pool',8,'32k pool','unknown'),
    bh.owner,
    bh.object_name
order by 1, 4, 3, 2;

6. 尋找熱塊
select obj,dbarfil,dbablk,tch from x$bh where tch > 10 order by tch asc;
select object_name,dbarfil,dbablk from x$bh a,dba_objects b where a.obj=b.object_id and dbarfil=1 and dbablk=338;

7. 整個資料庫所有檔案中總塊數
select sum(blocks) from dba_data_files;

8. 空閒空間的比例, 最好控制在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);

9. buffer cache 具體使用情況
set linesize 200 pagesize 1400
select /*+ ordered use_hash(o u) merge */
    decode(obj#,null,to_char(bh.obj),u.name||'.'||o.name) name,
    count(*) total,
    sum(decode((decode(lru_flag,8,1,0)+decode(sign(tch-2),1,1,0)),2,1,1,1,0)) hot,
    sum(decode(decode(sign(lru_flag-8),1,0,0,0,1)+decode(tch,2,1,1,1,0,1,0),2,1,1,0,0)) cold,
    sum(decode(bitand(flag,power(2,19)),0,0,1)) fts,
    sum(tch) total_tch,
    round(avg(tch),2) avg_tch,
    max(tch) max_tch,
    min(tch) min_tch
from x$bh bh, sys.obj$ o, sys.user$ u
where
    bh.obj<>4294967295 and
    bh.state in (1,2,3) and
    bh.obj=o.dataobj#(+) and
    bh.inst_id=userenv('INSTANCE') and
    o.owner#=u.user#(+) and
    u.name not like 'AURORA$%'
    --and o.owner# > 5
group by decode(obj#,null,to_char(bh.obj),u.name||'.'||o.name)
order by total desc;

10. buffer cache中每一個物件的使用情況
select
    t.name as tablespace_name,
    o.object_name,
    sum(decode(bh.status,'free',1,0)) as free,
    sum(decode(bh.status,'xcur',1,0)) as xcur,
    sum(decode(bh.status,'scur',1,0)) as scur,
    sum(decode(bh.status,'cr',1,0)) as cr,
    sum(decode(bh.status,'read',1,0)) as read,
    sum(decode(bh.status,'mrec',1,0)) as mrec,
    sum(decode(bh.status,'irec',1,0)) as irec
from v$bh bh
join dba_objects o on o.data_object_id=bh.objd
join v$tablespace t on t.ts#=bh.ts#
group by t.name,o.object_name
order by xcur desc;

11. 命中率
11-1. 緩衝區總命中率
select 100*(p1.value+p2.value-p3.value)/(p1.value+p2.value) "Hit Ratio(%)"
from v$sysstat p1, v$sysstat p2, v$sysstat p3
where p1.name = 'db block gets' and p2.name = 'consistent gets' and p3.name = 'physical reads';

11-2. 指定 session 緩衝區命中率
select (p1.value+p2.value-p3.value)/(p1.value+p2.value)
from v$sesstat p1,v$statname n1,v$sesstat p2,v$statname n2,v$sesstat p3,v$statname n3
where
    n1.name='db block gets' and
    p1.statistic#=n1.statistic# and
    p1.sid=(select distinct sid from v$mystat) and
    n2.name='consistent gets' and
    p2.statistic#=n2.statistic# and
    p2.sid=p1.sid and
    n3.name='physical reads' and
    p3.statistic#=n3.statistic# and
    p3.sid=p1.sid;

12. 各資料檔案在資料庫啟動後的訪問情況
select a.file_name, b.phyrds, b.phyblkrd from sys.dba_data_files a, v$filestat b
where b.file# = a.file_id
order by a.file_id

13. 清空buffer cache
alter system flush buffer_cache;

BUFFER CACHE 基礎知識

相關文章