S001buffer cache的调整

最新推荐文章于 2021-01-28 07:04:01 发布

原创最新推荐文章于 2021-01-28 07:04:01 发布 · 828 阅读

0 ·

CC 4.0 BY-SA版权

oracle系统专栏收录该内容

30 篇文章

订阅专栏

本文主要探讨了Oracle数据库中Buffer Cache的调整，通过分析不同Cache Size对物理读次数和时间的影响，提供了选择合适Cache Size的依据。此外，还展示了如何查看特定表的内存占用情况，以及对象在Buffer 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;