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;
L
ong 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
