Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> free buffers : ageing index partitions from cache
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