jump to navigation

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

Posted by Richard Foote in 11g, Invisible Indexes.
trackback

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.

I also previously discussed how the 11g CBO can now use index statistics to determine the appropriate cardinality of a query and how Index Monitoring doesn’t detect an index being “used” in this context as well.

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 😉

Comments»

1. Robert Klemme - November 25, 2008

Richard,

good to see you’re safely back!

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.

Kind regards

Like

Reply
2. Richard Foote - November 25, 2008

Thanks Robert

Indeed, they generate I/O, redo and consume resourses.

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.

Like

Reply
3. Greg Rahn - March 26, 2009

The optimizer using the statistics of an invisible index is not the expected behavior. See bug 8348392.

Like

Reply
Richard Foote - March 26, 2009

Hi Greg

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.

Like

Reply
4. Greg Rahn - March 28, 2009

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.

Like

Reply
5. Book Review: Pro Oracle SQL « Charles Hooper's Oracle Notes - January 17, 2011

[…] 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). […]

Like

Reply
6. Book Review: Oracle Database 11g Performance Tuning Recipes « Charles Hooper's Oracle Notes - September 11, 2011

[…] 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) […]

Like

Reply
7. Sokrates - May 13, 2013 
sokrates@11.2 > create table phantom ( c int );

Table created.

sokrates@11.2 > create unique index invisible_but_can_be_seen on phantom (c) invisible;

Index created.

sokrates@11.2 > insert into phantom values(0);

1 row created.

sokrates@11.2 > insert into phantom values(0);
insert into phantom values(0)
*
ERROR at line 1:
ORA-00001: unique constraint (SOKRATES.INVISIBLE_BUT_CAN_BE_SEEN) violated


sokrates@11.2 > drop table phantom purge;

Table dropped.

sokrates@11.2 > create table phantom ( c int, constraint cpk primary key (c) using index(create index invisible_but_can_be_used on phantom (c) invisible));

Table created.

sokrates@11.2 > insert into phantom values(0);

1 row created.

sokrates@11.2 > insert into phantom values(0);
insert into phantom values(0)
*
ERROR at line 1:
ORA-00001: unique constraint (SOKRATES.CPK) violated

Like

Reply
Richard Foote - May 15, 2013

Hi Sokrates

Yes, uniqueness is still enforced with invisible indexes. Invisible indexes are not really designed with uniqueness in mind (up to 11g that is).

Like

Reply

Leave a comment