请教如何保证一张表或者是IOT能永远CACHE 在内存里？不会被踢出去？

autumn_leaf

我在应用程序里没法全表扫描啊，只能 COUNT(*),可是COUNT( * ) 更本没把数据加载到KEEP 池
  drop table test;
  create table test
  (
    myid         number(6),
    mysum        number(16,4),
    constraint   pk_test primary key(myid)
  ) organization index nologging;
  
  insert into  test select rownum,rownum from dba_objects where rownum<=10000;
  commit;
  
  alter table test storage (buffer_pool keep);

alter system flush buffer_cache;
set autot trace
select count(*) from test;

        131  recursive calls
          0  db block gets
         69  consistent gets
         32  physical reads
         80  redo size
        411  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

select * from test;
         67  recursive calls
          0  db block gets
        726  consistent gets
          7  physical reads
          0  redo size
     176731  bytes sent via SQL*Net to client
       7711  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      10000  rows processed

autumn_leaf

我想首先确定一个问题
是否只要我的KEEP池足够大，就可以保证我的表一定会cache在KEEP池里？
比如KEEP池 30M,我的表永远只有 20M ？然后我全表扫描一次表，
即使我不再使用这个表，只要数据库不启动，不清缓存，我的表永远会在KEEP池里？

Yong Huang

> 我不清楚怎么把索引放到KEEP池了

If it's not a primary key index, try
alter index indexname storage (buffer_pool keep);

If it's IOT, I think it's just
alter table test storage (buffer_pool keep);

Answer to your message #11:

If you check the buffer status before you drop the table, they're all exclusive current. (I ran the test as user system in Oracle 10.2.0.4.)

SQL> select file_id, block_id, blocks from dba_extents where segment_name = 'PK_TEST' and owner = 'SYSTEM';

   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         1      60809          8
         1      60857          8
         1      60897          8

SQL> select distinct status from v$bh where file# = 1 and (block# between 60809 and 60816 or block# between 60857 and 60864 or block# between 60897 and 60904);

STATUS
-------
xcur

After you drop the table, they all become free. Immediately after you recreate the table (and suppose the database has no other activity), the *exactly same* blocks of the segment become xcur again. So they can readily be reused.

The above is true even if you don't assign the table to the keep pool. To properly test the effect of the keep pool, you have to configure a small buffer cache default pool, and a keep pool at least as large as the segment size, and run SQLs that cause all kinds of other table blocks to be cached in the default pool.

Answer to #12:

Please stop asking questions and start to do more test.

sundog315

有趣的现象，alter table test storage (buffer_pool keep);是否不是即时生效？

Yong Huang

What do you mean by "即时生效"? The table property is immediately changed in the data dictionary. But the table blocks will have to be (re-)scanned to go to the keep pool.

sundog315

按照楼主的测试，
insert into as select时使用的是default pool，然后变更到keep pool，进行查询时没有物理读，是否还是从default pool里直接读取呢？

Yong Huang

Now I see what you mean. I think if the blocks are still in buffer cache, regardless which pool, there's only memory read, and the table storage attribute (buffer_pool keep) won't have a chance to take effect. As soon as one block has to be read from datafile into buffer cache, this block will go to the keep pool. When I have time, I'll test it. Basically, we need to go from x$bh.set_ds (working set address I think) to x$kcbwds and to x$kcbwbpd to find which pool the buffer is in. The following query can be simplified to do exactly that:

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

sundog315

Yong Huang 发表于 2012-4-2 23:41
Now I see what you mean. I think if the blocks are still in buffer cache, regardless which pool, the ...

这个确实是最合理的做法，只是这时候是否依然遵从LRU算法？如果遵从，并且表里的块比较热的话，可能还会一直存在于default pool里。

不过，一般情况下，在哪也无所谓。呵呵

Yong Huang

> 这时候是否依然遵从LRU算法？

I remember reading documents saying the LRU (actually modified LRU) algorithm still applies. Indeed, if the block is always hot, it will never have a chance to go to the pool you assigned. You can force that by flushing buffer_cache.