The Cache Hint
From: Sidney Chen <huanshengchen_at_gmail.com>
Date: Sun, 11 Mar 2012 19:56:46 +0800
Message-ID: <CAM_ddu_4QKrkyWF4+YZFBLhSXseGhOcQkty96JoFMmMB2LnX7A_at_mail.gmail.com>
The online doc says that the cache hint can overrides the caching specification of the table. Is it possible to change the default specification on the medium or long table, that's for the tables cross the small table threshhold, defined by _small_table_threshhold. For the detail cache behavious on small/medium/long tables, you may want to refer to Jonathan's new book "Oracle Core", Secion Tablescans in Chapter 5. I've tested on both 10.2.0.5 and 11.2.0.2 Linux 32 bit, the cache hint actually don't change the behaviour of medium and long tables, the statistics: table scans (long tables) and table scans (short tables) don't change. So I wonder the cache hint is just for the small tables, placing the fresh blocks from disk to the MRU end of the LRU list, rathen than the middle point, to let the blocks live longer in buffer pool. I think it makes sense, because if the cache hint can be used to cache any table, it will be a serious potential threat to the buffer pool, anyone will select privelege on big table and do damage to the buffer pool.
Date: Sun, 11 Mar 2012 19:56:46 +0800
Message-ID: <CAM_ddu_4QKrkyWF4+YZFBLhSXseGhOcQkty96JoFMmMB2LnX7A_at_mail.gmail.com>
The online doc says that the cache hint can overrides the caching specification of the table. Is it possible to change the default specification on the medium or long table, that's for the tables cross the small table threshhold, defined by _small_table_threshhold. For the detail cache behavious on small/medium/long tables, you may want to refer to Jonathan's new book "Oracle Core", Secion Tablescans in Chapter 5. I've tested on both 10.2.0.5 and 11.2.0.2 Linux 32 bit, the cache hint actually don't change the behaviour of medium and long tables, the statistics: table scans (long tables) and table scans (short tables) don't change. So I wonder the cache hint is just for the small tables, placing the fresh blocks from disk to the MRU end of the LRU list, rathen than the middle point, to let the blocks live longer in buffer pool. I think it makes sense, because if the cache hint can be used to cache any table, it will be a serious potential threat to the buffer pool, anyone will select privelege on big table and do damage to the buffer pool.
Any discussion is appreciated.
http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#BABGEDAD
---quote----
The CACHE hint instructs the optimizer to place the blocks retrieved for
the table at the most recently used end of the LRU list in the buffer cache
when a full table scan is performed. This hint is useful for small lookup
tables.
---unquote----
--
Regards
Sidney Chen
--
http://www.freelists.org/webpage/oracle-l
Received on Sun Mar 11 2012 - 06:56:46 CDT