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

From: Neil Kodner <nkodner_at_gmail.com>
Date: Tue, 17 Nov 2009 15:28:46 -0700
Message-ID: <ae2c46ba0911171428n65dd296bh6dc9ce1df4a0b80a_at_mail.gmail.com>

I'm using
estimate_percent=>100 and method_opt=>'FOR ALL COLUMNS SIZE 1' for these tables

On Tue, Nov 17, 2009 at 3:27 PM, Greg Rahn <greg_at_structureddata.org> wrote:

> 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:28:46 CST