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

From: Vordos, Suzy <Suzy.Vordos_at_qwest.com>
Date: Wed, 18 Nov 2009 14:12:53 -0700
Message-ID: <F9BAF589B638564EBCF9495B0CE1C89805B83A184B_at_qtdenexmbm24.AD.QINTRA.COM>



Try setting optimizer_secure_view_merging=false. The default is =true, and changing to =false solved various SQL performance problems we had after upgrading 9i-->10g.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Neil Kodner Sent: Tuesday, November 17, 2009 5:02 PM To: oracle-l_at_freelists.org
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:
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.



This communication is the property of Qwest and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Nov 18 2009 - 15:12:53 CST

Original text of this message