oracle中enqueue,ORACLE: Enqueue 烂笔头

Oracle 的enqueue 包含以下模式：

模式代码

解释

1

Null mode

2

Sub-Share

3

Sub-Exclusive

4

Share

5

Share/Sub-Exclusive

6

Exclusive

Oracle的enqueue有如下类型：

Enqueue 缩写

缩写解释

BL

Buffer Cache management

BR

Backup/Restore

CF

Controlfile transaction

CI

Cross-instance Call Invocation

CU

Bind Enqueue

DF

Datafile

DL

Direct Loader Index Creation

DM

Database Mount

DR

Distributed Recovery Process

DX

Dirstributed Transaction

FP

File Object

FS

File Set

HW

High-water Lock

IN

Instance Number

IR

Instance Recovery

IS

Instance State

IV

Library Cache Invalidation

JI

Enqueue used during AJV snapshot refresh

JQ

Job Queue

KK

Redo Log “Kick”

KO

Multiple Object Checkpoint

L[A-p]

Library Cache Lock

LS

Log start or switch

MM

Mount Definition

MR

Media recovery

N[A-Z]

Library Cache bin

PE

Alter system set parameter =value

PF

Password file

PI

Parallel slaves

PR

Process startup

PS

Parallel slave synchronization

Q[A-Z]

Row Cache

RO

Object Reuse

RT

Redo Thread

RW

Row Wait

SC

System Commit Number

SM

SMON

SN

Sequence Number

SQ

Sequence Number Enqueue

SR

Synchronized replication

SS

Sort segment

ST

Space management transaction

SV

Sequence number Value

TA

Transaction recovery

TC

Thread Checkpoint

TE

Extend Table

TM

DML enqueue

TO

Temporary Table Object Enqueue

TS

Temporary Segement(also TableSpace)

TT

Temporary Table

TX

Transaction

UL

User-defined Locks

UN

User name

US

Undo segment, Serialization

WL

Being Written Redo Log

XA

Instance Attribute Log

XI

Instance Registration Lock

SELECT S.RESOURCE_NAME,

S.CURRENT_UTILIZATION ,

S.MAX_UTILIZATION  ,

S.INITIAL_ALLOCATION  ,

S.LIMIT_VALUE

FROM V$RESOURCE_LIMIT

S

WHERE S.RESOURCE_NAME IN ('enqueue_resources',

'enqueue_locks',

'dml_locks',

'processes',

'processes');

SELECT S.ADDR,

S.KADDR,

S.SID,

S.TYPE,

S.ID1,

S.ID2,

S.LMODE,

S.REQUEST,

S.CTIME,

S.BLOCK

FROM V$ENQUEUE_LOCK S;

SELECT s.sid,

s.event,

s.p1,

s.p1raw,

chr(bitand(s.p1, -16777216) / 16777215) ||

chr(bitand(s.p1, 16711680) / 65535) AS "TYPE",

MOD(s.p1, 16) AS "MODE"

FROM v$session_wait s ;

SELECT s.sid,

s.event,

s.p1,

s.p1raw,

chr(bitand(s.p1, -16777216) / 16777215) ||

chr(bitand(s.p1, 16711680) / 65535) AS "TYPE",

MOD(s.p1, 16) AS "MODE"

FROM v$session_wait s

WHERE s.event = 'enqueue';

SELECT * FROM V$ENQUEUE_STATISTICS;

SELECT * FROM V$ENQUEUE_STAT;

SELECT * FROM V$ENQUEUE_LOCK;

SELECT * FROM V$LOCK;

SELECT * FROM V$EVENT_NAME WHERE NAME  LIKE

'enq: MR%';

SELECT * FROM V$EVENT_NAME WHERE NAME  LIKE

'enq: AE%';

SELECT * FROM V$EVENT_NAME WHERE NAME  LIKE

'enq: CF%';

SELECT * FROM V$LOCK WHERE TYPE IN ('MR','AE','CF');

select sid,event,P1TEXT,state from v$session_wait where event

not in ('SQL*Net message from client');

SID

EVENT P1TEXT STATE

------- ------------------------------

------------------------------ -------------------

2719 rdbms ipc

message timeout WAITING

2720 rdbms ipc

message timeout WAITING

2724 rdbms ipc

message timeout WAITING

2725 ASM background

timer WAITING

2729 rdbms ipc

message timeout WAITING

2731 rdbms ipc

message timeout WAITING

2733 rdbms ipc

message