RE: Oracle 11g - Invisible Indexes -- Automated SQL Tuning ?
- From: Job Miller <jobmiller@xxxxxxxxx>
- To: robertgfreeman@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Tue, 21 Aug 2007 10:54:04 -0700 (PDT)
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.
job
Robert Freeman <robertgfreeman@xxxxxxxxx> 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... :-)
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@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
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
--
//www.freelists.org/webpage/oracle-l
---------------------------------
Choose the right car based on your needs. Check out Yahoo! Autos new Car
Finder tool.
Other related posts:
