ITPUB论坛－专业的IT技术社区

标题: buffer cache两三问 [打印本页]

作者: elysium_H 时间: 2010-9-15 11:24
标题: buffer cache两三问
1.alter table test cache;使对test全表扫描的数据全部cache到默认池的MRU端。那这些数据会慢慢移动到LRU端，进而移出内存么？

2.之后对此表的所有DML操作，oracle会自动把变化cache到默认池中吗？还是再次执行全表扫描时才会cache到默认池中？

3.如果会被移出内存，那么使用alter table test storge(buffer_pool keep) cache;是不是可以避免这个问题？

4.oracle在查询默认池中是否有需要的数据时，是从MRU端开始搜索吗？

5.如果是从MRU端搜索，那么上述情况是不是最好cache到保持池，以减少oracle对默认池中不必要数据的搜索

6.不想再把test表cache到内存中，应如何操作？

作者: zergduan 时间: 2010-9-15 14:21
1.会
2.cache和使用什么池没关系；所有的变化都先发生在内存中，然后flush到磁盘上
3.buffer_pool keep 使用keep 池，如果这个池足够大，就可以保证不会被移出keep pool~ cache参数没有这个作用
4.看看db cache的结构，MRU是用来选择age out内存的数据用的，不是用来搜索数据用的
5. .... 没发回答你.
6. alert table test nocache. 取消cache参数。

作者: elysium_H 时间: 2010-9-15 15:11
4. oracle 10 concept 上说：当Oracle 的用户进程（user process）首次查询某块数据时，将首先在数据缓存区内进行搜索。
那oracle是如何搜索呢，不是搜索LRU list么？如果会搜索LRU list，那么oracle又是怎样搜索的呢？

[ 本帖最后由 elysium_H 于 2010-9-15 15:50 编辑 ]

作者: elysium_H 时间: 2010-9-15 15:49
2.是不是可以这样理解，执行alter table test cache后，通过全表扫描把test的全表数据cache到内存中，之后所有的DML操作引起的数据变化，在提交后都会在内存中保留结果，即缓存中test表数据，是DB文件中test表数据的全镜像

作者: microsoft_fly 时间: 2010-9-15 18:08
标题: 回复 #3 elysium_H 的帖子
首先看数据块的地址是否符合，不符合跳过，接下来，地址符合后，看是否为CR块，如果是CR块，跳过，再则，看是否为READING中，如果是，等待，如果不是，再看data buffer上用户列表中是否正在使用，如果是，则看锁定模式是否和当前兼容，兼容则把DATA BUFFER中的数据块地址返回给进程并把当前进程号放到用户列表中，如果不兼容，用刚才那个块复制一个CR块，并把当前进程号放到用户列表中。如果找不到符合的地址，到存储或磁盘上去读到内存

作者: sundog315 时间: 2010-9-16 08:11

原帖由 elysium_H 于 2010-9-15 15:49 发表
2.是不是可以这样理解，执行alter table test cache后，通过全表扫描把test的全表数据cache到内存中，之后所有的DML操作引起的数据变化，在提交后都会在内存中保留结果，即缓存中test表数据，是DB文件中test表数据的全镜像

Oracle的机制在正常情况下（通过SGA）是不会直接对文件进行修改的，所有的变更都在buffer cache中，由dbwr及checkpoint来负责将buffer cache中的内容写入文件，如果buffer cache 中不存在这个表（没有被读入或者被换出）那么需要先将文件中的内容读入buffer cache然后才能继续操作

作者: sundog315 时间: 2010-9-16 08:13

原帖由 elysium_H 于 2010-9-15 11:24 发表
3.如果会被移出内存，那么使用alter table test storge(buffer_pool keep) cache;是不是可以避免这个问题？

可以，前提是keep buffer池足够大

作者: microsoft_fly 时间: 2010-9-16 17:57
再大都有可能移出

作者: sundog315 时间: 2010-9-16 19:33

原帖由 microsoft_fly 于 2010-9-16 17:57 发表
再大都有可能移出

为啥？

作者: Yong Huang 时间: 2010-9-17 03:26
Keep pool is really just a separate pool. I don't think there's anything magic. I hope somebody can test. Just allocate a few big tables to a small keep pool so the pool is definitely smaller than the total size of these few tables. Full scan one table. Find their blocks in the pool. Scan another. See if the first table's blocks have disappeared from the keep pool. I think they will.

Yong Huang

作者: zergduan 时间: 2010-9-17 06:46

原帖由 Yong Huang 于 2010-9-17 03:26 发表
Keep pool is really just a separate pool. I don't think there's anything magic. I hope somebody can test. Just allocate a few big tables to a small keep pool so the pool is definitely smaller than the total size of these few tables. Full scan one table. Find their blocks in the pool. Scan another. See if the first table's blocks have disappeared from the keep pool. I think they will.

Yong Huang

以前做过这样的测试

http://www.itpub.net/thread-1310824-2-1.html

作者: zergduan 时间: 2010-9-17 07:06
记得当时的结论是，当表是Scattered Read读入keep pool的时候，对于keep pool中的block，先进先出的方式被挤出keep pool;
如果表是以sequential read读入keep pool，那么似乎有一个LRU在工作，访问最少的block被挤出keep pool

作者: sundog315 时间: 2010-9-17 08:52
http://download.oracle.com/docs/ ... 1/memory.htm#i30935

7.2.4 Considering Multiple Buffer Pools

Multiple buffer pools let you address these differences. You can use a KEEP buffer pool to maintain frequently accessed segments in the buffer cache, and a RECYCLE buffer pool to prevent objects from consuming unnecessary space in the cache. When an object is associated with a cache, all blocks from that object are placed in that cache. Oracle Database maintains a DEFAULT buffer pool for objects that have not been assigned to a specific buffer pool. The default buffer pool is of size DB_CACHE_SIZE. Each buffer pool uses the same Least Recently Used (LRU) replacement policy (for example, if the KEEP pool is not large enough to store all of the segments allocated to it, then the oldest blocks age out of the cache).

作者: zergduan 时间: 2010-9-17 09:25

原帖由 sundog315 于 2010-9-17 08:52 发表
 http://download.oracle.com/docs/ ... 1/memory.htm#i30935

7.2.4 Considering Multiple Buffer Pools

Multiple buffer pools let you address these differences. You can use a KEEP buffer pool to maintain frequently accessed segments in the buffer cache, and a RECYCLE buffer pool to prevent objects from consuming unnecessary space in the cache. When an object is associated with a cache, all blocks from that object are placed in that cache. Oracle Database maintains a DEFAULT buffer pool for objects that have not been assigned to a specific buffer pool. The default buffer pool is of size DB_CACHE_SIZE. Each buffer pool uses the same Least Recently Used (LRU) replacement policy (for example, if the KEEP pool is not large enough to store all of the segments allocated to it, then the oldest blocks age out of the cache).

当时我测试的版本是9.2.0.8

的确9i的文档中也有同样的话，但是如果像文档中说的，怎么解释我第二个实验

TEST_T1(cache) ,TEST_T2(nocache)被依次通过full scan读入keep pool.

多次full scan test_t1后，通过full scan把test_t3读入keep pool.由于keep pool不能完全承受3张表，必定有一部分block被挤出keep pool.

如果有LRU,那么拥有cache属性并且被多次读取的test_t1不应该被挤出keep pool, 但是结果正好相反~

[ 本帖最后由 zergduan 于 2010-9-17 09:32 编辑 ]

作者: sundog315 时间: 2010-9-17 10:51
首先，cache属性对于keep buffer无效

Note:
The NOCACHE clause has no effect on a table in the KEEP cache.

其次，全表扫描，并不会将块放在LRU的热端，并且，相反，会放在LRU的冷端，先被age out出去。

作者: zergduan 时间: 2010-9-17 11:06

[ 本帖最后由 zergduan 于 2010-9-17 11:29 编辑 ]

作者: sundog315 时间: 2010-9-17 11:34
手够快的。

作者: sundog315 时间: 2010-9-17 11:37
1.select count(*) from test_t1;
test_t1的内容位于LRU的冷端
2.select count(*) from test_t2;
还可以放下，比test_t1靠近热端
3.循环select count(*) from test_t1;
结果不变
4.select count(*) from test_t3;
从冷端查找，刷掉test_t1

所以，这个就是为什么觉得是先进先出的原因。其实并不是先进先出。

作者: zergduan 时间: 2010-9-17 11:53

原帖由 sundog315 于 2010-9-17 11:34 发表
手够快的。

删掉回复，因为我不大肯定~

我印象中一个表被全表读入buffer pool，的确是被放在冷端，但是随着这个表上的block被使用，这些block会移动到热端。

假如keep pool中存在LRU链表
我上面的实验中第一次select count(*) from test_t1 (test_t2)把test_t1(test_t2)放入keep pool LRU 冷端，但是后面

declare
a number;
i number;
begin
i:=0;
for i in 1..50 loop
select count(*) into a from mydb.test_t1;
end loop;
end;

应该会让这些test_t1的block移动到热端(这里我不确定，是否full scan 表能改变这些block在LRU上的位置) ，随后我把test_t3通过全表加载道keep pool.那么如果存在LRU的话test_t2的block应该被挤出keep pool ,而不是test_t1.

作者: zergduan 时间: 2010-9-17 11:59

原帖由 sundog315 于 2010-9-17 11:37 发表
1.select count(*) from test_t1;
test_t1的内容位于LRU的冷端
2.select count(*) from test_t2;
还可以放下，比test_t1靠近热端
3.循环select count(*) from test_t1;
结果不变
4.select count(*) from test_t3;
从冷端查找，刷掉test_t1

所以，这个就是为什么觉得是先进先出的原因。其实并不是先进先出。

的确，如你说得可以解释我那个试验...

那么我这里修改一下试验

1.select count(*) from test_t1;
test_t1的内容位于LRU的冷端
2.select count(*) from test_t2;
还可以放下，比test_t1靠近热端
3. declare
a number;
i number;
begin
i:=0;
for i in 1..50 loop
select /*+ use_nl(test_t1,txx) */ count(object_type) into a from mydb.test_t1,mydb.txx where test_t1.object_id=txx.object_id;
end loop;
end;
/
这样我应该可以让TEST_T1上的block移动到热端了吧？

如下:

SQL> startup force
ORACLE instance started.

Total System Global Area  126950956 bytes
Fixed Size                454188 bytes
Variable Size             96468992 bytes
Database Buffers          29360128 bytes
Redo Buffers                667648 bytes
Database mounted.
Database opened.
SQL>  select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
  2          4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
  3          7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
  4          bh.object_name,bh.blocks
  5 from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,
  6          o.name object_name,count(*) BLOCKS
  7          from obj$ o, x$bh x where o.dataobj# = x.obj
  8          and x.state !=0 and o.owner# !=0
  9          group by set_ds,o.name) bh
10  where ds.set_id >= pd.bp_lo_sid
11  and ds.set_id <= pd.bp_hi_sid
12  and pd.bp_size != 0
13  and ds.addr=bh.set_ds;

SUBCACHE    OBJECT_NAME                               BLOCKS
------------ ---------------------------------------- ----------
DEFAULT    AQ$_QUEUES                                     2
DEFAULT    REPCAT$_REPPROP                                  1

Elapsed: 00:00:00.00
SQL> select count(*) from mydb.test_t1;

  COUNT(*)
----------
   19068

Elapsed: 00:00:00.01
SQL> select count(*) from mydb.test_t2;

  COUNT(*)
----------
   19068

Elapsed: 00:00:00.00
SQL>  select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
  2          4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
  3          7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
  4          bh.object_name,bh.blocks
  5 from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,
  6          o.name object_name,count(*) BLOCKS
  7          from obj$ o, x$bh x where o.dataobj# = x.obj
  8          and x.state !=0 and o.owner# !=0
  9          group by set_ds,o.name) bh
10  where ds.set_id >= pd.bp_lo_sid
11  and ds.set_id <= pd.bp_hi_sid
12  and pd.bp_size != 0
13  and ds.addr=bh.set_ds;

SUBCACHE    OBJECT_NAME                               BLOCKS
------------ ---------------------------------------- ----------
KEEP       TEST_T1                                        245
KEEP       TEST_T2                                        234
DEFAULT    AQ$_QUEUES                                     2
DEFAULT    REPCAT$_REPPROP                                  1

Elapsed: 00:00:00.00
SQL> declare
  2  a number;
  3  i number;
  4  begin
  5  i:=0;
  6  for i in 1..500 loop
  7  select /*+ use_nl(test_t1,txx) */ count(object_type) into a from mydb.test_t1,mydb.txx where test_t1.object_id=txx.object_id;
  8  end loop;
  9  end;
10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:21.09
SQL>  select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
  2          4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
  3          7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
  4          bh.object_name,bh.blocks
  5 from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,
  6          o.name object_name,count(*) BLOCKS
  7          from obj$ o, x$bh x where o.dataobj# = x.obj
  8          and x.state !=0 and o.owner# !=0
  9          group by set_ds,o.name) bh
10  where ds.set_id >= pd.bp_lo_sid
11  and ds.set_id <= pd.bp_hi_sid
12  and pd.bp_size != 0
13  and ds.addr=bh.set_ds;

SUBCACHE    OBJECT_NAME                               BLOCKS
------------ ---------------------------------------- ----------
KEEP       TEST_T1                                        245
KEEP       TEST_T2                                        234
DEFAULT    TXX                                           11
DEFAULT    T1_IND                                        41
DEFAULT    AQ$_QUEUES                                     2
DEFAULT    REPCAT$_REPPROP                                  1

6 rows selected.

Elapsed: 00:00:00.00
SQL> select count(*) from mydb.test_t3;

  COUNT(*)
----------
   19068

Elapsed: 00:00:00.00
SQL>  select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
  2          4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
  3          7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
  4          bh.object_name,bh.blocks
  5 from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,
  6          o.name object_name,count(*) BLOCKS
  7          from obj$ o, x$bh x where o.dataobj# = x.obj
  8          and x.state !=0 and o.owner# !=0
  9          group by set_ds,o.name) bh
10  where ds.set_id >= pd.bp_lo_sid
11  and ds.set_id <= pd.bp_hi_sid
12  and pd.bp_size != 0
13  and ds.addr=bh.set_ds;

SUBCACHE    OBJECT_NAME                               BLOCKS
------------ ---------------------------------------- ----------
KEEP       TEST_T1                                        233
KEEP       TEST_T2                                        33
KEEP       TEST_T3                                        234
DEFAULT    TXX                                           11
DEFAULT    T1_IND                                        41
DEFAULT    AQ$_QUEUES                                     2
DEFAULT    REPCAT$_REPPROP                                  1

7 rows selected.

Elapsed: 00:00:00.00
SQL>

可以看到TEST_T2被挤出了KEEP POOL.

可见sundog315说得是对的，keep pool 一样是有LRU链表的

作者: sundog315 时间: 2010-9-17 12:38

BTW,全表扫描不会移动到热端的，否则，cache属性就没有意义了。

作者: Yong Huang 时间: 2010-9-18 04:57

原帖由 sundog315 于 2010-9-16 22:38 发表

BTW,全表扫描不会移动到热端的，否则，cache属性就没有意义了。

The correct way to say that is: Full scan of a table whose size is bigger than _small_table_threshold blocks (2% of buffer cache by default) causes its blocks to be placed on the cold end of LRU, unless the cache flag of the table is set.

Very interesting discussion!

Yong Huang

欢迎光临 ITPUB论坛－专业的IT技术社区 (http://www.itpub.net/)