Re: SV - contention on RAC EXADATA

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 17 Jun 2016 22:50:22 +0100
Message-ID: <4547764D7EA04F67B7B619A2ECC3BEF8_at_Primary>

Okay,

I've got it now.

Two things that put me off track:
a) "enq: SV - contention" doesn't show up in session events, it's one of those "wait class other" events
b) The SV enqueue doesn't show up in v$enqueue_stats.

A call for nextval does two "global enqueue gets sync", one for a global SV enqueue and one for a global NB enqueue, and each get requires two latch gets.
Strangely we don't see any waits for the NB enqueue - but I think the SV enqueue operations may be more expensive because they have to go through a get_value/put_value cycle.

It certainly seems that the work required to get the SV enqueue is much higher than you might expect when only a single instance is running.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

• Original Message ----- From: "Pawel Smolarz" <pawel.smolarz_at_nordea.com> To: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> Cc: <oracle-l_at_freelists.org> Sent: Thursday, June 16, 2016 9:06 AM Subject: Re: SV - contention on RAC EXADATA

Hi,

4 instances - 3 disabled, only one active.

Problem is visible when we have one active instance - We afraid to start other instances, due to the fact that the problem of the sequence may increase significantly.

We also checking configuration of resource manager.

Pozdrawiam / Regards,
Paweł

On Thu, 16 Jun 2016 06:55:13 +0000
Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> I should have been more precise with my language.
> "installed on 4 nodes" doesn't necessarily mean that you've actually
> got 4 active instances accessing the database.
>
> Sorry to be pedantic, but can you confirm that when the problem is
> visible there are 4 active instances, and only one instance is
> operates the service that executes this code.
>
> My point about "not knowing about Oracle sequences" is that your
> supplier doesn't know WHY they chose ORDER, so whatever they thought
> they were trying to achieve they weren't, which makes it easier to
> decide that the ORDER is redundant: someone needs to argue the
> necessity for or against the case - but if you've got a test RAC
> system you could at least check the impact.
>
> The other thing that crosses my mind is that the resource manager may
> be causing the problem - has it been configured correctly for the
> change from single instance to Exadata RAC. Off the top of my head I
> can't remember if there were any significant changes in behaviour but
> maybe there's something that means you have to change the
> configuration manager to get the same effect.
>
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> _at_jloracle
>
> ________________________________________

>

---

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2016.0.7640 / Virus Database: 4604/12440 - Release Date: 06/17/16

---

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2016.0.7640 / Virus Database: 4604/12440 - Release Date: 06/17/16

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

Received on Fri Jun 17 2016 - 23:50:22 CEST