Unused Indexes and Scalability

I was asked an interesting question yesterday. The gist of the question is: Can eliminating unused indexes reduce latching in the database? The answer is likely “yes” in most production environments. Even though an index may not be used in an access path for any SQL in the database, the index is still maintained (in most cases) when the indexed columns of a table are modified. Even if we reduce latching owed to unused indexes, how does this improve the scalability of an application?

Let’s assume you have an unused B-tree index on a table. If an insert, delete or update affects the indexed columns for this index, then more times than not this index will need to be modified to accommodate the change. If this index is modified Oracle must find the relevant index block(s) to change. To do this it must read the root node of the index segment, probably a branch block and lastly the leaf block(s). All of this activity requires Oracle to hash the DBA (data block address) for each visited index block to determine if the block is already in the buffer cache.

Which latches are required for this investigation? Well, the DBA is hashed to determine which cache buffers chain on which the block “should” reside if it is in the buffer cache. Once the applicable cache buffers chain latch is acquired Oracle searches this linked list (cache buffers chain) , protected by that latch, hoping to find the current version of the leaf block (let’s not rule out a branch block change as well). If it is not found on the relevant cache buffers chain, then the block is not in the buffer cache. Therefore, the block must be read from “disk” and placed into the buffer cache. Oracle needs a free buffer to write the block into. This is accomplished by searching the applicable cache buffers LRU chain for a free buffer. The cache buffers LRU chain structure is protected by a cache buffers LRU chain latch. Once this latch is acquired and the block is written to the buffer cache, the block can be modified.

Of course, modifying the block requires Oracle to first allocate space in the log buffer to accommodate our change. Oracle will need to acquire the redo allocation latch to allocate this space in the log buffer. Then the redo change vectors, constructed in the PGA, are written onto the log buffer using a redo copy latch.

To maintain this unused index Oracle has performed unnecessary logical I/O (root, branch and leaf index blocks as well as undo segment header and undo segment blocks) and unnecessary latching. Moreover, unnecessary redo has been generated along the way.

Latching plays a major role in the ability of an application to scale, as concurrency is impacted via latch contention. Latches serialize operations on structures to ensure the integrity of that structure. So they are our friends, but they can easily become areas of frequent contention. The less latching Oracle does the greater the probability a process will succeed in getting the latch it needs on its first attempt to acquire it. Concurrency and, thusly, scalability are benefited. Latch contention can result in a process spinning (CPU intensive) on latches and sleeping (response time inhibitor) until the latch is available. Processes are not managed by a queue for latch access. That is, a process is not guaranteed a position in a queue based on the point in time it attempted to acquire the latch. It either acquires it immediately or it does not.

So, before we go adding indexes we think might be useful, we might want to consider the inherent maintenance overhead against the scope of any performance gain. If scalability is deemed paramount, each database or SQL change should be made with a high-level understanding of the resultant latching fresh in our minds.