Re: Updates with correlated subqueries slow after 9.2->10.2 upgrade

From: Greg Rahn <>
Date: Wed, 18 Nov 2009 14:38:07 -0800
Message-ID: <>

On Wed, Nov 18, 2009 at 10:36 AM, Neil Kodner <> wrote:
> Since the 10g upgrade, the plan was to establish a good statistics strategy
> and move away from RULE hints and never analyzing tables.  I'm tuning
> query-by-query and making progress but am wondering if the optimizer
> settings might be giving us false results.

I would suggest that you evaluate bringing things back to defaults before you hand tune all these queries. You may likely be doing a lot of work for not.

> It's a DW-style environment, optimizer_index_caching is set to 90,
> optimizer_index_cost_adj is 50.  I'm uncomfortable with these values since
> they tend to lean toward NL operations rather than hash operations.

These setting make very little sense for a DW (by a common definition), especially one that uses PX (parallel execution) where unless the object is below the small table threshold, it is a physical read from disk.
Getting a bad NL plan in a DW will kill performance.

Personally I avoid system stats (especially in a DW). I feel it adds yet another (mostly unnecessary) variable into the picture. Of course YMMV, but I approach it as if the performance of the current plans are acceptable, then why introduce it into the equation.

Greg Rahn
Received on Wed Nov 18 2009 - 16:38:07 CST

Original text of this message