Re: Updates with correlated subqueries slow after 9.2->10.2 upgrade
Date: Wed, 18 Nov 2009 08:56:37 -0800 (PST)
I'm just wondering.. In your move from 9i to 10g, did you generate system statistics? While I doubt it will make a difference, it might be worth trying. Probably even less important are fixed table statistics, etc...
Robert G. Freeman
Ask me about on-site Oracle Training! RMAN, DBA, Tuning, you name it! Author:
Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON IT'S WAY SOON! OCP: Oracle Database 11g Administrator Certified Professional Study Guide (Sybex) Oracle Database 11g New Features (Oracle Press) Oracle Database 10g New Features (Oracle Press) Other various titles
From: Neil Kodner <nkodner_at_gmail.com> To: oracle-l_at_freelists.org
Sent: Tue, November 17, 2009 3:02:23 PM
Subject: Updates with correlated subqueries slow after 9.2->10.2 upgrade
Now that things are slow and unresponsive, I've been asked to join the fray.
Little bit of background:
4 CPU Solaris 10
I didn't set the optimizer parameters, I'm of the 'leave them alone' school.
Once our environment was upgraded from 184.108.40.206 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.Received on Wed Nov 18 2009 - 10:56:37 CST