RE: Buffer pools and partitioning

Hi Mark,

The buffer pool specification is only used during a cache miss to determine which buffer pool the block will be placed in. Thereafter, it is at the mercy of the aging algorithm, which is a variation of the LRU theme based on touch counts, delays and so on with some sensitivity to block class. The persistence of your other blocks in the KEEP buffer pool will be contingent on whether the pattern and number of touches on each buffer is sufficient to keep them hot. There is a hidden parameter that can be used to increase the amount of the KEEP buffer pool that is treated as hot, and that may be worth adjusting it the wrong things begin to age out. There are also lots of other hidden knobs for tuning this stuff, but I expect that everything will work exactly as you would expect without needing to do that.

Regards,
Steve Adams

http://www.ixora.com.au/
http://www.oreilly.com/catalog/orinternals/
http://www.christianity.net.au/


-----Original Message-----
From:	mteehan_at_erggroup.com [SMTP:mteehan_at_erggroup.com]
Sent:	Wednesday, June 28, 2000 7:14 PM
To:	Multiple recipients of list ORACLE-L
Subject:	Buffer pools and partitioning

I have a very large transaction table (hundreds of millions of rows) which has a
 single index.
I need to perform fast inserts to this table (2000 - 2500/sec) and I can allocate approx 1GB RAM from the KEEP pool to the index blocks. After a few days, this index grows beyond 1GB of blocks, and it starts to age
required blocks from other objects out of KEEP. This is how I want to fix it :
- Add a new column to the table, type DATE, default SYSDATE

• All apps nominate columns when selecting, so it will NEVER be selected
• Partition by range on this column with each day in a new partition.
• Make its index local
• ALTER the index segment for todays partition BUFFER_POOL KEEP
• At rollover EOD, create a new partition, alter yesterdays index partition RECYCLE, and mark the new index partition KEEP.

My Question is : will oracle age yesterdays index segments out when it requires
blocks in the KEEP pool ;
effectively reusing yesterdays index blocks to build today's index? Does Oracle
check the current storage parameters
for an object before it decides whether or not to age one of its block out of
cache - i.e. does altering an object from buffer_pool  KEEP to buffer_pool recycle have any effect when the block is already in the
KEEP pool?

The other objects in KEEP dont roll over, and must not be aged out under any
circumstances, but I need to have current index blocks in cache to perform super-fast inserts.

Can anyone comment?

Mark Teehan
Perth/Australia

• ERG Group -------------------------- The contents of this email and any attachments are confidential and may only be read by the intended recipient.

  ---

--
Author:
  INET: mteehan_at_erggroup.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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