oracle cache table,ORACLE 11GR2 新特性CACHE表与以前的区别

alter system set db_keep_cache_size=80M --设置keep值大小

alter table TBL_AIRCOMPANY storage(buffer_pool keep)--把表TBL_AIRCOMPANY设置成keep在pool池中

ALTER TABLE TBL_AIRCOMPANY cache --让TBL_AIRCOMPANY可以缓存

SELECT OBJECT_NAME, A.STATUS, COUNT(*)

FROM V$BH A, USER_OBJECTS B

WHERE A.OBJD = B.OBJECT_ID

--AND OBJECT_NAME IN ('TBL_AIRCOMPANY')

GROUP BY OBJECT_NAME, A.STATUS; --查看是否在缓存keep中

--各个buffer的空间信息

select p.name,sum(a.cnum_repl) "total buffers",sum(a.anum_repl) "free buffers" from x$kcbwds a, v$buffer_pool p

where a.set_id>=p.LO_SETID and

a.set_id<=p.HI_SETID

group by p.name;

--脚本用来定位哪些object在buffer cache中存在，占用的buffer cache的量是多少以及占用的是什么类型的buffer cache。

select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',

4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',

7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,

bh.object_name,bh.blocks

from x$kcbwds ds,x$kcbwbpd pd,(select 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

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

order by  decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',

4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',

7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN'),bh.blocks;

select

(select value from V$SYSSTAT where name='physical write total bytes')/

(select value from V$SYSSTAT where name='physical read total bytes')*100||'%' 写读比例

from dual

一、对于普通表的cache方法：

SQL> conn test/test 已连接。

SQL> alter table t1 storage (buffer_pool keep) cache;

表已更改。

查询普通表是否已经被cache：

SQL> select table_name,cache,buffer_pool from user_TABLES;

TABLE_NAME                     CACHE      BUFFER_

------------------------------ ---------- -------

T1                                 Y      KEEP

二、对于普通LOB类型的segment的cache方法

SQL> desc t2

名称 是否为空? 类型

---------------------------------------- -------- ----------------------------

ID NUMBER

C2 CLOB

SQL> alter table t2 modify lob(c2) (storage (buffer_pool keep) cache);

表已更改。

三、对基于CLOB类型的对象的cache方法

SQL> desc lob1

名称 是否为空? 类型

----------------------------------------- -------- --------------- ID NUMBER

C1 XMLTYPE

SQL> alter table lob1 modify lob(c1.xmldata) (storage (buffer_pool keep) cache);

表已更改。

那么，怎么测试lob segment是否被cache了呢?

来看看oracle给我的回复:

Hi Frank,

To verify which buffer pool is used by a lob segment query dba_segments,

See below test case

SQL> create table test(name varchar2(10), address clob);

Table created.

SQL> select table_name,cache,buffer_pool from user_TABLES;

TABLE_NAME CACHE BUFFER_

------------------------------ ----- -------

TEST N DEFAULT

SQL> alter table test modify lob(address) (storage (buffer_pool keep) nocache);

Table altered.

SQL> select table_name,cache,buffer_pool from user_TABLES;

TABLE_NAME CACHE BUFFER_

------------------------------ ----- -------

TEST N DEFAULT

SQL> select segment_name,segment_type,buffer_pool from user_segments;

SEGMENT_NAME

--------------------------------------------------------------------------------

SEGMENT_TYPE BUFFER_

------------------ -------

TEST

TABLE DEFAULT

SYS_IL0000123006C00002$$

LOBINDEX KEEP

SYS_LOB0000123006C00002$$

LOBSEGMENT KEEP

SQL> select column_name,segment_name from user_lobs;

COLUMN_NAME

--------------------------------------------------------------------------------

SEGMENT_NAME

------------------------------

ADDRESS

SYS_LOB0000123006C00002$$

SQL>

Thus you can see the lob segment SYS_LOB0000123006C00002$$ is mapped to clob column ad

dress and is using

keep buffer pool as expected.

User_objects will display results for object as a whole not for individual columns.

See below, we need to alter the complete table to use keep buffer_pool and user_tables will then

display results as expected.

SQL> alter table test storage (buffer_pool keep);

Table altered.

SQL> select table_name,cache,buffer_pool from user_TABLES;

TABLE_NAME CACHE BUFFER_

------------------------------ ----- -------

TEST N KEEP

SQL>