Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Oracle 11g - Invisible Indexes -- Automated SQL Tuning ?

RE: Oracle 11g - Invisible Indexes -- Automated SQL Tuning ?

From: Job Miller <>
Date: Tue, 21 Aug 2007 10:54:04 -0700 (PDT)
Message-ID: <>

the sql tuning wizards indicate that the comprehensive analysis can be intensive, hence the ability to package up sql tuning sets and ship them to non-production dbs for identification/testing of fixes.    

  I have always presumed, without any testing/inspection to validate it that the identification of whether an index is of value can be done purely by an in-memory representation of that index and its associated statistics if it were to exist and their effect on the costing of query. so that while it doesn't have to physically exist and be invisible, the work required to understand how that indexes stats would shape up to better understand the end effect on cost have to be done, which could create lots of extra work for the system, although it would be short of the amount of work required to actually physically create it and mark it invisible.    

  could be wildly wrong, but that was my uneducated opinion. if someone knows otherwise as it relates to comprensive tuning in 10g/11g, I'd love to know for sure what's going on behind the scenes.    


Robert Freeman <> wrote:   Ahhhh, that is the question. Kind of like what is the impact of the SQL query result testing that goes on with Automatic SQL Tuning in 11g to determine the benefit of a given profile. It all happens in the background without you knowing it. I would suspect that *IF* this were to be a feature, you could disable it. Perhaps they will also introduce a method of building indexes that reduces IO flash (like the RMAN duration parameter). It's all speculation of course... Don't slam me just because I'm theorizing on what might be around the corner... :-)


Robert G. Freeman
Oracle Consultant/DBA/Author
Principal Engineer/Team Manager
The Church of Jesus Christ of Latter-Day Saints Father of Five, Husband of One,
Author of various geeky computer titles
from Osborne/McGraw Hill (Oracle Press)
Oracle Database 11g New Features Now Available for Pre-sales on! BLOG: Sig V1.2

-----Original Message-----
From: Hemant K Chitale [] Sent: Tuesday, August 21, 2007 9:12 AM
To:; Subject: Re: Oracle 11g - Invisible Indexes -- Automated SQL Tuning ?

If Oracle's "Automatic Tuning" were to "decide to create an index and test it"
at "any" time {"any" meaning : when the DBA is not prepared for it , in the middle of the month-end job runs when the database is busiest and Oracle wants to do it's utmost to "auto tune" your database}, what is the impact of the sudden burst of I/O to actually create the index (the "invisibility" comes only after having really created the whole index) ?!


At 03:09 AM Tuesday, Robert Freeman wrote:
>I rather suspect where Oracle is going with the
>invisible index is associated with the automated SQL
>tuning. Say, for example, Oracle reviews SQL that has
>run and determines that an index would benefit the
>execution plan. Why not create it as an invisible
>index (thus, you don't impact other execution plans),
>test it and quantify the performance and then, if you
>get say 3 or 4x performance, you make it visible.
>Automatic index tuning, perhaps a new feature of
>Oracle 11gR2. It just seems a natural outgrowth of
>automatic SQL tuning in 11g.

Hemant K Chitale

"First they ignore you, then they laugh at you, then they fight you, then you win" !"
Mohandas Gandhi Quotes


Choose the right car based on your needs.  Check out Yahoo! Autos new Car Finder tool.
Received on Tue Aug 21 2007 - 12:54:04 CDT

Original text of this message