Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

The stats$enqueuestat Table

Oracle Tips by Burleson Consulting
Don Burleson

It's important to remember when you take a look at the stats$enqueuestat table that enqueue waits are a normal part of Oracle processing. It is only when you see an excessive amount of enqueue waits for specific processes that you need to be concerned in the tuning process.

Oracle locks protect shared resources and allow access to those resources via a queuing mechanism. A large amount of time spent waiting for enqueue events can be caused by various problems, such as waiting for individual row locks or waiting for exclusive locks on a table. Look at the highly contended enqueues in the enqueue activity section of the STATSPACK report to determine which enqueues are waited for. At snapshot time, this table is populated by querying the x$ksqst view:

SELECT ksqsttyp "Lock",
ksqstget "Gets",
ksqstwat "Waits"
FROM X$KSQST where KSQSTWAT>0;

Here is a description for this table in Oracle8 and Oracle8i:

SQL> desc STATS$ENQUEUESTAT;
Name Null? Type
----------------------------------------- -------- -------------------
SNAP_ID NOT NULL NUMBER(6)
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
NAME NOT NULL VARCHAR2(2)
GETS NUMBER
WAITS NUMBER


In Oracle9i the table name changes to stats$enqueue_stat.

SQL> desc STATS$ENQUEUE_STAT
Name Null? Type
----------------------------------------- -------- -----------
SNAP_ID NOT NULL NUMBER(6)
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
EQ_TYPE NOT NULL VARCHAR2(2)
TOTAL_REQ# NUMBER
TOTAL_WAIT# NUMBER
SUCC_REQ# NUMBER
FAILED_REQ# NUMBER
CUM_WAIT_TIME NUMBER


There are 26 lock types that could be captured in the stats$enqueuestat table, but only a handful of these are meaningful for Oracle tuning:

  • CI (Cross-instance lock) The CI lock is called the cross-instance lock, but
    it is not an Oracle Parallel Server lock. The name of this lock is misleading because it doesn't deal with distributed transactions. Rather, the CI lock is used to invoke specific actions in background processes on a specific instance or all instances. Examples would include checkpoints, log switches, or when the instance is shut down.

  • CU (Cursor bind lock) This is a cursor bind lock that is set whenever a cursor is used in an SQL statement.

  • JQ (Job queue lock) When a job is submitted using dbms_job.submit, the running job is protected by a JQ enqueue lock.

  • ST (Space management enqueue lock) This lock is usually associated
    with too much space management activity due to insufficient extent sizes. The ST enqueue needs to be held every time the session is allocating or deallocating extents.

  • TM (DML enqueue lock) This is a general table lock. Every time a session wants to lock a table (for an UPDATE, INSERT, or DELETE), a TM enqueue is requested. These locks are normally of very short duration, but they can be held for long periods when updating a table when foreign-key constraints have not been properly indexed.

  • TX (Transaction lock) A transaction is set when a change begins and is held until the transaction issues a COMMIT or ROLLBACK. When simultaneous tasks want to update the same rows, the TX locks allow the tasks to enqueue, waiting until the row is freed.

  • US (User lock) This lock is set when a session has taken a lock with the dbms_lock.request function. Application developers sometimes use this function to set serialization locks on parallelized tasks.

You can use the standard statsrep.sql script, or the custom rpt_enqueue.sql script to identify possible lock contention issues over time. Here is a sample report against the stats$enqueuestat table:


Yr. Mo Dy       Hr       NAME       GETS       WAITS
------------- -------------------- -------- ------------
2002-12-11      16       TX         1,784       2
2002-12-11      18       TM         1,789       20

 

Also,  see my notes on tuning to reduce index contention.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational