RE: concurrency waits after partitioning with SPB
Date: Tue, 23 Oct 2012 16:39:26 +0000
Message-ID: <BAA6E28B6241F046AED1E62D8697516C6F50E01B_at_COPDCEXMB08.cable.comcast.com>
Sorry, the UPDATE statement is a single row on a sequence generated column. It does not include the partitioning key though so it'd have to check across all partitions. This happens immediately when I either start the application with SPB turned on, or when I turn it on. When you ask about "optimizing the update statements" - what exactly do you mean? I can't let it run in this state or the systems come to a crawl. If you have queries for the child cursors could you suggest them. I'll look at that if we try this again.
-----Original Message-----
From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk]
Sent: Tuesday, October 23, 2012 9:48 AM
To: Walker, Jed S; oracle-l_at_freelists.org
Subject: Re: concurrency waits after partitioning with SPB
I think the first thing I'd check is how frequently you're optimising the update statements and the number of child cursors you're generating (and possibly discarding), also how many stored baselines you've got for the statements. You don't give us any clue about how complex the update statements are, and how badly they may be affected by bind sensitivity or cardinality feedback.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
- Original Message ----- From: "Walker, Jed S" <Jed_Walker_at_cable.comcast.com> To: <oracle-l_at_freelists.org> Sent: Tuesday, October 23, 2012 7:41 AM Subject: concurrency waits after partitioning with SPB
| I'm writing this in the hope that someone has maybe seen this and found a
solution. I also have an SR with Oracle.
| RHEL5.6, oracle 11.2.0.3, 8-node RAC
|
| Two RACS with one-direction Golden Gate replication. On the first I have
10 live schemas, and on the second 2 live schemas. Both had
optimizer_capture/use_sql_plan_baselines=true set due to instability in the
past. I have an OLTP table that gets millions of inserts per day, and then
is cleaned with a delete cleanup procedure. I have partitioned this table
so I can do "drop partition" for cleanup instead (the delete cleanup often
causes significant delay in replication, not to mention all the redo).
|
| I partitioned on a small schema on the 2nd RAC with 2 schemas. I
immediately began getting huge concurrency waits, specifically "cursor: pin
s wait on x" on UPDATEs on this table. I did some research and turned off
optimizer_use_sql_plan_baselines and the waits went away. I tried to turn
this off on the first RAC hoping it was a simple fix, but the RAC didn't
handle it well, so I turned it back on. I'm working with oracle and
installed a patch for this particular issue, but it didn't work.
|
| So, essentially, partitioning this table when
optimizer_sql_plan_baselines = true results in these waits. Removing
partitioning or setting optimizer_sql_plan_baselines = false resolves it.
The main query is an UPDATE on the table, and SQL Tuning Advisor comes up
with no suggestions.
|
| Thanks,
|
| Jed
|
|
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Oct 23 2012 - 18:39:26 CEST