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

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

RF

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 Amazon.com! BLOG: http://robertgfreeman.blogspot.com/ Sig V1.2

-----Original Message-----
From: Hemant K Chitale [mailto:hkchital_at_singnet.com.sg] Sent: Tuesday, August 21, 2007 9:12 AM
To: robertgfreeman_at_yahoo.com; oracle-l_at_freelists.org 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) ?!

Hemant

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

Hemant K Chitale
http://web.singnet.com.sg/~hkchital
and
http://hemantscribbles.blogspot.com
and
http://hemantoracledba.blogspot.com

"First they ignore you, then they laugh at you, then they fight you, then you win" !"
Mohandas Gandhi Quotes
: http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html

--
http://www.freelists.org/webpage/oracle-l