Please, oh, please will you be kind enough to post the query and the table
sizes so someone can comment on why you are observing what you are
observing.
Ashish
"achkar" <kachkar_at_tablimited.com.au> wrote in message
news:acb78eab.0203052059.642aeabe_at_posting.google.com...
> Hi All and thanks for your input ,
>
> This is what I did ,
> after analyzeing the 3 tables used in my query ( analyze table x,y,z
> estimate statistics sample 30 percent ) , deleting the statistics of
> the index I created or computing it , does not change the cost of the
> optimiser , and that index is not used in the execution plan and the
> optimzer is doing a full table sacn ,even I compute statistics for my
> index ( my tables are big , about 7000000 rows each )
>
> now listen to this ,
>
> I created a histogram on the column used in my index and the cost has
> changed from 61169 ( 24
> min ) to 12792 ( 17 min ) , both cases , the optimiser is not using
> the my index and still doing full table scan but it is faster , now
> whet is the relation between the histogram and the optimizer , keep
> in mind the optimizer is NOT using the index .
>
> also listen to this :
>
> I did this
> alter session set optimizer_index_cost_adj = 5 ;
> alter session set optimizer_index_caching = 95 ;
> alter session set optimizer_max_permutations = 1000 ;
>
> and the execution plan says the cost is 2422 ( wow ) I thought my
> query will fly but guess what it took 33 min to run , I dont get it.
>
>
> Reg,
> Achkar
>
>
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:<1015230286.9058.0.nnrp-07.9e984b29_at_news.demon.co.uk>...
> > The purpose of using the analyze command
> > (deprecated from 8.1.7 in favour of the
> > dbms_stats package) is to tell the database
> > engine as much as you can about the database.
> >
> > However, there are lots of either factors involved
> > in telling the truth about your database. This
> > includes, but is not restricted to, things like:
> > column X is never null
> >
> > column Y holds values unique across the table
> >
> > I can read 128 sequential blocks off a disc in
> > no more time than it takes to read just one block.
> >
> > I have half a GB of memory available for each
> > sort/merge join that I do.
> >
> > User X is allowed no more than 10% of the CPU
> > if the CPU is stressed.
> >
> >
> > If you mislead Oracle on ANY of this information,
> > you can find that rule based optimisation gets a
> > result faster than cost-based optimisation - especially
> > when incorrect details for points 3 and 4 are supplied
> > and persuade Oracle to use tablescans instead of
> > available indexes.
> >
> >
> > --
> > Jonathan Lewis
> > http://www.jlcomp.demon.co.uk
> >
> > Now running 3-day intensive seminars
> > http://www.jlcomp.demon.co.uk/seminar.html
> >
> > Host to The Co-Operative Oracle Users' FAQ
> > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> >
> > Author of:
> > Practical Oracle 8i: Building Efficient Databases
> >
> >
> > godmann wrote in message
<95cd51c.0203032148.4e6c9df5_at_posting.google.com>...
> > >Howard,
> > >
> > > How do you revert back says analyze .. compute statistics is bad? If
that
> > >is the case what is the point of analyzing some tables or indexes with
a
> > view
> > >to provide better stats. Oracle may not use it at all!
> > >
> > > I always thought that at all times, I should do analyze to make it
run
> > >faster!
> > >
> > >Allan W. Tham
> > >Oracle DBA
> > >
Received on Wed Mar 06 2002 - 08:51:34 CST
