Re: Updates with correlated subqueries slow after 9.2->10.2 upgrade
- From: Neil Kodner <nkodner@xxxxxxxxx>
- To: Robert Freeman <robertgfreeman@xxxxxxxxx>
- Date: Wed, 18 Nov 2009 11:36:04 -0700
I did generate system statistics after the 10g upgrade.
One of my growing concerns with our 10g system are the optimizer
parameters. All of my other systems have default optimizer parameters. Now
that I'm involved with this system, I'm uncomfortable tweaking them. The
past DBA was insistent on RBO for all these years and have configured the DB
as such.
Since the 10g upgrade, the plan was to establish a good statistics strategy
and move away from RULE hints and never analyzing tables. I'm tuning
query-by-query and making progress but am wondering if the optimizer
settings might be giving us false results.
It's a DW-style environment, optimizer_index_caching is set to 90,
optimizer_index_cost_adj is 50. I'm uncomfortable with these values since
they tend to lean toward NL operations rather than hash operations.
On Wed, Nov 18, 2009 at 9:56 AM, Robert Freeman <robertgfreeman@xxxxxxxxx>wrote:
> 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...
>
> RF
>
> Robert G. Freeman
> Oracle ACE
> 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
> Blog: http://robertgfreeman.blogspot.com
>
>
> ------------------------------
> *From:* Neil Kodner <nkodner@xxxxxxxxx>
> *To:* oracle-l@xxxxxxxxxxxxx
> *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:
> 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:
