Re: Updates with correlated subqueries slow after 9.2->10.2 upgrade
Date: Wed, 18 Nov 2009 07:50:02 +0000
This rings a bell. Some time around 22.214.171.124 there was a change to the way connect by queries are processed. I believe you can set _OLD_CONNECT_BY_ENABLED = TRUE to return to the old behaviour (after checking with Oracle support of course). Be careful though, there seem to be some downsides to setting it - see e.g. metalink note 445180.1.
2009/11/17 Neil Kodner <nkodner_at_gmail.com>:
> 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 126.96.36.199 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.
> Would love to hear help/feedback/whatever - I'm struggling with this and
> altering the session each time is unacceptable.