|
"Sometimes when you
look back on a situation, you realize it wasn't all you thought
it was. A beautiful girl walked into your life. You fell in
love. Or did you? Maybe it was only a childish infatuation, or
maybe just a brief moment of vanity." Henry Bromel, Northern
Exposure, The Big Kiss, 1991 |
Read
first then play the video:
PRF-VIDEO -Monitoring
and Detecting Lock Contention
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 iself.emp table
exclusively.
SQL> LOCK TABLE iself.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 iself.emp table in
the shared mode.
SQL> LOCK TABLE iself.emp IN SHARE MODE
/
Now, the iself.emp table is in the shared mode.
Go to another session and login as the iself 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 = 'ISELF'
/
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 ISELF 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.
|
"Many persons have
a wrong idea of what constitutes true happiness. It is not
attained through self-gratification but through fidelity to a
worthy purpose." Helen Keller (1880 - 1968) |
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 = 'ISELF'
/
Q: How do you monitor a lock acquired by a transaction in the
Oracle database?
|