Blocking sessions causing performance issues

Worldwide most of DBAs are facing performance issues in OLTP oracle databases. Basically in Oracle 10g / 11g there are more views to analyze when any performance issue call received. AWR / ADDM reports clearly tell what happen at that time. But it depends snap time set. For immediate troubleshot, few queries can be executed to find issues with database while issue is related to database slowness only.


~ Case-: Find blocking sessions and clearing them

Most of cases, blocking sessions create more more performance issues. Find whether what query is blocking, log on time and type of blocking. If select query is blocking, then collect blocking session id, username and sql_id and follow beloe steps.

-- Find BLOCKING SESSION id
select a.INST_ID,a.sid,a.serial#,a.BLOCKING_SESSION,a.username,a.status,a.schemaname,
a.BLOCKING_SESSION_STATUS,a.program,a.sql_id,a.LOGON_TIME,a.EVENT
from gv$session a 
where BLOCKING_SESSION  IS NOT NULL;

-- Use blocking session id, sql_id and username to kill the session
select inst_id,'alter system kill session ''' || SID || ',' || SERIAL# || ',@'||inst_id ||''' IMMEDIATE;'
from gv$session gg
where username=<blocking_username>  
and gg.SID in(<BLOCKING SESSION id>);

Note: Don't kill Insert/ Update/ Delete queries. Find what type of event you are getting and try to understand the issue and take a decision accordingly.

-- Find query from sql_id

select * from gv$sql where sql_id='sql_id';

Note: Take the killing session queries and run it ( run any one of the node in RAC). Don't use @inst_id in case of stand alone database servers.