Oracle Database Discussions

DB version: 19c (Standalone Oracle Restart DB)

Application support complained of slowness with their application. So, I looked into the DB.

Output from Query1,

Query2

and

Enterprise Manager's WebGUI > Performance > Blocking Sessions confirmed that the below session is the
blocking session.

Query1.

SQL> select * from DBA_BLOCKERS;

HOLDING_SESSION     CON_ID
--------------- ----------
          7616          8

Query2.

SELECT
 2    INST_ID,
 3    blocking_session "BLOCKING_SESSION_SID",
 4    sid "BLOCKED_SESSION",
 5    serial# "BLOCKED_SERIAL#",
 6    seconds_in_wait/60 "WAIT_TIME(MINUTES)"
 7  FROM Gv$session
 8  WHERE blocking_session is not NULL
 9  ORDER BY blocking_session;
 
  INST_ID BLOCKING_SESSION_SID BLOCKED_SESSION BLOCKED_SERIAL# WAIT_TIME(MINUTES)
---------- -------------------- --------------- --------------- ------------------
        1                 7616            3036           60110         14.3833333

But the above session is running a regular SELECT query (2wp47gtp1zknw) like below.
The blocked session however, was running a query with FOR UPDATE clause which means this session was hold an exclusive row level lock. So, shouldn't this session be the blocking session ?

But, as soon as I killed the session with SID :7616 and Serial# 60491, Query 1 and Query2 above returned now rows. Which means no more blocking sessions in the DB.

But, as far as I know, a regular SELECT does not cause locks. Am I missing something here ?

Blocking session with SID :7616 and Serial# 60491
Blocking SQL: 2wp47gtp1zknw

select LID_KEY_RNR ,LEG_KEY_MODNAME ,LEG_KEY_SEQNR ,LEG_MODNR ,LEG_BATCH_DAT ,LEG_BATCH_PROGID ,LEG_ONL_DAT ,LEG_ONL_PROGID ,LEG_ONL_PERSNR ,LEG_DEL_PDL ,LEG_SEXE ,LEG_NAISSANCE ,LEG_NOM ,LEG_PRENOMS ,LEG_COMPLET ,LEG_CODECOMM ,LEG_COMMUNE ,LEG_IND_COM ,LEG_DIPLO ,LEG_STRAAT ,LEG_CODE_STR ,LEG_HUISNR ,LEG_IND ,LEG_POSTAL ,LEG_RESTE_ADR ,LEG_NAT ,LEG_BS ,LEG_RNR_CONJ ,LEG_CODE_MODIF ,LEG_DATDEC ,LEG_DATETC ,LEG_DATNAT ,LEG_DATADR ,LEG_DATPRI ,LEG_DATREP ,LEG_DATCHG ,LEG_CO ,LEG_RNR_CO ,LEG_DATCOH ,rowid
from PKTMS_LEG
where ((MUTFILE=:b1 and LID_MOD128=:b2) and LID_KEY_RNR>=:b3) 
order by MUTFILE,LID_MOD128,LID_KEY_RNR,LEG_KEY_SEQNR


Blocked SQL: 4d27gdnsbt0s1

select LID_HPP_DAT_WIJZNR ,LID_HPP_DAT_MODSEQ into :b1,:b2
from PKTMS_LID
where rowid =:b3 for update;


SQL> select sid, serial#, sql_id from v$session where sid = 7616;

      SID    SERIAL# SQL_ID
---------- ---------- -------------
     7616      60491 2wp47gtp1zknw

SQL> alter system kill session '7616,60491' immediate;

System altered.

SQL> select * from DBA_BLOCKERS;

no rows selected


SELECT
 2    INST_ID,
 3    blocking_session "BLOCKING_SESSION_SID",
 4    sid "BLOCKED_SESSION",
 serial# "BLOCKED_SERIAL#",
 seconds_in_wait/60 "WAIT_TIME(MINUTES)"
 6    7  FROM Gv$session
 8  WHERE blocking_session is not NULL
 9  ORDER BY blocking_session;
 
no rows selected