Visible Invisible Indexes (The Invisible Band) November 20, 2008

After spending the last few weeks traveling throughout Europe, it’s about time I wrote a new post on the Blog !!

I’ve previously posted a simple demo on Invisible Indexes, a new 11g feature that allows you to quickly make an index “invisible” to the CBO.

Recently, a comment on the above post by Michael Sorensen mentions a nice post by Christian Antognini in which he demonstrates how so-called Invisible Indexes on Foreign Keys can still be used by Oracle to prevent locking and performance related issues when delete/update operations are performed on the parent records.

I previously posted how Index Monitoring on FK indexes doesn’t pick up the fact so-called “Unused Indexes” can actually be “used” by Oracle for exactly this reason, quickly determining whether there’s an existing FK record that needs to prevent the update or delete operation on the parent record from proceeding.

It should therefore come as no surprise that Invisible Indexes might not be so invisible after all when it comes to the CBO potentially using the index statistics of an Invisible Index to determine the correct cardinality of a query.

This demo on Invisible Indexes and Index Statistics clearly shows that Oracle can still use the statistics of an index, even after they’ve been made invisible, to determine a more accurate cardinality for a query.

This means you need to be very careful how you use and apply Invisible Indexes and have a clear understanding on how Invisible Indexes are implemented by Oracle and the purpose for their use.

As I explain in my Indexing Seminar, Invisible Indexes are primarily intended to be applied when an application has suddenly and inexplicably started to use an index inappropriately and the database has significant performance issues as a result. The scenario is such that the performance of the database or application is so bad, it has caused the drain of resources to such an extent that normal business functions can’t continue and drastic action is required to address the issue.

The drastic action is to make the problematic index invisible so that the CBO can no longer see and use the index within an execution plan. This is drastic because it means other queries that could currently be using the index appropriately will also be prevented from using the index but performance is so bad that such a consequence can’t make matters any worse. By making the index invisible at least the resource drain can be prevented and hopefully most of the other business activities can now continue within the database.

By making the index invisible rather than simply dropping it, it means some sessions can investigate what the problem might be that’s causing the inappropriate use of the index (by altering sessions to OPTIMIZER_USE_INVISIBLE_INDEXES = true). Once the issue has been solved and addressed, the index can quickly be made visible again, rather than having to recreate the index from scratch.

Invisible indexes could prove to be a handy option in such scenarios.

Invisible indexes are also often viewed as being a useful method of determining whether it’s safe to drop an existing index that may not actually be used by an application. An unused index is a waste of both storage and resources in maintaining the thing but it’s often difficult to determine what the ramifications might be if an index were to be dropped. By making an index invisible, it may be possible to “test the waters” so to speak and determine how an application may suddenly behave without the index.

However, making an index invisible may not necessarily provide an accurate determination of the ramifications of dropping such an index because as has already been demonstrated, there are a number of important and key areas in which invisible indexes are still being used by Oracle. Dropping such an index is not the same as simply making an index invisible and may result in the application suddenly behaving in an unexpected manner that could cause significant performance issues.

Invisible indexes are not entirely invisible if you see what I mean ;)

Share this:

Like this:

Related

To me invisible indexes were sold by Oracle personnel as a helpful tuning means: “You create the index invisible, test it in your session where you enable access to invisible indexes without affecting regular business and then either make it visible or drop it if it isn’t helpful.”

I am not sure whether you mentioned this other obvious area where invisible indexes cannot really be invisible: for modifications of the base table it cannot be invisible since otherwise it would simply go from invisible to invalid. :-) And also in this case they can have an impact on regular operations, e.g. if the added IO cannot be done via buffer cache but makes physical IO necessary etc.

I guess the bit I wanted to highlight is that invisble indexes can affect regular business processes because in a number of ways, they’re not entirely invisible after all. So long as that’s understood, they can hopefully be used appropriately.

Thanks for the bug reference. Unfortunately, it’s not one we humble Oracle customers can look at. When addressed, it would certainly make testing and seeing system behaviour without the index more predicable.

Sorry about the visibility, but the gist is that the costing should be the same for a nonexistent index and an invisible one, which is why it is broken. I’ll keep an eye on it and try and follow up here when a fix is produced.

[…] Page 397: States, “There is another use case for the invisible indexes. These indexes are useful to reduce the risk while dropping unused indexes… From Oracle Database version 11g onwards, you can mark the index as invisible, wait for few weeks, and then drop the index if no process is affected with less risk.” It is important that the book states “less risk” and not that there is no risk – it would have been helpful if the book discussed what risks remain (but again this might exceed the intended scope of the book). Just because an index is not used for a couple of weeks does not mean that the index will not be used during month-end closing, year-end processing, or some other infrequently occurring activity. Invisible indexes on foreign key columns may still be used to prevent table locking problems on the child table when the parent’s primary key columns are updated. Additionally, the statistics from the invisible indexes may be used by the optimizer to more accurately determine cardinality estimates (reference reference2). […]

[…] Recipe 2-11 claims to show a method of adding an index to a database such that adding that index will not affect third party vendor applications. The recipe provides a SQL statement that sets the OPTIMIZER_USE_INVISIBLE_INDEXES parameter to TRUE at the SYSTEM level (ALTER SYSTEM), and then states, “use a hint to tell the optimizer that the index exists”. The hint is entirely unnecessary, changing the parameter at the SYSTEM scope is sufficient to allow all sessions to use the invisible index, thus potentially affecting the third part vendor application. Creating a UNIQUE invisible index will still affect the third party application on inserts, updates, and deletes regardless of the setting of the OPTIMIZER_USE_INVISIBLE_INDEXES parameter. Regardless of the setting of the parameter, the invisible indexes may still be used by the optimizer for cardinality calculations (thus adding the index could change execution plans, even if the session cannot use the index), and to prevent TM enqueues on foreign key columns. The recipe also states that one of the uses of invisible indexes is as a test to determine whether or not it is safe to later drop the index – however, for the reason just mentioned, this is not a complete/safe test to determine whether or not an index may be safely dropped. (pages 70-71 reference reference2) […]