Oracle9i Performance Tuning

1
Oracle9i Performance Tuning

• Chapter 9
• Detecting Lock Contention

2
Chapter Objectives

• Learn about locks and their uses
• Differentiate lock types
• Understand different lock modes
• Learn to distinguish between different locking
  levels
• Learn to use the LOCK statement
• Learn the impact of the SELECT...FOR UPDATE
  statement on transactions

3
Chapter Objectives (continued)

• Work through some practical examples of locking
• Work with the DML_LOCKS initialization parameter
• Use the SET TRANSACTION statement in transactions
• Understanding the workings of deadlocks
• Detect and resolve lock contention
• Learn the Best Practices for detecting lock
  contention

4
Oracle Locking Process
5
Lock Characteristics

• Locks
• Enforce consistency and integrity data and
  objects maintain their integrity and consistency
  for the duration of the transaction
• Provide a queue structure that allows all
  sessions to join a queue for the object when the
  object is not available immediately
• Oracle automatically handles lock mechanisms
• The duration of the lock is equal to the length
  or processing time of the transaction submitted

6
Lock Types
7
Locks and Transactions
8
Lock Modes
9
Lock Modes and DML Statements
10
DDL Statements and Lock Modes
11
Locking Levels

• There are four levels of locks that can be
  implemented in a database
• Database level
• Table level
• Row level
• Column level

12
Using Oracle Enterprise Manager
13
SHARE (S) Lock Mode
14
ROW SHARE (RS) Lock Mode
15
ROW EXCLUSIVE (RX) Lock Mode
16
SHARE ROW EXCLUSIVE (SRX) Lock Mode
17
EXCLUSIVE (X) Lock Mode
18
SELECTFOR UPDATE
19
DML_LOCKS Initialization Parameter

• The DML_LOCKS initialization parameter
• Allows values in the range of 20 to an unlimited
  value, inclusive
• Cannot be modified dynamically

20
SET TRANACTION Statement

• SET TRANSACTION can be set to SERIALIZABLE or
  READ COMMITTED
• SERIALIZABLE If a DML statement is attempting to
  update data in an object that has been updated
  and committed by another session, the DML
  statement fails
• READ COMMITTED If a DML statement is attempting
  to update data in an object that has been updated
  by another session and not committed at any time
  during the session, the DML statement waits until
  the other session completes its transaction
• This is the default behavior as shown in all
  previous examples

21
Deadlocks
22
Illustration of the Deadlock Process
23
Detecting and Resolving Lock Contention

• Use the following tools to detect and resolve
  lock contention
• VLOCK view
• VLOCKED_OBJECT view
• DBA_BLOCKERS view
• DBA_WAITERS view
• UTLLOCKT.SQL script
• DBMS_LOCK package

24
Detecting and Resolving Lock Contention
(continued)
25
Using Oracle Enterprise Manager
26
Summary

• A lock is a mechanism that protects a database
  object from being altered while it is being
  modified by other processes or users
• An enqueue is a data structure for locks that
  informs Oracle of who is waiting for a resource
  that is locked by another session
• Locks are held and released by Oracle
  automatically according to the start and
  completion of a transaction
• Application logic indirectly controls locks
• The lowest lock level Oracle provides is at the
  row level
• DBAs use LOCK statements to manually lock a table
  in any desired lock mode