jump to navigation

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

About these ads

Comments»

1. H.Tonguç Yılmaz - December 11, 2007

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 :)

2. Richard Foote - December 12, 2007

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.

3. Yas - December 12, 2007

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.

4. Richard Foote - December 12, 2007

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.

5. H.Tonguç Yılmaz - December 12, 2007

[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.

6. Jonathan Lewis - December 12, 2007

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

7. Richard Foote - December 13, 2007

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 !!

8. Fidel - December 13, 2007

Hi,
The error is caused by oracle bug 6344547.

9. H.Tonguç Yılmaz - December 14, 2007

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().

10. Michael Garfield Sørensen - November 5, 2008

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.

11. Richard Foote - November 8, 2008

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.

12. Brain Injury Attorney - June 18, 2009

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

Richard Foote - June 19, 2009

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.

13. 12c Invisible Columns (The Invisible Man) | Richard Foote's Oracle Blog - November 19, 2013

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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,808 other followers

%d bloggers like this: