-
db_block_buffers
Hi
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
-
the answer from Oracle doc :
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.
-
Thanks Pipo
Hi
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
-
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
Last edited by Thomasps; 03-12-2003 at 10:09 PM.
-
Re: Pipo... Got a piece of info..
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,
Last edited by grjohnson; 03-12-2003 at 11:27 PM.
OCP 8i, 9i DBA
Brisbane Australia
-
_SMALL_TABLE_THRESHOLD
Thanks Johnson
Again I tested in my test envioronment the results are as follows
Version : 8.1.6
db_block_buffers : 16384
_SMALL_TABLE_THRESHOLD : 327 (4*327 = 1308KB)
db_file_multiblock_read_count: 16
db_block_size : 4KB
Ie the value should be = 4 * 16 = 64KB and is not matching with the formule you give. But rather it is 2% of the db_block_buffers!
I checked two databses both are 2% of the buffer cache.
Thanks Johnson
Thomas
-
Now, I'm curious again...time to do some investigation.
From initial tests, from:
BLOCK_SIZE: 16384
DB_CACHE_SIZE: 12582912
DB_FILE_MULTIBLOCK_READ_COUNT: 16
Created a table called t1 (object id = 29230) (190 Blocks);
Created a table called t2 (object id = 29231) (190 Blocks);
Startup
Shutdown
SELECT COUNT(*) FROM t1;
SELECT COUNT(*) FROM v$bh where obj = '29230';
COUNT(*)
---------
17
Which is db_file_mulitblock_readcount + 1 (extra one is a buffer header)
SELECT COUNT(*) FROM t2;
SELECT COUNT(*) FROM v$bh where obj = '29231';
COUNT(*)
---------
17
Then I went back to see if the other T1 fts data was present...
SELECT COUNT(*) FROM v$bh where obj = '29230';
COUNT(*)
---------
1
Only one block in the BufferCache was present.
Did
SELECT block#, objd from v$bh
order by block#;
And the blocks toward the LRU end contain the objects' blocks.
Last edited by grjohnson; 03-13-2003 at 02:25 AM.
OCP 8i, 9i DBA
Brisbane Australia
-
_small_table_threshold
Johnson, I am curious to know the value for the parameter too _small_table_threshold
select ksppinm,ksppstvl,ksppstdf,
decode(bitand(ksppiflg/256,1),1,'TRUE' ,'FALSE'), decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE'),
decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE'),
decode(bitand(ksppstvf,2),2,'TRUE','FALSE'), ksppdesc
from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and KSPPINM ='_small_table_threshold' ;
Please run it and post the result in the same database.
Thomas
-
SQL> connect / as sysdba
Connected.
SQL> select ksppinm,ksppstvl,ksppstdf,
decode(bitand(ksppiflg/256,1),1,'TRUE' ,'FALSE'), decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'D
EFERRED',3,'IMMEDIATE','FALSE'),
decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE'),
decode(bitan 2 3 4 d(ksppstvf,2),2,'TRUE','FALSE'), ksppdesc
from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and KSPPINM ='_small_table_threshold' ;
5
KSPPINM
----------------------------------------------------------------
KSPPSTVL
--------------------------------------------------------------------------------
KSPPSTDF DECOD DECODE(BI DECODE(BIT DECOD
--------- ----- --------- ---------- -----
KSPPDESC
----------------------------------------------------------------
_small_table_threshold
20
TRUE TRUE DEFERRED FALSE FALSE
threshold level of table size for direct reads
OCP 8i, 9i DBA
Brisbane Australia
-
_small_table_threshold
_small_table_threshold =20
And
BLOCK_SIZE: 16384
DB_CACHE_SIZE: 12582912
DB_FILE_MULTIBLOCK_READ_COUNT: 16
(Assume you got Oracle 9i)
So what is the relation..!!!
Again 2% is wrong..?
and also block_size * DB_FILE_MULTIBLOCK_READ_COUNT is also not matching..!! Any more idea Johnson..!!
Pando, Please jump in
Thomas
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|