Blocking session on RAC database

Blocking session on RAC database

Blocking session of the databases

Generate the report for Blocking session

The SQL will provide Blocking sessions with SID, node information

SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE
|| ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING '
|| S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' 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 S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID
AND L1.BLOCK > 0 AND L2.REQUEST > 0
AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2;

Blockers information

select s1.username || '@' || s1.machine  || ' ( 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 ;
---------------------------------------------

select b.session_id ,a.SERIAL#, a.username "Blocker Details"
from gv$session a,dba_lock b
where b.session_id = a.sid
and b.blocking_others = 'Blocking';
--------------------------------

 select distinct 'alter system kill session '''||s1.sid||','||s1.serial#||',@'||s1.inst_id||''' immediate;'
  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
  order by 1;


You can determine what type of Oracle process this is by querying the data dictionary:
SELECT
'USERNAME : ' || s.username || CHR(10) ||
'SCHEMA : ' || s.schemaname || CHR(10) ||
'OSUSER : ' || s.osuser || CHR(10) ||
'PROGRAM : ' || s.program || CHR(10) ||
'SPID : ' || p.spid || CHR(10) ||
'SID : ' || s.sid || CHR(10) ||
'SERIAL# : ' || s.serial# || CHR(10) ||
'KILL STRING: ' || '''' || s.sid || ',' || s.serial# || '''' || CHR(10) ||
'MACHINE : ' || s.machine || CHR(10) ||
'TYPE : ' || s.type || CHR(10) ||
'TERMINAL : ' || s.terminal || CHR(10) ||
'SQL ID : ' || q.sql_id || CHR(10) ||
'SQL TEXT : ' || q.sql_text
FROM v$session s
,v$process p
,v$sql q
WHERE s.paddr = p.addr
AND p.spid = '&&PID_FROM_OS'
AND s.sql_id = q.sql_id(+);

session details – any connection from Developer tool

set linesize 200
col MACHINE format a30
col OSUSER format a10
col SCHEMANAME format a10
col MODULE format a20

Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss'),osuser,status,schemaname,machine,SID,module,INST_ID from gv$session where type !='BACKGROUND' and module like '%Developer' order by logon_time asc;

Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss'),osuser,status,schemaname,machine,SID,module, INST_ID from gv$session where type !='BACKGROUND' and USERNAME='&a' order by logon_time asc;

Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss'),osuser,status,schemaname,machine,SID,module, INST_ID from gv$session where type !='BACKGROUND' and SID='&a' order by logon_time asc;



Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss'),osuser,status,schemaname,machine,SID,module, INST_ID from gv$session where type !='BACKGROUND'  order by logon_time asc;

Detail report  - the blocking session for the particular session

set lines 200
set pages 1000
select inst_id,sid,serial#,sql_id,prev_sql_id,sql_hash_value,status,osuser,username,program,machine,last_call_et,to_char(logon_time,'DD/MON/YYYY hh24:mi') logon_time
from gv$session
where sid in (&sid)
order by inst_id;

SQL information

select s.sid, s.sql_id, q.sql_text from gv$sqltext q, gv$session s
where q.address = s.sql_address
and s.sid = &sid
order by piece;

Kill the sessions

Command to kill the session: 
---------------------------
ALTER SYSTEM KILL SESSION 'sid, serial#';

ALTER SYSTEM KILL SESSION 'sid, serial#' immediate;

ALTER SYSTEM KILL SESSION 'sid, serial#,@<instance_id>';  (RAC)



-- SID
select p.SPID, substr(s.sid,1,6) SID, substr(s.serial#,1,8) SERIAL , s.status STATUS,
substr(s.username,1,10) USERNAME , substr(s.osuser,1,10) OSUSER,
to_char(s.logon_time,'dd-mm-yyyy hh24:mi') LOGON_TIME, s.program PROGRAM
from gv$session s , gv$process p
where s.paddr = p.addr
and s.sid = &sid;

select p.SPID, s.process CPID, substr(s.sid,1,6) SID, substr(s.serial#,1,8) SERIAL , s.status STATUS,
substr(s.username,1,10) USERNAME , substr(s.osuser,1,10) OSUSER,
to_char(s.logon_time,'dd-mm-yyyy hh24:mi') LOGON_TIME, s.program PROGRAM
from gv$session s , gv$process p
where s.paddr = p.addr
and s.program like '%sqlplus%'

select p.SPID, s.process CPID, substr(s.sid,1,6) SID, substr(s.serial#,1,8) SERIAL , s.status STATUS,
substr(s.username,1,10) USERNAME , substr(s.osuser,1,10) OSUSER,
to_char(s.logon_time,'dd-mm-yyyy hh24:mi') LOGON_TIME,
round(s.last_call_et/60) IDLE_MIN, s.program PROGRAM
from gv$session s , gv$process p
where s.paddr = p.addr
and s.program like '%sqlplus%' ;

select p.SPID, s.process CPID, substr(s.sid,1,6)||','||substr(s.serial#,1,8) SIDSERIAL , s.status STATUS,
to_char(s.logon_time,'dd-mm-yyyy hh24:mi') LOGON_TIME, round(s.last_call_et/60) IDLE_MIN,
substr(s.username,1,10) USERNAME , substr(s.osuser,1,10) OSUSER,
s.program PROGRAM
from gv$session s , gv$process p
where s.paddr = p.addr
and s.username IS not null
and s.username not in ('SYS')
order by IDLE_MIN desc;

Long running

column username format 'a10'
column osuser format 'a10'
column module format 'a16'
column program_name format 'a20'
column program format 'a20'
column machine format 'a20'
column action format 'a20'
column sid format '9999'
column serial# format '99999'
column spid format '99999'
set linesize 200
set pagesize 30
select
a.sid,a.serial#,a.username,a.osuser,c.start_time,
b.spid,a.status,a.machine,
a.action,a.module,a.program
from
gv$session a, gv$process b, gv$transaction c,
gv$sqlarea s
Where
a.paddr = b.addr
and a.saddr = c.ses_addr
and a.sql_address = s.address (+)
and to_date(c.start_time,'mm/dd/yy hh24:mi:ss') <= sysdate - (60/1440) -- running for 60 minutes
order by c.start_time
/

Notification Text: The following list of SQL statements have been running for over 60 minutes.

SQL Statement:

select 'SID:'||s.sid||', Serial#:'||s.serial#||', Username:'||s.username||', Machine:'||s.machine||
       ', Program:'||s.program||', HashValue:'||s.sql_hash_value||', SQL Text:'||nvl(substr(sql.sql_text,1,40),'Unknown SQL'), last_call_et
from v$session s
left outer join v$sql sql on sql.hash_value=s.sql_hash_value and sql.address=s.sql_address
where s.status='ACTIVE'
and s.type <> 'BACKGROUND'

and last_call_et >= 3600

SID detail information 

col sid format 9999
col username format a10
col osuser format a10
col program format a25
col process format 9999999
col spid format 999999
col logon_time format a13

set lines 150

set heading off
set verify off
set feedback off

undefine sid_number
undefine spid_number
accept sid_number number prompt "pl_enter_sid:"

col sid NEW_VALUE sid_number noprint
col spid NEW_VALUE spid_number noprint


         select  s.sid   sid,
                p.spid  spid
--              ,decode(count(*), 1,'null','No Session Found with this info') " "
         FROM v$session s,
              v$process p
         WHERE s.sid LIKE NVL('&sid', '%')
         AND p.spid LIKE NVL ('&OS_ProcessID', '%')
         AND s.process LIKE NVL('&Client_Process', '%')
         AND s.paddr = p.addr
--       group by s.sid, p.spid;

PROMPT Session and Process Information
PROMPT -------------------------------

col event for a30

select '    SID                         : '||v.sid      || chr(10)||
       '    Serial Number               : '||v.serial#  || chr(10) ||
       '    Oracle User Name            : '||v.username         || chr(10) ||
       '    Client OS user name         : '||v.osuser   || chr(10) ||
       '    Client Process ID           : '||v.process  || chr(10) ||
       '    Client machine Name         : '||v.machine  || chr(10) ||
       '    Oracle PID                  : '||p.pid      || chr(10) ||
       '    OS Process ID(spid)         : '||p.spid     || chr(10) ||
       '    Session''s Status           : '||v.status   || chr(10) ||
       '    Logon Time                  : '||to_char(v.logon_time, 'MM/DD HH24:MIpm')   || chr(10) ||
       '    Program Name                : '||v.program  || chr(10)
from v$session v, v$process p
where v.paddr = p.addr
and v.serial# > 1
and p.background is null
and p.username is not null
and sid = &sid_number
order by logon_time, v.status, 1
/


PROMPT Sql Statement
PROMPT --------------

select sql_text
from v$sqltext , v$session
where v$sqltext.address = v$session.sql_address
and sid = &sid_number
order by piece
/

PROMPT
PROMPT Event Wait Information
PROMPT ----------------------

select '   SID '|| &sid_number ||' is waiting on event  : ' || x.event || chr(10) ||
       '   P1 Text                      : ' || x.p1text || chr(10) ||
       '   P1 Value                     : ' || x.p1 || chr(10) ||
       '   P2 Text                      : ' || x.p2text || chr(10) ||
       '   P2 Value                     : ' || x.p2 || chr(10) ||
       '   P3 Text                      : ' || x.p3text || chr(10) ||
       '   P3 Value                     : ' || x.p3
from v$session_wait x
where x.sid= &sid_number
/

PROMPT
PROMPT Session Statistics
PROMPT ------------------

select        '     '|| b.name  ||'             : '||decode(b.name, 'redo size', round(a.value/1024/1024,2)||' M', a.value)
from v$session s, v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and name in ('redo size', 'parse count (total)', 'parse count (hard)', 'user commits')
and s.sid = &sid_number
and a.sid = &sid_number
--order by b.name
order by decode(b.name, 'redo size', 1, 2), b.name
/

COLUMN USERNAME FORMAT a10
COLUMN status FORMAT a8
column RBS_NAME format a10

PROMPT
PROMPT Transaction and Rollback Information
PROMPT ------------------------------------

select        '    Rollback Used                : '||t.used_ublk*8192/1024/1024 ||' M'          || chr(10) ||
              '    Rollback Records             : '||t.used_urec        || chr(10)||
              '    Rollback Segment Number      : '||t.xidusn           || chr(10)||
              '    Rollback Segment Name        : '||r.name             || chr(10)||
              '    Logical IOs                  : '||t.log_io           || chr(10)||
              '    Physical IOs                 : '||t.phy_io           || chr(10)||
              '    RBS Startng Extent ID        : '||t.start_uext       || chr(10)||
              '    Transaction Start Time       : '||t.start_time       || chr(10)||
              '    Transaction_Status           : '||t.status
FROM v$transaction t, v$session s, v$rollname r
WHERE t.addr = s.taddr
and r.usn = t.xidusn
and s.sid = &sid_number
/

PROMPT
PROMPT Sort Information
PROMPT ----------------

column username format a20
column user format a20
column tablespace format a20

SELECT        '    Sort Space Used(8k block size is asssumed    : '||u.blocks/1024*8 ||' M'             || chr(10) ||
              '    Sorting Tablespace                           : '||u.tablespace       || chr(10)||
              '    Sort Tablespace Type                 : '||u.contents || chr(10)||
              '    Total Extents Used for Sorting               : '||u.extents
FROM v$session s, v$sort_usage u
WHERE s.saddr = u.session_addr
AND s.sid = &sid_number
/


set heading on
set verify on

clear column



directly query data dictionary views such as V$SQL to determine which SQL statements
are using excessive I/O—for example:
SELECT *
FROM
(SELECT
parsing_schema_name
,direct_writes
,SUBSTR(sql_text,1,75)
,disk_reads
FROM v$sql
ORDER BY disk_reads DESC)
WHERE rownum < 20;
To determine which sessions are currently waiting for I/O resources, query V$SESSION:
SELECT
username
,program
,machine
,sql_id
FROM v$session
WHERE event LIKE 'db file%read';

You can determine what type of Oracle process this is by querying the data dictionary:
SELECT
'USERNAME : ' || s.username || CHR(10) ||
'SCHEMA : ' || s.schemaname || CHR(10) ||
'OSUSER : ' || s.osuser || CHR(10) ||
'PROGRAM : ' || s.program || CHR(10) ||
'SPID : ' || p.spid || CHR(10) ||
'SID : ' || s.sid || CHR(10) ||
'SERIAL# : ' || s.serial# || CHR(10) ||
'KILL STRING: ' || '''' || s.sid || ',' || s.serial# || '''' || CHR(10) ||
'MACHINE : ' || s.machine || CHR(10) ||
'TYPE : ' || s.type || CHR(10) ||
'TERMINAL : ' || s.terminal || CHR(10) ||
'SQL ID : ' || q.sql_id || CHR(10) ||
'SQL TEXT : ' || q.sql_text
FROM v$session s
,v$process p
,v$sql q
WHERE s.paddr = p.addr
AND p.spid = '&&PID_FROM_OS'
AND s.sql_id = q.sql_id(+);


the process is running a SQL statement, further details about the query can be
extracted by generating an execution plan:
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('&&sql_id')));


SELECT sid, sql_text FROM v$sql_monitor
WHERE status = 'EXECUTING';

see all executions for a given query (based on the SQL_ID column), we can get that
information by querying on the three necessary columns to drill to a given execution of a SQL query:

SELECT * FROM (
SELECT sql_id, to_char(sql_exec_start,'yyyy-mm-dd:hh24:mi:ss') sql_exec_start,
sql_exec_id, sum(buffer_gets) buffer_gets,
sum(disk_reads) disk_reads, round(sum(cpu_time/1000000),1) cpu_secs
FROM v$sql_monitor
WHERE sql_id = 'fcg00hyh7qbpz'
GROUP BY sql_id, sql_exec_start, sql_exec_id
ORDER BY 6 desc)
WHERE rownum <= 5;

top five recent SQL statements that performed the highest disk I/O, you could
issue the following query:
SELECT sql_text, disk_reads FROM
(SELECT sql_text, buffer_gets, disk_reads, sorts,
cpu_time/1000000 cpu, rows_processed, elapsed_time
FROM v$sqlstats
ORDER BY disk_reads DESC)
WHERE rownum <= 5;

SELECT sid, buffer_gets, disk_reads, round(cpu_time/1000000,1) cpu_seconds
FROM v$sql_monitor
WHERE SID=100
AND status = 'EXECUTING';

the top five most CPU-consuming queries in your database, you could
issue the following query:
SELECT * FROM (
SELECT sid, buffer_gets, disk_reads, round(cpu_time/1000000,1) cpu_seconds
FROM v$sql_monitor
ORDER BY cpu_time desc)
WHERE rownum <= 5;

If you join V$SQLSTATS to V$SQL, you can see information for particular users. See the
following example:
SELECT schema, sql_text, disk_reads, round(cpu,2) FROM
(SELECT s.parsing_schema_name schema, t.sql_id, t.sql_text, t.disk_reads,
t.sorts, t.cpu_time/1000000 cpu, t.rows_processed, t.elapsed_time
FROM v$sqlstats t join v$sql s on(t.sql_id = s.sql_id)
WHERE parsing_schema_name = 'SCOTT'
ORDER BY disk_reads DESC)
WHERE rownum <= 5;


SQL> set autotrace on
Then, you can run a query using AUTOTRACE, which will show the execution plan and query execution
statistics for your query:
SELECT last_name, first_name
FROM employees NATURAL JOIN departments
WHERE employee_id = 101;
LAST_NAME FIRST_NAME
------------------------- --------------------
Kochhar Neena
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 2 (0)| 00:00:01
| 1 | NESTED LOOPS | | 1 | 33 | 2 (0)| 00:00:01
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 26 | 1 (0)| 00:00:01
|* 3 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01
|* 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 11 | 77 | 1 (0)| 00:00:01
|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01
--------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
490 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)

1 rows processed