everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

Performance

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15
<< Previous

Chapter # 11

Next >>


 

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?

 

 

     Reviews and Templates for FrontPage
     

Copyright © everythingOracle.bizhat.com 2006 All Rights Reserved.