concurrency waits after partitioning with SPB
Date: Tue, 23 Oct 2012 06:41:02 +0000
Message-ID: <BAA6E28B6241F046AED1E62D8697516C6F50D8E0_at_COPDCEXMB08.cable.comcast.com>
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 - 08:41:02 CEST