S001buffer cache的调整

1、Oracle Buffer Cache大小调整：
在OLTP系统中，对于DB_CACHE_SIZE的设置，推荐配置是：
DB_CACHE_SIZE = SGA_MAX_SIZE/2～ SGA_MAX_SIZE*2/3
使用advice来确认buffer cache的大小


S001A:
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');

       缓存大小 影响因子            buffer块数                       物理读次数  物理读时间

Cache Size (MB)     SIZE_FACTOR      Buffers        EST_READ_FACTOR        ESTD_PHY_RED       EST_PHY_RED_T
-----------------------    --------------------       ----------        -----------------------------        ------------------------       -------------------------
             16       .0889          1964             4.5101            3169888                         3137
             32       .1778       3928          2.4811      1743817          1608
             48       .2667       5892          2.0591      1447227          1290
             64       .3556       7856          1.8053      1268849          1098
             80       .4444       9820          1.6132      1133825           954
             96       .5333      11784          1.4694      1032745           845
            112       .6222      13748          1.3711       963632           771
            128       .7111      15712          1.2702       892727           695
            144          .8      17676          1.1703       822510           620
            160       .8889      19640          1.0809       759692           552
            176       .9778      21604          1.0077       708237           497
            180           1      22095               1       702838           491
            192      1.0667      23568           .9439       663434           449
            208      1.1556      25532           .8794       618103           400
            224      1.2444      27496           .8102       569422           348
            240      1.3333      29460           .7486       526128           302
            256      1.4222      31424           .6805       478270           250
            272      1.5111      33388           .5945       417845           186
            288         1.6      35352           .4964       348916           186
            304      1.6889      37316           .4142       291141           186
            320      1.7778      39280           .3051       214420           186


21 rows selected.

2、查看某张表占用内存的情况：

SQL>select object_name,DBARFIL,DBABLK from x$bh a,dba_objects b 
where a.obj=b.object_id and object_name='T2'；

OBJECT_NAME             DBARFIL     DBABLK
--------------------  ---------- ----------
T2                             1       91697
T2                             1       91696

2 rows

S001B:

通过上面语句返回的信息，查看该对象占用几号文件的那个块，然后再深入查询该块在内存中的具体状态：

SQL>select class, flag, state, lru_flag from x$bh
where dbarfil = 1 and dbablk = 91697;

     CLASS       FLAG      STATE   LRU_FLAG
    ---------- ----------   ----------  ----------
         1            33554433    1          0

1 rows

块的状态对照表：

state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi'

S001C:

所有对象占用内存情况

SQL>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;

可以找出占用buffer最多的对象

S001D:

和上面的语句相同，只不过更详细了。

SQL>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 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
--and object_name='T2'
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;