Home » RDBMS Server » Performance Tuning » Would making indexes "invisible" be the right approach for this? (11.2.0.3 / CentOS Linux)
Would making indexes "invisible" be the right approach for this? [message #610962] Wed, 26 March 2014 06:02 Go to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
OK, the background story:

I have an application where someone (about 15 years ago on Oracle 7.4 I believe) created about 30 (quite similar) indexes on a table. I have checked them with "monitor usage" and all of them seem to be used now and then. But some specific queries run 2-3 times as long now after the change to 11.2.0.3 as they did under 10.2.0.4. I have some idea how I could change some of the indexes to improve then, and even replace 4-5 of the original indexes with a single one.

Now the question:

For indexes we basically have the option to make them "disabled", "unusable" and "invisible". Are the following conclusions I drew from here correct?

Disabled is only a special case for function based indexes. (so that is not what I want).

Marking an index unusable would require me to rebuild it. (so that is not what I want).

Making it invisible on the other hand would only prevent the optimizer from using it, but it still be maintained in the background so that I could switch it on again when I find it is indeed needed for rarely used queries that I so far haven't encountered in tracing the database. (so that would be what I want)

Have I understood that correctly, and/or has anyone ever encountered any pitfalls while proceeding in that direction?

Thanks

Thomas.



Re: Would making indexes "invisible" be the right approach for this? [message #610965 is a reply to message #610962] Wed, 26 March 2014 06:17 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
I seem to recall that invisible indexes aren't that invisible. The take away was the optimizer considers the indexes on the table in the calculations before it checks for visibility - it can cause weirdness on edge cases of the optimizer permutations. It'll also use them for constraints.

However I doubt you'd fall prey to that because they are already there, visible and being considered.

I can't see issues in your plan, assuming these are stand alones and not enforcing constraints and so forth, just keep in mind that internally invisible != not there. It'll give you an idea from the end user space certainly but it isnt a cast iron guarantee that nothing will break if they are dropped - it is better than nothing if you have nowhere else to test.

[Updated on: Wed, 26 March 2014 06:19]

Report message to a moderator

Re: Would making indexes "invisible" be the right approach for this? [message #610982 is a reply to message #610965] Wed, 26 March 2014 08:01 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Thanks, then I will try that and see what happens to the Oracle plans and the "user experience" Wink
Re: Would making indexes "invisible" be the right approach for this? [message #612294 is a reply to message #610982] Tue, 15 April 2014 12:52 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Just a quick feedback about what happened.:

I set different sets of indexes to "invisible" for 2-3 days each and checked Server IO/CPU/Memory utilization during that time. I also did a little questionnaire for key users where they could rate the responsiveness of the application during those periods. From the traces I did I could also verify that the invisible indexes where indeed not used during the time they where invisible.

Doing that in different combinations I came up with six indexes to replace the original thirty that even give a slightly better user experience. So I kept those six and dropped the rest.

Re: Would making indexes "invisible" be the right approach for this? [message #612376 is a reply to message #610962] Thu, 17 April 2014 04:30 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
ThomasG wrote on Wed, 26 March 2014 16:32

Making it invisible on the other hand would only prevent the optimizer from using it, but it still be maintained in the background so that I could switch it on again when I find it is indeed needed for rarely used queries


I find your way indeed nice.

If you have made these indexes invisible, one good thing is that, you could just set OPTIMIZER_USE_INVISIBLE_INDEXES parameter to TRUE at session level for the rarely used queries, so the invisible indexes would be used by the optimizer for those queries and then set the OPTIMIZER_USE_INVISIBLE_INDEXES parameter back to false after the query is executed. This should not have any adverse affects to anything else.
Previous Topic: query optimization phase
Next Topic: Archive log issues
Goto Forum:
  


Current Time: Thu Mar 28 13:20:28 CDT 2024