Re: Updates with correlated subqueries slow after 9.2->10.2 upgrade
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: nkodner@xxxxxxxxx
- Date: Wed, 18 Nov 2009 00:10:05 +0100
Neil,
Ooops, I had read your post a bit fast - I have since found the
queries and what did I find in them?
Cough, cough.
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
