Re: explain plan assistance needed

On Mon, 04 Jun 2007 10:03:09 -0700, pfa <peter_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