Re: searching for records with skewed data values (skewed data changes rapidly)

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Thu, 19 Aug 2004 06:25:56 -0700
Message-ID: <1092922004.615882@yasure>

Matt wrote:

> Daniel,
>
> I agree with you - the partitioning option is too expensive and this
> is a regularly updated table. I would guess that it is not suitable
> for a bitmap - but I would have to test to be sure. In any case, I
> think that this is a simpler issue relating to histogram gathering.
>
>

>>>The question I have for Matt is simple ... why do you care? What is

>
> the
>

>>>issue? And how do you know the issue exists?

>
>
> I care because I can see that the server is doing lots of full scans
> (I see the reads in performance monitor). A statspack report
> highlights that this statement carries out 30-50% of the I/O
> (depending on time of day). When the query does an FTS it takes 8-10
> seconds, with an index hint it takes 0.1 seconds. So, I know that the
> index is the way to go - I just can't seem to get the optimizer to
> choose the index.
>
> I think that the issue is that my dbms_stats call is incorrect and not
> generating sufficient buckets "for columns status size x" does not
> seem to create x buckets. Either there is something wrong with the
> call or I misunderstand how histograms work.
>
> I'm away from a test machine for a few days, so can'y say much more at
> the moment.
>
> Hey, if you can reference any good detailed material on histograms - I
> will read it (I read the tahiti doco, need something with more real
> world examples).
>
> I appreciate the responses.
>
> Mat.

Search th archives of this group. I believe Jonathan Lewis has posted some very good examples with the past year.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Thu Aug 19 2004 - 08:25:56 CDT