Re: enq: IV - contention

Hi

I have a four nodes RAC DWH running in 11.2.0.4, it is a very busy DWH peaking 180 active session per second and we had very tough time when running partition maintenance operations such as splitting and dropping partitions, some of them takes as long as 24 hours to split 6000 partitions and it was caused by two events, ENQ-IV CONTENTION and reliable message (reliable message seems effect of IV contention).

I applied a merge patch for both Bug 19771961 and 15985351 almost 3 weeks ago and the contention dropped by great magnitude, the operation which used to take hours and hours (8 to 24 hours) now takes roughly 1 hour. Is your system patched with 19771961 (15985351 seems be included in 12.1.0.1)?

Thanks

On Thu, Dec 21, 2017 at 11:42 AM, Uwe K <uwe_at_kuechler.org> wrote:

> Dear oracle-l'ers,
>
> I'm interested to see whether some of you ever experienced the wait event
> "enq: IV - contention" being an issue on an Oracle RAC. To me, this is an
> exotic event and it is far from well documented.
> Reason: I have an open SR for five months now and no resolution to the
> issue yet. But before I describe the SR history in full length, i thought
> I'd make a short poll.
>
> My scenario very briefly described:
>
> - Windows 2012R2
> - Two nodes
> - Oracle Grid Infrastructure and DB 12.1.0.2, CpuOct17
> (but for a while the effect can also be seen on another system running
> 11.2.0.4 CpuOct17)
> - Batch Job running
> - "Delete from" several tables (housekeeping)
> - gather_schema_stats( cascade=>false )
> - Index rebuild
> - All DDL run in serial
> - Application Servers shut down during batch runs (to avoid concurrency
> with apps)
>
> Comparing earlier run times of this job on the 11.2 production, in 12.1
> they were around twice as high.
> Top wait by far is "enq: IV - contention" during the time span when gather
> stats and index rebuilds run.
>
> I've already tried to confine the job to a service that runs on one
> instance only to avoid cluster-related waits (also top waits). That didn't
> change the top waits.
> The only thing that helps was shutting down the other instance completely.
>
>
> Any ideas?
>
>
> Cheers,
> Uwe
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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