 |
|
Tuning the Undo Records
(Rollback
Segments)
Oracle Tips by Burleson
|
Oracle uses Undo records (formally called
rollback segments prior to Oracle) to maintain the “before” images
for rows. Whenever a task terminates with an abort or a
rollback, Oracle goes to the undo records and reapplies the
previous images for the rows. Oracle supports both rollback segments
or undo records, but rollback segments for managing undo space is
being deprecated in Oracle. Oracle strongly recommends that you use
automatic undo management and manage undo space using an undo
tablespace.
With rollback segments, the DBA was required to
create several static rollback segments of a uniform size with a
rollback segment tablespace. With undo tablespaces, Oracle creates
and manages undo records automatically and internally as they are
needed, and the DBA is only required to specify the overall tablespace
size.
Remember, you can continue to use rollback
segments in Oracle, and Oracle provides a parameter called
undo_management to control the method. If the value of
undo_management is set to RMU, Oracle will use rollback segments,
and setting the value of undo_management to SMU will use undo
segments. The default for a new Oracle database is AUTO, which
creates undo segments.
The v$ views and STATSPACK tables for rollback
management have changed dramatically in Oracle. Oracle introduced
the v$undostat view to replace the v$rollstat view from Oracle8i and
earlier releases.
The v$undostat view contains statistics for
monitoring the effects of transaction execution on undo space in the
current instance. These are available for space usage, transaction
concurrency and length of query operations. The v$undostat view
contains information that spans over a 24 hour period and each row in
v$undostat view contains data for a 10 minute interval.
Inside the database instance, there needs to be
enough undo record space to accommodate all concurrent update tasks.
In addition, the undo record tablespace must be large enough to hold
all of the “before” images between the start and the end (commit or
rollback) checkpoints.
There is a relationship between the undo
segments and system latches. A transaction writing rollback data has
to first access the transaction table stored in the undo segment
header and acquire a slot in that table. This requires momentary
latching of the table to serialize concurrent update to it. If the
database is update-intensive and has a small number of rollback
segments, user transactions will wait on the latch to access the
transaction table.
In Oracle8i and before, rollback segments
should be the same size and created with a large number of small
extents. For large transactions, special rollback segments can be
created and the task directed to the rollback segment with the set
transaction use rollback segment xxx command.
The STATSPACK tables provide a table called
stats$undostats (called stats$rollstat prior to Oracle) that keeps
statistics on the behavior of each rollback segment. The important
metrics include the number of wraps for each rollback segment and any
waits that may happen within a rollback segment.
The script below displays rollback segment and
undo segment information:
rpt_undo.sql (Oracle Only)
column c1
heading "Start|Time" format a15;
column c2 heading "End|Time" format a15;
column c3 heading "Total|Undo|Blocks|Used" format 999,999;
column c4 heading "Total|Number of|Transactions|Executed" format
999,999;
column c5 heading "Longest|Query|(sec)" format 9,999;
column c6 heading "Highest|Concurrent|Transaction|Count" format
9,999;
select
TO_CHAR(Begin_Time,'DD-MON-YY HH24:MI') c1,
TO_CHAR(End_Time,'DD-MON-YY HH24:MI') c2,
SUM(Undoblks) c3,
SUM(Txncount) c4,
MAX(Maxquerylen) c5,
MAX(Maxconcurrency) c6
from
stats$undostat
group by
TO_CHAR(Begin_Time,'DD-MON-YY HH24:MI'),
TO_CHAR(End_Time,'DD-MON-YY HH24:MI')
;
Here is the output from the rpt_undo.sql
script.
Total
Total Highest
Undo Number of Longest
Concurrent
Start End Blocks Transactions Query
Transaction
Time Time Used Executed (sec)
Count
--------------- --------------- -------- ------------ -------
-----------
08-DEC-01 18:30 08-DEC-01 18:40 0 2
0 1
08-DEC-01 18:40 08-DEC-01 18:50 0 2
0 1
08-DEC-01 18:50 08-DEC-01 19:00 0 2
0 1
08-DEC-01 19:00 08-DEC-01 19:10 1 2
0 1
08-DEC-01 19:10 08-DEC-01 19:20 0 2
0 1
08-DEC-01 19:20 08-DEC-01 19:30 0 2
0 1
08-DEC-01 19:30 08-DEC-01 19:40 0 2
0 1
08-DEC-01 19:40 08-DEC-01 19:50 1 2
0 1
08-DEC-01 19:50 08-DEC-01 20:00 0 2
0 1
08-DEC-01 20:00 08-DEC-01 20:10 183 380
1 3
08-DEC-01 20:10 08-DEC-01 20:20 161 2,008 97
3
08-DEC-01 20:20 08-DEC-01 20:30 253 1,786
12 3
13-DEC-01 11:56 13-DEC-01 12:06 1 12
14 1
13-DEC-01 12:06 13-DEC-01 12:16 0 2
0 1
13-DEC-01 12:16 13-DEC-01 12:26 0 2
0 1
13-DEC-01 12:26 13-DEC-01 12:36 0 2
0 1
13-DEC-01 12:36 13-DEC-01 12:46 0 2
0 1
13-DEC-01 12:46 13-DEC-01 12:56 0 2
0 1
13-DEC-01 12:56 13-DEC-01 13:06 1 9
0 2
13-DEC-01 13:06 13-DEC-01 13:16 812 2,411
105 4
13-DEC-01 13:16 13-DEC-01 13:26 9,032 1,172
78 3
13-DEC-01 13:26 13-DEC-01 13:36 6,966 480
78 3
13-DEC-01 13:36 13-DEC-01 13:46 0 2
0 1
13-DEC-01 13:46 13-DEC-01 13:56 0 2
0 1
Here we see the undo activity segregated by hour
of the day and the total number of transactions within the undo
segments by the hour.
Below we see the Oracle8i version of this script.
rpt_rbs.sql (Pre-Oracle version)
set pages
9999;
column buffer_busy_wait format 999,999,999
column mydate heading 'Yr. Mo Dy Hr.' format a16
column c0 heading "Name" format 99
column c1 heading "sz now" format 9,999
column c2 heading "extends" format 9,999
column c3 heading "# trans." format 9,999
column c4 heading "wraps" format 9,999
column c5 heading "High WM" format 999;
column c7 heading "Shrinks" format 999;
column c6 heading "status"
column c8 heading "Waits" format 9,999;
select
to_char(snap_time,'yyyy-mm-dd HH24') mydate,
new.usn c0,
(new.rssize-old.rssize)/1048576 c1,
(new.hwmsize-old.hwmsize)/1048576 c5,
new.extends-old.extends c2,
new.waits-old.waits c8,
new.xacts-old.xacts c3,
new.wraps-old.wraps c4,
new.shrinks-old.shrinks c7
from
perfstat.stats$rollstat old,
perfstat.stats$rollstat new,
perfstat.stats$snapshot sn
where
(new.rssize-old.rssize) > 0
and
new.xacts-old.xacts > 0
and
new.snap_id = sn.snap_id
and
old.snap_id = sn.snap_id-1
and
new.usn = old.usn
;
Here is the output from the STATSPACK script.
Here we see the size of each rollback segment, the high-water mark,
the number of extends, the number of waits, the number of transactions
within the rollback segment, the number of rollback segment wraps, and
the number of shrinks within the rollback segments.
Yr. Mo Dy
Hr. Name sz now High WM extends Waits # trans. wraps Sh
------------ --- ------ ------- ------- ------ -------- ------
-------
2001-01-01 01 3 6 6 6 1 1 63
0
2001-01-07 17 1 2 2 2 0 1 73
0
2001-01-10 23 2 6 6 6 1 1 79
0
2001-01-10 23 3 5 5 5 3 1 155
0
This is an excerpt from "Oracle
High Performance tuning with STATSPACK" by Oracle Press.
 |
If you like Oracle tuning, you may enjoy the 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 of Oracle tuning scripts. |
|
