« 工具: ass109.awk 分析 Oracle 的跟踪文件 | Blog首页 | 顺利结束北京站 - OU 深入解析Oracle 培训课程 »

数据字典视图之:V$ENQUEUE_STATISTICS 结构

V$ENQUEUE_STATISTICS用于显示队列锁的统计数据：

V$ENQUEUE_STATISTICS displays statistics on the number of enqueue (lock) requests for each type of lock. V$ENQUEUE_STATISTICS encompasses V$ENQUEUE_STAT and gives more detailed information (several rows for same enqueues with different reasons).

其视图结构定义如下：

SELECT st.inst_id, eqt.NAME, st.ksqsttyp, st.ksqstrsn, st.ksqstreq,
       st.ksqstwat, st.ksqstsgt, st.ksqstfgt, st.ksqstwtm, st.ksqstexpl,
       st.ksqstevidx
  FROM x$ksqst st, x$ksqeqtyp eqt
 WHERE (st.inst_id = eqt.inst_id)
   AND (st.ksqsttyp = eqt.resname)
   AND (st.indx > 0);

这里包含了非常重要的一个信息，就是锁定及其描述：
SQL> col REQ_DESCRIPTION for a100
SQL> set linesize 120
SQL> l
  1* select EQ_TYPE,REQ_DESCRIPTION from V$ENQUEUE_STATISTICS
SQL> /

EQ REQ_DESCRIPTION
-- ----------------------------------------------------------------------------------------------------
KM Synchronizes various Resource Manager operations
KT Synchronizes accesses to the current Resource Manager plan
CI Coordinates cross-instance function invocations
PR Synchronizes process startup
PE Synchronizes system parameter updates
PG Synchronizes global system parameter updates
FP Synchronizes various File Object(FOB) operations
PV Synchronizes slave start_shutdown
PV Synchronizes instance shutdown_slvstart
FM Synchronizes access to global file mapping state
XY Lock used for internal testing

EQ REQ_DESCRIPTION
-- ----------------------------------------------------------------------------------------------------
AS Synchronizes new service activation
AS modifying service attributes
PD Prevents others from updating the same property
RU Serializes rolling upgrade operations
RU Results of rolling upgrade CIC
CF Synchronizes accesses to the controlfile
SW Coordinates the 'alter system suspend' operation
DS Prevents a database suspend during LMON reconfiguration
TC Lock held to guarantee uniqueness of a tablespace checkpoint
TC Lock of setup of a unqiue tablespace checkpoint in null mode
PW DBWR 0 holds enqueue indicating prewarmed buffers present in cache

EQ REQ_DESCRIPTION
-- ----------------------------------------------------------------------------------------------------
PW Direct Load needs to flush prewarmed buffers if DBWR 0 holds enqueue
RO Coordinates flushing of multiple objects
RO Coordinates fast object reuse
KO Coordinates fast object checkpoint
WR Coordinates access to logs by Async LNS and ARCH/FG
WL Coordinates access to redo log files and archive logs
RN Coordinates nab computations of online logs during recovery
DF Enqueue held by foreground or DBWR when a datafile is brought online in RAC
IS Enqueue used to synchronize instance state changes
FS Enqueue used to synchronize recovery and file operations or synchronize dictionary check
DM Enqueue held by foreground or DBWR to syncrhonize database mount/open with other operations

EQ REQ_DESCRIPTION
-- ----------------------------------------------------------------------------------------------------
RP Enqueue held when resilvering is needed or when datablock is repaired from mirror
RT Thread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status
KK Lock held by open redo thread, used by other instances to force a log switch
IR Synchronizes instance recovery
IR Synchronizes parallel instance recovery and shutdown immediate
MR Lock used to coordinate media recovery with other uses of datafiles
BR Lock held to prevent file from decreasing in physical size during RMAN backup
BR Lock held to allow cleanup from backup mode during an RMAN proxy-copy backup
ID Lock held to prevent other processes from performing controlfile transaction while NID is running
MN Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the s
   ame LogMiner session

EQ REQ_DESCRIPTION
-- ----------------------------------------------------------------------------------------------------

PL Coordinates plug-in operation of transportable tablespaces
SB Synchronizes Logical Standby metadata operations
XR Lock held during database quiesce
XR Lock held during database force logging mode
CT Lock held during change tracking space management operations that affect the entire change tracking
   file

CT Lock held during change tracking space management operations that affect just the data for one threa
   d


EQ REQ_DESCRIPTION
-- ----------------------------------------------------------------------------------------------------
CT Lock held by one instance while change tracking is enabled, to guarantee access to thread-specific r
   esources

CT Lock held while enabling or disabling change tracking, to ensure that it is only enabled or disabled
    by one user at a time

CT Lock held while enabling or disabling change tracking in RAC
CT Lock held while enabling or disabling change tracking in RAC
CT Lock held to ensure that only one CTWR process is started in a single instance
CT Lock held to ensure that change tracking data remains in existence until a reader is done with it
RS Lock held to prevent file from accessing during space reclaimation

EQ REQ_DESCRIPTION
-- ----------------------------------------------------------------------------------------------------
RS Lock held to prevent file from accessing while reusing circular record
RS Lock held to prevent deleting file to reclaim space
RS Lock held to prevent aging list update
RS Lock held to make alert level persistent
RS Lock held to read alert level
RS Lock held to write alert level
FL Synchronization
FL Enqueue used to synchronize Flashback Database and and deletion of flashback logs.
FD Synchronization
FD Synchronization
FD Synchronization

EQ REQ_DESCRIPTION
-- ----------------------------------------------------------------------------------------------------
FD Synchronization
FD Synchronization
TM Synchronizes accesses to an object
ST Synchronizes space management activities in dictionary-managed tablespaces
TF Serializes dropping of a temporary file
DT Serializes changing the default temporary table spaceand user creation
HW Lock used to broker the high water mark during parallel inserts
TS Serializes accesses to temp segments
FB Ensures that only one process can format data blcoks in auto segment space managed tablespaces
SS Ensures that sort segments created during parallel DML operations aren't prematurely cleaned up
SK Serialize shrink of a segment

EQ REQ_DESCRIPTION
-- ----------------------------------------------------------------------------------------------------
TA Serializes operations on undo segments and undo tablespaces
TX Lock held by a transaction to allow other transactions to wait for it
TX Lock held on a particular row by a transaction to prevent other transactions from modifying it
TX Allocating an ITL entry in order to begin a transaction
TX Lock held on an index during a split to prevent other operations on it
TW Lock held by one instance to wait for transactions on all instances to finish
US Lock held to perform DDL on the undo segment
SU Serializes access to SaveUndo Segment
TT Serializes DDL operations on tablespaces
IM Serializes block recovery for IMU txn
TD KTF dumping time/scn mappings in SMON_SCN_TIME table

EQ REQ_DESCRIPTION
-- ----------------------------------------------------------------------------------------------------
TE KTF broadcasting
CN during registration
CN during transaction commit to see concurrent registrations
CN during descriptor initialization
SQ Lock to ensure that only one process can replenish the sequence cache
HV Lock used to broker the high water mark during parallel inserts
DL Lock to prevent index DDL during direct load
HQ Synchronizes the creation of new queue IDs
HP Synchronizes accesses to queue pages
DV Synchronizes access to lower-version Diana (PL/SQL intermediate representation)
RW Lock held by CREATE/ALTER/DROP materialized viewwhile updating materialized view flags in detail tab

EQ REQ_DESCRIPTION
-- ----------------------------------------------------------------------------------------------------
   les

OC Synchronizes write accesses to the outline cache
OL Synchronizes accesses to a particular outline name
CU Recovers cursors in case of death while compiling
PF Synchronizes accesses to the password file
IL Synchronizes accesses to internal label data structures
CL Synchronizes accesses to label cache when dropping a label
CL Synchronizes accesses to label cache for label comparison
MK changing values in enc$
OW initializing the wallet context

EQ REQ_DESCRIPTION
-- ----------------------------------------------------------------------------------------------------
OW terminate the wallet context
AU lock held to operate on the audit index file
DX Serializes tightly coupled distributed transaction branches
DR Serializes the active distributed recovery operation
JD Synchronizes dates between job queue coordinator and slave processes
JQ Lock to prevent multiple instances from running a single job
DB Synchronizes modification of database wide supplementallogging attributes
MD Lock held during materialized view log DDL statements
MS Lock held during materialized view refresh to setup MV log
PI Communicates remote Parallel Execution Server Process creation status
PS Parallel Execution Server Process reservation and synchronization

EQ REQ_DESCRIPTION
-- ----------------------------------------------------------------------------------------------------
TO Synchronizes DDL and DML operations on a temp object
IT Synchronizes accesses to a temp object's metadata
BF Allocate a bloom filter in a parallel statement
KP Synchronizes kupp process startup
SR Coordinates replication / streams operations
SI Prevents multiple streams tabel instantiations
ZG Coordinates file group operations
IA
JI Lock held during materialized view operations (like refresh, alter) to prevent concurrent operations
    on the same materialized view


EQ REQ_DESCRIPTION
-- ----------------------------------------------------------------------------------------------------
AT Serializes 'alter tablespace' operations
UL Lock used by user applications
SE Synchronizes transparent session migration operations
TQ TM access to the queue table
TQ TM access to the queue table
TQ TM access to the queue table
DP Synchronizes access to LDAP parameters
MH Lock used for recovery when setting Mail Host for AQ e-mail notifications
ML Lock used for recovery when setting Mail Port for AQ e-mail notifications
PH Lock used for recovery when setting Proxy for AQ HTTP notifications
SF Lock used for recovery when setting Sender for AQ e-mail notifications

EQ REQ_DESCRIPTION
-- ----------------------------------------------------------------------------------------------------
XH Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications
WA Lock used for recovery when setting Watermark for memory usage in AQ notifications
RF Ensures r/w atomicity of DG configuration metadata
RF Ensures atomicity of log transport setup
RF Anoints 1 instance's DMON as chief to other instances' DMONs
RF Anoints 1 instance's DMON as healthcheck master
RF Synchronizes apply instance failure detection and failover operation
RF Synchronizes selection of the new apply instance
RF Synchronizes critical apply instance among primary instances
RF Means for sharing Fast-Start Failover state changes around a RAC
RF Means for waiting for lock request to timeout

EQ REQ_DESCRIPTION
-- ----------------------------------------------------------------------------------------------------
RF Means for detecting whether standby has connectivity to primary
RF Means for synchronizing Fast-Start Failover lock operations
RF Means for detecting whether database has been observed recently
AW Global access synchronization to the AW$ table
AW Row lock synchronization for the AW$ table
AW Synchronizes user accesses to a particular workspace
AW In-use generation state for a particular workspace
AG Synchronizes generation use of a particular workspace
AO Synchornizes access to objects and scalar variables
OQ Synchronizes access to olapi history allocation
OQ Synchronizes access to olapi history flushing

EQ REQ_DESCRIPTION
-- ----------------------------------------------------------------------------------------------------
OQ Synchronizes access to olapi history parameter CB
OQ Synchronizes access to olapi history closing
OQ Synchronizes access to olapi history globals
AM Registers DB instance to ASM client state object hash
AM Prevent DB instance registration during ASM instance shutdown
AM Reserve a Rollback COD entry
CM serialize access to instance enqueue
CM indicate ASM diskgroup is mounted
XQ prevent relocation during _recovery_asserts checking
XQ wait for recovery before doing relocation
AD Synchronizes accesses to a specific ASM disk AU

EQ REQ_DESCRIPTION
-- ----------------------------------------------------------------------------------------------------
AD Synchronizes accesses to a specific ASM disk AU
DG Synchronizes accesses to ASM disk groups
DD Synchronizes local accesses to ASM disk groups
HD Serializes accesses to ASM SGA data structures
DN Serializes group number generations
FA Synchronizes accesses to open ASM files
FR begin recovery of disk group
FG only 1 process in the cluster may do ACD relocation in a disk group
FG resolve race condition to acquire Disk Group Redo Generation Enqueue
FG resolve race condition to acquire Disk Group Redo Generation Enqueue
FT allow LGWR to generate redo in this thread

EQ REQ_DESCRIPTION
-- ----------------------------------------------------------------------------------------------------
FT prevent LGWR from generating redo in this thread
FC LGWR opens an ACD thread
FC SMON recovers an ACD thread
RB Serializes ASM rollback recovery operations
PT Synchronizes access to ASM PST metadata
WF This enqueue is used to serialize the flushing of snapshots
WP This enqueue handles concurrency between purging and baselines
FU This enqueue is used to serialize the capture of the DB Feature           Usage and High Water Mark
   Statistics

MW This enqueue is used to serialize the calibration of the           manageability schedules with the

EQ REQ_DESCRIPTION
-- ----------------------------------------------------------------------------------------------------
   Maintenance Window

TB Synchronizes writes to the SQL Tuning Base Existence Cache
TB Synchronizes writes to the SQL Tuning Base Existence Cache
SH Should seldom see this contention as this Enqueue is always  acquired in no-wait mode
AF This enqueue is used to serialize access to an advisor task
TL Serializes threshold log table read and update
JS Synchronizes accesses to the job cache
JS Lock to prevent job from running elsewhere
JS Lock to recover jobs running on crashed RAC inst
JS Lock on internal scheduler queue

EQ REQ_DESCRIPTION
-- ----------------------------------------------------------------------------------------------------
JS Scheduler non-global enqueues
JS Lock obtained when cleaning up q memory
JS Lock got when adding subscriber to event q
JS Lock got when dropping subscriber to event q
JS Lock got when doing window open/close
JS Lock got during event notification

208 rows selected.

以上信息来自Oracle 10g。

-The End-

By eygle on 2009-11-20 08:08 | Comments (1) | | 2460 |

Knowledge