On 6 5 , 2 44 , sybra..._at_hccnet.nl wrote:
> On Mon, 04 Jun 2007 10:03:09 -0700, pfa <p..._at_falson.net> wrote:
> >I'm struggling to find information that will explain the results of my
> >explain plan. What I'm trying to do is write a verification routine
> >which shows the benfits of adding an index. From past experience it is
> >accepted that just creating an index isn't (necessarily) enough but an
> >analyze table ... compute statistics or a dbms_stats.gather... process
> >be run. But in my test process the explain is showing an index scan
> >without the stats and after the stats the only difference is the
> >output of the rows/bytes.
>
> >Can anybody shed some light on this or point me to a url with some
> >reading material that explains this?
>
> Oracle 10g will just estimate the stats (on every execution) if they
> lack in the dictionary (see your 'typical' keyword in the init.ora
> parameters you supplied).
> Also Oracle will know how much cheaper (or more expensive) a single
> block read is compared to a multiblock read. This information is being
> used to compare a FTS vs an index access.
> However, you would need to run this statement with event 10053 set to
> get a breakdown of optimiser decisions in a trace file
>
> --
> Sybrand Bakker
> Senior Oracle DBA
Received on Tue Jun 05 2007 - 00:53:45 CDT
