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 


 

 

 


 

 

 
 

enqueue memory scripts

Oracle Database Tips by Donald BurlesonJuly 12, 2013

 

Shared Memory Structures

Enqueues are shared memory structures that serialize access to database resources and are associated with a session or transaction. In Real Application Clusters (RAC), enqueues can be global to a database. If Real Application Clusters are not enabled, enqueues are then local to one instance.

Also see Oracle enqueues.

An enqueue is a lock that protects a shared resource, such as data, in order to prevent processes from updating the same data simultaneously. An enqueue includes a First In First Out (FIFO) queuing mechanism.

Enqueue waits usually point to TX enqueues, TM enqueues, ST enqueues and HW enqueues, explained as follows:

TX Enqueue - This type of enqueue is the most common enqueue wait. For example, one issue could be multiple updates to the same bitmap index fragment. A single bitmap fragment may contain multiple rowids. When multiple users are trying to update the same fragment, a commit or rollback needs to be issued to free the enqueue. The situation is most likely to surface when multiple users are updating the same block. If there are no free ITL slots, a block-level lock could occur. This scenario can be avoided by increasing the initrans and/or maxtrans to allow multiple ITL slots, and/or by increasing the pctfree on the table.

TM Enqueue - This type of enqueue occurs during DML to prevent DDL to the affected object. Indexes on foreign keys should be used to avoid this general locking issue.

ST Enqueue - This type of enqueue is used for space management and allocation for dictionary-managed tablespaces. Use LMTs, or try to preallocate extents or at least make the next extent larger for problematic dictionary-managed tablespaces.

HW Enqueue - This type of enqueue is used with the high-water mark of a segment; manually allocating the extents can circumvent this wait.



The following script will display three simple reports for analyzing the enqueues of the database:

rem
rem ENQUEUES9i.SQL
rem Mike Ault
rem
ttitle 'Enqueues Report'
spool enqueues
prompt Enqueues
col name format a25
col lock format a4 heading 'Lock'
col gets format 9,999,999 heading 'Gets'
col waits format 9,999,999 heading 'Waits'
col Mode format a4

SELECT *
FROM v$sysstat
WHERE class=4
;

SELECT chr(bitand(p1,-16777216)/16777215)||
chr(bitand(p1, 16711680)/65535) "Lock",
to_char( bitand(p1, 65535) ) "Mode"
FROM v$session_wait
WHERE event = 'enqueue'
/

Prompt Enqueue Stats

select * from v$enqueue_stat where cum_wait_time>0
order by cum_wait_time desc
/
spool off
ttitle off
Code Depot Username = reader, Password = arsenal
Enqueues

Wed Sep 22 page 1
Enqueues Report

INST_ID STATISTIC# NAME CLASS VALUE
------- ---------- ------------------------- ---------- ----------
4 22 enqueue timeouts 4 65
4 23 enqueue waits 4 54133624
4 24 enqueue deadlocks 4 0
4 25 enqueue requests 4 252483462
4 26 enqueue conversions 4 425394
4 27 enqueue releases 4 252482896

6 rows selected.

Lock Stats

Wed Sep 22 page 1
Enqueues Report

INST_ID Lock Mode
------- ---- ----
4 US 6
4 US 6
4 US 6
4 TX 6
4 TX 6

5 rows selected.

Enqueue Stats


Wed Sep 22 page 1
Enqueues Report

INST_ID EQ TOTAL_REQ# TOTAL_WAIT# SUCC_REQ# FAILED_REQ# CUM_WAIT_TIME
------- -- ---------- ----------- ---------- ----------- -------------
4 US 54876879 53410542 54876822 0 1533644257
4 TX 63851995 461325 63867209 0 11031417
4 SQ 585304 243592 585304 0 5375456
4 CF 234529 1617 234470 59 24744
4 FB 12902 9571 12902 0 26668
4 HW 10517 4581 10517 0 16212
4 TA 2609 1568 2609 0 5491
4 CU 1594631 35 1594625 0 1426

8 rows selected.


The enqueues of concern will show the greatest amount of cumulative wait time (cum_wait_time) and will be shown first in the listing. The report also shows the enqueue related wait events and the current lock status for enqueue related activity. Since the report uses the GV version of the dynamic performance tables, it will list results for all instances in a RAC cluster. In the report shown above, instance 4 was the only active instance when the report was run.

   
Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training
 
 


 

 

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

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster