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

  • From: "Robert Freeman" <robertgfreeman@xxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 21 Aug 2007 09:51:27 -0600

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!
Sig V1.2

-----Original Message-----
From: Hemant K Chitale [mailto:hkchital@xxxxxxxxxxxxxx]
Sent: Tuesday, August 21, 2007 9:12 AM
To: robertgfreeman@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Oracle 11g - Invisible Indexes -- Automated SQL Tuning ?

If Oracle's  "Automatic Tuning" were to "decide to create an index and test
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


Other related posts: