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

From: Greg Rahn <greg_at_structureddata.org>
Date: Tue, 17 Nov 2009 14:27:10 -0800
Message-ID: <a9c093440911171427w61915a2ete7de4da20a81cb44_at_mail.gmail.com>

First, are the cardinality estimates representative? See "Using GATHER_PLAN_STATISTICS With DBMS_XPLAN.DISPLAY_CURSOR" http://structureddata.org/2007/11/21/troubleshooting-bad-execution-plans/

If the cardinality estimates are off by a significant factor, then validate your statistics. This may require a higher sample size than what is currently used.

There is no use in mucking with parameters/hints until the stats are representative.

On Tue, Nov 17, 2009 at 2:02 PM, Neil Kodner <nkodner_at_gmail.com> wrote:
> Once our environment was upgraded from 9.2.0.4 to 10.2.0.4, everything
> seemed ok except for UPDATE statements that contain correlated select
> statements.  Across the board, we received poor performance compared to
> running them in 9.2.

-- 
Regards,
Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 17 2009 - 16:27:10 CST