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

Xho,

>>Is the nature of the skew permanent (a few "A" and many "B", >>all the time), or does the skew oscillate (beginning of the month has
>>many "A", few "B", middel of month has few "A", many "B")?

Records arrive in rapid succession and are pushed through the various statuses. In the day there are typically 1% of pending records that are picked up (hopefully via the index) before some processing occurs and the status is then changed. So, the status changes probably happen every few seconds (depending on how fast the query is!)

>>This doesn't make sense to me, unless the skewness oscillates wildly. The
>>nature of statistics is, well, statistical. It doesn't matter whether any
>>particular record is in some particular state at the time statistics are
>>gathered.

At the end of the day no more records arrive and the automatic processing of records ensure that all pending records are processed and their state changed. Thus, when stats are generated they don't (necessarily) include the pending state.

>>-1) Verify that you are in fact collecting histogram data, rather than just
>>table statistics without histograms.

I think that this is the problem (see earlier post from me to Daniel). However, I am not clear how many histograms I should find in user_histograms when my dbms_stats call is defined correctly. I think some further reading and testing is required.

>>0) Not use bind variables for the status part of the where clause.

Agreed. And the statement uses literals (to take advantage of histograms)

cheers for the respnse.

Mat.

ctcgag_at_hotmail.com wrote in message news:<20040818132621.276$sq_at_newsreader.com>...
> mathewbutler_at_yahoo.com (Matt) wrote:
> > 9.2.0.4.0 Win 2K SP5
> >
> > What options are there to take when a column may take one a 5 values
> > to indicate a records state (states are skewed).
>
> Is the nature of the skew permanent (a few "A" and many "B",
> all the time), or does the skew oscillate (beginning of the month has
> many "A", few "B", middel of month has few "A", many "B")?
>
> > The state gets
> > updated so that some states exist for short periods of time, so short
> > that they are sometimes not caught in statistics
>
> This doesn't make sense to me, unless the skewness oscillates wildly. The
> nature of statistics is, well, statistical. It doesn't matter whether any
> particular record is in some particular state at the time statistics are
> gathered.
>
> > causing expensive FTS
> > rather than index accesses.
>
> This seems even more unlikely. If a status is so rare that it gets missed
> altogether, this should cause the CBO to error (if at all) in the direction
> of index-based lookups, rather than in the direction of FTS. No?
>
> > 1) I can hint the query
> > 2) I can use plan stability
> > 3) Anything else....?
>
> -1) Verify that you are in fact collecting histogram data, rather than just
> table statistics without histograms.
>
> 0) Not use bind variables for the status part of the where clause.
>
> Xho
Received on Thu Aug 19 2004 - 05:04:10 CDT