Analyzing Performance

Finding the reason why sessions are queued.

COL EVENT FORMAT a50;
COL WAIT_CLASS FORMAT a30;
COL WAIT_TIME FORMAT 999;
COL SECONDS_IN_WAIT FORMAT 999;
COL STATE FORMAT a20;
COL SID FORMAT 9999;
Set linesize 1000
select EVENT, WAIT_CLASS, WAIT_TIME, SECONDS_IN_WAIT, STATE, SID from v$session_wait
where wait_class &lt;&gt; 'Idle'
order by seconds_in_wait desc;

COL EVENT FORMAT a50;

COL WAIT_CLASS FORMAT a30;

COL WAIT_TIME FORMAT 999;

COL SECONDS_IN_WAIT FORMAT 999;

COL STATE FORMAT a20;

COL SID FORMAT 9999;

Set linesize 1000

select EVENT, WAIT_CLASS, WAIT_TIME, SECONDS_IN_WAIT, STATE, SID from v$session_wait

where wait_class <> 'Idle'

order by seconds_in_wait desc;

Block Change

COL SERIAL FORMAT 9999;
COL program FORMAT a80 ;
COL block_changes FORMAT 99999999 ;
set linesize 1000 ;
SELECT s.SID, s.serial#, s.username, s.program, i.block_changes
FROM v$session s, v$sess_io i
WHERE s.SID = i.SID
and
i.block_changes &gt; 0
ORDER BY i.block_changes DESC, s.SID, s.serial#, s.username, s.program;

COL SERIAL FORMAT 9999;

COL program FORMAT a80 ;

COL block_changes FORMAT 99999999 ;

set linesize 1000 ;

SELECT s.SID, s.serial#, s.username, s.program, i.block_changes

FROM v$session s, v$sess_io i

WHERE s.SID = i.SID

and

i.block_changes > 0

ORDER BY i.block_changes DESC, s.SID, s.serial#, s.username, s.program;

RedoLog Switch Frequency

COL DAY FORMAT a15;
COL HOUR FORMAT a4;
COL TOTAL FORMAT 999;
SELECT TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY,
TO_CHAR(FIRST_TIME,'HH24') HOUR,
COUNT(*) TOTAL
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')
ORDER BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')
ASC;

COL DAY FORMAT a15;

COL HOUR FORMAT a4;

COL TOTAL FORMAT 999;

SELECT TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY,

TO_CHAR(FIRST_TIME,'HH24') HOUR,

COUNT(*) TOTAL

FROM V$LOG_HISTORY

GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')

ORDER BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')

ASC;

Getting the sid (system ID) and serial of the locked and the locker.

select sid, serial#
from v$session
where sid IN (select holding_session from dba_waiters);s

select sid, serial#

from v$session

where sid IN (select holding_session from dba_waiters);s

Finding the users awaiting a lock and kill immediate.

Select 'alter system kill session '''||sid||','||serial#||''' immediate ;' from v$session where lockwait is not null order by osuser;

1	Select 'alter system kill session '''\|\|sid\|\|','\|\|serial#\|\|''' immediate ;' from v$session where lockwait is not null order by osuser;

Viewing the locks

select * from dba_blockers
select * from dba_waiters (wating_session, holding_session)

1 2	select * from dba_blockers select * from dba_waiters (wating_session, holding_session)

select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request &gt; 0
and l1.id1=l2.id1
and l1.id2=l2.id2

select l1.sid, ' IS BLOCKING ', l2.sid

from v$lock l1, v$lock l2

where l1.block =1 and l2.request > 0

and l1.id1=l2.id1

and l1.id2=l2.id2

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

select s1.username || '@' || s1.machine

|| ' ( SID=' || s1.sid || ' ) is blocking '

|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status

from v$lock l1, v$session s1, v$lock l2, v$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 row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
* from v$session where sid=479 ;

1 2	select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# * from v$session where sid=479 ;

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';

SET LINESIZE 100

COLUMN spid FORMAT A10

COLUMN username FORMAT A10

COLUMN program FORMAT A45

SELECT s.inst_id,

s.sid,

s.serial#,

p.spid,

s.username,

s.program

FROM gv$session s

JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE s.type != 'BACKGROUND';

ALTER SYSTEM KILL SESSION '115,15268' immediate;

1	ALTER SYSTEM KILL SESSION '115,15268' immediate;

ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

1	ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

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

1	ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

1	ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;s

1	ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;s

alter system disconnect session '30,7';

1	alter system disconnect session '30,7';

orakill ORACLE_SID spid
kill -9 spid
ps -ef | grep ora

orakill ORACLE_SID spid

kill -9 spid

ps -ef | grep ora

vmstat 2

vmstat 2

sar -u 2 5
sar -P ALL
sar -d 2
sar -d | grep dev3s
sar -r -n DEV -f /var/log/sa/sa16 -- write to the sa16 file
sar -n DEV | more

sar -u 2 5

sar -P ALL

sar -d 2

sar -d | grep dev3s

sar -r -n DEV -f /var/log/sa/sa16 -- write to the sa16 file

sar -n DEV | more