首页 » ORACLE » 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.

打赏

对不起,这篇文章暂时关闭评论。