Locks
Version: 1/7/2007
Author: Anjo Kolk
1.1. BL, Buffer Cache Management
1.2. CF,
Controlfile Transaction
1.3. CI, Cross-instance Call Invocation
1.6. DL, Direct Loader Index Creation
1.14. IV, Library Cache Invalidation
1.17. L[A-P], Library Cache Lock
1.21. P[A-Z], Library Cache Pin (will be
N[A-Z] in 7.1.3)
1.25. PS, Parallel Slave Synchronization
1.27. SC, System Commit Number
1.30. SQ, Sequence Number Enqueue
1.31. SR, Synchronized Replication
1.33. ST, Space Management Transaction
1.34. SV, Sequence Number Value
1.35. TA, Transaction Recovery
1.37. TS, Temporary Segment (also
TableSpace)
1.42. US, Undo segment, Serialization
1.43. WL, Being written Redo Log
1.44. XA, Instance Attribute Lock
1.45. XI, Instance Registration Lock
How Many
Resources:
The total
number of BL resources (or PCM resources) is determined by the following
formula:
up to 7.3.1:
prime(gc_db_locks)
+ 3 + prime(gc_segments * 1.5) + prime(gc_segments * 1.5 * 5) +
prime(gc_tablespaces)
+ prime(gc_save_rollback_locks) + (gc_rollback_segments *
(gc_rollback_locks
+ 1))
after 7.3.1:
prime(gc_db_locks)
+ 3 + prime(gc_segments) + prime(gc_freelist_group) +
prime(gc_tablespaces)
+ prime(gc_save_rollback_locks) + (gc_rollback_segments *
(gc_rollback_locks
+ 1)) + gc_releasable_locks
How Many
Locks:
The number
of locks is determined by the number of instances running. For each instance
running
there will be one lock allocated for each BL resource.
How Many
Users:
All
processes, indirectly through LCK* processes
Who Uses:
LCK*
processes
When and
How Used:
Page 60
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 60
When
accessing database blocks. All acquired in NULL mode by each instance’s LCK*
processes
when an instance is started. Converted up and down to different modes by LCK*
processes in
response to requests from user foregrounds and instance backgrounds. Each lock
covers a
portion of the physical database on disk, by file and block range, in
accordance with the
setting of
‘gc_file_to_locks’.
Id1, Id2
Combination:
Lock Element
Number, Block Class
Lock
Value Block:
Thread
number of last change and SCN.
Init.ora
Parameters:
gc_db_locks,
gc_segments, gc_tablespaces, gc_save_rollback_locks, gc_rollback_segments,
gc_rollback_locks.
Scope:
Global Lock.
Deadlock
Sensitive:
No.
Operation:
Asynchronous.
How Many
Resources:
1 to
serialize controlfile transactions, 1 to serialize reads and writes from shared
information
portion of
controlfile, which is a special global notepad area of the controlfile.
How Many
Locks:
1 Lock for
each process that tries to perform a Control File operation.
How Many
Users:
(depends)
Who Uses:
Any
foreground or background doing a controlfile transaction
When
Used:
- switching
logfiles, held exclusive.
- updating
checkpoint information for datafiles, held exclusive.
- opening a
logfile for redo reading during recovery, held shared.
- getting
information to perform archiving, held shared.
- performing
crash recovery, held exclusive.
- performing
instance recovery, held exclusive.
- performing
media recovery, held exclusive.
- dumping
logfile history, held shared.
- updating
archiving information, then exclusive.
- creating a
database, held exclusive.
- mounting a
database, held shared.
- closing a
database, held shared.
- adding a
logfile or logfile member, held exclusive.
Page 61
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 61
- dropping a
logfile or logfile member, held exclusive.
- checking
information about logfile group members, held shared.
- adding a
new datafile, held exclusive.
- dropping a
datafile, held exclusive.
-
re-creating (as empty) an existing database file, held shared to find the file,
held exclusive to
perform the
update to the file entry in the controlfile when the zeroing task is complete.
-
identifying or re-identifying database files, held shared.
- setting a
set of datafiles OFFLINE, held exclusive twice in a two step process.
- setting a
set of datafiles ONLINE, held exclusive, and then again when the transaction
subsystem
completes applying save undo to the on-lined files, a two-step process.
- marking a
set of datafiles READONLY, held exclusive twice in a two-step process.
- marking a
set of READONLY datafiles READWRITE, held exclusive twice in a two-step
process.
- formatting
a new controlfile, i.e. CREATE CONTROLFILE, held exclusive.
- beginning
a hot backup, held exclusive.
- ending a
hot backup, held exclusive.
- checking
to see, after a crash, whether datafiles are in hot backup mode, held shared,
then
exclusive.
- executing
a ALTER DATABASE BACKUP CONTROLFILE TO TRACE, held shared.
- opening
the database (controlfile), held exclusive by first instance to open, shared by
subsequent
instances.
- renaming
datafiles or logfiles, held exclusive.
- marking a
controlfile as valid and mountable, held exclusive.
- handling
an error encountered in the controlfile, held exclusive.
- validating
data dictionary entries against matching controlfile records for file entries,
held
exclusive.
- updating
controlfile format after a software upgrade, held exclusive.
- scanning
for log file info by log sequence #, held shared.
- finding
the highest-in-use entry of a particular controlfile record type, held shared.
- getting
information about the number of log entries and their lowest/highest sequence
number
and log file
numbers, held shared.
- looking up
a controlfile record matching a given filename, held shared.
- retrieving
or updating a controlfile record, held shared for retrieval, exclusive for
update.
- making a
backup controlfile, held exclusive for the duration of the copy.
- dumping
the contents of the controlfile during debugging, held shared.
- dumping
contents of a current redo logfile during debugging, held shared.
- dumping
contents of redo log headers, held shared.
- dumping
contents of datafile headers, held shared.
- updating
MAC security mode in Trusted Oracle, held exclusive.
- reading
shared information from the controlfile, held exclusive until shared
information is
written back
updated, or done looking at it. Shared information is read and updated
infrequently
and is used primarily for instances to agree on compatibility, modes of
operation,
and for
system commit number housecleaning in instance recovery situations.
Id1, Id2
Combinations:
TABLE 19.
Id1, Id2 Combinations for CF
Resource.
Page 62
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 62
Id1 Id2
Meaning
0 0
Serialize Control file actions
0 1 Shared
Information Access.
Lock
Value Block:
Init.ora
parameters:
processes
Scope:
Local and
Global Enqueue.
Deadlock
Sensitive:
Yes.
Operation:
Synchronous.
How Many
Resources:
44 Resources
(4 per function, 11 Cross Instance Functions)
4/unique
type of cross-instance call, acquired on the fly as a particular call is
requested
(call,parameter,queue,return). The four locks for each call are used as a
mechanism to
allow an RPC on a remote instance background process.
How Many
Locks:
The number
of locks depend on the number of concurrent processes that execute the Cross
Instance
Call.
How Many
Users:
Potentially
all foreground or backgrounds
Who Uses:
All
foreground and backgrounds
When
Used:
Used to
invoke specific actions in background processes on a specific instance or all
instances.
Examples
include checkpoint, log switch, shutting down, identifying or re-identifying
datafiles,
etc. All in
all, there are a small number (10’s) of predefined cross-instance call types.
Id1, Id2
Combination:
TABLE 20.
Id1 for CI resource.
Id1
Meaning
0 Flush
buffers for reuse as new class
1 LGWR
checkpointing and Hot Backup
2 DBWR
synchronization of SGA with control file
3 Log file
add/drop/rename notification
4 Write
buffer for CR read
5 Test Call
6 Invalidate
KCK cache in all instances
7 Alter
rollback segment optimal
Page 63
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 63
8 Signal
Query Servers/coordinator
9 Create
Remote Parallel Query Server
10 Set
Global Partitions
11 Stop Disk
Writes
12 Drop Sort
Segments
13 Release
unused space from Sort Segments
14 Instance
Recovery for Parallel operation Group
15 Validate
parallel slave Lock Value
16 Check Transaction
State Objects
TABLE 21.
Id2 for CI resource.
Id2
Meaning
1 Pass in
Parameters
2 Invoke the
call in background process
3 Foreground
has not returned yet
4 Used to
allocate the CI call
5 Used to
queue up interested clients
Lock
Value Block:
Init.ora
Parameters:
processes
Scope:
Local and
Global Enqueue.
Deadlock
Sensitive:
Yes.
Operation:
Synchronous.
How Many
Resources:
1/child
cursor
How Many
Locks:
1/child
cursor/process.
How Many
Users:
processes *
open_cursors.
Who Uses:
All processes.
When
Used:
This keeps
us from hanging on the exclusive pin if somebody else sets the bind types and
gets
the
shared-pin for the execute before we get the exclusive pin for bind (which
would be
unnecessary
at that point). Once we get the enqueue, we’ll check whether the cursor is
still
Page 64
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 64
unbound, in
which case we’ll verify that it was bound the way that we wanted it, or we’ll
go
ahead and
bind it ourselves.
Id1, Id2
Combination:
Child Object
Handle, 0.
Lock
Value Block:
Not Used.
Init.ora
Parameters:
max_open_cursors
Scope:
Local
Enqueue.
Deadlock
Sensitive:
Yes.
Operation:
Synchronous.
How Many
Resources:
1/ on-line
file
How Many
Locks:
1 for each
instance.
How Many
Users:
1/ instance.
Who Uses:
DBWR’s
When
Used:
Obtained
shared by DBWR in every instance as the instance comes up or upon request of a
reidentify
request. It
is held shared for the life of the instance, and is used to notice changes like
a
file being
off-lined on another instance. It is also held exclusive when bringing a file
on-line.
Id1, Id2
Combination:
Always 0,
File Number.
Lock
Value Block:
Not Used.
Init.ora
Parameters:
db_files
Scope:
Global Lock.
Deadlock
Sensitive:
No.
Operation:
Synchronous.
Page 65
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 65
How Many
Resources:
1/ table
being direct loaded
How Many
Locks:
1 for each
sqlldr process loading the table.
How Many
Users:
1/ direct
loader / object
Who Uses:
SQL*Loader
in Direct-path mode
When
Used:
When
initializing to do a direct load of a table, held shared by every direct loader
session
loading that
object for the duration of the load. Acquired and held shared exclusive when
creating an
index on the table.
Id1, Id2
Combination:
Object
Number, Always 0.
Lock
Value Block:
Not Used.
Init.ora
Parameters:
processes.
Scope:
Local and
Global Enqueue.
Deadlock
Sensitive:
Yes.
Operation:
Synchronous.
How Many
Resources:
1 if mounted
EXCLUSIVE, 5 if mounted SHARED
How Many
Locks:
1 Lock per
instance.
How Many
Users:
Any
processes trying to mount/open a database
Who Uses:
Any
processes trying to mount/open a database
When
Used:
When
mounting or opening a database, if the database is being mounted exclusive,
then one lock
is acquired
to ensure no other instances can successfully mount it; if the database is
being
mounted
shared, all five locks are used to ensure that all processes attempting to
mount or open
on all
instances have a consistent view of the mount or open state of the database.
Id1, Id2
Combination:
TABLE 22.
Id1, Id2 Combination for DM Lock.
Page 66
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 66
Id1 Id2
Meaning
0 0 Mount
type. This is used to determine if the DB may be mounted
shared or
exclusive. It is always nowait. Only acquired by
DBWR.
0 1 Mount
gate. This is used to serialize checking for the first
instance to
mount. It is only acquired by foregrounds attempting
to mount the
database.
0 2 Hold
mount. This is held to indicate that the db is mounted by
some
instance some where. Its lockvalue contains enough
information
to validate that the correct controlfiles are opened.
The first
foreground gets it shared. After the database is
successfully
mounted, the DBWR holds this lock shared until the
DB is
dismounted.
1 0 Open
flag. This is used to verify that no other instance has the
database
open or is in process of opening it. It is similar to the
hold open
lock and is acquired and released at the same time.
This is a
local enqueue rather then an instance lock. This is
separate of
the hold open lock so that testing does not interfere
with
deciding if this is the first instance to open. If one instance
was getting
the hold lock just to see if the db was open any
where,
another instance might think the database was already
opened.
1 1 Open
gate. This functions identically to the mount gate except
that it is
used for database open.
1 2 Hold
open. This functions identically to hold mount except that it
is used for
database open, and its lock value is not used.
Lock
Value Block:
No.
Init.ora
Parameters:
_db_no_mount_lock.
Scope:
Local and
Global Enqueue (but also Global Lock).
Deadlock
Sensitive:
Yes for the
Local and Global Enqueue, no for the Global Lock.
Operation:
Synchronous.
How Many
Resources:
1
How Many
Locks:
1 per RECO
process that tries to do Distributed Recovery.
How Many
Users:
1/ instance
Who Uses:
Page 67
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 67
RECO’s
When
Used:
Used to
ensure that only one RECO is doing distributed transaction recovery actions at
a time.
Id1, Id2
Combination:
Always 0,
Always 0
Lock
Value Block:
Not Used.
Init.ora
Parameters:
distributed_transactions.
(Distributed Database Option)
Scope:
Global Lock.
Deadlock
Sensitive:
No.
Operation:
Synchronous.
How Many
Resources:
1
How Many
Locks:
1 per
process that tries to do a distributed transaction.
How Many
Users:
1/ instance
Who Uses:
RECO’s
When
Used:
Used to
ensure that only one process is using a distributed transaction slot at a time.
Id1, Id2
Combination:
Slot Number
(of distributed transaction table), Always 0
Lock
Value Block:
Not Used.
Init.ora
Parameters:
distributed_transactions.
(Distributed Database Option)
Scope:
Local Enqueue.
Deadlock
Sensitive:
Yes.
Operation:
Synchronous.
Page 68
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 68
How Many
Resources:
2
How Many
Locks:
Depends of
the number of concurrent processes that tries to do these operations.
How Many
Users:
Used by
processes performing actions that require that the files that make
up the
database do not change the set of files it may be performing the action on.
Who Uses:
Used by
processes performing actions that require that the files that make up the
database do not
change the
set of files it may be performing the action on.
When
Used:
The first
lock is used to hold the set consistent for actions like dropping or renaming a
datafile.
The second
is used to when validating controlfile information regarding files with
information
stored in
the data dictionary.
Id1, Id2
Combination:
TABLE 23.
Id1, Id2 Combination for FS Lock.
Id1 Id2
Meaning
0 0 Take
offline
0 1 Do
dictionary check.
Lock
Value Block:
Not Used.
Init.ora
Parameters:
None.
Scope:
Local and
Global Enqueue.
Deadlock
Sensitive:
Yes.
Operation:
Synchronous.
How Many
Resources:
1/ active
instance
How Many
Locks:
1/active
instance
How Many
Users:
1/ instance
Who Uses:
DBWR’s
When
Used:
Used to
either obtain or ensure that the instance_number parameter used for freelist
management,
is unique for every instance accessing a database.
Id1, Id2
Combination:
Page 69
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 69
Always 0,
Instance Number.
Lock
Value Block:
Not Used.
Init.ora
Parameters:
instance_number
Scope:
Global Lock.
Deadlock
Sensitive:
No.
Operation:
Synchronous.
How Many
Resources:
1/database
How Many
Locks:
at most
1/instance
How Many
Users:
at most 1/
instance
Who Uses:
SMON’s and
processes attempting to open the database
When
Used:
Used to
ensure that only one SMON process or ALTER DATABASE OPEN is performing
instance
recovery at any given time.
Id1,Id2
Combination:
TABLE 24.
Id1 Id2
Meaning Scope
0 0 To
serialize instance recoverers. Local and Global
Enqueue
1 0 Used to
verify if writes are allowed in the database. Global Lock
Lock
Value Block:
Not Used.
Init.ora
Parameters:
None.
Scope:
Local and
Global Enqueue and Global Lock.
Deadlock
Sensitive:
Yes for the
Local and Global Enqueue only.
Operation:
Synchronous.
Page 70
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 70
How Many
Resources:
3
How Many
Locks:
Could range
from 0 to the number of processes in an instance.
How Many
Users:
All processes
that want to mount/dismount or open/close a database. Also held by users
who want to
keep the instance in its current state.
Who Uses:
All
processes that want to mount/dismount or open/close a database. Also held by
users
who want to
keep the instance in its current state.
When
Used:
Processes
that wish to modify the instance state acquire the locks in exclusive mode.
Processes
that want to
hold the instance in a particular mode acquire them shared.
Id1, Id2
Combination:
TABLE 25.
Id1, Id2 Combination for IS Lock.
Id1 Id2
Meaning
0 0
0 1 Changing
mount or open state of the database state on this
instance.
0 3 Modify
Lock
Value Block:
Not Used.
Init.ora
Parameters:
None.
Scope:
Local
Enqueue.
Deadlock
Sensitive:
Yes.
Operation:
Synchronous.
How Many
Resources:
1 / valid
and existent database object that is currently cached in the library cache
(valid and
existent
table, view, procedure, function, package, package body, trigger, index,
cluster,
synonym);
cursors (SQL and PL/SQL), pipes, invalid or non-existent objects, and any other
transient
objects do not use this lock
How Many
Locks:
1/per
instance/object.
How Many
Users:
All
processes
Page 71
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 71
Who Uses:
LCK? and the
process which wants to invalidate objects cached across all instances
When
Used:
When a valid
and existent database object is brought into the library cache, LCK? is asked
to
acquire the
lock in the correct mode (always S). It is held until the object becomes
invalid or
non-existent
or is aged out of the library cache. This lock is used to invalidate objects
cached
across all
instances. The process that wants to invalidate an object simply acquires the
lock in X
mode on the
object causing all instances to invalidate their cached objects responding to
BAST’s
and release
their IV locks on their objects; then, the invalidating process immediately
releases
the X lock.
Id1, Id2
Combination:
Object
Number, Timestamp.
Lock
Value Block:
Not Used.
Init.ora
Parameters:
None.
Scope:
Global Lock.
Deadlock
Sensitive:
No.
Operation:
Synchronous.
How Many
Resources:
1 / job
How Many
Locks:
1/job.
How Many
Users:
All SNP
process
Who Uses:
SNP?
processes.
When Used:
To
synchronize access to the job.
Id1, Id2
Combination:
0, job
number.
Lock
Value Block:
Not Used.
Init.ora
Parameters:
job_queue_processes,
snapshot_refresh_processes.
Scope:
Local and
Global Enqueue.
Deadlock
Sensitive:
Page 72
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 72
Yes.
Operation:
Synchronous.
How Many
Resources:
1/ redo
thread, i.e. 1/instance in 7.x.
How Many
Locks:
1/instance.
How Many
Users:
1/ instance
Who Uses:
LGWR’s
When
Used:
Used to keep
on-line redo logs archiving off of idle instances, while other active instances
generate
redo and archive on-line logs. The intent is to keeps archive streams from all
participating
instances close to each other in time, so that a set of archived logs for all
redo
streams/instances
can easily be identified and managed for backup or recovery. Also used in
executing
the ALTER SYSTEM ARCHIVE LOG CURRENT command, which is used to cause
all
instances to archive their current logs.
Id1, Id2
Combination :
Always 0,
Thread Number.
Lock
Value Block:
Not Used.
Init.ora
Parameters:
thread
Scope:
Global Lock.
Deadlock
Sensitive:
No.
Operation:
Synchronous.
How Many
Resources:
1 / database
object referenced during parsing or compiling of SQL or PL/SQL statements
(table,
view,
procedure, function, package, package body, trigger, index, cluster, synonym);
released at
the end of
parse or compilation; cursors (SQL and PL/SQL areas), pipes and any other
transient
objects do
not use this lock.
How Many
Locks:
How Many
Users:
Page 73
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 73
All
processes
Who Uses:
All
processes
When
Used:
When a
database object (table, view, procedure, function, package, package body,
trigger, index,
cluster,
synonym) is referenced during parsing or compiling of a SQL (DML/DDL) or PL/SQL
statement,
the process parsing or compiling the statement acquires the lock in the correct
mode.
It is held
until the parse or compilation completes (for the duration of the parse call).
In V6,
these locks
are known as parse or DDL locks except that in V6 these locks are held for the
duration of
the cursor (i.e., until the cursor is closed); in Oracle7, these locks are held
only
during the
parse call.
Id1, Id2
Combination:
Lock
Value Block:
Not Used.
Init.ora
Parameters:
shared_pool
Scope:
Global Lock.
Deadlock
Sensitive:
Yes.
Operation:
Synchronous.
How Many
Resources:
1/ read-only
database being mounted to the read-write database
How Many
Locks:
How Many
Users:
Any process
trying to mount a read-only database under Trusted or any process asking
for library
cache cleanout of remote objects.
Who Uses:
Any process
trying to mount a read-only database under Trusted or any process asking for
library
cache cleanout of remote objects.
When
Used:
Only used by
Trusted Oracle7 currently.
Id1, Id2
Combination:
Lock
Value Block:
Init.ora Parameters:
Scope:
Page 74
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 74
Deadlock
Sensitive:
No.
Operation:
Synchronous.
How Many
Resources:
1+1/ file.
How Many
Locks:
1 for each
DBWR in each instance.
How Many
Users:
Any proc.
doing media recovery or ALTER DATABASE OPEN RESETLOGS
Who Uses:
Any proc.
doing media recovery or ALTER DATABASE OPEN RESETLOGS
When
Used:
Held shared
by any process performing media recovery to prevent any other process from
performing
an ALTER DATABASE OPEN RESETLOGS. The file access locks are held shared
by on-line
users of a particular datafile to prevent recovery actions from taking place.
They are
held
exclusive by processes performing recovery actions on that file.
Id1, Id2
Combination:
TABLE 26.
Id1, Id2 Combinations for MR Lock
Id1 Id2
Meaning
0 0 Media
Recovery Enqueue
X - doing Reset Logs
S - doing Recovery
file# 0 File
Access Enqueue, used to coordinate recovery.
Lock
Value Block:
Not Used.
Init.ora
Parameters:
db_files
Scope:
Local and
Global Enqueue
Deadlock
Sensitive:
Yes.
Operation:
Synchronous.
How Many
Resources:
Page 75
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 75
1/ row cache
entry of any type (corresponds to dc_* params) (ts$, fet$, seg$, undo$,
uet$, tsq$,
file$, user$, obj$, tables, views, clusters, synonyms, user names, object ids,
constraints,
sequence cache entries, profiles, database links, histograms).
How Many
Locks:
1 per
instance.
How Many
Users:
All
processes
Who Uses:
All
processes
When
Used:
Used to keep
row cache entries coherent across instances. Row cache entries are caches of
recently
used data dictionary information. The second part of the type [A-Z] is
determined by
the type of
the parent cache (0=A, 1=B ... 25=Z)
Id1, Id2
Combinations:
Both values
are being hashed from ??.
Lock
Value Block:
Init.ora
Parameters:
shared_pool
Scope:
Global Lock.
Deadlock
Sensitive:
No.
Operation:
Synchronous.
How Many
Resources:
1 / database
object that is currently cached in the library cache (table, view, procedure,
function,
package,
package body, trigger, index, cluster, synonym); in the library cache, a
database object
is cached in
2 parts: “handle” and “object”; only when the “object” part is cached, this
lock is
held;
cursors (SQL and PL/SQL areas), pipes and any other transient objects do not
use this lock.
How Many
Locks:
1 per process
pinning the cursor.
How Many
Users:
All
processes
Who Uses:
Foregrounds.
When
Used:
Used when
pinning “object” parts of database objects (see above) in an instance’s library
cache.
Held until
the “object” part is flushed or aged out (e.g., as the result of another object
needing
cache space,
and the object to be flushed is not pinned or in use).
Id1, Id2
Combinations:
Page 76
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 76
Lock
Value Block:
Init.ora
Parameters:
cursor_space_for_time.
Scope:
Global Lock.
Deadlock
Sensitive:
No.
Operation:
Synchronous.
How Many
Resources:
1
How Many
Locks:
How Many
Users:
All
processes either accessing or modifying the password file.
Who Uses:
All
processes either accessing or modifying the password file.
When
Used:
Acquired
exclusive to read or update the password file.
Id1, Id2
Combination:
Lock
Value Block:
Init.ora
Parameters:
Scope:
a
Deadlock
Sensitive:
No.
Operation:
Synchronous.
How Many
Resources:
1/ parallel
query, index, recovery process group
How Many
Locks:
How Many
Users:
Page 77
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 77
All
processes trying to add parallel slaves to a slave server group
Who Uses:
All
processes trying to add parallel slaves to a slave server group
When
Used:
To
synchronize the creation of new parallel slave servers and their addition to a
parallel slave
group on
behalf of a parallel query, a parallel index creation, or a parallel recovery
session.
Id1, Id2
Combinations:
Lock
Value Block:
Init.ora
Parameters:
Scope:
Deadlock
Sensitive:
No.
How Many
Resources:
1
How Many
Locks:
1/process
How Many
Users:
Any process
starting up a background process or an MTS server.
Who Uses:
Any process
starting up a background process or an MTS server.
When
Used:
Acquired
exclusive to serialize creation of background or MTS server processes.
Id1, Id2
Combinations:
0, 0.
Lock
Value Block:
Not used.
Init.ora
Parameters:
processes
Scope:
Local.
Deadlock
Sensitive:
No.
Operation:
Synchronous.
Page 78
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 78
How Many
Resources:
1/ parallel
slave server process
How Many
Locks:
1/process
How Many
Users:
All
processes working on behalf of a parallel query, index creation, or recovery
action.
Who Uses:
All
processes working on behalf of a parallel query, index creation, or recovery
action.
When
Used:
Used to
control execution flow of parallel slaves for parallel query, parallel index
creation, and
parallel
recovery.
Id1, Id2
Combination:
Instance Id,
Server Id
Instance Id
<< 16, Server Id
Lock
Value Block:
process
queue at remote site (pointer), flag (1 byte)(0x01=descriptor initialized,
0x02=Query
Coordinator
Queue, 0x80=Enqueue test bit), server id (1 byte), instance id (2 bytes), OS
specific
descriptor
(8 bytes or less).
Init.ora
Parameters:
min_parallel_servers,
max_parallel_servers.
Scope:
Local,
Global.
Deadlock
Sensitive:
No.
Operation:
Synchronous.
How Many
Resources:
Up to 12,
one for LGWR, one for DBWR, and one for each configured LCK process
[LCK0-LCK9]
How Many
Locks:
How Many
Users:
Up to
3-12/instance
Who Uses:
Each of the
set above, and by processes testing to see if instance has failed.
When
Used:
Used to
detect the death of an instance after a BL lock is found to be invalid. Each is
acquired
exclusive by
the associated process of each instance. If they can ever all be granted for a
given
set of
processes associated with a particular instance, then instance failure can be
inferred.
Page 79
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 79
Id1, Id2
Combination:
TABLE 27.
Id1 Id2
Meaning
Thread 0
Thread 1
LGWR
Thread 2
DBWR
Thread 3
LCK0
Thread 4
LCK1
Thread 5
LCK2
Thread 6 LCK3
Thread 7
LCK4
Thread 8
LCK5
Thread 9
LCK6
Thread 10
LCK7
Thread 11
LCK8
Thread 12
LCK9
Lock
Value Block:
Not Used.
Init.ora
Parameters:
gc_lck_procs
Scope:
Local (Mount
Queue only), Global
Deadlock
Sensitive:
No.
Operation:
Synchronous.
How Many
Resources:
1
How Many
Locks:
1/process
How Many
Users:
All
processes
Who Uses:
All
processes
When
Used:
The System
Commit Number (SCN) is used to serialize time within a single instance, and
across
all
instances. This lock resource caches the current value of the SCN, the value is
incremented in
response to
many database event, but most notably COMMIT WORK. Access to the SCN lock
value to get
and store the SCN is batched on most cluster implementations, so that every
process
that needs a
new SCN gets one and stores a new value back on one instance, before the SCN
Page 80
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 80
lock is
released so that it may be granted to another instance. Processes get the SC
lock once and
then use
conversion operations to manipulate the lock value.
Id1, Id2
Combination:
TABLE 28.
Id1 Id2
Meaning
0 0 SCN
Global lock
0 2 Local
SCN batching enqueue
Lock
Value Block:
System
Commit Number, System Commit Number.
Init.ora
Parameters:
processes
Scope:
Local
(batching enqueue only), Global.
Deadlock
Sensitive:
No.
Operation:
Synchronous.
How Many
Resources:
1
How Many
Locks:
1 for each
instance.
How Many
Users:
1/ instance
Who Uses:
SMON’s
When
Used:
Used
whenever an SMON wakes up to see if there is work to do. Acquired when SMON’s
are
started in
NULL mode, and converted to exclusive and back when checking for work to do,
currently
every five minutes.
Id1, Id2
Combinations:
Always 0,
Always 0.
Lock
Value Block:
Timestamp (4
bytes) in seconds of when SMON was run the last time.
Init.ora
Parameters:
No.
Scope:
Global.
Deadlock
Sensitive:
No.
Operation:
Synchronous.
Page 81
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 81
How Many
Resources:
1/ cached
sequence
How Many
Locks:
How Many
Users:
All
processes using sequences.
Who Uses:
All
processes using sequences.
When
Used:
When using
sequence number generators.
Id1, Id2
Combinations:
Always 0,
Object Number.
Lock
Value Block:
Init.ora
Parameters:
sequence_cached_entries
Scope:
Deadlock
Sensitive:
No.
Operation:
Synchronous.
How Many
Resources:
1/ cached
sequence
How Many
Locks:
How Many
Users:
All
processes using sequences.
Who Uses:
All
processes using sequences.
When
Used:
When using
sequence number generators.
Id1, Id2
Combinations:
Always 0,
Object Number.
Lock
Value Block:
Init.ora
Parameters:
Page 82
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 82
Scope:
Deadlock
Sensitive:
No.
Operation:
Synchronous.
How Many
Resources:
1/ cached
sequence
How Many
Locks:
How Many
Users:
All
processes using sequences.
Who Uses:
All
processes using sequences.
When
Used:
When using
sequence number generators.
Id1, Id2
Combinations:
Always 0,
Always 0.
Lock
Value Block:
Init.ora
Parameters:
Scope:
Deadlock
Sensitive:
No.
Operation:
Synchronous.
How Many
Resources:
2/
tablespace
How Many
Locks:
How Many
Users:
All
processes using sort segments.
Who Uses:
All
processes using sert segments.
Page 83
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 83
When
Used:
When
sorting.
Id1, Id2
Combinations:.
TABLE 29.
Id1 Id2
Meaning
tablespace
id1 Local enqueue, to notify SMON to request some
action for
tablespace identified by tablespace id.
tablespace
id2 Global enqueue, to release space for releasing space
in
tablespace identified by tablespace id.
Lock
Value Block:
Init.ora
Parameters:
Scope:
Enqueue
(Local, Global)
Deadlock
Sensitive:
Yes.
Operation:
Synchronous.
How Many
Resources:
1
How Many
Locks:
How Many
Users:
Any process
allocating, freeing, or coalescing free space.
Who Uses:
Any process
allocating, freeing, or coalescing free space.
When
Used:
Used to
serialize space transactions across all instances, which are executed within a
transaction
when space
requires allocation or de-allocation. Also acquired by SMON or foreground
processes
when two or more physically adjacent free extents are coalesced back into one
extent.
Id1, Id2
Combinations:
Always 0,
Always 0.
Lock
Value Block:
Init.ora
Parameters:
Scope:
Local,
Global.
Deadlock
Sensitive:
Page 84
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 84
No.
Operation:
Synchronous.
How Many
Resources:
1/ cached
sequence
How Many
Locks:
How Many
Users:
All
processes using sequences.
Who Uses:
All
processes using sequences.
When
Used:
When using
sequence number generators.
Id1, Id2
Combinations:
Always 0,
Object Number.
Lock
Value Block:
Init.ora
Parameters:
Scope:
Local,
Global.
Deadlock
Sensitive:
No.
Operation:
Synchronous.
How Many
Resources:
2 + 2/
in-use, non-SYSTEM rollback segment
How Many
Users:
Any process
attempting to do transaction recovery.
Who Uses:
LGWR’s,
SMON’s and any process doing transaction recovery.
When
Used:
An SMON
acquires locks exclusive to do instance recovery. LGWR’s hold the locks shared
and/or
exclusive for in-use/on-line rollbacks segments.Held exclusive by foreground
processes
when brining
rollback segments on-line.
Id1, Id2
Combination:
TABLE 30.
Id1, Id2 Combinations for TA lock.
Id1 Id2
Meaning
Page 85
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 85
1 undo seg#
Instance lock on undo segment
2 0 Instance
lock on instance existence
3 undo seg#
instance lock on undo segment
4 0
mutual-exclusive lock starting up
5 0
transaction enqueue
Lock
Value Block:
Not Used.
Init.ora
Parameters:
gc_rollback_segments,
max_rollback_segments.
Scope:
Global.
Deadlock
Sensitive:
No.
Operation:
Synchronous.
How Many
Resources:
1/database +
1/ table referenced.
How many
Locks:
1/process
accessing a table in (update, insert,delete).
How Many
Users:
1/ instance
+ n/ table for n concurrent users of a table
Who Uses:
All
processes when referencing tables *and* dml_locks > 0
When
Used:
The instance
lock is used when an instance attempts to mount the database to ensure that all
participating
instances either have dml_locks = 0 or dml_locks != 0, if not ORA-61 is
returned
and the
mount attempt fails. The per table locks are acquired during the execution of a
transaction
when referencing a table with a DML statement so that the object is not dropped
or
altered
during the execution of the transaction, if and only if the dml_locks parameter
is nonzero.
Id1, Id2
Combinations:
TABLE 31.
Id1 Id2
Meaning
0 0 Check if
the instances have dml_locks = 0
0 object#
Global lock on table identified by object#
Lock
Value Block:
Not Used.
Init.ora
Parameters:
dml_locks
Scope:
Page 86
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 86
Local,
Global.
Deadlock
Sensitive:
No.
Operation:
Synchronous.
How Many
Resources:
2/ temporary
segment (e.g. for sorts)
1/
tablespace being dropped or having a rollback segment created in it.
How Many
Locks:
1 for each
process doing one of operations below.
How Many
Users:
All
processes using temp space, DROP TABLESPACE, or CREATE ROLLBACK SEGMENT
Who Uses:
All
processes using temp space, DROP TABLESPACE, or CREATE ROLLBACK
SEGMENT
When
Used:
The two
temporary segment locks are used for two different purposes on each temp
segment.
The first
use is to serialize the “high water mark” or where the highest allocated point
of the
segment is.
The second is used to serialize creation, use, and deletion of a temp segment.
It is
acquired by
LCK?. The tablespace enqueue is acquired either when dropping that tablespace
or
when
creating a rollback segment in it. The purpose is to avoid deadlocks that can
occur on
resource in
the row cache (dictionary cache).
Id1, Id2
Combinations:
TABLE 32.
Id1 Id2
Meaning
segment dba
0 Serialize access to the “high water mark”
segment dba
1 Create, use, delete temp segment
tablespace
number
2 prevent
deadlock during create rollback segment and create
tablespace.
Lock
Value Block:
Not Used.
Init.ora
Parameters:
_bump_highwater_mark_count
(7.3).
Scope:
Local,
Global
Deadlock
Sensitive:
No.
Operation:
Synchronous.
Page 87
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 87
How Many
Resources:
1
How Many
Locks:
1 per
instance.
How Many
Users:
Any process
starting an instance.
Who Uses:
Any process
starting an instance.
When
Used:
Used after
recovery of an instance at instance startup to allocate and initialize private
and public
rollback
segments to the instance being started. Acquired exclusive for the duration of
the
operation.
Id1, Id2
Combinations:
Always 0,
Always 0.
Lock
Value Block:
Not Used.
Init.ora
Parameters:
rollback_segments,
gc_rollback_segments (if running in Parallel mode).
Scope:
Global Lock.
Deadlock
Sensitive:
No.
Operation:
Synchronous.
How Many
Resources:
1/ active
transaction
How Many
Locks:
1/transaction
+ 1/process waiting for a locked row by that transaction.
How Many
Users:
1 + 1/
process waiting for something locked by this transaction
Who Uses:
All
processes
When
Used:
Acquired
exclusive when a transaction initiates its first change and held until the
transaction
does a
COMMIT or ROLLBACK. Also acquired exclusive by SMON when doing recovery
(undo) of a
transaction. Used as a queuing mechanism for processes waiting for something
locks
by a
transaction’s change to become available again.
Id1, Id2
Combinations:
undo segment
number << 16 | slot, sequence
Page 88
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 88
Lock Value
Block:
Not Used.
Init.ora
Parameters:
transactions
Scope:
Local and
Global Enqueue.
Deadlock
Sensitive:
Yes.
Operation:
Synchronous.
How Many
Resources:
As many as
put in use by the DBMS_LOCK package.
How Many
Locks:
1 per process
acquiring a lock on a resource.
How Many
Users:
As many as
are using the DBMS_LOCK package to synchronize user applications
between
processes on multiple instances.
Who Uses:
Any
application process using the DBMS_LOCK package.
When
Used:
In
accordance with the client applications use of the DBMS_LOCK package in PL/SQL.
Id1, Id2
Combinations:
Application
Dependent, Application Dependent.
Lock
Value Block:
Not Used.
Init.ora
Parameters:
No.
Scope:
Local and
Global Enqueue.
Deadlock
Sensitive:
Yes.
Operation:
Synchronous.
How Many
Resources:
1/ active
userid
How Many
Locks:
Page 89
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 89
How Many
Users:
1/ session
using a particular userid
Who Uses:
Processes
logging in to the database or doing DROP USER.
When
Used:
During
login, locks are acquired shared to ensure that no other process does a DROP
USER of
that
particular user’s information in the dictionary. Acquired exclusive during DROP
USER.
Id1, Id2
Combinations:
User Number,
Always 0.
Lock
Value Block:
Not Used.
Init.ora
Parameters:
No.
Scope:
Local and
Global Enqueue.
Deadlock
Sensitive:
Yes.
Operation:
Synchronous.
How Many
Resources;
1 per
rollback segment.
How Many
Locks:
1 per user
requesting access to a RBS.
How Many
Users:
1 ..
processes
Who Uses:
All
foregrounds, SMON and PMON.
When
Used:
To serialize
DDL on a given undo segment (rollback segment). It serializes the following
operations:
- CREATE
ROLLBACK SEGMENT
- DROP
ROLLBACK SEGMENT
- ALTER
ROLLBACK SEGMENT ONLINE
- ALTER
ROLLBACK OFFLINE
- ALTER
ROLLBACK SEGMENT SHRINK
- ALTER
ROLLBACK SEGMENT STORAGE
- Offlining
PENDING OFFLINE RBS by SMON
- SMON -
abortive offline cleanup.
- STARTUP.
Id1, Id2
Combination:
Page 90
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 90
Undo
Segment#, Always 0.
Lock
Value Block:
No.
Init.ora
Parameters:
gc_rollback_locks,
rollback_segments.
Scope:
Local and
Global Enqueue.
Deadlock
Sensitive:
Yes.
Operations:
Synchronous.
How Many
Resources:
1/ redo log
being archived
How many
Locks:
How Many
Users:
1/ instance
+ any foreground sessions which are archiving
Who Uses:
ARCH’s + any
foreground sessions which are archiving
When
Used:
Used to
determine whether or not a logfile is available for archiving (it is
unavailable if some
other
process is already archiving it). Acquired shared to do the test, and acquired
exclusive by
the process
actually doing archiving.
Id1, Id2
Combinations:
Log Number,
Always 0.
Lock
Value Block:
Not Used.
Init.ora
Parameters:
No.
Scope:
Local and
Global Enqueue.
Deadlock
Sensitive:
Yes.
Operation:
Synchronous.
How Many
Resources:
1/
registered attribute
Page 91
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 91
How many
Locks:
1/registered
attribute
How Many
Users:
1/ instance
+ any foreground sessions which are archiving
Who Uses:
DBWR’s hold
the lock. Any processes interested in the atribute reads the lock.
When
Used:
Used to
store attribute values. Read when a process wants to know a certain atribute of
another
process.
Id1, Id2
Combinations:
.
Lock
Value Block:
Init.ora
Parameters:
No.
Scope:
Global.
Deadlock
Sensitive:
No.
Operation:
Synchronous.
How Many
Resources:
1/ instance
+ (total lock, bitmap lock(s), join lock, recover lock)
How many
Locks:
How Many
Users:
1/ instance
+ any foreground sessions which are archiving
Who Uses:
DBWR’s hold
the locks. Foregrounds and backgrounds use the locks to register the instances.
When Used:
Used to
assign unique instance numbers to instances. Also used to notify instances
about system
reconfigurations
(instances coming up or down).
Id1, Id2
Combinations:
.
Lock
Value Block:
Init.ora
Parameters:
No.
Scope:
Global.
Deadlock
Sensitive:
Page 92
Description
of Oracle Wait Events and Enqueues 03/21/96 Page 92