Dear Gurus,
Could you give me some explanation why select query could blocked other update sessions, please find below
SID_BLOCKER BLOCKER WAITER
-------------------- --------------------------- ----------------------------------------
Session ID: 22339 in instance 1 is blocking Session ID: 296 in instance 1
Session ID: 22339 in instance 1 is blocking Session ID: 6673 in instance 1
Session ID: 22339 in instance 1 is blocking Session ID: 26091 in instance 1
Elapsed: 00:00:01.77
12:21:45 SQL> @ssid
Elapsed: 00:00:00.00
enter value for sid: 22339
enter value for instance id: 1
SID SERIAL# SERVER_PID CLIENT_PID USERNAME OSUSER STATUS LOGON_TIME PROGRAM MACHINE SYSDATE
------ -------- ------------------------ ------------------------ ----------- ---------- -------- -------------------- ---------- ---------------- --------------------
EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE SQL_ID
------------------------------ -------------------- ---------- --------------- ------------------- -------------
22339 40705 18135 26767 XLAPPC cmwl21 INACTIVE 15-SEP-2013 23:25:35 java@cbtcm cbtcmpap02 18-SEP-2013 12:21:53
pap02 (TNS
V1-V3)
SQL*Net message from client Idle 0 6649 WAITING
Elapsed: 00:00:00.08
PIECE SQL_TEXT
---------- ----------------------------------------------------------------------------------------------------
0 SELECT ch_objects.obj_id, ch_objects.obj_type, ch_objects.obj_ex
1 ternal_id, ch_objects.obj_name, ch_objects.obj_desc, ch_arcs.arc
2 _relation, TO_CHAR (ch_arcs.arc_valid_from, 'YYYYMMDDHH24MISS'),
3 TO_CHAR (ch_arcs.arc_valid_to, 'YYYYMMDDHH24MISS'), TO_CHAR (ch
4 _objects.sys_update_date, 'YYYYMMDDHH24MISS') FROM ch_objects, c
5 h_arcs WHERE ch_arcs.arc_child_id = :m_startObjID AND ch_arcs.ar
6 c_relation IN (:relTypes0:relTypesInd0,:relTypes1:relTypesInd1,:
7 relTypes2:relTypesInd2,:relTypes3:relTypesInd3,:relTypes4:relTyp
8 esInd4,:relTypes5:relTypesInd5) AND ch_arcs.arc_father_id = ch_o
9 bjects.obj_id AND TO_DATE (:m_effectiveTime, 'YYYYMMDDHH24MISS')
10 BETWEEN ch_arcs.arc_valid_from AND ch_arcs.arc_valid_to
Elapsed: 00:00:00.71
12:21:54 SQL> @ssid
Elapsed: 00:00:00.00
enter value for sid: 6673
enter value for instance id: 1
SID SERIAL# SERVER_PID CLIENT_PID USERNAME OSUSER STATUS LOGON_TIME PROGRAM MACHINE SYSDATE
------ -------- ------------------------ ------------------------ ----------- ---------- -------- -------------------- ---------- ---------------- --------------------
EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE SQL_ID
------------------------------ -------------------- ---------- --------------- ------------------- -------------
6673 51927 15196 19846 XLAPPC cmwl61 ACTIVE 16-SEP-2013 18:25:53 java@cbtaa cbtaampap01 18-SEP-2013 12:22:08
mpap01 (TN
S V1-V3)
enq: TX - row lock contention Application 0 2681 WAITING a30z7fqk9sd1u
Elapsed: 00:00:00.03
PIECE SQL_TEXT
---------- ----------------------------------------------------------------------------------------------------
0 update ch_objects set obj_name=:b0,sys_update_date=TO_DATE(:b1,
1 'YYYYMMDDHH24MISS'),operator_id=:b2:b3,application_id=:b4:b5,dl_
2 service_code='30' where obj_id=:b6
Elapsed: 00:00:00.69
12:22:09 SQL> @ssid
Elapsed: 00:00:00.00
enter value for sid: 6673
enter value for instance id: 1
SID SERIAL# SERVER_PID CLIENT_PID USERNAME OSUSER STATUS LOGON_TIME PROGRAM MACHINE SYSDATE
------ -------- ------------------------ ------------------------ ----------- ---------- -------- -------------------- ---------- ---------------- --------------------
EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE SQL_ID
------------------------------ -------------------- ---------- --------------- ------------------- -------------
6673 51927 15196 19846 XLAPPC cmwl61 ACTIVE 16-SEP-2013 18:25:53 java@cbtaa cbtaampap01 18-SEP-2013 12:22:21
mpap01 (TN
S V1-V3)
enq: TX - row lock contention Application 0 2695 WAITING a30z7fqk9sd1u
Elapsed: 00:00:00.03
PIECE SQL_TEXT
---------- ----------------------------------------------------------------------------------------------------
0 update ch_objects set obj_name=:b0,sys_update_date=TO_DATE(:b1,
1 'YYYYMMDDHH24MISS'),operator_id=:b2:b3,application_id=:b4:b5,dl_
2 service_code='30' where obj_id=:b6
Elapsed: 00:00:00.63
12:22:22 SQL> @ssid
Elapsed: 00:00:00.00
enter value for sid: 26091
enter value for instance id: 1
SID SERIAL# SERVER_PID CLIENT_PID USERNAME OSUSER STATUS LOGON_TIME PROGRAM MACHINE SYSDATE
------ -------- ------------------------ ------------------------ ----------- ---------- -------- -------------------- ---------- ---------------- --------------------
EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE SQL_ID
------------------------------ -------------------- ---------- --------------- ------------------- -------------
26091 15759 57714 32378 XLAPPC cmwl11 ACTIVE 17-SEP-2013 14:08:58 java@cbtcm cbtcmpap01 18-SEP-2013 12:22:45
pap01 (TNS
V1-V3)
enq: TX - row lock contention Application 0 2051 WAITING a30z7fqk9sd1u
Elapsed: 00:00:00.04
PIECE SQL_TEXT
---------- ----------------------------------------------------------------------------------------------------
0 update ch_objects set obj_name=:b0,sys_update_date=TO_DATE(:b1,
1 'YYYYMMDDHH24MISS'),operator_id=:b2:b3,application_id=:b4:b5,dl_
2 service_code='30' where obj_id=:b6
Elapsed: 00:00:03.89
Regards,