Mviews hanging sessions and clearing them

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.

Note: Use v$ for stand alone servers and use gv$ for RAC databases.

Case: Mviews hanging sessions and clearing them

Some times refreshing of materialized views may cause the slowness. To find which mviews are refreshing now, below query may used. Killing the mviews session will improve the performance. Some times this issue will come when link between remote database has issue or remote database is high loaded or local database issued with complete refresh for big table etc.

-- To find which mviews are blocking ( along with killing session query) -- In any version of Oracle
select VS.INST_ID,VL.SID||','||VS.SERIAL#,
VS.USERNAME,ao.object_name,
'alter system kill session '''||vl.SID||','||vs.SERIAL#|| ',@'||vs.inst_id ||''' IMMEDIATE;'
  from GV$LOCK VL, Gv$session VS, all_objects ao
 where vl.type = 'JI' and vl.Lmode = 6 and VS.SID=VL.SID
 and VS.INST_ID=VL.inst_id and vl.ID1=ao.object_id;

OR
-- Simple view ( above 10.1.x.x)

select * from v$mvrefresh;

-- Find query from sql_id
select * from gv$sql where sql_id='sql_id';

Note that don't kill any session unless until confirmation from client/ customer and you need to find what exact mview refresh is happening.