首页 » ORACLE 9i-23ai » Performance Tunning: enq: JI – contention
Performance Tunning: enq: JI – contention
前几日软件版本上线后，数据库出现了大显的ENQ: JI – contention等待事件的活动会话队列，OEM显示负载如下：
SQL> @snapper ash 5 1 all
Sampling SID all with interval 5 seconds, taking 1 snapshots...
-- Session Snapper v4.14 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
----------------------------------------------------------------------------------------------------
Active% | INST | SQL_ID | SQL_CHILD | EVENT | WAIT_CLASS
----------------------------------------------------------------------------------------------------
5465% | 1 | | | enq: JI - contention | Other
1300% | 1 | 4xn6quazzvph6 | 0 | enq: TX - row lock contention | Application
500% | 1 | gb66fupbc9qk8 | 0 | enq: TX - row lock contention | Application
253% | 1 | 3z788mk74xfgs | 0 | enq: TX - row lock contention | Application
200% | 1 | | | enq: DX - contention | Other
100% | 1 | f8259f2fj244v | 0 | db file sequential read | User I/O
100% | 1 | 97xm6jtruayk8 | 0 | enq: TX - row lock contention | Application
100% | 1 | 5mr5gn7frhp9p | 0 | enq: TX - row lock contention | Application
94% | 1 | 4vs13q93q3nqp | 0 | db file sequential read | User I/O
94% | 1 | 8xgccgb6bwgc8 | 0 | db file sequential read | User I/O
-- End of ASH snap 1, end=2016-10-27 09:03:29, seconds=5, samples_taken=17
SQL> @sw 4294
SID STATE EVENT SEQ# SEC_IN_WAIT P1 P2 P3 P1TRANSL
------- -------------- ------------------------- ---------- ----------- ------------------- ------------------- ------------------- ------------------------------------------
4294 WAITING enq: JI - contention 467 3 name|mode= view object #= 0= 0 0x4A490006: JI mode 6
0x000000004A490006 1105224
SQL> @oid 1105224
owner object_name object_type SUBOBJECT_NAME CREATED LAST_DDL_TANBOBE status DATA_OBJECT_ID
------------------------- ------------------------------ ------------------ --------------------- ----------------- ----------------- --------- --------------
ANBOB ANBOB_INV_SPECIALTELNUM TABLE 20161027 05:06:59 20161027 05:08:30 VALID 1105224
SQL> @o ANBOB.ANBOB_INV_SPECIALTELNUM
owner object_name object_type status OID D_OID CREATED LAST_DDL_TANBOBE
------------------------- ------------------------------ ------------------ --------- ---------- ---------- ----------------- -----------------
ANBOB ANBOB_INV_SPECIALTELNUM MATERIALIZED VIEW VALID 1105225 20161027 05:08:30 20161027 08:47:47
ANBOB ANBOB_INV_SPECIALTELNUM TABLE VALID 1105224 1105224 20161027 05:06:59 20161027 05:08:30
SQL>@ase
USERNAME SID SEQ# EVENT MACHINE MODULE STATUS LAST_CALL_ET SQL_ID SQLTEXT BS
---------- ---------- ---------- -------------------- ---------- -------------------- ---------------- ------------ --------------- ------------------------------ ----------
ANBOB 3670 29401 On CPU / runqueue weejar1901 ANBOBSERV2@weejar1901 (T ACTIVE 2205 59cvxv0b4vqyy /* MV_REFRESH (DEL) */ DELETE :
ANBOB 1769 24351 enq: JI - contention weejar1901 ANBOBSERV2@weejar1901 (T ACTIVE 2211 2:3670
ANBOB 2527 12418 enq: JI - contention weejar1901 ANBOBSERV2@weejar1901 (T ACTIVE 2240 2:3670
ANBOB 4809 58007 enq: JI - contention weejar1502 ANBOBSERV2@weejar1502 (T ACTIVE 2244 2:3670
ANBOB 1404 17308 enq: JI - contention weejar1901 ANBOBSERV2@weejar1901 (T ACTIVE 2283 2:3670
ANBOB 3538 20407 enq: JI - contention weejar1901 ANBOBSERV2@weejar1901 (T ACTIVE 2289 2:3670
ANBOB 2661 616 enq: JI - contention weejar1101 TMS_ANBOB2@weejar1101 (T ACTIVE 2290 2:3670
ANBOB 3040 1017 enq: JI - contention qmnet3 TMS_ANBOB2@qmnet3 (TNS ACTIVE 2306 2:3670
ANBOB 775 6347 enq: JI - contention weejar1901 ANBOBSERV2@weejar1901 (T ACTIVE 2308 2:3670
ANBOB 5175 858 enq: DX - contention qmnet4 TMS_ANBOB2@qmnet4 (TNS ACTIVE 2320 2:2163
ANBOB 3534 39349 enq: JI - contention weejar1001 ANBOBSERV1@weejar1001 (T ACTIVE 2330 2:3670
ANBOB 2407 692 enq: DX - contention weejar1101 TMS_ANBOB2@weejar1101 (T ACTIVE 2376 2:5067
ANBOB 4414 751 enq: DX - contention weejar1901 TMS_ANBOB2@weejar1901 (T ACTIVE 2387 2:4310
ANBOB 1287 8273 enq: JI - contention weejar1501 ANBOBSERV2@weejar1501 (T ACTIVE 2390 2:3670
ANBOB 146 768 enq: JI - contention qmnet3 TMS_ANBOB2@qmnet3 (TNS ACTIVE 2399 2:3670
ANBOB 5067 1016 enq: JI - contention weejar1101 TMS_ANBOB2@weejar1101 (T ACTIVE 2497 2:3670
ANBOB 4298 8255 enq: JI - contention weejar1101 ANBOBSERV2@weejar1101 (T ACTIVE 2505 2:3670
ANBOB 5428 935 enq: JI - contention weejar3501 TMS_ANBOB2@weejar3501 (T ACTIVE 2574 2:3670
ANBOB 5 28829 enq: JI - contention weejar1701 ANBOBSERV2@weejar1701 (T ACTIVE 2622 2:3670
ANBOB 531 704 enq: JI - contention weejar1501 TMS_ANBOB2@weejar1501 (T ACTIVE 2639 2:3670
ANBOB 512 13000 enq: JI - contention weejar1701 ANBOBSERV2@weejar1701 (T ACTIVE 2667 2:3670
SQL> @sed ji
Show wait event descriptions matching %ji%..
EVENT# EVENT_NAME WAIT_CLASS PARAMETER1 PARAMETER2 PARAMETER3 ENQUEUE_NAME REQ_REASON
------ ------------------------------------------------------- -------------------- ------------------------- ------------------------- ------------------------- ------------------------------ --------------------------------
REQ_DESCRIPTION
----------------------------------------------------------------------------------------------------
309 JOX Jit Process Sleep Idle
940 enq: JI - contention Other name|mode view object # 0 Materialized View contention
Lock held during materialized view operations (like refresh, alter) to prevent concurrent
operations on the same materialized view
# MVIEW DDL
CREATE MATERIALIZED VIEW ANBOB.ANBOB_INV_SPECIAL (REGION,xxx,...)
TABLESPACE DATA999
BUILD ANBOBMEDIATE
REFRESH FORCE ON COMMIT
WITH PRANBOBARY KEY
AS
/* Formatted on 2016-10-27 8:57:16 (QP5 v5.256.13226.35510) */
SELECT it.region,
...
-- MOS note
Cause:
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.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.
Solution:
Don’t same time refresh the same Mview object by many sessions.
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。