Updates with correlated subqueries slow after 9.2->10.2 upgrade
- From: Neil Kodner <nkodner@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 17 Nov 2009 15:02:23 -0700
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.
Other related posts:
