Re: Query Optimization question

vk02720_at_my-deja.com wrote:
>
> In article <39A27459.703_at_yahoo.com>,
> connor_mcdonald_at_yahoo.com wrote:
> > vk02720_at_my-deja.com wrote:
> > >
> > > What is a good way to analyze slow running queries ?
> > > Does the order of table names after 'from' or clauses after 'where'
> > > matter ? If so are there anu rules of thumb to for from and where ?
> > >
> > > DBMS : Oracel 7.3.4 / 8.1.6
> > > OS: AIX 4.2.x 4.3.x
> > >
> > > Any answers or pointers to info on the web appreciated.
> > >
> > > TIA
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> > The answer as always is... depends
> >
> > If you are using rule based optimiser, the query is evaluated based on
> > its syntax - thus table order etc is important. If you are using cost
> > based optimiser, then Oracle tries to make an informed decision about
> > the best way of going about things...
> >
> > The ANALYZE command is used to keep these stats up to date.
> >
> > HTH
> > --
> > ===========================================
> > Connor McDonald
> > http://www.oracledba.co.uk
> >
> > We are born naked, wet and hungry...then things get worse
> >
>
> I am not explicitly using any particluar optimizer. What is the
> difference in rule based or cost based optimizer ? Does Oracle select
> an optimizer by default. If so how do I specify one ?
> I read about the autotrace option and EXPLAIN PLAN a little bit.
> On my site the PLAN_TABLE does not exist and I do not control its
> creation. Is it always a good idea for it to exist ? Does this affect
> the performance of queries ?
> Any general rules for the order of table names and where clauses in a
> query ?
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

PLAN_TABLE will have no affect on your perform - its simply a place to store information when you run explain plain (or use autotrace).

Check v$parameter for the optimizer mode - if its "choose" (the default), then this means that if statistics can be found then use the cost optimizer, otherwise the rule optimizer will be used...

Oracle generally recommend the cost optimizer (as do I), which also implies keeping those stats up to date with judicious use of the analyze command.

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse