Re: Updates with correlated subqueries slow after 9.2->10.2 upgrade
Date: Wed, 18 Nov 2009 14:38:07 -0800
On Wed, Nov 18, 2009 at 10:36 AM, Neil Kodner <nkodner_at_gmail.com> 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.
-- Regards, Greg Rahn http://structureddata.org -- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 18 2009 - 16:38:07 CST