Reuse Of Empty Index Leaf Blocks (Free Four)

Richard Foote's Oracle Blog

A recent question by Stalin Subbiah has prompted me to write a quick post on the reuse of empty leaf blocks. In part, the question asked:

«Is there anyway I could monitor the effectiveness of empty blocks being reused from freelist of an index resulting from purge process that we are planning to start soon?»

I’ve previously discussed how Oracle can recycle index blocks that contain nothing but deleted index entries as such blocks are effectively added to the index freelist to be reused by subsequent index block splits. In my «Index Internals — Rebuilding The Truth» presentation, I mention a number of methods of how to see this reuse in operation, such as via block dumps, tree dumps and INDEX_STATS.

However, another simple method which I don’t think I’ve discussed here before is the use of the DBMS_SPACE package. So to help answer Stalin’s question, a simple…

View original post ещё 291 слово