Invisible Indexes December 11, 2007
Posted by Richard Foote in 11g, Index Access Path, Invisible Indexes, Oracle Cost Based Optimizer, Oracle Indexes, Performance Tuning.trackback
New in 11g are “Invisible Indexes”, which are basically indexes that exist and are maintained by Oracle but are “invisible” to the CBO. Specific sessions can be set to see these invisible indexes as necessary.
Potentially useful if one has a problematic (and very large) index causing performance issues that you want to make invisible until the specific issue is addressed without the expensive of having to drop and latter recreate the index. Also useful if you want to introduce a new index but want it to be invisible until it’s been given a workout first in a specific “test” session.
Here’s a bit of a demo: Invisible Indexes
Richard Foote's Oracle Blog 
Richard did you try dbms_stats.gather_table_stats with cascade option after making an index invisable? do you also experience an error 904 invalid identifier error 🙂
LikeLike
Yes I did without hitting the error. I tried a number of variations without success (or should I say with success, you know what I mean).
This was running 11g on Windows XP. I’m planning on installing and running 11g on AIX in the coming days.
LikeLike
I had a post about invisible indexes here: http://oracletoday.blogspot.com/2007/08/invisible-indexes-in-11g.html
Maybe you were able to gather stats because OPTIMIZER_USE_INVISIBLE_INDEXES was set to true.
LikeLike
Hi Yas
No, OPTIMIZER_USE_INVISIBLE_INDEXES was set to false.
The full command I use was basically as in the demo with a few variations.
I’ll try again tomorrow to see if I can produce the error with the defaults you used.
LikeLike
[oracle@tcellhost ~]$ uname -a
Linux tcellhost 2.6.9-42.0.0.0.1.ELhugemem #1 SMP Sun Oct 15 14:06:18 PDT 2006 i686 i686 i386 GNU/Linux
[oracle@tcellhost ~]$ sqlplus hr/hr
SQL*Plus: Release 11.1.0.6.0 – Production on Wed Dec 12 15:03:23 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show rel
release 1101000600
SQL> show parameter invisible
NAME TYPE VALUE
———————————— ———– ——————————
optimizer_use_invisible_indexes boolean FALSE
SQL> create table tab as select * from all_users;
Table created.
SQL> create index nui_tab_username on tab(username) invisible;
Index created.
SQL> exec dbms_stats.gather_table_stats(user, ‘tab’, cascade => TRUE);
BEGIN dbms_stats.gather_table_stats(user, ‘tab’, cascade => TRUE); END;
*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at “SYS.DBMS_STATS”, line 17806
ORA-06512: at “SYS.DBMS_STATS”, line 17827
ORA-06512: at line 1
SQL> alter session set optimizer_use_invisible_indexes=true ;
Session altered.
SQL> exec dbms_stats.gather_table_stats(user, ‘tab’, cascade => TRUE);
PL/SQL procedure successfully completed.
LikeLike
Tonguc,
I’ve just re-run your test, and get the same ORA-00904.
The biggest difference between your test and Richard’s demo is that he doesn’t collect statistics while the index is invisible.
The problem lies in the query that Oracle uses to collect index stats – it uses a hint to scan the index and a special function (sys_op_lbid())to generate leaf_block identifiers. But the hint can’t work because the index is invisible ! So the function causes a failure reporting error 904.
Regards
Jonathan Lewis
LikeLike
Yes, I went through my test cases and the index was visible each and every time I collected statistics. Making it invisible replicated the error.
Thanks for the input, much appreciated !!
LikeLike
Hi,
The error is caused by oracle bug 6344547.
LikeLike
Fidel I didn’t know that this was already issued as a bug, thank you for the bug#. This bug will crash atomized statistics gathering jobs if this new feature is used after 11g migration.
Thank you Jonathan for the information on function sys_op_lbid().
LikeLike
Hi Richard,
Just attended you Index Internals Seminar in Belgium. Very happy with it – thanks!
There is a blog entry by Christian Antognini (author of Troubleshooting Oracle Performance) suggesting that invisible indexes aren’t always invisible – Oracle may decide to use them in order to avoid locking issues for example (even if they are (supposedly) invisible). See http://antognini.ch/2008/10/invisible-indexes-and-locks/ for details.
LikeLike
Hi Michael
Glad you enjoyed it. Unfortunately, I got a bad cold just as I left Brussels and my voice barely made it in Helsinki !! Thankfully I have a few days to recover before the next seminar here in Dusseldorf.
Indeed, sometimes invisible indexes are not necessarily invisible when Oracle knows they’re being used for good not harm. Remember, the main reason we would want to make an index invisible is when Oracle is using an index inappropriately, causing resource overheads that is bringing performance down to it’s knees.
If an index can prevent an unecessary lock, then it makes sense to do so. As I discuss in the seminar, indexes on FKs can be critical if you delete/update parent keys.
LikeLike
Hello richard.
I am Mack, a brain damage attorney working from california. I am seeking an help in oracle querying…
have a table and i m using this query for that and i m getting the following errors
delete from usermaster
where empno=100;
ERROR at line 1:
ORA-01000: maximum open cursors exceeded
or if i do like this..
update usermaster
set username=’satya’ where empno=100;
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
What does this mean..
This usermaster table has got no foreign keys under that..
If u have any solution let me know plz i am learning…
thank you very much
LikeLike
Hi Mack
The problem is that your session has opened more than the allowable 100 cursors in your session. This is often an indication that the application is not doing the right thing and closing cursors once it’s finished with them.
The quick fix is that You can increase the number of cursors per session by uping the OPEN_CURSORS database parameter but the better course of action would be to find out why you’re using 100 concurrent cursors and whether or not they’re all actually needed.
LikeLike
[…] based optimizer regardless of whether the column is invisible or not. So don’t be confused by an invisible index with an index on an invisible column, they’re two entirely different […]
LikeLike
[…] will only create new indexes as INVISIBLE indexes, which are not considered by default by the CBO. The intent here is that the DBA can […]
LikeLike