Pipo... Got a piece of info..
Hi
got a piece of info and looking for more..
Oracle Says [from metalink]
Full table scan's can make the most needed blocks from the SGA to age out due to the LRU algorithm. In Oracle7 a new init.ora parameter called SMALL_TABLE_THRESHOLD has been added. This determines the number of buffers in the SGA that are available for full table scans. The basic assumption is that the blocks read due to full table scans may not be required in near future and therefore should not age out other blocks already in SGA.
Thats it..! So SMALL_TABLE_THRESHOLD this is the threshold value. And if anybody know the corresponding parameter in 8i and 9i please update.
Good luck Pipo and thanks joining for the trasure hunt..!
Regards
Thomas
Re: Pipo... Got a piece of info..
Quote:
Originally posted by Thomasps
Hi
got a piece of info and looking for more..
Oracle Says [from metalink]
Full table scan's can make the most needed blocks from the SGA to age out due to the LRU algorithm.
Thats it..! So SMALL_TABLE_THRESHOLD this is the threshold value. And if anybody know the corresponding parameter in 8i and 9i please update.
Good luck Pipo and thanks joining for the trasure hunt..!
Regards
Thomas
SMALL_TABLE_THRESHOLD ( _small_table_threshold) is also used by CBO (based on the tables statistics) to determine if a table should have a FTS performed on it or not. A short table is usually loaded intot the MRU end of teh Buffer Cache... YOu can FLOOD the BUFFER_CACHE by CACHE (ing) LARGE tables. (Small lookup tables should be created with the CACHE option). And when read, are read into the MRU end.
Also, Full Table Scans (on NOCACHE/LARGE tables) are read into the LRU end of the Buffer Cache at a rate of DB_FILE_MULTIBLOCK_READ_COUNT * BLOCK SIZE. Therefore, if you have a Buffer_cache of 1000 Blocks, a DB_FILE_MULTIBLOCK_READ_COUNT of 8, (8K block size) each read will read in 8 (* 8k = 64K) blocks into the LRU of the buffercache each read. Leaving the other 992 blocks untouched. (Although there could be other Blocks of the same table scattered thoguhout the Buffer_cache from other transactions).
One Mr Jonathan Lewis proved this to me a couple of years ago.
Cheers,