concurrency waits after partitioning with SPB
- From: "Walker, Jed S" <Jed_Walker@xxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 23 Oct 2012 06:41:02 +0000
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:
