Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> free buffers : ageing index partitions from cache

free buffers : ageing index partitions from cache

From: <mteehan_at_erggroup.com>
Date: Tue, 4 Jul 2000 13:22:33 +0800
Message-Id: <10547.111151@fatcity.com>


This is a questions for the low level experts out there. I have a large table, with a single index, partitioned by day, with 10-14 million rows inserted per day.
To maintain performance, I need to cache the index blocks (but not the table blocks). Index is in the KEEP pool, data is in the recycle pool. However, I can only keep index blocks for a single day in memory - so the index is partitioned locally. Fine so far.
I have been having problems getting yesterdays index blocks out of the KEEP pool to make space for todays index blocks : there are other objects in the KEEP pool that I cannot afford to have aged out by having 2 day's indexes in the cache.
I thought I could do this by altering the buffer_pool storage for the index partition - but this has no effect on buffer ageing. Interestingly, I found that rebuilding an index partition marks (almost) all of its buffer cache blocks from 'xcur' to 'free' - effectively ageing it out of cache.

My question is : will cache blocks marked 'free' always be chosen over 'xcur' blocks when space is required in the cache for new objects? (in my case the next day's index partition). If so then it fixes the problem of KEEP cache being blown by a rolling daily window, by allowing me to roll portions of my KEEP pool, and maintain other portions statically (this may seem like overkill but if you saw my performance requirements for this database you would understand). There is the overhead of having to rebuild the index partition each day, but I can live with that - I can do it online with 816.

Can anyone see any problems with this approach?

Regards
Mark Teehan
Perth/Australia

Received on Tue Jul 04 2000 - 00:22:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US