| |
 |
|
Oracle Tips by Burleson |
Latch Sleeps
The next few sections of the report deal with
latches. The first two will be skipped as their use in tuning has
been found to be minimal. The third one which deals with latch sleeps
and spins is of interest. The listing below shows an example Latch
Sleeps report from the AWRRPT report.
Latch Sleep
Breakdown DB/Inst: SSD/ssd2 Snaps: 3-4
-> ordered by
misses desc
Latch Name
----------------------------------------
Get
Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3
--------------
----------- ----------- ---------- -------- -------- --------
cache buffers
chains
12,875,058 1,550 1,045 990 110 425
25
library cache
lock
26,765 444 96 352 88 4 0
cache buffers
lru chain
230,511 200 20 180 20 0 0
library cache
231,516 183 29 154 29 0 0
row cache
objects
101,189
160 2 158 2 0 0
enqueue hash
chains
241,402 148 2 146 2 0 0
session
allocation
38,907 102 58 53 41 7 1
KCL gc element
parent latch
587,433 89 26 70 12 7 0
gcs resource
hash
486,587 80 1 79 1 0 0
ges enqueue
table freelist
78,029 62 2 60 2 0 0
library cache
pin
173,817 53 7 46 7 0 0
ges resource
hash list
88,560 53 6 47 6 0 0
messages
208,554 30 2 28 2 0 0
undo global data
76,852 26 1 25 1 0 0
enqueues
95,817 18 1 17 1 0 0
shared pool
106,889 17 2 15 2 0 0
redo allocation
32,950 12 2 10 2 0 0
KJC message pool
free list
15,059 5 1 4 1 0 0
slave class
create
8 1 1 0 1 0 0
-------------------------------------------------------------
When a process requests a latch and there is no
latch available, the process spins and waits for the latch. This is
known as a latch sleep, and high numbers of sleeps for a
particular latch indicate that some tuning effort may be required in
the area of the Oracle system in which the latch is located. In the
example report, the highest sleeps are in the area of the buffer cache
and library caches. These could be due to transaction issues. They
could also be due to a particular area being too large, which is not
evident in this case. In all actuality for the activity level in
this database, the values are probably acceptable and would be
corrected by correcting the transaction problems previously noted.
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. |
|
