| |
 |
|
Oracle Tips by Burleson |
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.
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 9i script will display three simple reports for
analyzing the enqueues of the database
* ENQUEUES9i.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
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.
The above book excerpt is from:
Oracle
Tuning Power Scripts
With 100+ High Performance
SQL Scripts
Oracle In-Focus Series
ISBN
0-9744486-7-2
Mike Ault, Donald K.
Burleson. Harry Conway
http://www.rampant-books.com/book_2005_1_power_tuning.htm |
