experienceing the long two phase lock.big suprise!!!

最新推荐文章于 2025-08-23 16:56:09 发布

原创最新推荐文章于 2025-08-23 16:56:09 发布 · 618 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#user #null #jobs #session #sql #object

本文通过查询Oracle数据库中的活动作业、锁定情况及待决的两阶段提交事务，揭示了数据库内部运行状况。展示了如何使用SQL查询来诊断潜在的问题区域，如长时间运行的任务和资源竞争。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

first,
I found there are long job running:
SQL> select * from dba_jobs_running;

       SID        JOB   FAILURES LAST_DATE
---------- ---------- ---------- --------------
LAST_SEC                                         THIS_DATE
------------------------------------------------ --------------
THIS_SEC                                           INSTANCE
------------------------------------------------ ----------
        15        364          9 07-9月 -08
09:25:15                                         09-9月 -08
02:00:02                                                  0

        53        906          6 05-9月 -08
21:43:28                                         08-9月 -08
06:50:11                                                  0

       SID        JOB   FAILURES LAST_DATE
---------- ---------- ---------- --------------
LAST_SEC                                         THIS_DATE
------------------------------------------------ --------------
THIS_SEC                                           INSTANCE
------------------------------------------------ ----------

       170        925          0 08-9月 -08
20:39:51                                         09-9月 -08
01:51:41                                                  0

second,
1 SELECT /*+ RULE */
2 LS.OSUSER OS_USER_NAME,
3 LS.USERNAME USER_NAME,
4 DECODE(LS.TYPE,
5          'RW','ROW WAIT ENQUEUE LOCK',
6          'TM','DML ENQUEUE LOCK',
7          'TX','TRANSACTION ENQUEUE LOCK',
8          'UL','USER SUPPLIED LOCK') LOCK_TYPE,
9 O.OBJECT_NAME OBJECT,
10 DECODE(LS.LMODE,
11          1,NULL,
12          2,'ROW SHARE',
13          3,'ROW EXCLUSIVE',
14          4,'SHARE',
15          5,'SHARE ROW EXCLUSIVE',
16          6,'EXCLUSIVE',
17          NULL) LOCK_MODE,
18 O.OWNER,
19 LS.SID,
20 LS.SERIAL# SERIAL_NUM,
21 LS.ID1,
22 LS.ID2
23 FROM SYS.DBA_OBJECTS O,
24         (SELECT S.OSUSER,
25                 S.USERNAME,
26                 L.TYPE,
27                 L.LMODE,
28                 S.SID,
29                 S.SERIAL#,
30                 L.ID1,
31                 L.ID2
32            FROM V$SESSION S, V$LOCK L
33           WHERE S.SID = L.SID) LS
34 WHERE O.OBJECT_ID = LS.ID1
35     AND O.OWNER <> 'SYS'
36* ORDER BY O.OWNER, O.OBJECT_NAME
OS_USER_NAME
------------------------------------------------------------
USER_NAME
------------------------------------------------------------
LOCK_TYPE
----------------------------------------------------------------
OBJECT
--------------------------------------------------------------------------------
LOCK_MODE
----------------------------------------------------------------
OWNER                                                               SID
------------------------------------------------------------ ----------
SERIAL_NUM        ID1        ID2
---------- ---------- ----------
oracle
CCIM
DML ENQUEUE LOCK
SC_CLIENT_REGISTRATION
ROW EXCLUSIVE
CCIM                                                                 15
      4449      87376          0

oracle
CCIM

SC_CLIENT_REGISTRATION
EXCLUSIVE
CCIM                                                                 15
      4449      87376          0

oracle
SYS

/ea877347_JWindowAccessibleJWi
SHARE
PUBLIC                                                              132
     16294      25622          0

oracle

V$TRANSACTION
SHARE
PUBLIC                                                               45
         1       1023          0

oracle
REPORTER
DML ENQUEUE LOCK
REPORTER_STATUS_MV
ROW EXCLUSIVE
REPORTER                                                             53
      1402      44178          0

oracle
REPORTER

REPORTER_STATUS_MV
EXCLUSIVE
REPORTER                                                             53
      1402      44178          0

oracle
SGREPORTS

SUMMARY
EXCLUSIVE
SGREPORTS                                                           170
     20652      90595          0

oracle
SGREPORTS
DML ENQUEUE LOCK
SUMMARY
ROW EXCLUSIVE
SGREPORTS                                                           170
     20652      90595          0

oracle
SGREPORTS

UI_TYPETOSERVICE
EXCLUSIVE
SGREPORTS                                                           170
     20652      92411          0

third,
SQL> select * from DBA_2PC_PENDING;
LOCAL_TRAN_ID
--------------------------------------------
GLOBAL_TRAN_ID
--------------------------------------------------------------------------------
STATE                            MIXED AD
-------------------------------- ------ --
TRAN_COMMENT
--------------------------------------------------------------------------------
FAIL_TIME      FORCE_TIME     RETRY_TIME
-------------- -------------- --------------
OS_USER
--------------------------------------------------------------------------------
OS_TERMINAL
--------------------------------------------------------------------------------
HOST
--------------------------------------------------------------------------------
DB_USER
------------------------------------------------------------
COMMIT#
--------------------------------
10.29.971712
CNSRPT.NILE002A_ZONE01.EAST.cff4dc5b.10.29.971712
collecting                       no

06-2月 -08                    02-9月 -08
localuser
GROUCHY
MSHOME/GROUCHY
REPORTER
8312022983562

10.33.931719
CNSRPT.NILE002A_ZONE01.EAST.cff4dc5b.10.33.931719
collecting                       no

06-1月 -08                    02-9月 -08
Administrator
MOUNTAINEER
WORKGROUP/MOUNTAINEER
SGREPORTS
8308525688197

10.24.983207
CNSRPT.NILE002A_ZONE01.EAST.cff4dc5b.10.24.983207
collecting                       no

23-2月 -08                    05-9月 -08
oracle
UNKNOWN
nile002a-zone01
SGREPORTS
8314053160664

7.4.216944
CNSRPT.NILE002A_ZONE01.EAST.cff4dc5b.7.4.216944
collecting                       no

07-4月 -08                    06-9月 -08
Administrator
MOUNTAINEER
WORKGROUP/MOUNTAINEER
SGREPORTS
8319660930334

5.27.102183
CNSRPT.NILE002A_ZONE01.EAST.cff4dc5b.5.27.102183
collecting                       no

09-4月 -08                    03-9月 -08
Administrator
MOUNTAINEER
WORKGROUP/MOUNTAINEER
SGREPORTS
8319831100656

7.46.226911
CNSRPT.NILE002A_ZONE01.EAST.cff4dc5b.7.46.226911
collecting                       no

06-6月 -08                    04-9月 -08
Administrator
MOUNTAINEER
WORKGROUP/MOUNTAINEER
REPORTER
8328880245773

The earliest one is 06-1月 -08.
I am shocked by this lock: