| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Buffer Pool Testing
I think you are seeing expected behaviour.
Blocks subject to tablescan are loaded in to the LRU end of the cache, even when using a RECYCLE cache. However, if there are free blocks in the cache (state = 0) Oracle uses those rather than flushing other blocks.
Consequently, when you startup and scan
a 400 block table with a 1,000 block cache,
the whole 400 blocks will get into memory. Then
the next 10,000 block scan will start by using
the last 600 blocks of the cache before recycling
the last db_file_multiblock_read_count blocks. By this
time, though, the 1st 400 blocks are at the MRU
end of the chain, and are not moved by the subsequent
tablescan.
If you start with the 10,000 block scan, the whole cache is filled. The second scan then keeps recycling the last db_file_multiblock_read_count blocks (though in your case I guess it's plus one - possibly a cleanout block, possibly the segment header block which may go into the Default pool in v9 - without pushing out any more of the first 1,000 blocks from the first scan.
Periods of time shortly after startup are always likely to show anomalous behaviour.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
____England______January 21/23
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Date: 31 December 2002 19:31
>I'm spending some time today experimenting with buffer pools in
8.1.7. I have two tables that I have assigned to the RECYCLE pool. I
have been running various queries that perform full table scans, then
checking the buffers to see what gets aged out. During my testing, it
seems like the first blocks to get into the RECYCLE buffer pool will
stay there. The following two tables are assigned to the RECYCLE
pool. No other segments are assigned to it:
>
>WORK_ORDER_STEP - 428 blocks of data
>ALRA_TRANSACTION_HISTORY - 14152 blocks of data
>
>The RECYCLE pool has 1000 blocks.
>
>I startup the database, query the WORK_ORDER_STEP table (1 time),
then run multiple queries against the ALRA_TRANSACTION_HISTORY table
(6 times), I see the following in the buffers (the source for this
query is at the end of my email):
>
>BP_NAME OBJ_OWNER NAME
BLOCKS MAX_TOUCH MIN_TOUCH AVG_TOUCH
>-------------------- --------------- ------------------------------ -
--------- --------- --------- ---------
>RECYCLE BIS ALRA_TRANSACTION_HISTORY
569 14 0 .02
>RECYCLE WRKORD WORK_ORDER_STEP
431 1 0 .00
991 2 0 .00
>RECYCLE WRKORD WORK_ORDER_STEP
9 4 0 .44
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: jonathan_at_jlcomp.demon.co.uk Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Jan 02 2003 - 09:20:26 CST
 |
 |