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.

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

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

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,

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.

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

_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

Johnson

From metalink

Quote:

---

This parameter has a different meaning in Oracle9i, Oracle8i, and in Oracle8.0 and earlier and it is not recommended that this parameter be set since it changes the default the value. The default is 2% of the buffer cache or 4 blocks (min 20 blocks in 9i).

---

So you got Oracle 9i..Right?

Thomas

Quote:

---

Originally posted by Thomasps
_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

---

In actual fact, it seems the information I've proved satisfies my theory. ie. I've shown you that a FTS leaves DB_FILE_MULTIBLOCK_READ_COUNT + 1 blocks of the object being FTSed in the DB_BUFFER_CACHE. Therefore, as long as you are perfoming a FTS on an UNCACHED table, your BUFFER_CACHE will NOT be exhausted.

Also, "Any more idea Johnson..!! Pando, Please jump in"

I'm not sure how tio inteprete this, when I read it first, you fired me up. But, I've re-read it again and I'll give you the benefit of the doubt for now and read it as though, you are still legitimatly seeking additional information.

Hi

I appreciate very much Johnson's answers. Please don't take my words in a negative sense. Please understand I never go for a fighting or abuse people specially professionals like you... Don't like all.. Johnson, Relaly I respect you.

Once again thanks

Closing the thread.


Regards

Thomas

Nah, nah, that's cool. You've still got me curious.

I performed the following:

I create a new table t1 with the CACHE option. I inserted records into the table (168703 records), equating to 1137 Blocks. Remembering the BUFFER_CACHE is 768 Blocks.

Then I did a FTS on T1 and the number of blocks from the table in the buffer cache was 463.

Then I created another table (t2) WITH the NOCACHE OPTION

i.e. CREATE TABLE T2 NOCACHE AS SELECT * FROM t1;

same number of records and did a FTS. Only 17 Blocks in the CACHE.

Interesting... cause it seems I can't completly flush the buffer_cache with a cached table.


Also, a FTS on a CACHED table seems to load into the LRU end of the BUFFER_CACHE... i.e. from LRU towards the MRU (like left to right)... and not from MRU to the LRU (right to left)... this is wierd.

If I perform the

SELECT block#, objd from v$bh order by block# asc;

Would it be correct in assuming the highest number block# is at the LRU end of the buffer cache?

Opening the thread back!

Quote:

---

Also, a FTS on a CACHED table seems to load into the LRU end of the BUFFER_CACHE... i.e. from LRU towards the MRU (like left to right)... and not from MRU to the LRU (right to left)... this is wierd.

---

I think the reason is due to the mid-point insertation. I 8i onwards the algorithem of adding datablocks to the DB buffers is changed. Up to 8i, the datablocks either added in the LRU end or MRU end of the LRU list.

Again

Quote:

---

I create a new table t1 with the CACHE option. I inserted records into the table (168703 records), equating to 1137 Blocks. Remembering the BUFFER_CACHE is 768 Blocks.

Then I did a FTS on T1 and the number of blocks from the table in the buffer cache was 463.

Then I created another table (t2) WITH the NOCACHE OPTION

i.e. CREATE TABLE T2 NOCACHE AS SELECT * FROM t1;

same number of records and did a FTS. Only 17 Blocks in the CACHE.

Interesting... cause it seems I can't completly flush the buffer_cache with a cached table.

---

I think this behaviour is OK. Because you did a FTS on a cached table so it is inserting towards the MRU end (from the mid point, have no idea how the algorthim is calculating the inserting point). WHile doing a FTS on a NOCACHE table, the blocks inserted towards the LRU end (from the mid point. But I think the mid point is calculated differently on various situations.. But I am not sure!)

And in your case
DB_FILE_MULTIBLOCK_READ_COUNT: 16
_small_table_threshold:20

A single read putting 17 blocks (DB_FILE_MULTIBLOCK_READ_COUNT: 16
+ 1 header block) in the buffer cache. Since the consequent reads cannot allocate next 17 blcoks (becasue _small_table_threshold:20 and also note the usage of _small_table_threshold is different in 7,8,8i and 9i ) so reusing the 17 blocks further leaving the other blocks as is. I think this is the reason why you are seeing only 17 blocks in the buffer cache from a FTS with NOCACHE.

Still I need to investigate further. Unfortunately don't have 9i installed and no way to do it now.

Thanks Johnson


Thomas