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

     Posting a sample update statement might be useful; otherwise it will just be pokes in the dark. You mention "correlated" statements. What I have already seen after upgrades is weird correlations (typically, IN with a correlated subquery - if you don't see why it's weird, it's that i's just like an infinite recursion) taking the plunge in terms of performance because doing this type of thing properly is a hit-or-miss affair for the optimizer. Any minor patch could well take it off course.
Might be something similar. Rather than tweaking parameters and risking harming other stuff, it might be sounder to check whether the problem cannot be addressed at the root. I don't take the fact that it used to run in 3 minutes as an argument :-). Perhaps it could have run even faster, anyway.

SF

Neil Kodner wrote:
> Now that things are slow and unresponsive, I've been asked to join the
> fray.
>
> Little bit of background:
> DW-type environment
> 4 CPU Solaris 10
> optimizer_index_caching 90
> optimizer_index_cost_adj 50
>
> I didn't set the optimizer parameters, I'm of the 'leave them alone'
> school.
>
> 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.
>
> Another disclaimer-I didn't write this query-but it ran in 3 minutes
> in 9i.
>
> Altering the session and setting optimizer_index_cost_adj=100 and
> optimizer_index_caching=0 didn't make a difference.
>
> A common denominator is that the session-altered plans have a SORT
> UNIQUE step and the default 10g plans do not.
>
> Rather than clutter up the post with the queries and 2 versions of
> explain plans, I wrote everything to a file on my site.
> http://www.neilkodner.com/plan_differences.txt
>
> Would love to hear help/feedback/whatever - I'm struggling with this
> and altering the session each time is unacceptable.

--
http://www.freelists.org/webpage/oracle-l