Re: Massive MERGE statement causing massive locks

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_at_gmail.com> wrote:
> 
> 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 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?
>>> 

> 
> --
> http://www.freelists.org/webpage/oracle-l
> 
> 
> 

--
http://www.freelists.org/webpage/oracle-l