Transcript Blocking Locks - Northern California Oracle Users Group

Blocking Locks
Vil Roufchaie
www.dbpulsegroup.com
What Will Be Covered In This
Presentation?





In this presentation I will briefly point out what lock are
and how they operate in an Oracle database
Several locking and blocking lock examples will be
discussed
Several useful SQL scripts to demonstrate blocking locks
and how to terminate all such locks will also be
presented
The details of Oracle locks are beyond the scope of his
presentation
This presentation will enable you to address
your locking issues at work with efficacy and
more confidence
7/20/2015
www.dbpulsegroup.com
2
Users Often Call & Scream:






Why is the database so slow!
My program is going nowhere
Don’t understand why my program is blocking
other programs?
Please kill these blocking sessions, they are
making my [life miserable] program slow…
Please kill my programs ASAP, they are being
[unkind to me] blocked
DBAs/Managers must 1st investigate before
taking any actions
7/20/2015
www.dbpulsegroup.com
3
Information Is Power!

You (DBA/Mgr) doesn’t know:







What database/Unix privileges the users may possess?
And what actions they may have already taken?
And whether they’ve already killed the SID they are calling you
about – Oracle quickly re-assigns SIDs, and if you kill that SID
without probing 1st?
What information they are not sharing with you?
Whether they know the consequences of what they are asking
for?
Whether they know what they are really asking you to do for
them?
You (DBA/Mgr.) must probe into the situation and figure
out what’s been happening…
7/20/2015
www.dbpulsegroup.com
4
Why Is Locking An Important
Subject





From a user’s perspective, her/his application is the most
important on the system
She may feel that her application will be the only
program on the database,
and hence system resources may not get allocated and
utilized wisely in a program’s development phase…
The truth of the matter is, there are almost always many
other applications that are running in the database
system concurrently that require system resources…
Locking provides synchronized access to Oracle
resources
7/20/2015
www.dbpulsegroup.com
5
Why Is Locking An important
Subject

Unless care is taken in programming these
applications, particularly how locks get utilized,
 and the granularity with which they are
implemented, which affects concurrency of data
access,
 a database can come to a stand-still due to
what’s commonly termed as: Blocking Locks
• Example:
A long-running transaction takes a shared mode lock on a frequently
accessed table, and all subsequent updates will be enqueued
waiting for the lock taken by the 1st transaction to become free
7/20/2015
www.dbpulsegroup.com
6
What Must A DBA know

Being knowledgeable of:



Being able to determine:




locked objects (tables),
relevant data blocks,
locked rows
is extremely important in a time-sensitive situation
The ability to identify:



locks,
blocking locks,
the session blocking other sessions
the blocked sessions
Having the confidence to kill


The correct blocking/blocked sessions
And not the wrong sessions!
7/20/2015
www.dbpulsegroup.com
7
Oracle Locks

A database without a locking system is like a city with roads that are
void of traffic signals!
DBMS


Lock
Record Size
(bytes)
Where
Held
Oracle
40
Database
Block
DB2
250
RAM
Microsoft
100
RAM
Oracle uses locks to control concurrent access to shared resources
Oracle Holds lock records in the database inside block headers where
locked rows resides in what’s called:
“Interested Transaction List” - ITL
7/20/2015
www.dbpulsegroup.com
8
Lock Types





DML Locks:
Select for update, Delete, Insert, Update
DDL Locks:
Create , Drop, Alter, etc.
Internal Locks and Latches:
Light-wait serialization mechanism
Distributed Locks:
(OPS cache consistency)
PCM Locks (used for cached blocked in OPS)
7/20/2015
www.dbpulsegroup.com
9
DML Locks


TX (transaction) Locks
Row-level locks are held automatically by
oracle inside a transaction for DML (Select,
Delete, Insert, Update) operations on a row, and
other transactions will have to wait until
this transaction either commits or rolls back
TM (Table/DML Enqueue) Locks
Are used to lock a table during DML operations so
table structure may not get changed from under
while these operations are underway. No table
“create”, “alter” or “drop” command will be
allowed while this lock is being held
7/20/2015
www.dbpulsegroup.com
10
DDL Locks



1.
2.
3.
DDL lock:
Is placed during DDL operation and is
released immediately upon completion
(DDL statements Are preceded by a
“commit”)
DDL Lock Types:
Exclusive DDL Locks
Share DDL Locks
Breakable Parse Locks
7/20/2015
www.dbpulsegroup.com
11
What’s A Transaction?
 To
understand a database, it is essential to
know how it manages it’s transactions
 A transaction is made up of one or more
SQL operations
 A transaction starts with a read/write
operation and ends with a commit/rollback
statement
7/20/2015
www.dbpulsegroup.com
12
What’s A Transaction?
To
manage a database is to
manage it’s workload of: short
and long transactions
These transaction hit the
database at any time - serially
or concurrently at will
7/20/2015
www.dbpulsegroup.com
13
Transaction Process
 Transaction
locks are maintained through
the life of the transaction
 All locks and resources will be released as
a transaction ends
7/20/2015
www.dbpulsegroup.com
14
Transactions & Concurrency


1.
2.
3.
4.
Oracle maintains a high degree of transaction concurrency while
keeping the database consistent via locks (enqueued locks) and
what is called multiversion concurrency control system
What’s mulitversion concurrency?
Oracle allows a row to be written and read at the same time
“Writers won’t block Readers, Readers won’t block Writers”
Oracle’s Slogan – but writers wait for other writers if they attempt
to update identical rows
In order to maintain read consistency, Oracle maintains multiple
temporary versions of data for read consistency
A reader reads an older version of data that’s locked and is being
modified by another process
7/20/2015
www.dbpulsegroup.com
15
Transactions & Read Consistency
Example:
While transaction #2 is modifying block, transaction #1 tries to read same block
Current Version (as viewed by transaction #2, SCN=101)
Oracle Block Header
SCN =101
ITL
Free Space …
Rows …
Empname = Jones, Extension = 7777
Old Version (as viewed by transaction #1, SCN = 100)
Oracle Block Header
SCN =100
ITL
Free Space …
Rows …
Empname = Jones, Extension = 6666
7/20/2015
www.dbpulsegroup.com
16
Transactions & Concurrency
 The
higher the concurrency, the higher the
throughput of a database
 To enable a high level of concurrency,
Oracle applies locks in a least restrictive
way – row-level locking
 Oracle supports row- as well as table-level
locking
 These locks are implicitly applied by
Oracle
7/20/2015
www.dbpulsegroup.com
17
Oracle Lock Granularity




Every time a user issues a lock, another
process can be held back from using the
locked resource
The lower lock granularity, the higher
concurrency of access to Oracle database
A table-level lock prevents many users from
modifying table data entirely
A row-level lock is the preferred lock and is the
default lock used by Oracle
7/20/2015
www.dbpulsegroup.com
18
Row-level Locking Strategy
 Each
row within a table can be locked
individually
 The locking process has exclusive right to
update the locked row
 The remaining rows can be updated by
other processes
 All rows being updated can be read by
other processes – they see the old version
of the updated data via RBS blocks
7/20/2015
www.dbpulsegroup.com
19
What Happens When A Row-level
Lock Is Placed On A Row?
 1st


a DML lock is placed on the row
No other processes can lock or update this
row
This lock will be released when the locking
process commits or rolls back
 2nd
a DDL lock is placed on the table
containing the row to prevent anyone from
altering or dropping the table while the row
is undergoing the update
7/20/2015
www.dbpulsegroup.com
20
What Happens When A Table-level
Lock Is Placed On A Table?
 The



Whole table is locked
Only the table-locking process can update
rows in the table
(The above process, trying to update a row in
the table, will obtain a Row Share Exclusive
lock on the table as a whole)
No rows in the table can be updated by other
processes – writers will block other writers
7/20/2015
www.dbpulsegroup.com
21
What Happens When A Table-level
Lock Is Placed On A Table?
…



The Whole table is locked
Other processes can query all rows, including
the one being updated in the table – writers
won’t block readers
These query-only processes are redirected to
the locking process’s rollback data – writer’s
rollback data used for read consistency
(The Row Share Exclusive lock is released
upon transaction commit/rollback)
7/20/2015
www.dbpulsegroup.com
22
Oracle Lock Modes

There are two basic lock modes:

Exclusive (X)
• A lock held in this mode is for update and the resource held can be
changed only by the holding process (until the lock is released)

Share (S)
• Allows resource to be shared among multiple concurrent users
• Prevents other users to acquire an exclusive lock to modify the
shared resource




Row exclusive (RX)
Share (S) table locks
Share row exclusive (SRX) table lock
Exclusive (X) table lock
7/20/2015
www.dbpulsegroup.com
23
Exclusive Locks Examples
SQL Type
Lock Mode
Select ... From table …
No lock needed
Insert … Into table …
RX
Delete From table …
RX
Update table …
RX
Select ... From table for update
RX
Lock table in Row Exclusive mode
RX
Lock Table in Share Row Exclusive mode
Lock Table in Exclusive mode
7/20/2015
www.dbpulsegroup.com
SRX
X
24
Shared Locks Examples
SQL Type
Lock Mode
Lock table in Row Share Mode
Lock table in Share Mode
7/20/2015
www.dbpulsegroup.com
RS
S
25
Each Type of Lock Allows Other Locks To Be
Granted On A Resource:
Exclusive (X) =
Allow SELECT queries only
Share (S) =
Allow ROW SHARE or SHARE locks to be granted to the locked
rows.
Share Row Exclusive (SRX) =
Allow ROW SHARE locks to be granted to the locked rows.
Row Exclusive (RX) =
Allow ROW EXCLUSIVE or ROW SHARE locks to be granted to
the locked rows.
Row Share (RS) =
Allow ROW EXCLUSIVE or ROW SHARE or SHARE locks to be
granted to the locked rows.
7/20/2015
www.dbpulsegroup.com
26
Latches

Latches provide exclusive access to data
structures in the SGA
 They generally only allow a single process to
examine a data structure of interest – and hence
are very restrictive and do not promote
concurrency
 Latches act as short-term locks on simpler data
structures
 There is no request queuing mechanism for
latches – they keep trying until the resource is
acquired
7/20/2015
www.dbpulsegroup.com
27
Lock Management By Oracle

Lock management is done implicitly on your
behalf by Oracle
 There is no lock management system in Oracle
 Oracle resolves deadlocks automatically with
small impact on system performance
Select name, value
From v$sysstat
Where name = ‘enqueue deadlocks’;
NAME
-----------------------------enqueue deadlocks
7/20/2015
VALUE
---------0
www.dbpulsegroup.com
28
Lock Management By DBA

As DBA you will be mainly concerned about a lock being held by a process
which may be blocking other processes’ ability to modify the locked
resource
 Processes serialize on a resource due to a blocking lock
 Oracle provides a utility: utllockt.sql
C:\oracle\ora92\rdbms\admin
SQL> @utllockt
WAITING_Session
-------------------------18
10
LOCK_TYP MODE_requested
----------------- -----------------------None
Transaction
Excl
MODE_held
----------------Excl
LOCK_ID1
--------------524296
LOCK_ID2
-------------4376
SQL> desc dba_locks;
Name
Null? Type
----------------------------------------- -------- -------------SESSION_ID
LOCK_TYPE
MODE_HELD
MODE_REQUESTED
LOCK_ID1
LOCK_ID2
LAST_CONVERT
BLOCKING_OTHERS
7/20/2015
NUMBER
VARCHAR2(26)
VARCHAR2(40)
VARCHAR2(40)
VARCHAR2(40)
VARCHAR2(40)
NUMBER
VARCHAR2(40)
www.dbpulsegroup.com
29
Blocking Locks
7/20/2015
www.dbpulsegroup.com
30
Blocking Locks

(When processes requiring exclusive access to
the same data are fired off at the same time,
all will queue up behind the one process which
grabs the lock 1st)
 User actions may cause a process to be blocked
example:
a user kills a process with “kill -9” and submits
the same process later, all the while the initial
process is running and holding on to the
blocking lock assuming that it had been “killed”
7/20/2015
www.dbpulsegroup.com
31
Blocking Locks


(Blocking locks can stop applications from processing)
Blocking locks are mainly:



TX (transaction) locks or
TM (table) locks
Example:
session 1:
SQL> Select * from scott.emp1 for update nowait; - (User goes to lunch)
- Guarantees user the 1st right to update emp table rows
session 2:
SQL> update scott.emp1 set empno=empno+1;
- session 2 hangs -
waiting for session 1 to commit or rollback
7/20/2015
www.dbpulsegroup.com
32
Blocking Locks

Identify Blocking and blocked sessions
SQL> Select * from v$lock;
ADDR
-------------682BE310
682BE48C
67AA4418
682BE3A8
67AF2914
67AA4394
KADDR
SID TY
ID1
ID2
-------------- --------------682BE320 4 XR
4
0
682BE49C 5 TS
2
1
67AA442C 9 TM 30778
0
682BE3B8 9 TX 131095 5539
67AF2A20 10 TX 131095 5539
67AA43A8 10 TM 30778
0
LMODE
---------1
3
3
0
6
2
REQUEST
-------------0
0
0
6
0
0
CTIME
---------5713
5701
7
7
100
100
BLOCK
---------0
0
0
0
1
0
SQL> select * from dba_blockers;
HOLDING_SESSION
----------------------------10
SQL> select l1.sid "Blocking SID", l2.sid "Blocked 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;
Blocking SID Blocked SID
----------------------------------10
9
7/20/2015
www.dbpulsegroup.com
33
Blocking Locks

Identify the Object:
Select object_name
From dba_objects
Where object_id=&id
Enter value for id: 30778
OBJECT_NAME
---------------------EMP1
 Identify the obj#, file#, block#, and rowid of the blocked row:
Select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
From v$session
Where sid=&sid;
Enter value for sid: 9
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------------------- --------------------------- ------------------------------ ----------------------------30778
1
50554
0
7/20/2015
www.dbpulsegroup.com
34
Avoid Blocking Locks In Your Apps

If pessimistic (early) locking implemented
“Select * from emp for update nowait”


Reduced app scalability through Blocking other processes

Have some mechanism in your app to time-out idle sessions
If optimistic locking (late locking) implemented
“Update emp set empno = empno -10”
Where ename = ‘Johnson’ ”


Improved app scalability – there is no advance locking

But be wary of Lost Updates
Opt for fine-granularity locking in your
applications – row-level is better than table-level,
etc.?
7/20/2015
www.dbpulsegroup.com
35
(Blocking) Locks
Useful Oracle Views & Scripts





v$session is a useful view:
• ROW_WAIT_OBJ#
• ROW_WAIT_FILE#
• ROW_WAIT_BLOCK#
• ROW_WAIT_ROW#
Database object
File number
Block number
Row number
(for row-level locks)
v$lock
v$process
utllockt.sql – Oracle’s report showing blocking and waiting sessions in a tree-structured format
v$waiters (run catblock.sql 1st)
Name
---------------WAITING_SESSION
HOLDING_SESSION
LOCK_TYPE
MODE_HELD
MODE_REQUESTED
LOCK_ID1
LOCK_ID2

dba_blockers:
Name
Null?
----------------------------------------- --------
Type
----------
HOLDING_SESSION
NUMBER
7/20/2015
www.dbpulsegroup.com
36
User-Provided Data
V$session:
1.
osuser
2.
process
3.
module
Client
Box
v$session
1. sid
v$process
1. spid
Oracle Server
Box
1. Oracle SID
1. User login name (os)
2. Shadow process id
2. User process id (os)
(dedicated connection)
3. User program module Name
(user-submitted program name)
7/20/2015
www.dbpulsegroup.com
37
User-Provided Data
A user may provide any or part of any of the following information to
you, the DBA:
Client Machine/where he launched his program:
 OS User name (v$session.osuser)
User id name on client machine: vr1994

OS Process id (v$session.process)
User program process id number on client machine

Program Module name (v$session.module)
User program module name, iSQL*PLUS, SQL*PLUS
Oracle Server Machine:
 OS Shadow Process id (v$process.spid)
Shadow process id number – dedicated connection

Oracle Session id (v$session.sid)
User session’s sid
7/20/2015
www.dbpulsegroup.com
38
Identifying the Blocking Session
 Identifying
a session in an environment
with numerous instances and databases
can be a daunting experience
 Users mostly know their application’s
names and not more
 You’ll need to make sense of the
bits-and-pieces you may get from your
users,
 In order to pin-point the culprit session(s)
7/20/2015
www.dbpulsegroup.com
39
Identifying the Blocking Session

Ask user if s/he knows any of the following:






process id
Shadow process id
OS user id
Module name
(iSQL*Plus, SQL*Plus, application name, etc.)
Session id
The SQL that was running when their process was
blocked
7/20/2015
www.dbpulsegroup.com
40
Identifying the Blocking Session:
User Supplies The DBA What He Deems To Be His Job’s OS Process
id On the Client Box




1.
2.
3.
4.
5.
6.
7.
8.
User supplies the process id of the program he launched from his
machine (client-server1) or some parent/child process he believes is the
process id: 2021
DBA must determine the target database user is connected to
DBA logs into remote-server1 and runs UNIX’s
process status (ps)command
Traverse child process ids to the end
$ ps -ef |grep 2021|grep -v grep
vr7191 2050 2021 0 11:29:13 ?
$ ps -ef |grep 2050 |grep -v grep
vr7191 2059 2050 0 11:29:13 ?
$ ps -ef |grep 2059 |grep -v grep
vr71912231 2059 314 11:29:13 ?
$ ps -ef |grep 2231 |grep -v grep
vr7191 2231 2059 314 11:29:13 ?
7/20/2015
www.dbpulsegroup.com
41
Identifying the Blocking Session:
Using Program OS ID, determine The SID On Server
Machine
select
s.sid sid, s.serial# serial, s.osuser osuser, s.username
username, s.module module,p.spid spid, s.process process,
s.machine machine, last_call_et active_length,
to_char(s.logon_time, 'mm/dd/yy hh24:mi:ss') logontime, s.status
status
from
v$process p, v$session s
where
s.paddr = p.addr (+)
and s.process = '&process'
SQL> /
Enter value for process: 2231
Module
Shadow
Remote
Remote
Session
Session
SID Serial OS User Username name
Process id Process id Server name active_length
Logon Time
----- ------- ----------- -------------- ------------- ------------- ------------- -------------------- ---------------- -------------------115 10366 vr2451
system
iSQL*Plus
22671
2231
client-server1
17033
12/18 11:14:18
7/20/2015
www.dbpulsegroup.com
Session
Status
----------ACTIVE
42
Modify This SQL Where
High-lighted In Green According To What’s On The
Next two Slides
select
s.sid sid, s.serial# serial, s.osuser osuser, s.username
username, s.module module,p.spid spid, s.process process,
s.machine machine, last_call_et active_length,
to_char(s.logon_time, 'mm/dd/yy hh24:mi:ss') logontime, s.status
status
from
v$process p, v$session s
where
s.paddr = p.addr (+)
and s.process = '&process'
SQL> /
Enter value for process: 2231
Module
Shadow
Remote
Remote
Session
Session
SID Serial OS User Username name
Process id Process id Server name active_length
Logon Time
----- ------- ----------- -------------- ------------- ------------- ------------- -------------------- ---------------- -------------------115 10366 vr2451
system
iSQL*Plus
22671
2231
client-server1
17033
12/18 11:14:18
7/20/2015
www.dbpulsegroup.com
Session
Status
----------ACTIVE
43
User Provided Data
(Client Machine)

OS User name (v$session.osuser)
User id name on client machine: vr1994
where
s.paddr = p.addr (+)
and s.osuser = '&osuser‘
SQL>/
Enter value for osuser: vr1994

OS Process id (v$session.process)
User program process id number on client machine
where
s.paddr = p.addr (+)
and s.process = '&process‘
SQL>/
Enter value for process: 2231

Program Module name (v$session.module)
User program module name, iSQL*Plus, SQL*Plus, etc.
Enter value for module: iSQL*PLUS
where
s.paddr = p.addr (+)
and s.module = '&module‘
7/20/2015
www.dbpulsegroup.com
44
User Provided Data
(Server Machine)
Client
Box

Server Box
OS Shadow Process id (v$process.spid) - Shadow process id number (dedicated connection)
ps –ef | grep oracleTEST [instance name]| grep LOCAL=NO| grep 10:
oracle 2135 1327 10:33:31 ?
oracle 15651 1343 10:20:01 ?
oracle 15551 1359 10:20:12 ?
32:05 oracleTEST (LOCAL=NO)
49:12 oracleTEST (LOCAL=NO)
44:13 oracleTEST (LOCAL=NO)
oracle 14321
46:07 oracleTEST (LOCAL=NO)
1 351 10:20:14 ?
where
s.paddr = p.addr (+)
and p.spid = '&spid‘
SQL> /
Enter value for shadow_pid:15551

Oracle Session id (v$session.sid) - User session’s sid
where
s.paddr = p.addr (+)
and s.sid = '&sid‘
SQL> /
Enter value for sid:15
7/20/2015
www.dbpulsegroup.com
45
Some v$session columns
definitions
select
s.sid sid, s.serial# serial, s.osuser osuser,
s.username username, s.module module,p.spid spid,
s.process process, s.machine machine, last_call_et
active_length, to_char(s.logon_time, 'mm/dd/yy
hh24:mi:ss') logontime, s.status status
from
v$process p, v$session s



last_call_et: shows you how long the active session has
been active
logon_time: Time when the user loged in
Status: Session status (active/inactice)
7/20/2015
www.dbpulsegroup.com
46
Use Top Command to Display
Shadow Process id
$ top
CPU TTY
19
?
11
?
33
?
7/20/2015
PID U SERNAME PRI NI
1204 oracle
146 20
1330 oracle
146 20
1870 oracle
156 20
SIZE
3501M
3501M
3501M
RES
6410K
6410K
6410K
STATE TIME %WCPU %CPU COMMAND
run
30:07 15.32
30.20 oracleTEST
sleep 30:40 14.15
23.42 oracleTEST
sleep
4:09 10.80
22.34 oracleTEST
www.dbpulsegroup.com
47
Identifying Blocking Locks
select l.sid SID,
decode(l.type,'TM','DML','TX',
'Trans','UL','User',l.type) Lock_Type,
decode(l.lmode,0,'None',1,'Null',2,'Row-S',3,'Row-X',
4,'Share',5,'S/Row-X',6,'Exclusive', l.lmode) Lock_Held_In,
decode(l.request,0,'None',1,'Null',2,'Row-S',3,'Row-X',
4,'Share',5,'S/Row-X',6,'Exclusive', l.request) Lock_Req_In,
l.ctime Duration_Seconds,
decode(l.block,0,'NO',1,'YES') Blocking
from v$lock l
where l.request != 0 or l.block != 0
order by l.id1, l.lmode desc, l.ctime desc
/
SID
-----115
77
Lock_Type Lock_Held_In Lock_Req_In Duration_Seconds
--------------- ------------------------------- ------------------------Trans
Exclusive
None
129
Trans
None
Exclusive
70
7/20/2015
www.dbpulsegroup.com
Blocking
----------YES
NO
48
Kill The Blocking Session
select
s.sid sid, s.serial# serial, s.osuser osuser, s.username username,
s.module module,p.spid spid, s.process process, s.machine machine,
last_call_et active_length, to_char(s.logon_time, 'mm/dd/yy hh24:mi:ss')
logontime, s.status status
from
v$process p, v$session s
where
s.paddr = p.addr (+)
and s.sid = '&sid'
SQL> /
Enter value for sid: 115
Module
Shadow
SID Serial OS User Username name
Process id
----- -------- ----------- -------------- ------------- ------------115 10366 vr2451
system
iSQL*Plus 22671

Remote
Remote
Session
Session
Session
Process id Server name
active_length
Logon Time
Status
-------------- -------------------- ------------------ -----------------------------2231
client-server1
17033
12/18 11:14:18
ACTIVE
Alter system kill session ‘115,10366’ immediate;
7/20/2015
www.dbpulsegroup.com
49
Identifying the Blocking Session:
User Provides Locked Object’s Name

From a separate session we submitted the following query:
SQL> lock table scott.emp in exclusive mode;
select l.sid sid, serial# Serial#,
decode(l.type,'TM','DML','TX','Trans','UL','User',l.type) Lock_Type,
decode(l.lmode,0,'None',1,'Null',2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6,'Excl') Lock_Mode,
o.owner
Owner,
o.object_name
Obj_Name,
l.ctime Duration_Seconds,
o.status
Status
from dba_objects o, v$lock l, v$session s
where l.id1 = o.object_id
and o.object_name = upper (‘&object_name’)
and l.sid=s.sid
order by o.owner
Enter value for object_name: emp
SID Serial# Lock_Type
Lock_Mode
----- ---------- ------------------ ----------------13
4466
DML
Excl

Owner
---------------SCOTT
Obj_Name
-------------EMP
Duration_Seconds Status
------------------------- ---------4431
VALID
Alter system kill session ‘13,4466’ immediate;
7/20/2015
www.dbpulsegroup.com
50
Identifying Session’s Serial#
User Provides Blocking SID

From a separate session we submitted the following query:
SQL> lock table scott.emp in exclusive mode;
select l.sid sid, serial# Serial#,
decode(l.type,'TM','DML','TX','Trans','UL','User',l.type) Lock_Type,
decode(l.lmode,0,'None',1,'Null',2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6,'Excl') Lock_Mode,
o.owner
Owner,
o.object_name
Obj_Name,
l.ctime Duration_Seconds,
o.status
Status
from dba_objects o, v$lock l, v$session s
where l.id1 = o.object_id
and l.sid = &sid
and l.sid=s.sid
order by o.owner, o.object_name;
Enter value for sid: 13
SID Serial# Lock_Type
Lock_Mode
----- ---------- ------------------ -----------------
13

Owner
----------------
Obj_Name
--------------
4466
DML
Excl
SCOTT
EMP
Alter system kill session ‘13,4466’ immdediate;
7/20/2015
Duration_Seconds Status
------------------------- ---------4431
www.dbpulsegroup.com
VALID
51
Precautionary Measures Before
Killing a Session





Make every effort to contact the user
If the user cannot be reached, then contact his manager, (or
someone with authority to give you the go-ahead to kill the session)
Ask if the user has killed any session(s)
Ask the user to email a listing of all the actions he’s taken so far
Collect and verify user-supplied data about the session to be killed:






SID -- Oracle re-uses (completed, aborted and
rolled- back) SIDs very quickly
Osuser
Oracle user name
Session start time
Module name
SQL being executed
7/20/2015
www.dbpulsegroup.com
52
Some Useful Hints
1st Kill your session by using Oracle’s
“Alter system kill session ‘pid,serial#’ immdediate;”
 UNIX “Kill -9 process#” will not guarantee session
termination
 Use “kill -15”, instead of “kill -9”, wherever possible
 Killing an active session can take minutes or sometime
hours depending on:
- Extent of un-committed changes made to the
database by the transaction
 When rollback process is completed, all session
resources, locks, various memory spaces, are freed up

7/20/2015
www.dbpulsegroup.com
53
Killing Steps

Display and verify all session information possible
Module
SID Serial OS User Username name
----- ------- ----------- -------------- ------------115 10366 vr2451


system
iSQL*Plus
Shadow
Remote
Remote
Session
Process id Process id Server name active_length
------------- ------------- -------------------- ---------------22671
2231
client-server1
17033
Session
Logon Time
-------------------12/18 11:14:18
Session
Status
-----------
ACTIVE
Alter system kill session ‘115,10366’ immdediate;
Confirm your kill:
Module
SID Serial OS User Username name
----- ------- ----------- -------------- ------------115 10366 vr2451
system
iSQL*Plus
Shadow
Remote
Remote
Session
Process id Process id Server name active_length
------------- ------------- -------------------- ---------------22671
2231
client-server1
17033
Session
Logon Time
--------------------
Session
Status
-----------
12/18 11:14:18
killed
Kill Shadow process: Kill -15 22671

Verify again:
SQL> / (see slides xx & zz for the SQL)
Enter value for sid: 115
No rows selected
(unless SID was reassigned in the interim!)

7/20/2015
www.dbpulsegroup.com
54
Session Being Killed Is taking Long?
After killing a session, you may monitor the roll-back progress by
running the following query, if session status remains ACTIVE:
select s.sid
Sid,
s.username
User_Name,
t.start_time
Start_time,
t.used_ublk
Blks_in_RBS,
t.status
Xaction_Status
from v$session s, v$transaction t
where t.ses_addr = s.saddr
and s.sid = &sid;
Xaction
Sid User_Name
Start_Time Blks_in_RBS
Status
----- ------------------------------------ ------------------ -----------456
vr1994
08/17 10:13:21
111
ACTIVE

7/20/2015
www.dbpulsegroup.com
55
Example
User provides Module Name

A user calls and asks that you must kill a series
of sessions as they are blocked, providing you
with module name (SQL*Plus)
 Following is as example of what might have
happened:
 In Session 1 user submits the following
statement through a SQL*Plus session:
Select * from scott.emp for update nowait;
 From SQL*Plus sessions 3, 4, and 5 he submits
the following simultaneously:
update scott.emp1 set empno = y;
7/20/2015
www.dbpulsegroup.com
56
Example
User Provides Module Name,
Show Blocking/Blocked Session
SQL> l
Select l.sid SID,
decode(l.type,'TM','DML','TX',
'Trans','UL','User',l.type) Lock_Type,
decode(l.lmode,0,'None',1,'Null',2,'Row-S',3,'Row-X',
4,'Share',5,'S/Row-X',6,'Exclusive', l.lmode) Lock_Held_In,
decode(l.request,0,'None',1,'Null',2,'Row-S',3,'Row-X',
4,'Share',5,'S/Row-X',6,'Exclusive', l.request) Lock_Req_In,
l.ctime Duration_Seconds,
decode(l.block,0,'NO',1,'YES') Blocking
From v$lock l
Where l.request != 0 or l.block != 0
order by l.id1, l.lmode desc, l.ctime desc
SQL> /
SID
LOCK_TYPE LOCK_ HELD_IN
LOCK_REQ_IN
DURATION_SECONDS
BLOCKING
--------- ----------- -------------- --------------- -------------------- ---------9
10
15
12
7/20/2015
Trans
Trans
Trans
Trans
Exclusive
None
None
None
None
Exclusive
Exclusive
Exclusive
www.dbpulsegroup.com
2,069
2,037
2,031
2,028
YES
NO
NO
NO
57
Example
User Provides Module Name,
Display SID, Serial#, shadow_pid, etc.
select
s.sid sid, s.serial# serial, s.osuser osuser, s.username username, s.module
module,p.spid spid, s.process process, s.machine machine, last_call_et
active_length, to_char(s.logon_time, 'mm/dd/yy hh24:mi:ss') logontime, s.status
status
from
v$process p, v$session s
where
s.paddr = p.addr (+)
and s.module = '&module‘
SQL> /
Enter value for module: SQL*Plus
SID SERIAL
OSUSER
USERNAME
MODULE
SPID
PROCESS
LOGONTIME
STATUS
---- -------- ----------- ------------ ------------ ------------ ------------ ----------------- -------9
10
12
15
9
8
18
290
7/20/2015
PULSER\Vil
PULSER\Vil
PULSER\Vil
PULSER\Vil
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SQL*Plus
SQL*Plus
SQL*Plus
SQL*Plus
8032
1112
4964
6048
7452:2216
4864:7728
2756:7668
2304:7796
07/24/06 16:54:24
07/24/06 14:46:24
07/24/06 14:47:19
07/24/06 16:57:29
www.dbpulsegroup.com
ACTIVE
ACTIVE
ACTIVE
ACTIVE
58
Example
Using Shadow_pid, Create Script To Kill Multiple
Shadow Processes
spool kill_shadows.ksh
select 'kill -15 ' || p.spid
from v$process p , v$session s
where s.module = '&Module_Name'
and s.paddr = p.addr (+)
order by s.sid
/
spool off
Enter value for Module_Name: SQL*Plus
kill -15 8032
kill -15 1112
kill -15 4964
kill -15 6048
7/20/2015
www.dbpulsegroup.com
59
Example
Create Script to Kill Oracle Sessions
Spool alter_kill_sessions.sql
Select 'alter system kill session ' || ''''|| sid
|| ',' || serial# ||'''' || ' immediate;'
From v$session
Where module = '&module'
Order by sid
/
Spool off
alter system kill session '9,9' immediate;
alter system kill session '10,8' immediate;
alter system kill session '12,18' immediate;
alter system kill session '15,290' immediate;

Then execute scripts in the following order:
1.
SQL> @alter_kill_sessions.sql
$ kill_shadows.ksh
2.
7/20/2015
www.dbpulsegroup.com
60
Conclusion:
What Can A DBA or Manager Do?

Proactively monitor Blocking Locks in the system
Limit the time that a row/resource is locked

Ask developers to solve it in the application so that the locking resource
session times out
Ask DBAs to use Resource Profiles so that the locking resource session
times out when it reaches “Kernel Limits”
“Maximum connect/idle time”


SQL> select * from dba_profiles;
PROFILE
-----------DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
…

RESOURCE_NAME
---------------------------CONNECT_TIME
IDLE_TIME
CPU_PER_SESSION
PASSWORD_GRACE_TIME
PASSWORD_GRACE_TIME
RESOURCE
----------------KERNEL
KERNEL
KERNEL
PASSWORD
PASSWORD
LIMIT
---------------UNLIMITED
UNLIMITED
UNLIMITED
UNLIMITED
UNLIMITED
To enable kernel limits 1st set resource limit to TRUE
NAME
-----------------------------------enqueue_resources
resource_limit
7/20/2015
TYPE
----------integer
boolean
VALUE
---------968
FALSE
www.dbpulsegroup.com
61
What Can you Do AS DBA or
Manager

Work closely with developers and check
out their code before they get into
production systems
7/20/2015
www.dbpulsegroup.com
62
www.dbpulsegroup.com
(925) 216-6239
[email protected]
7/20/2015
www.dbpulsegroup.com
63