Re: Unnecessary table scans

Hi,
Our data is uniformly distributed so we are not using histograms. Perhaps we should be using them anyway ?
I'm told by our DBA's that the parameters are okay although I haven't got the knowledge to prove this.

The values of the parameters you mentioned are db_file_multiblock_read_count = 8

sort_area_size=1048576
sort_area_retained_size=0
hash_area_size=20971520
hash_multiblock_io_count =0

db_block_size=8192

Hope this helps.
Steve Grant

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:986375417.125.1.nnrp-01.9e984b29_at_news.demon.co.uk...
>
> How many of the columns need histograms, and
> are any of these missing ?
>
> How realistic are your values for init.or parameters
> that affect I/O cost estimates, e.g. db_file_multiblock_read_count,
> sort_area_size, sort_area_retained_size, hash_area_size,
> hash_io_multiblock_count ?
>
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
> Publishers: Addison-Wesley
>
> Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>
>
>
> Steve Grant wrote in message ...
> >Hi,
> >Has anyone come across this before and is it a known problem or a
> >configuration issue ? We are running a datamart on 8.1.6 on AIX. All our
> >fact tables have a composite primary key and bitmap indexes on the
 indidual
> >columns of the primary keys. Statistics are adequate and up-to-date.
> >A lot of our queries are however defaulting to a full table scan which is
> >leading to long query times.
> >
> >An example of of timings would be
> >
> >A - Run query with no hints
> > Time to run - 6 mins +
> > Cost - 13736
> >
> >B - Run query forcing it to use composite index.
> > Time to run - 1 min +
> > Cost - 35558
> >
> >C - Run query forcing it to use bitmap indexes.
> > Time to run - 10 secs
> > Cost - 15478
> >
> >Any views or insights to this would be welcome.
> >Thanks
> >
> >Steve
> >
> >
> >
> >
> >
> >
>
>
Received on Wed Apr 04 2001 - 09:29:38 CDT