|
Monitoring and Detecting Lock Contention
Introduction
As a DBA, you
are responsible for monitoring and detecting
a lock contention in case of a performance
problem. Your job�s responsibilities dictate
that you should at least be informed of the
following basic fundamental subjects:
Monitoring and
detecting a lock contention
Locking a
table in the exclusive mode
Locking a
table in the shared mode
Displaying
locks contention
Killing a
session
Using the
V$LOCK view
Using the
DBA_OBJECTS view
Using the
V$LOCKED_OBJECT view
Using the
V$SESSION view
The SID column
The SERIAL#
column
Types of locks
TX-Transaction
Enqueue
TM-DML Enqueue
UL- User
Supplied
Commands:
LOCK TABLE IN
EXCLUSIVE MODE
LOCK TABLE IN
SHARE MODE
ALTER SYSTEM
KILL SESSION
Hands-on
In
this exercise you will learn how to monitor
and detect a lock contention, lock a table
exclusively, query locks contention, and
kill a session.
First, connect to SQLPlus as the
SYSTEM/MANAGER user.
SQL> CONNECT
system/manager AS SYSDBA
Locks
The
Oracle server automatically manages object
locking, so most application developers
don't need to focus on lock management. You
should avoid any lock contention. It does
affect your performance very significantly.
The lock contention is a delay that Oracle
is not able to lock a record or records due
to exclusive use of that object or objects.
Oracle has user and system locks. Here we
only take about user locks. System locks are
held for extremely short periods of time and
will be control by Oracle. Oracle has lots
of system locks. The following are some of
example of system locks.
|
BL |
Buffer
hash table instance |
NA..NZ |
Library cache pin instance (A..Z =
namespace) |
|
CF |
Control file schema global enqueue |
PF |
Password File |
|
CI |
Cross-instance function invocation
instance |
PI, PS |
Parallel operation |
|
CU |
Cursor
bind |
PR |
Process startup |
|
DF |
Data
file instance |
QA..QZ |
Row
cache instance (A..Z = cache) |
|
DL |
Direct
loader parallel index create |
RT |
Redo
thread global enqueue |
|
DM |
Mount/startup db primary/secondary
instance |
SC |
System
commit number instance |
|
DR |
Distributed recovery process |
SM |
SMON |
|
DX |
Distributed transaction entry |
SN |
Sequence number instance |
|
FS |
File
set |
SQ |
Sequence number enqueue |
|
HW |
Space
management operations on a specific
segment |
SS |
Sort
segment |
|
IN |
Instance number |
ST |
Space
transaction enqueue |
|
IR |
Instance recovery serialization
global enqueue |
SV |
Sequence number value |
|
IS |
Instance state |
TA |
Generic enqueue |
|
IV |
Library cache invalidation instance |
TS |
Temporary segment enqueue (ID2=0) |
|
JQ |
Job
queue |
TS |
New
block allocation enqueue (ID2=1) |
|
KK |
Thread
kick |
TT |
Temporary table enqueue |
|
LA ..
LP |
Library cache lock instance lock (A..P
= namespace) |
UN |
User
name |
|
MM |
Mount
definition global enqueue |
US |
Undo
segment DDL |
|
MR |
Media
recovery |
WL |
Being-written redo log instance |
Lock a table
manually
You
can lock a table manually. Lock the
oracle.emp table exclusively.
SQL> LOCK TABLE
oracle.emp IN EXCLUSIVE MODE
/
Now, the EMP table is in the
exclusive mode. We strongly advise you not
to lock any tables exclusively unless you
have to.
Lock
a table by Oracle database
Lock the
oracle.emp table in the shared mode.
SQL> LOCK TABLE
oracle.emp IN SHARE MODE
/
Now, the oracle.emp table is in
the shared mode.
Go to another session and login as the
oracle user. Update an employee record.
SQL> UPDATE
emp
SET sal = 1000
WHERE empno = 7788;
View locks
Come back to your previous session and query
the V$LOCK view to show information about
user locked objects. Notice that the TYPE
column can be TX, TM, and UL. If TYPE equals
TX, it means TRANSACTION ENQUEUE. If TYPE
equals TM, it means DML ENQUEUE. If TYPE
equals UL, it means USER SUPPLIED.
SQL> SELECT o.owner,
o.object_name, o.object_type, l.type
FROM dba_objects o, v$lock l
WHERE o.object_id = l.id1
AND o.owner = 'ORACLE'
/
Notice that the EMP table is in
the EXCLUSIVE lock.
View locks by
transactions
Query the V$LOCKED_OBJECT view to show locks
acquired by transactions in the system.
SQL> SELECT
oracle_username, object_id, session_id
FROM v$locked_object
/
Notice that the SYS user is
locking the ORACLE user. Please take a note
on its SESSION_ID column.
Check if the session has a locked
transaction.
SQL> SELECT
username, sid, serial#
FROM v$session
WHERE sid IN
(SELECT
session_id FROM v$locked_object)
/
Note the SID
and SERIAL# columns.
Resolve lock
problem
Kill the insignificant session.
SQL> ALTER SYSTEM
KILL SESSION '&SID,&SERIAL'
/
The session was killed.
Check locks
Query the V$LOCKED_OBJECT view again.
SQL> SELECT
oracle_username, object_id, session_id
FROM v$locked_object
WHERE oracle_username <> 'SYS'
/
There should be no locked
objects. Killing a session is one way to
resolve lock contention.
Questions:
Q: What is a
lock contention in the Oracle database?
Q: How do you
monitor and detect a lock contention?
Q: How do you
lock a table in the exclusive mode?
Q: How do you
lock a table in the shared mode?
Q: How do you
kill a session?
Q: Describe
the different types of user locks?
Q: What does
the following SQL statement?
SQL> SELECT
o.owner, o.object_name, o.object_type,
l.type
FROM dba_objects o, v$lock l
WHERE o.object_id = l.id1
AND o.owner = 'ORACLE'
/
Q: How do you monitor a lock acquired by a
transaction in the Oracle database?
|
