RE: concurrency waits after partitioning with SPB
- From: "Walker, Jed S" <Jed_Walker@xxxxxxxxxxxxxxxxx>
- To: "Walker, Jed S" <Jed_Walker@xxxxxxxxxxxxxxxxx>, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 23 Oct 2012 18:31:36 +0000
I have 4 SQL Plan baselines (1 accepted) for this SQL. What is interesting is
that I don't see any baselines for the other schema that hasn't been
partitioned yet. I would have expected to see some.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Walker, Jed S
Sent: Tuesday, October 23, 2012 10:39 AM
To: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx
Subject: RE: concurrency waits after partitioning with SPB
Sorry, the UPDATE statement is a single row on a sequence generated column. It
does not include the partitioning key though so it'd have to check across all
partitions.
This happens immediately when I either start the application with SPB turned
on, or when I turn it on. When you ask about "optimizing the update statements"
- what exactly do you mean? I can't let it run in this state or the systems
come to a crawl. If you have queries for the child cursors could you suggest
them. I'll look at that if we try this again.
-----Original Message-----
From: Jonathan Lewis [mailto:jonathan@xxxxxxxxxxxxxxxxxx]
Sent: Tuesday, October 23, 2012 9:48 AM
To: Walker, Jed S; oracle-l@xxxxxxxxxxxxx
Subject: 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@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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
