How Does An Execution Plan Suddenly Change When The Statistics (And Everything Else) Remains The Same ? (In Limbo)

Richard Foote's Oracle Blog

I’ve slipped this post in as there have been a number of discussions recently on how execution plans have changed while nothing else appears to have changed in the database. How can an execution plan suddenly change when no one has made any changes to the database ?
By no changes, it means that there have been no alterations to any segments, no new indexes have been added, no changes associated  bind peeking (indeed, there may not even be any bind variables), no parameters changes, no new patches or upgrades, no new outlines or profiles, no new system stats and perhaps most prevalent of all, no changes to any CBO statistics.
The DBA hasn’t touched a thing and yet suddenly, for no apparent reason, execution plans suddenly change and (say) an inappropriate index is suddenly used and causes performance degradation.
How can this be possible ?
There are…

View original post ещё 1 271 слово

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 слово