Re: Massive MERGE statement causing massive locks

On 10/17/2016 04:40 PM, Lothar Flatz wrote:
> Last time I looked it was Richard Foote.

It probably still is, unless he has changed his name to something like Tom Haverford. Silly mistake on my part.

> And yes, rebuilding indexes is a myth .

So the DBA lore says. However, the original poster claims that it has resolved his problem. That is why I asked for the opinion.

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

I have nothing against guessing. SQL is, after all, an abbreviation which means "Said Quixotically on Laurel", a reference to the original Oracle, an early leader in the business decision support systems, located in Delphi, Greece. The events around the announcement of Oracle 12.2 convinced me that the ancient ways of using laurel leaves are not quite forgotten today. The curious case of index rebuilds solving the problem is the reason why I tried soliciting Richard's take on the whole thing. I have read both Richard's articles you quoted above and I place Richard very high on the Mladen's pyramid of greatness.

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

-- 
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com

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