Oracle 18 New Wait Event: 'index (re)build lock or pin object'

Oracle 18 introduced a new wait event: 'index (re)build lock or pin object' when modifying indexes. For example, one rebuild triggers 4 occurrences, started with one lock_mode / pin_mode being 2, followed by three with mode 3, but only second wait on index (namespace=4):

PARSING IN CURSOR #140684589165328 len=32 dep=0 uid=49 oct=9 lid=49 tim=13635775869273 hv=4012613321 ad='a4693328' sqlid='229yc47rkr7q9'
alter index test_tab#idx rebuild

WAIT #140684589165328: nam='index (re)build lock or pin object' ela= 6 namespace=1 lock_mode=2 pin_mode=2 obj#=-1 tim=13635775869402
WAIT #140684589165328: nam='index (re)build lock or pin object' ela= 7 namespace=4 lock_mode=3 pin_mode=3 obj#=-1 tim=13635775872729
WAIT #140684589165328: nam='index (re)build lock or pin object' ela= 10 namespace=1 lock_mode=3 pin_mode=3 obj#=-1 tim=13635778508162
WAIT #140684589165328: nam='index (re)build lock or pin object' ela= 4 namespace=1 lock_mode=3 pin_mode=3 obj#=-1 tim=13635778508204

  -- Note: namespace=4 is INDEX

The event information is described as:

SQL > select * from v$event_name where name = 'index (re)build lock or pin object';

  EVENT#          : 333
  EVENT_ID        : 3347698104
  NAME            : index (re)build lock or pin object
  PARAMETER1      : namespace
  PARAMETER2      : lock_mode
  PARAMETER3      : pin_mode
  WAIT_CLASS_ID   : 4166625743
  WAIT_CLASS#     : 3
  WAIT_CLASS      : Administrative
  DISPLAY_NAME    : index (re)build lock or pin object

The Lock/pin mode seems referring to those documented in v$libcache_locks:

  Lock/pin mode:
      0 - No lock/pin held
      1 - Null mode
      2 - Share mode
      3 - Exclusive mode

Since this wait event reveals metrics on index operations related to library cache and shared cursors, it can help us understand shared pool mutex activities.

Here a short demo on the number of waits for different index operations.

SQL > drop table test_tab purge;

SQL > create table test_tab as select 1 x from dual; 

SQL > select total_waits from v$system_event where event = 'index (re)build lock or pin object';

    TOTAL_WAITS
    -----------
          20000

-------------------------- create index (4 Waits) --------------------------

SQL > create index test_tab#idx on test_tab(x);

SQL > select total_waits from v$system_event where event = 'index (re)build lock or pin object';

    TOTAL_WAITS
    -----------
          20004

-------------------------- gather index stats (0 Waits) --------------------------

SQL > exec dbms_stats.gather_index_stats('K', 'TEST_TAB#IDX');

SQL > select total_waits from v$system_event where event = 'index (re)build lock or pin object';

    TOTAL_WAITS
    -----------
          20004   
      
-------------------------- rebuild index (4 Waits) --------------------------

SQL > alter index test_tab#idx rebuild;

SQL > select total_waits from v$system_event where event = 'index (re)build lock or pin object';

    TOTAL_WAITS
    -----------
          20008
      
-------------------------- rebuild index online (3 Waits) --------------------------

SQL > alter index test_tab#idx rebuild online;

SQL > select total_waits from v$system_event where event = 'index (re)build lock or pin object';

    TOTAL_WAITS
    -----------
          20011       (Note: rebuild index online increases only 3)
      
-------------------------- rebuild index reverse (4 Waits) --------------------------

SQL > alter index test_tab#idx rebuild reverse;

SQL > select total_waits from v$system_event where event = 'index (re)build lock or pin object';

    TOTAL_WAITS
    -----------
          20015
      
-------------------------- drop index (0 Waits) --------------------------

SQL > drop index test_tab#idx;

SQL > select total_waits from v$system_event where event = 'index (re)build lock or pin object';

    TOTAL_WAITS
    -----------
          20015