Use the CACHE clauses to indicate how OracleDatabase should store blocks in the buffer cache. If you specify neither CACHEnor NOCACHE, then:
In a CREATE TABLE statement, NOCACHE is thedefault
In an ALTER TABLE statement, the existing valueis not changed.
CACHE For data that is accessed frequently,this clause indicates that the blocks retrieved for this table are placed at the most recently used end of the least recently used (LRU) list in the buffercache when a full tablescan is performed. This attribute is useful for small lookup tables.
As a parameter in the LOB_storage_clause, CACHEspecifies that the database places LOB data values in the buffer cache forfaster access.
Restriction on CACHE You cannot specify CACHEfor an index-organized table. However, index-organized tables implicitlyprovide CACHE behavior.
NOCACHE For data that is not accessedfrequently, this clause indicates that the blocks retrieved for this table areplaced at the least recently used end of the LRU list in the buffer cache whena full table scan is performed. NOCACHE is the default for LOB storage.
As a parameter in the LOB_storage_clause,NOCACHE specifies that the LOB value either is not brought into the buffercache or is brought into the buffer cache and placed at the least recently usedend of the LRU list. The latter is the default behavior.
Restriction on NOCACHE You cannot specifyNOCACHE for an index-organized table.
CACHE READS CACHE READS applies only to LOBstorage. It specifies that LOB values are brought into the buffer cache onlyduring read operations but not during write operations.
Alter table …cache语句不会将表块放入buffer cache 中,只是干预了检索的表块在buffer cache中的行为(将表块放在lru的热端)。当执行全表扫描时,才会将表块放入buffer cache中。
全表扫描一个大表,会将buffer cache中的块老化。
默认的行为(nocache)能够防止将常用的块挤出buffer cache。你全表扫描时需要buffer cache中更多的空间,在默认的行为下,这次的全表扫描会挤出刚刚由于全表扫描放入buffer cache中的块(这些块在lru的冷端),而不是挤出其它的块数据。
如果对表实施alter table t CACHE,只是改变了这种行为。由于对表实施alter table CACHE后,会将所读入的块放入lru热端,当块读入buffer cache中时,首先挤出的不再是先前全表扫描的buffer cache中的块,而是挤出其它的buffer cache块数据。
Keep pool与alter table … cache是两个不同的东东,没有可比性,keep pool( alter table test storage(buffer_pool keep); )是将表缓存到保留池中,而alter cache只是决定了读入的表块以什么方式存储在缓存中的(default pool,keep pool等),
对于Default Pool,默认情况下是nocache,这时候如果采用的是全表扫描,因为这部分数据用的次数比较少,所以该数据是放在LRU段,即会尽快的被age out出buffer。如果启用了cache,那么当全表扫描时,数据会放到MRU端,这样保存的时间就会长,即老化的慢。
但是对于Keep Pool,它的策略不一样,不管是否用全表扫描,数据都会放在MRU端,当keep Pool 空间不足时,会从LRU端age out。并且如果采用Keep Pool,Data 都会cache到内存中,所以会忽略对象本身的Cache 和Nocache。
测试待续...