Re: Unnecessary table scans

Jonathan,
Thankyou for your info. It has been very useful and at last we are getting somewhere with reducing our number of full table scans.

Steve

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:986395806.8069.0.nnrp-01.9e984b29_at_news.demon.co.uk...
>
> Steve,
>
> I've looked at the init.ora parameters you
> sent me in an offline post, and your comments
> that the data distribution is pretty uniform.
>
> The problem is your value for hash_area_size,
> which is set to 20 MB. This is not a particularly
> unreasonable size for a typical datamart system,
> but it is having an adverse effect where you have
> potential star_transformations such as these.
>
> I would guess that your very slow query
> is taking some sort of multiple hashing
> path through the data.
>
> Try
> alter session set hash_area_size = 4000000;
>
> and see if the fast path comes back and is more
> consistent. You might also like to look at
> bitmap_merge_area_size which defaults to 1M,
> perhaps increasing this to 2 to 4 MB (if the hash
> area change doesn't sort the problem).
>
> One silly question - with apologies -do you have
>
> star_transformation_enabled = true
>
> This is the parameter that allows Oracle
> to consider using multiple bitmap access
> paths in this fashion anyway.
>
>
> --
> 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 ...
> >SELECT
> > TBDN_STO_FIXED.STO_NO,
> > sum(TBFD_SLS.UNITS - TBFD_SLS.RTN_UNITS),
> > TBDN_SKU_FIXED.SHORT_SKU,
> > TBDN_STO_FIXED.STO_DES
> >FROM
> > TSDN_CAL_WK,
> > TBDN_STO_FIXED,
> > TBFD_SLS,
> > TBDN_SKU_FIXED
> >WHERE
> > ( TBDN_STO_FIXED.STO_ID=TBFD_SLS.STO_ID )
> > AND ( TBDN_SKU_FIXED.SKU_ID=TBFD_SLS.SKU_ID )
> > AND ( TBFD_SLS.WK_ID=TSDN_CAL_WK.WK_ID )
> > AND (
> > TSDN_CAL_WK.YR_NO = 2000
> > AND TSDN_CAL_WK.WK_SHORT IN (10,11)
> > AND TBDN_SKU_FIXED.SHORT_SKU = 1234567
> > )
> >GROUP BY
> > TBDN_STO_FIXED.STO_NO,
> > TBDN_SKU_FIXED.SHORT_SKU,
> > TBDN_STO_FIXED.STO_DES;
> >
> >What we have found is that if we have just the one value of
> >TSDN_CAL_WK.WK_SHORT, the bitmap indexes are used, but as soon as we have
> >more than one value, a tablescan is performed.
> >There are currently about 100 distinct values of this column.
> >
> >
>
>
Received on Thu Apr 05 2001 - 04:04:23 CDT