Re: indexing

From: Taral Desai <taral.desai_at_gmail.com>
Date: Tue, 19 Feb 2013 19:42:37 -0600
Message-ID: <CAO4+9HWHTsyRKw5ebaRte6tyAYYK-EQ0C28ukukbp4UDsL4yBw_at_mail.gmail.com>

As other people told. Sometime monitoring can give false alarm http://desaitaral.wordpress.com/2010/10/20/oracle-index-monitoring/

On Tue, Feb 19, 2013 at 3:19 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk
> wrote:

>
> ----- Original Message -----
> From: "Wolfgang Breitling" <breitliw_at_centrexcc.com>
> To: "Tim Gorman" <tim_at_evdbt.com>; "Zelli, Brian"
> <Brian.Zelli_at_RoswellPark.org>
> Cc: <oracle-l_at_freelists.org>
> Sent: Tuesday, February 19, 2013 7:32 PM
> Subject: Re: indexing
>
>
> |However, an index may be used by the optimizer during hard parsing even if
> it does not get used in the plan eventually. I am sure it was Jonathan
> Lewis who demonstrated this. An index may be used to get a more accurate
> cardinality estimate for a row source but not any further. This does not
> get recorded in v$object_usage but if you take that index away - because it
> is "unused" - the cardinality estimate changes and with it potentially the
> access path.
>
>
> Correct, but fortunately this "sanity check" currently applies (for no
> obvious reason I can think of) only to unique indexes, and unique indexes
> are less likely to be dropped casually.
> Other logical problems with index monitoring, though:
>
> a) Oracle may not be using an index it ought to be using
> b) Oracle may be using an index it ought not to be using
>
> I think Mark Farnhan has already pointed out the two opposite extremes of
> the timing problem.
> Basically index monitoring was (in my opinion) a pointless exercise coded
> up as an easy add-on in response to popular demand.
>
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com/all-postings
>
> Author: Oracle Core (Apress 2011)
> http://www.apress.com/9781430239543
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 

Thanks & Regards,
Taral Desai


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

Received on Wed Feb 20 2013 - 02:42:37 CET