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-l