db_block_buffers

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