Re: Updates with correlated subqueries slow after 9.2->10.2 upgrade
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: nkodner@xxxxxxxxx
- Date: Wed, 18 Nov 2009 14:38:07 -0800
On Wed, Nov 18, 2009 at 10:36 AM, Neil Kodner <nkodner@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
