RE: Buffer Pool Testing

Jay,

Have you considered the default value of '_small_table_threshold'? Apparently it defaults to 2% of the Buffer cache in 8i (haven't seen any official docs to support this though).... Also wondering how this works in the case of the 'special' buffer pools in combination with the 'CACHE' and 'NOCACHE' option on the tables in question. Is my understanding that the kernel would load a 'small' table into the MRU end rather than the LRU correct?

Two myths here:

1. The 'old' docs (V6, V7.0??) said that small tables were 4 blocks or lesser (or was it 5 blocks?). This carried over into the later versions
2. The KEEP and RECYCLE Pools aren't really special in any sense: the buffer algorithms are the same for these as for the DEFAULT pool - it is just that these pools are named so that tables can be defaulted to load into any one of them. By using this feature judiciously, you might be able to avoid the problems of unneccesary buffering during FTS on tables smaller than the threshhold without actually changing the undocumented parameter.

Jonathan: Awaiting your expert comments on this new twist :)

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

What would you see if you were allowed to look back at your life at the end of your journey in this earth?

• The opinions and statements above are entirely my own and not those of my employer or clients **

> -----Original Message-----
> From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk]
> Sent: Thursday, January 02, 2003 3:44 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Buffer Pool Testing
>
>
>
> 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
> >
>
>
> --
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.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).