In case of a full table scan, table data fetched and deposited to the LRU end of the buffer cache. And if the table is CACHED the retrived data blocks will placed in the MRU end. Fine then the question is
Suppose the DB_BLOCK_BUFFERS=10000 and the full table (say table X) scan fetched 9,500 data blocks. There there will be two cenerios
1. The whole 9,500 block from the table X replace the 9,500 blokcs in the in the buffer cache and remaining 500 data blocks from other objects (For a certin point of time). So the whole Buffer Cache is exhausted. If this is true what will happen if a table Y is fetched with more that 10,000 blocks?
Or
2. Out of 10,000 blocks in the Buffer cache certin number of blocks are used for stroing the data from the table X and after a threshold value these blocks reused further. If this is true what is the threshold value? Which parameter specying this value?
Experts please.
(Note : Leaving for today...bye)
Regards
Thomas
Thomas Saviour(royxavier@yahoo.com)
Technical Lead (Databases)
Thomson Reuters (Markets)
When an Oracle process accesses a buffer, the process moves the buffer to the most recently used (MRU) end of the LRU list. As more buffers are continually moved to the MRU end of the LRU list, dirty buffers "age" towards the LRU end of the LRU list.
The first time an Oracle user process requires a particular piece of data, it searches for the data in the database buffer cache. If the process finds the data already in the cache (a cache hit), it can read the data directly from memory. If the process cannot find the data in the cache (a cache miss), it must copy the data block from a datafile on disk into a buffer in the cache before accessing the data. Accessing data through a cache hit is faster than data access through a cache miss. Before reading a data block into the cache, the process must first find a free buffer. The process searches the LRU list, starting at the least recently used end of the list. The process searches either until it finds a free buffer or until it has searched the threshold limit of buffers.
If the user process finds a dirty buffer as it searches the LRU list, it moves that buffer to the dirty list and continues to search. When the process finds a free buffer, it reads the data block from disk into the buffer and moves the buffer to the MRU end of the LRU list.
If an Oracle user process searches the threshold limit of buffers without finding a free buffer, the process stops searching the LRU list and signals the DBW0 background process to write some of the dirty buffers to disk.
... [a bit further] ...
The cache has a limited size, so not all the data on disk can fit in the cache. When the cache is full, subsequent cache misses cause Oracle to write dirty data already in the cache to disk to make room for the new data. (If a buffer is not dirty, it does not need to be written to disk before a new block can be read into the buffer.)
Subsequent access to any data that was written to disk results in additional cache misses.
Agree to you. But my question is very specific to a full table scan. yesterady I was talking to a DBA and he told me " A Full table scn cannot exhaust the buffer cache. Rather there certain limits to control the usage of the buffer cache." ie A 'single FULL Table scan' cannot use the complete or above a certin limit of the buffer pool. But i couldn't find any thing in the docs explaining about this. Once agin please read my previous post.
Appreciate your answer.
Regards
Thomas
Thomas Saviour(royxavier@yahoo.com)
Technical Lead (Databases)
Thomson Reuters (Markets)
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
Last edited by Thomasps; 03-12-2003 at 09:09 PM.
Thomas Saviour(royxavier@yahoo.com)
Technical Lead (Databases)
Thomson Reuters (Markets)
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.
Reply With Quote
Bookmarks