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: searching for records with skewed data values (skewed data changes rapidly)

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

From: Joel Garry <joel-garry_at_home.com>
Date: 18 Aug 2004 17:33:39 -0700
Message-ID: <91884734.0408181449.585ff017@posting.google.com>


ctcgag_at_hotmail.com wrote in message news:<20040818131047.706$3s_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).

Look at plan stability, you perhaps need to test for the proper stats for your situation then lock in the plan for that.

>
> 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 "B", many "A")?
>
> > 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?

I recently figured out a customer issue based on a bad index statistic. Using the index rather than the FTS that the CBO normally decided on correctly meant the difference between a minute and a half and not finishing overnight. Yes, the FTS is that much faster, deep in the plan, index range scan gets 300K rows v. table access full 1K rows. And this is stuff that isn't changing at all.

This was more difficult to figure out than it might sound in retrospect, as OAS10g was involved, giving a timeout error in the browser, but no clue that the query continued to run on the db. Multiple times as it turned out. Then when I come along and figure it must be some bad sql (since I looked at the plan, and hey, it's using indices), and try to make minor changes in the report by editing in the navigator, which can't save changes because it can't get a lock on the package object which is still running and pinned, but actually it does eventually because things keep running on the server even though erred out in the browser ... and how can you tell which session is real, since the OSUSER is apache and the USER is PORTAL_PUBLIC or PORTAL, and they're all running the same code? (partial answer: session start time)

jg

--
@home.com is bogus.
Kill 'em all and blame the network.
Received on Wed Aug 18 2004 - 19:33:39 CDT

Original text of this message

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