And my first inclination, (or assumption, as you stated as well…) would be
exchange partition considering how partial I am to it for this type of
requirement, but I really, really hate assuming anything and would recommend it
worth the time required to trace the processing and find out what is really
going on behind the scenes. We may all find we’re barking up the wrong Oracle
tree… :)
Kellyn
On Oct 17, 2016, at 4:09 PM, Deas, Scott <Scott.Deas@xxxxxxx> wrote:
The original post was "why is my MERGE causing deadlocks?", which OP answered
for himself, "indexes needed to be rebuilt". I know having unusable (or no)
indexes on FK columns can cause deadlocks, so I assumed it was a similar
scenario.
My assumption is that they're marking the indexes unusable as part of their
maintenance job that deletes records. My recommendation back to OP was to
look at truncating partitions or using partition exchange to take the place
of the delete, since the objects in question are partitioned. This would
eliminate any index maintenance if the indexes are all local.
Obviously, there's a fair amount of assuming going on here, so without more
info from OP, we won't know the root cause of the issue.
Thanks,
Scott
On Oct 17, 2016, at 17:04, Kellyn Pot'Vin-Gorman <dbakevlar@xxxxxxxxx> wrote:Notice of Confidentiality: **This E-mail and any of its attachments may
Richard may be a bit slow in responding- he’s enjoying a well-deserved
European holiday with his family… :)
I think we need to look at the bigger picture here, which is what the poster
had originally started to move towards, but had been deterred by the short
term “solution". The overall process is flawed in the way it’s handling the
data purge. Yes, merge is better than the current processing and the next
step would be to look at the objects and see what other processing is going
on that creates the deadlock situation. I believe you’re going to find
there is a lot processing to the objects at hand than just a massive delete
to create a situation where an index rebuild is “correcting” the problem.
Anybody for a trace? :)
Kellyn
On Oct 17, 2016, at 2:40 PM, Lothar Flatz <l.flatz@xxxxxxxxxx> wrote:
Last time I looked it was Richard Foote. And yes, rebuilding indexes is a
myth .
https://richardfoote.wordpress.com/2007/12/11/index-internals-rebuilding-the-truth/
https://richardfoote.wordpress.com/category/index-rebuild/
In the case below we have no evidence. We don't know what was wrong before
or might be better now. I prefer to work with facts rather than guess.
regards
Lothar
On 17.10.2016 21:49, Mladen Gogala wrote:
On 10/13/2016 09:01 AM, David Ramírez Reyes wrote:
The problem was that the indexes were not rebuild after purging the
records (every month there's a process that deletes any record older than
3 months); once rebuilded, all deadlocks disappeared.
About the use of MERGE, I google it and found that in general gives a
better performance than just using INSERT/UPDATE instead so, I think I
can let it working as it is (just need to be sure that indexes are
rebuilded after the monthly purging).
I will try anyway the usage of parallel processing for it, that may help
also,
Thanks everyone!
Hmmmm, that's very strange! Indexes usually don't need rebuilding. I would
be interested in what Chris Foote has to say about this, if he still
follows this list. What particular resource was getting locked?
--
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
contain
Lincoln National Corporation proprietary information, which is privileged,
confidential,
or subject to copyright belonging to the Lincoln National Corporation family
of
companies. This E-mail is intended solely for the use of the individual or
entity to
which it is addressed. If you are not the intended recipient of this E-mail,
you are
hereby notified that any dissemination, distribution, copying, or action
taken in
relation to the contents of and attachments to this E-mail is strictly
prohibited
and may be unlawful. If you have received this E-mail in error, please notify
the
sender immediately and permanently delete the original and any copy of this
and any printout. Thank You.**
