Oracle Scratchpad

March 28, 2018

Lock Types

Filed under: Locks,Oracle — Jonathan Lewis @ 12:24 pm BST Mar 28,2018

Every now and again I have to check what a particular lock (or enqueue) type is for and what the associated parameter values represent. This often means I have to think about the names of a couple of views and a collection of columns – then create a few column formats to make the output readable (though sometimes I can take advantage of the “print_table()” procedure that Tom Kyte published a long time ago.  It only takes a little time to get the code right but it’s a nuisance when I’m in a hurry so I’ve just scribbled out a few lines of a script that takes a lock type as an input parameter and reports all the information I want.

rem
rem     Script:         lock_types.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Mar 2018
rem     Usage:          start lock_types {lock type}
rem

define m_lock_type='&1'

column  display new_value m_display

select
        case when substr(version,1,2) in ('12', '18', '19')
        then
                'display_name'
        else
                'name'
        end                     display
from
        v$instance
;

set linesize 160
set pagesize 60
set trimspool on

column type             format a4
column name             format a32
column description      format a80
column id1_tag          format a32
column id2_tag          format a32
column is_user          format a4 heading "User"
column is_recycle       format a4 heading "Rcyc"

set feedback off
set verify off
break on report skip 1

spool lock_types

select  *
from    V$lock_type
where   type = upper('&m_lock_type')
order by
        type
;

column  name                    format a42
column  parameter1              format a9
column  parameter2              format a24
column  parameter3              format a22
column  wait_class              format a14
column  display_name            format a42

select
        eve.name,
        eve.parameter1,
        eve.parameter2,
        eve.parameter3,
        eve.wait_class,
        nullif(eve.&m_display, eve.name) display_name
from
        v$event_name    eve
where
        eve.name like 'enq: ' || upper('&m_lock_type') || '%'
order by
        nullif(eve.wait_class,'Other'),
        eve.name
;

set feedback on

I’ve included a check (and hack) on the value of the major version because 12c introduced a “display_name” as well as a “name” for events, and the latter is sometimes a little more descriptive than the former, so it’s nice to have a single script that could print two different values for the versions that have them.

Here’s a sample of the output when I pass ‘IV’ as an input parameter:

TYPE NAME                             ID1_TAG                          ID2_TAG                          User Rcyc
---- -------------------------------- -------------------------------- -------------------------------- ---- ----
DESCRIPTION                                                                                              CON_ID
---------------------------------------------------------------------------------------------------- ----------
IV   Library Cache Invalidation       object #                         time stamp                       NO   NO
Synchronizes library cache object invalidations across instances                                              0


NAME                                       PARAMETER PARAMETER2               PARAMETER3             WAIT_CLASS     DISPLAY_NAME
------------------------------------------ --------- ------------------------ ---------------------- -------------- -----------------------
enq: IV -  contention                      type|mode id1                      id2                    Other

As you can see from the presence of the con_id column in v$lock_type this output came from a 12c instance. I picked the IV lock because that’s the one that prompted me to check the meanings of the id[12] and parameter[123] columns when a question about waits for the IV lock appeared recently on Oracle-L. I’ve got two reasons for carrying on with this particular example – first that it demonstrates that the descriptions can be wrong, second that it allows me to demonstrate a quick tip on translation.

The question on Oracle-L related to a 4-node RAC system and reported one instance suffering long waits on the IV enqueue on a fairly regular basis when running a particular batch task. The OP reported the following values as the p1, p2, p3 values from v$session while the wait was going on:


P1    type|mode 1230372869
P2    id1       1398361667
P3    id2                3

According to the details in v$lock_type the enqueue is about library cache invalidation across instances – and that fits the OPs complaint because the system is a RAC system. The id1 value is supposed to be an obj# (object_id), but the OP said it wasn’t; and the id2 value is supposed to be a timestamp, but 3 is an odd value for a timestamp (though it might represent – for example – the 3 second wait that is a common time-out interval for enqueues). So, clearly, the descriptions can be wrong.

Translation

Take another look at p1 and p2, and turn them into Hexadecimal:


1230372869 (dec) = 0x49560005 (hex)
1398361667 (dec) = 0x53594E43 (hex)

If you happen to be good with Hex and ASCII code you’ll know that 2-byte values in the range 41-5F are mostly the capital letters of the Roman alphabet (while 61 – 7f are mostly the lower case letters), so a second translation step gives us:


1230372869 (dec) = 0x49560005 (hex) = 'IV'  5
1398361667 (dec) = 0x53594E43 (hex) = 'SYNC'

The p1 parameter is described (correctly) as “type/mode” – this is an IV enqueue held or requested in mode 5; the p2 parameter is not an object number, it looks more like a text description of why the enqueue is being requested (the enqueue is, after all, described as being used to “synchronize library cache object invalidation”).

I still don’t know what the final parameter represents – I doubt if it’s really about a three second wait (but that could be checked by examining v$session_wait over a period of several seconds or v$session_wait_history), it might be an indication of the instance that the session is trying to synchronize with (though, again, that seems a long shot), or it might just be a “reason-code” describing why the synchronisation is necessary.

Whenever in doubt about the meaning for the various parameters, it’s always worth a quick dec -> hex -> ASCII check, just in case it offers some clues about the function of the enqueue.

Update (Feb 2021)

I’ve just received the following (paraphrased) email:

We are getting this wait event enq IV – contention very frequently while doing a MERGE statement in our RAC oracle database due to which the merge statement is taking a long time after upgrading to  oracle 12c

I happened to see this article but it does not give any info as to how to resolve this issue. 

Any comments from you would be appreciated.

The little script was originally written to run on 12c, but I’ve just tweaked it to include 18c and 19c and run it on 19c and got an additional line in the results from v$event_name; here’s the latest output:


TYPE NAME                             ID1_TAG                          ID2_TAG                          User Rcyc
---- -------------------------------- -------------------------------- -------------------------------- ---- ----
DESCRIPTION                                                                                              CON_ID
---------------------------------------------------------------------------------------------------- ----------
IV   Library Cache Invalidation       object #                         time stamp                       NO   NO
Synchronizes library cache object invalidations across instances                                              0


NAME                                       PARAMETER PARAMETER2               PARAMETER3             WAIT_CLASS     DISPLAY_NAME
------------------------------------------ --------- ------------------------ ---------------------- -------------- ----------------------
enq: IV - cross instance invalidation      lock idx                                                  Concurrency
enq: IV -  contention                      type|mode id1                      id2                    Other

This tells us something very important about the IV wait events, which we can probably carry back from 19c to 12c: “IV Library Cache Invalidation” isn’t necessarily the same as “enq: IV – contention”, so we shouldn’t assume that we can interpret the P2 and P3 values according to the id1 and id2 tags in v$lock_type.

With a new reason for researching the enqueue it’s easy to start with a simple search, resulting in two hits on MOS documents:

  • Doc ID 2028503.1 12c RAC DDL Performance Issue: High “enq: IV – contention” etc if CPU Count is Different
  • Doc ID 2301046.1 “Enq: IV – Contention”, summary invalidations, and summary management compile

The first of these has already been mentioned in the comments below and relates to a configuration problem where cross-instance library cache invalidation can be slow if the number of LMD processes differs between instances; it’s interesting to note that the document specifically mentions a bug (closed as a duplicate) that reported performance degradation of a grant statement after upgrade to 12c  (and presumably the same would have been true for revoke and I can’t help wondering if this is also true of the set role command – it doesn’t in single instance – since this acts indirectly to grant privileges to users)

The second document describes, amongst other bits of information, the effect of DML on the base tables of materialized views and the subsequence need to invalidate “summary objects” across all instance – and it includes a simple query against [g]v$db_object_cache that might help to identify objects that are frequently invalidated. Following a slightly circuitous trail this also leads to bug 21418655 : “PATCH 19450314 INCOMPLETE: MORE UNNECESSARY INVALIDATIONS IN 12C” which is reported as fixed in 18.1, but has fixes included in patch sets for earlier versions.

So – bottom line response to the email that triggered this update:

  • if you’ve managed to collect some p1, p2, p3 values (e.g. from tracing or ASH) from the IV enqueue waits then you may have clues from object numbers;
  • if you check gv$db_object_cache for objects with high values for invalidations that may give you clues to interesting objects;
  • if you have code that frequently grants or revokes privileges, that might have something to do with the problem
  • if you have code that freqently modifies the base tables of materalized views especially if you have many, possibly related, materialized view

 

6 Comments »

  1. Have you had any more insights into the IV enqueue and any issues revolving around it in 12.2.0.2?…. I have a case right now on a 2 node RAC system (January RU applied) where I’ve got some PL/SQL that just never finishes… — with the AWR’s and 10046 trace files showing a load of ‘enq: IV – contention’ waits along with row cache locks and library cache locks and pins.

    If I run the same PL/SQL (with the same data set) on an 11.2.0.3 or 12.1 RAC configuration, or the same as non-RAC configurations with similar memory settings and data sets, etc – it runs just fine. So, this smacks of a bug of some sort. There is a bug, 19450314 – related to object invalidation/revalidations, that’s supposed to be fixed in 12.2…. Right now, that’s about all I see of this particular problem. Have an SR open that I’m working with Oracle on…

    Comment by Robert Freeman — May 8, 2018 @ 10:37 pm BST May 8,2018 | Reply

    • Robert,

      I haven’t done anything to follow up the problem of the list server (I’ve just added a link to the original Oracle-L posting) so I don’t know any more; however I’ve just done a quick check on MoS and there is a note (which may be the result of your SR): ID 2028503.1 “12c RAC DDL Performance Issue: High “enq: IV – contention” etc if CPU Count is Different “ that says you can run into problems of this type if the number of LMD processes differs between instances (which may happen if the machines have different numbers of CPUs). Workaround is to set hidden parameter _ges_server_processes explicitly for all instances

      Comment by Jonathan Lewis — May 9, 2018 @ 2:11 pm BST May 9,2018 | Reply

      • Yep, saw that note and checked the cpu counts on both nodes, they are the same.

        Working SR with Oracle. I’ll let you know what we find…

        Comment by Robert freeman — May 9, 2018 @ 10:30 pm BST May 9,2018 | Reply

  2. Hi Robert,
    Did you get any solution for the above? As I am also getting the same issue in my 12.2.0.1 (2 node RAC system).

    Comment by Vimal — April 6, 2019 @ 2:52 pm BST Apr 6,2019 | Reply

    • Vimal,

      If you haven’t already tried it you might be better off posting a message on the Oracle-L list server.

      Alternatively I see that Robert Freeman has left a link to his blog in the “Comment by …” line of the original comment, and if you follow this to his blog there’s a link to “about me” which contains his email address.

      Comment by Jonathan Lewis — April 6, 2019 @ 3:20 pm BST Apr 6,2019 | Reply

  3. Thanks, Jonathan. I will touch base both options.

    Comment by Vimal — April 6, 2019 @ 6:57 pm BST Apr 6,2019 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.