[oracle] 锁 enq: TX

ASH 报表显示 enq: TX - allocate ITL entry

SQL> SELECT D.SQL_ID, COUNT(1)
FROM DBA_HIST_ACTIVE_SESS_HISTORY D 
WHERE D.SAMPLE_TIME BETWEEN TO_DATE('2020-04-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2020-04-09 18:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND D.EVENT = 'enq: TX - row lock contention' GROUP BY D.SQL_ID ; 

SQL_ID          COUNT(1)
------------- ----------
4bvv7w64bnzwg          2
a3w48gj94fxfj        222
gst615mfyydun          2
42844jbz4b6vc          5
9juv9rhatjttq          6
6kkyrb1urfzqk          1
fmtaha5s2nk40          1
2nhq6cw8586qg         13
fqyhfw5h5rs5q        512

9 rows selected.

SQL> SELECT D.SQL_ID, COUNT(1),D.CURRENT_OBJ#
FROM DBA_HIST_ACTIVE_SESS_HISTORY D
WHERE D.SAMPLE_TIME BETWEEN TO_DATE('2020-04-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2020-04-09 18:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND D.EVENT = 'enq: TX - row lock contention' GROUP BY D.SQL_ID ,D.CURRENT_OBJ#; 

SQL_ID          COUNT(1) CURRENT_OBJ#
------------- ---------- ------------
fqyhfw5h5rs5q       1449       607732
fmtaha5s2nk40          9       691775
009vt6w9376c3          8       378684
gst615mfyydun         18       444201
9juv9rhatjttq         44       573144
6kkyrb1urfzqk         19      1352415
4bvv7w64bnzwg        152       691760
2ct0w6dqyy1td          2       444201
42844jbz4b6vc        609       725534
2nhq6cw8586qg         26       676920
f0srg6qs7fu3y         77       378657
dykx4b1j17cwr         14       609817
dqq7b878ypa9s        130       676920
g6jpmdvsr33vh          6       568833
9uy5xvft9z6xk         20       676920
32frpd234vfwf         19       718002
a3w48gj94fxfj        295       607590
14t9uk1vj3zg0         63      1273230
49apy9s4jmyu3         21       607590
49apy9s4jmyu3          2           -1

20 rows selected.


SQL> SELECT D.SQL_ID,CHR(BITAND(P1, -16777216) / 16777215) || CHR(BITAND(P1, 16711680) / 65535) "Lock",BITAND(P1, 65535) "Mode",
 COUNT(1),COUNT(DISTINCT d.session_id ) 
 FROM DBA_HIST_ACTIVE_SESS_HISTORY D 
 WHERE D.SAMPLE_TIME BETWEEN TO_DATE('2020-04-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2020-04-09 18:00:00', 'YYYY-MM-DD HH24:MI:SS') 
 AND D.EVENT = 'enq: TX - row lock contention' 
 GROUP BY D.SQL_ID,(CHR(BITAND(P1, -16777216) / 16777215) || CHR(BITAND(P1, 16711680) / 65535)),(BITAND(P1, 65535));

SQL_ID        Lock         Mode   COUNT(1) COUNT(DISTINCTD.SESSION_ID)
------------- ------ ---------- ---------- ---------------------------
14t9uk1vj3zg0 TX              6         63                          29
2nhq6cw8586qg TX              6         26                           4
gst615mfyydun TX              6         18                          15
49apy9s4jmyu3 TX              4         23                           3
9uy5xvft9z6xk TX              6         20                           3
32frpd234vfwf TX              6         19                          17
dqq7b878ypa9s TX              6        130                          19
2ct0w6dqyy1td TX              4          2                           2
f0srg6qs7fu3y TX              6         77                           2
42844jbz4b6vc TX              6        609                          24
fqyhfw5h5rs5q TX              6       1449                          21
9juv9rhatjttq TX              6         44                          10
g6jpmdvsr33vh TX              6          6                           1
009vt6w9376c3 TX              6          8                           1
4bvv7w64bnzwg TX              6        152                          63
6kkyrb1urfzqk TX              6         19                          12
dykx4b1j17cwr TX              6         14                           8
a3w48gj94fxfj TX              6        295                          23
fmtaha5s2nk40 TX              6          9                           8

19 rows selected.



SQL> select distinct current_obj# from
(SELECT  D.current_obj#,D.current_file#,D.current_block#,D.current_row#,D.EVENT,D.P1TEXT,D.P1,D.P2TEXT,
D.P2,CHR(BITAND(P1, -16777216) / 16777215) || CHR(BITAND(P1, 16711680) / 65535) "Lock",BITAND(P1, 65535) "Mode",
D.BLOCKING_SESSION,D.BLOCKING_SESSION_STATUS,D.BLOCKING_SESSION_SERIAL#,D.SQL_ID,TO_CHAR(D.SAMPLE_TIME, 'YYYYMMDDHH24MISS') SAMPLE_TIME
FROM DBA_HIST_ACTIVE_SESS_HISTORY D
WHERE D.SAMPLE_TIME BETWEEN TO_DATE('2020-04-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND  TO_DATE('2020-04-09 18:00:00', 'YYYY-MM-DD HH24:MI:SS') 
AND D.EVENT = 'enq: TX - row lock contention' ) t ; 
 
CURRENT_OBJ#
------------
      609817
      573144
      691775
          -1
      676920
      718002
      607590
      568833
     1273230
      725534
      378684
     1352415
      444201
      607732
      691760
      378657

16 rows selected.


SQL> select OBJECT_NAME,STATUS,NAMESPACE,OBJECT_ID from dba_objects where OBJECT_ID='573144' or OBJECT_ID='691775' or OBJECT_ID='676920' 
or OBJECT_ID='607590'  or OBJECT_ID='725534' or OBJECT_ID='1352415' or OBJECT_ID='444201' or OBJECT_ID='607732' or OBJECT_ID='691760'

OBJECT_NAME                    STATUS   NAMESPACE  OBJECT_ID
------------------------------ ------- ---------- ----------
NRIV                           VALID            1     444201
/REX/R_PP_STZU                 VALID            1     573144
LAGP                           VALID            1     607590
LEIN                           VALID            1     607732
DBVM                           VALID            1     676920
LTAP                           VALID            1     691760
LTBP                           VALID            1     691775
EKPO                           VALID            1     725534
/RBR1/D_SD_HDRIN               VALID            1    1352415

9 rows selected.