| |
 |
|
Oracle
Metric
undo segment extension
Oracle Tips by Burleson Consulting
|
The
undo segment extension
Oracle metric occurs when the undo segment extension is being
extended or shrunk. The session must wait until the operation on the
undo segment has finished.
select
swh.seq#,
sess.sid,
sess.username username,
swh.event event,
swh.p1,
swh.p2
from
v$session
sess,
v$session_wait_history swh
where
sess.sid = 74
and
sess.sid = swh.sid
order by
swh.seq#;
SEQ# SID USERNAME
EVENT P1 P2
---- --- --------
--------------------------- ---------- ----------
1 74 PCS buffer busy
waits 3 21277
2 74 PCS latch: cache
buffers chains 1556332118 172
3 74 PCS latch: cache
buffers chains 1556332118 172
4 74 PCS buffer busy
waits 4 155
By querying the
$session_wait_history
view, it becomes clear that users were faced with some additional
waits including buffer busy waits for UNDO segment determined by P1=3 and
P2=21277. Also, it appears that the session had many waits for
cache buffer chain latch. This contention has two likely causes:
very long buffer chains or very heavy access to the same data
blocks.
This, and many other Oracle performance metrics are discussed in
my book "Oracle
Tuning" by Rampant TechPress. You can buy it directly from
the publisher and save 30% at this link:
http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm
 |
Is your RAC database Healthy?
Get the experts at Burleson Consulting to conduct a two day
RAC
health check and ensure the health of your RAC database.
Why guess? Have your RAC database certified by experienced RAC
experts. |
|
|
