Oracle Blocking Sessions and Lock Scripts -2

Hi,

While you are using your program which is connected to Oracle database, you can feel slowness on application related with Oracle database. This problem is probably related with blocking session and database lock.

You can find momentarily blocking session and lock with below scripts.

select s1.username || '@' || s1.machine
|| ' ( THIS SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

You can generate Kill script of Blocking sessions like below.

SELECT 
'alter system kill session ''' || SID || ',' || s.serial# || ',@'||inst_id||''';',sid,username,serial#,process,NVL (sql_id, 0),
sql_address,blocking_session,wait_class,event,p1,p2,p3,seconds_in_wait
FROM gv$session s WHERE blocking_session_status = 'VALID'
OR sid IN (SELECT blocking_session
FROM gv$session WHERE blocking_session_status = 'VALID');

query result will be like below if you have blocking session in your database.

alter system kill session '1,39390,@1';
alter system kill session '2,2536,@1';
alter system kill session '3,26324,@1';

then run above script in new session and kill these blocking sessions to solve problem.

Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.

https://ittutorial.org/oracle-database-performance-tuning-tutorial-12-what-is-the-automatic-sql-tuning-and-how-to-automated-sql-tuning/