Re: Statistics slowing down query

From: Michael Rothwell <marothwellRemoveThis_at_yahoo.com>
Date: Mon, 27 Oct 2003 11:41:04 -0800
Message-ID: <7YWdnagU4YJh6QCiRVn-gA@comcast.com>

Richard Foote wrote:
> "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.
>

I'll take a look into those items. I've done a lot of checking, and so far, nothing pops up at me.

Thanks.

> Cheers
>
> Richard
>
>

-- 
There is no worse tyranny than to force a man to pay for what he does 
not want merely because you think it would be good for him.   -- Robert 
Heinlein

Received on Mon Oct 27 2003 - 13:41:04 CST