Re: Buffer Pool Testing

  The reason that I consider it a problem is that I have increased the physical IO on my database. I see Full Table Scans on these two tables, which are large tables relative to the others. I moved them to they RECYCLE pool so that they would have less of an impact on the DEFAULT pool. By doing so, it looks like I am forcing the database to do more physical reads than it may have been doing with these tables in the DEFAULT pool. I thought the FTS blocks would "fight" with each other (the blocks queried the most often would stay in the pool) over the RECYCLE pool, but the algorithm keeps the first blocks in place. Knowing this, I think I need to re-evaluate whether or not these tables were causing a problem in the DEFAULT pool to begin with.   I tried _db_percent_hot_recycle=10, but didn't really notice much of a difference. Since all of my blocks are read into this pool by FTS, I was hoping that it would consider some of them to be hot, thereby causing the rest to be flushed out faster. I just wanted the hottest FTS blocks to stay in the RECYCLE pool, but that wasn't the case.

Jay

>>> jonathan_at_jlcomp.demon.co.uk 01/02/03 06:44PM >>>

The 'problem' is, as you say, related to the tablescan. Craig is correct that NORMALLY a full tablescan will only permit a limited number of blocks to get into the cache at the LRU end of the chain - the number is typically the size of db_file_multblock_read_count.

There are a couple of special cases though, the first being when there are free blocks in the buffer, Oracle will just keep packing in the multiblock reads into the cache until there are no free blocks left. (And specifically "free" means state=0).

The other special case I can think of at the moment is when you have multiple tablescans going on concurrently, and depending on precise timing you can end up with multiples of db_file_multiblock_read_count blocks from different tables near the LRU end of the cache.

There was a period, I believe, when the RECYCLE pool did behave a little differently (can anyone confirm this ?) but in 9.2.0.2, it handles tablescans just the same way as the default pool.

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: 02 January 2003 19:03

> So it seems like my problem is the full table scan. Craig
Shallahammer mentions this in his "All About Oracle's Touch-Count Data Block Buffer Algoithm" paper - "The modified LRU algorithm places full-table scanned blocks read into the buffer cache at the LRU end of the LRU chain and only permits a limited number of these blocks to exist in the cache at once."
> Using my second example (query ALRA_TRANSACTION_HISTORY then
WORK_ORDER_STEP), I can get more blocks of WORK_ORDER_STEP into the cache if I run queries that don't do full table scans.
> I still expected multiple queries against a table (full-scan or
otherwise) to replace the cache blocks that I was no longer using - especially in the RECYCLE pool. But it appears as though the algorithm doesn't work that way.
>
>Thanks,
>Jay
>

also send the HELP command for other information (like subscribing).

**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of D&E except to the extent that it relates to their official business.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jay Hostetter
  INET: jhostetter_at_decommunications.com

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).