 |
|
Oracle Tips by Burleson |
Enqueue Activity
The enqueue
section of the AWRRPTreport is
next. It shows statistics for the various enqueue resources that
protect memory structures in the database and force serialization of
access to shared resources. The following listing shows the report
excerpt:
Enqueue
Activity DB/Inst: SSD/ssd2 Snaps: 3-4
-> Enqueue stats
gathered prior to 10i should not be compared with 10i data
-> ordered by
Wait Time desc, Waits desc
Enqueue Type
(Request Reason)
------------------------------------------------------------------------------
Requests
Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms)
------------
------------ ----------- ----------- ------------ --------------
TX-Transaction
(row lock contention)
202 202 0 200 213 1,065.90
TX-Transaction
(allocate ITL entry)
2 2 0 2 3 1,485.00
US-Undo Segment
935 935 0 77 1 10.91
HW-Segment High
Water Mark
418 418 0 256 0 .39
CF-Controlfile
Transaction
5,365 5,365 0 83 0 1.20
FB-Format Block
68 68 0 40 0 .75
TX-Transaction
8,081 8,081 0 16 0 1.88
TX-Transaction
(index contention)
24 24 0 17 0 1.18
TS-Temporary
Segment
10 10 0 9 0 2.22
PS-PX Process
Reservation
102 102 0 24 0 .42
TT-Tablespace
227 227 0 20 0 .00
TA-Instance Undo
16 16 0 13 0 .00
WF-SWRF Flush
11 11 0 11 0 .00
TM-DML
33,886 33,886 0 6 0
.00
UL-User-defined
6 6 0 6 0 .00
DR-Distributed
Recovery
2 2 0 2 0 .00
PI-Remote PX
Process Spawn Status
6 6 0 2 0 .00
AF-Advisor
Framework (task serialization)
7 7 0 1 0 .00
PW-Buffer Cache
PreWarm (flush prewarm buffers)
1 0 1 1 0 .00
IR-Instance
Recovery
114 114 0 1 0 .00
-------------------------------------------------------------
Transaction based enqueues, TX and TL, dominate
the report. These usually indicate transaction locking issues and
block sharing issues in the application. It should be noted that this
database was built using automatic segment space management
tablespaces, and in some high transaction environments, these have
shown ITL related waits, so that is probably what is seen here with
the TL enqueues. The TX enqueues
occur when an exclusive row lock
is requested and it indicates processes are competing for the same
data.
The AWRRPT
report section deals with undo
segments, the following shows this section of the report.
Undo Segment
Summary DB/Inst: SSD/ssd2 Snaps: 3-4
-> Undo segment
block stats:
-> uS -
unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
-> eS -
expired Stolen, eR - expired Released, eU - expired reUsed
Undo
Undo Num Max Qry Max Tx Snap OutOf uS/uR/uU/
TS#
Blocks Trans Len (s) Concurcy TooOld Space eS/eR/eU
----
------------ -------------- -------- -------- ------ -----
---------------
5
2,770 8,792 38 44 0 0 0/0/0/0/0/0
-------------------------------------------------------------
Undo Segment
Stats DB/Inst: SSD/ssd2 Snaps: 3-4
-> ordered by
Time desc
Undo Num Max Qry Max Tx Snap OutOf
uS/uR/uU/
End
Time Blocks Trans Len (s) Concy TooOld Space eS/eR/eU
------------ ----------- ------------ ------- ------- ------ -----
------------
28-Jul
20:02 123 141 0 3 0 0
0/0/0/0/0/0
28-Jul
19:52 5 36 0 1 0 0
0/0/0/0/0/0
28-Jul
19:42 423 1,433 0 38 0 0
0/0/0/0/0/0
28-Jul
19:32 747 2,631 22 39 0 0
0/0/0/0/0/0
28-Jul
19:22 687 2,211 38 44 0 0
0/0/0/0/0/0
28-Jul
19:12 365 1,276 0 34 0 0
0/0/0/0/0/0
28-Jul
19:02 420 1,064 0 16 0 0
0/0/0/0/0/0
-------------------------------------------------------------
From this report, the stress on the undo segments
can be determined. In the example above, there were no
snapshot too old errors and
no undo segment ran out of space.
In an OLTP type benchmark, an experienced DBA would not expect to see
out of space or
snapshot too old errors. The
high number of transactions per undo segments should be noted as this
could be why some undo contention exists. However, since the database
was using automated undo management, there is not much benefit to
using SSD.
The
above book excerpt is from:
Oracle
Solid State Disk Tuning
High Performance Oracle
tuning with RAM disk
ISBN
0-9744486-5-6
Donald K. Burleson & Mike Ault
http://www.rampant-books.com/book_2005_1_ssd.htm
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
