RE: concurrency waits after partitioning with SPB
Date: Tue, 23 Oct 2012 15:43:29 +0000
Message-ID: <BAA6E28B6241F046AED1E62D8697516C6F50DE90_at_COPDCEXMB08.cable.comcast.com>
Yes, SPB = SQL Plan Baselines
By "live" I mean that is where the applications connect to. Golden Gate replicates those changes to the other RAC. The problem (high concurrency waits) occurs when combining SPB with the partitioned table. I am using partitioning for cleanup, and it works fine, other than when SPB is turned on I get the concurrency waits. This data is OLTP, not bulk loaded.
I'm hoping Oracle will have a solution as it certainly appears to be a bug (it looks like the one I just put the patch on for in fact.)
-----Original Message-----
From: Mark W. Farnham [mailto:mwf_at_rsiz.com]
Sent: Tuesday, October 23, 2012 8:14 AM
To: Walker, Jed S; oracle-l_at_freelists.org
Subject: RE: concurrency waits after partitioning with SPB
I think by SPB you mean sql plan baselines.
I'm not sure what you mean by a "live" schema.
I'm not sure what sql plan baselines are stored in the database you're having a problem on. Is turning on the use of sql plan baselines a problem as a general artifact of the feature or because inappropriate plans for the replicant database are being activated.
Apart from all my uncertainly, you probably want to look into three notions about rafts of transaction inserts followed by transaction actions and "cleaning up"
- partition exchange (search up Tim Gorman and the "virtuous cycle"). While primarily targeted at the datawarehousing crowd, the concepts are pretty universal. Dropping the exchanged out partition which has become just a table unconnected to your active table has some advantages over drop partition.
- copy keep instead of delete, with a simple rotation of names. Then the table needing the cleanup is out of the active mix and you simply drop it. This dovetails well with partition exchange and the virtuous cycle, but can also be used without partitioning if the amount that needs to be kept is reasonable. Use as deep a grandfathering of names as makes sense in your situation.
- Null as a final status (meaning the transaction is ready to delete), so that the index on the status used to "copy keep" is very tiny.
Whether any of these notions are applicable to your exact situation depends on some operational details.
Good luck,
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Walker, Jed S
Sent: Tuesday, October 23, 2012 2:41 AM
To: oracle-l_at_freelists.org
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
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Oct 23 2012 - 17:43:29 CEST