The Cache Hint
- From: Sidney Chen <huanshengchen@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 11 Mar 2012 19:56:46 +0800
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
