Re: explain plan assistance needed

Ok, my lack of knowledge is shining through here :-)....

I turned off the auto-tune and set the stats level to BASIC. Still getting the IRS as opposed to FTS before gathering stats. I tried:

sqlplus system/<passwd>
...
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1' /
ALTER SESSION SET CURRENT_SCHEMA="SCOTT" /
...<rest of script>

However, I'm lost looking at the trace.

I realize that ideally you'd want to see the index being used but I'm actually trying to show that a gather_stats is needed at least on some occasions and I'm struggling to produce a simple example.

I also tried on 9.2.0.4.0 (again, I changed the statistics_level to 'BASIC') If there's a simple explanation of what I can change to get the results I'd appreciate it (e.g. init.ora settings perhaps).

If it's a matter of interpretting the resulting trace file to get the answers I need then I'll accept I have some reading to do.

On Jun 4, 11:44 am, 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 willexplainthe results of my
> >explainplan. 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 theexplainis 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 Mon Jun 04 2007 - 20:10:39 CDT