RE: Massive MERGE statement causing massive locks

Richard's point was that regular rebuilding of all indexes was a total waste of time as ALMOST ALL rebuilds were unnecessary.  One of the boundary cases where rebuilds MIGHT be appropriate was following a very large delete.

The OP has said that every month a process deletes everything older than 3 months - which means deleting 25% of the data. This is an ideal candidate for identifying
a) Which indexes could be made unusable before the delete and then rebuilt
b) Which indexes should be rebuilt after the delete
c) Which indexes could, or should, be coalesced after the detel
d) Which indexes need no action at all.

I haven't been following the thread closely, so I don't know which version of the database is in use, but there have been various bugs relating to index  block reuse (on inserts) after very large deletes - for which there's at least one clue in the statistic called something like "abort on recursive index block reclaim" - and one such bug could be "fixed" which goes away after a rebuild.  

A key question, for analysis, is whether the deadlock is TX/4 (index based) or TX/6 (data based).









Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

________________________________________
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Lothar Flatz [l.flatz_at_bluewin.ch]
Sent: 17 October 2016 21:40
To: oracle-l_at_freelists.org
Subject: Re: Massive MERGE statement causing massive locks

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

--