Opening the thread back!

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

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