Re: Statistics slowing down query

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Sat, 25 Oct 2003 13:17:10 GMT
Message-ID: <qJumb.164929$bo1.98184@news-server.bigpond.net.au>

"mcstock" <mcstock_at_enquery.com> wrote in message news:9pGdnRJPlL8f5QWiRVn-hQ_at_comcast.com...
> i thought i saw a post recently that asserted that with 9.2 and statistics
> the optimizer is so good that we will never need to use hints anymore --
> sounds like you have ran smack dab into neverland
>
> one possibility is that the optimizer now believes that for the size of
your
> tables and the complexity of the joins, everything would happen faster in
> memory -- i have heard that this can sometimes be a side-affect of larger
> the SORT_AREA_SIZE settings
>

Hi Mark

I think the quote from Tom Kyte was more along the lines that if the database was configured correctly and appropriate statistics were taken, then hints should be redundent.

I'm not too sure that's necessarily the case here.

If FTS appear to be accessed incorrectly, then there are a number of possible factors for this. Classically, they include:

The db_file_multiblock_read_count is configured incorrectly
The optimizer_* parameters are configured incorrectly
Statistics lack appropriate histograms to document skewed data distributions

To Michael, all of the above are worth investigating.

Cheers

Richard Received on Sat Oct 25 2003 - 08:17:10 CDT