Oracle DBA by Example. Code samples, Scripts, Reference.: Wait Event enq: JI

===============

Issue

===============

The top wait is "JI enqueue contention"
What is it, and how to fix it?

Top 5 Timed Events Avg %Total

~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) Time

---------------------------- --------- --------- ------ ------

enq: JI - contention 720 3,602 5002 99.0

db file async I/O submit 1,175 12 10 .3

CPU time 11 .3

db file sequential read 7,150 4 1 .1

control file parallel write 3,119 3 1 .1

-------------------------------------------------------

Foreground Wait Events DB/Inst: IGT/igt Snaps: 135715-135716

-> Only events with Total Wait Time (s) >= .001 are shown

-> ordered by Total Wait Time desc, Waits desc (idle events last)

Avg %Total

%Tim Total Wait wait Waits Call

Event Waits out Time (s) (ms) /txn Time

---------------------------- ------ ---- ---------- ----- ----- -----

enq: JI - contention 720 100 3,602 5002 0.2 99.0

db file sequential read 7,127 0 4 1 2.3 .1

log file sync 2,648 0 3 1 0.8 .1

cursor: pin S 76 0 0 2 0.0 .0

control file sequential read 3,304 0 0 0 1.1 .0

Disk file operations I/O 134 0 0 0 0.0 .0

SQL*Net message from client 80,952 0 165,951 2050 25.9

jobq slave wait 14,277 98 7,139 500 4.6

SQL*Net message to client 80,952 0 0 0 25.9

SQL*Net more data from clien 2,195 0 0 0 0.7

-------------------------------------------------------------

===============

Oracle Theory

===============

JI enqueue is used to serialize the refresh of a materialized view, JI enqueue is acquired in exclusive mode on the mview base (container) table when the refresh is being performed, it ensures that two or more refresh processes do not try to refresh the same object.

===============

Solution

===============

You may want to see which session is holding the JI enqueue with the help of Note 1020008.6 or <Note 1020007.6>.

If the JI enqueue holder is an intended refresh session, the other session(s) that intend to refresh the same mview may just need to wait until the first one finishes.

If however the JI enqueue holder session is stuck (e.g. was killed without the immediate option), then you may want to take further actions to remove/terminate this session, so that the intended refresh can acquire the JI enqueue on the mview and proceed with the refresh.

Materialized View Refresh is Hanging With JI Contention (Doc ID 1358453.1)

set lines 200

set pagesize 66

break on Kill on sid on username on terminal

column sid format 99999

column Kill heading 'Kill String' format a13

column res heading 'Resource Type' format 999

column id1 format 9999990

column id2 format 9999990

column state FOR A10

column event FOR A30

column locking heading 'Lock Held/Lock Requested' format a40

column lmode heading 'Lock Held' format a20

column request heading 'Lock Requested' format a20

column serial# format 99999

column username format a30 heading "Username"

column terminal heading Term format a6

column tab format a40 heading "Table Name"

column owner format a9

column LAddr heading "ID1 - ID2" format a18

column Lockt heading "Lock Type" format a80

column command format a25

SELECT

l.sid,

s.serial#,

nvl(s.username, 'Internal') username,

s.event,

s.state,

decode(

s.command, 0, 'None',

decode(l.id2, 0, u1.name|| '.'

|| substr(t1.name, 1, 20), 'None')) tab,

c.command_name command,

decode(

l.lmode, 1, 'No Lock', 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', '--none--'

) lmode,

decode(

l.request, 1, 'No Lock', 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', '--none--'

) request,

l.id1

|| '-'

|| l.id2 laddr,

l.type

|| ' - '

|| lt.description lockt

FROM

gv$lock l,

gv$session s,

sys.user$ u1,

sys.obj$ t1,

v$sqlcommand c,

v$lock_type lt

WHERE

l.sid = s.sid

AND l.inst_id = s.inst_id

AND t1.obj# = decode(

l.id2, 0, l.id1, 1

)

AND u1.user# = t1.owner#

AND s.type != 'BACKGROUND'

AND c.command_type = s.command

AND lt.type = l.type

ORDER BY

s.inst_id,

s.sid;

SID SERIAL# Username EVENT STATE Table Name COMMAND Lock Held Lock Requested

------ ------- ------------ ------------------------------ ---------- ---------------------------------- ---------------------- -------------------- --------------------

ID1 - ID2 Lock Type

------------------ --------------------------------------------------------------------------------

1182 14171 MY_SCHEMA SQL*Net message from dblink WAITING MY_SCHEMA.GSM_NP_PSTN_ACS_SS SELECT Exclusive --none--

61267-0 JI - Lock held during materialized view operations (like refresh, alter) to