Re: concurrency waits after partitioning with SPB
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <Jed_Walker@xxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 23 Oct 2012 16:47:33 +0100
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@xxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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
|
|
--
//www.freelists.org/webpage/oracle-l
Other related posts:
