Re: Wait Event - enq: IV - contention

Jonathan, Thank you. The "synchronising library cache invalidation" is a great help. Now we can concentrate on the database instead of the program. I am trying to determine from EBS history which node it was running when the job got stuck.

All four nodes of the database were cycled on March 15th. The user said the problem has been happening for over a month. I do not see any solid data evidence in the system as the job request history is available only for past 10 days.

Thanks again for helping me pointing where to investigate further.

Biju

On Tue, Mar 27, 2018 at 2:11 PM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> Biju,
>
> You say "databsae reboot".
> Is this restarting all 4 instances in turn, or stopping all 4 then
> restarting them, or just restarting the stuck instance ?
>
> IV is supposed to be "synchronising library cache invalidation" - so the
> randomness may be due to the luck of which instance a process starts to run
> on; and if you don't stop all the instances before restarting that might
> explain why the problem can persist across restarts. This is all
> conjecture of course, trying to prompt some ideas that I would then check
> if I had my hands on the system.
>
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Biju Thomas <biju.thomas_at_gmail.com>
> Sent: 27 March 2018 19:56
> Cc: oracle-l_at_freelists.org
> Subject: Re: Wait Event - enq: IV - contention
>
> Thank you, Jonathan. The user canceled and resubmitted the program and it
> completed. I checked SGA resize ops, and do not see many resize operations,
> and there was none during when the program was stuck last time. The odd
> part is that the problem persists even after a database reboot. Not
> consistent, on an average 1, out of 8 tries get stuck.
>
> This is an EBS concurrent program. The program has more frequency of
> getting stuck if the user submits it as part of a "request set". User says
> it gets stuck almost all the time. If submitted as a single request, it
> completes most of the time. Still investigating other behaviors.
>
> - Biju
>
>
> On Tue, Mar 27, 2018 at 12:52 PM Jonathan Lewis <
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>> wrote:
>
> If you convert decimal to Hexadecimal to ASCII:
>
> P1 is just a repeat of the lock information, reading IV, mode 5
> P2 is supposed to be an object_id for the IV lock, but yours actually
> reads "SYNC"
> P3 = 3
>
> Which node is the process running on ? The IV is supposed to synchronising
> library cache invalidation across instances.
> Perhaps P3 = 3 is a large-scale function, perhaps it's saying that this
> instance is waiting for instance 3 (which might be 4 depending on whether
> Oracle is counting from 0 or 1 in this case).
>
> One guess: do you see SGA resizes at the time this is happening - perhaps
> one instance starts resizing and locks up the other instances as it does so.
>
> Regards
> Jonathan Lewis
>
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>
> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on
> behalf of Biju Thomas <biju.thomas_at_gmail.com<mailto:biju.thomas_at_gmail.com
> >>
> Sent: 27 March 2018 18:40
> To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
> Subject: Wait Event - enq: IV - contention
>
> Trying to troubleshoot a stuck program. The program gets stuck at a
> particular wait event 1 out of every 8 runs on an average. When the program
> completes, it finishes in few minutes. When it is stuck, the P1, P2 values
> are always the same. Can you please tell what the P1, P2, P3 values
> represent.
>
> Current Wait Event enq: IV - contention
> Current Wait Class Other
> P1 type|mode 1230372869
> P2 id1 1398361667
> P3 id2 3
> Object None
>
> They are not OBJECT_IDs.
>
> Version: Oracle database 11.2.0.4
> RAC Database, 4 nodes.
>
> Thanks for your help.
> Biju Thomas
>
> --
>
>
> --
> Best,
> Biju Thomas
> www.bijoos.com<http://www.bijoos.com>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
Best,
Biju Thomas
www.bijoos.com

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