Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: analyze index and cost

Re: analyze index and cost

From: achkar <kachkar_at_tablimited.com.au>
Date: 5 Mar 2002 20:59:33 -0800
Message-ID: <acb78eab.0203052059.642aeabe@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 Tue Mar 05 2002 - 22:59:33 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US