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