|
"Complaining is
good for you as long as you're not complaining to the person
you're complaining about." Lynn Johnston (1947 - ), For Better
or For Worse, 11-06-03 |
Read
first then play the video:
PRF-VIDEO -Tuning
Rollback or UNDO Segment
Tuning rollback or undo
segment
Introduction
As a DBA, you are
responsible for tuning rollback or undo segments in case of
performance problems. Your job"s responsibilities dictate that you
should at least be informed of the following basic fundamental
subjects:
Tuning Rollback or UNDO
Segments
Setting the UNDO_MANAGEMENT
parameter
Setting the UNDO _RETENTION
parameter
Setting the UNDO_TABLESPACE
parameter
Calculate an estimate of
UNDO spaces for
The UNDO retention
requirement
Tuning the manual UNDO
segments
Listing block contentions
Calculating the UNDO
segment Hit Ratio
Using the V$UNDOSTAT view
Using the V$ROLLNAME view
Using the V$ROLLSTAT view
Using the V$WAITSTAT view
Using the V$SYSSTAT view
Checking the DB BLOCK GETS
parameter
Checking the CONSISTENT
GETS parameter
Checking the SHRINKS column
Checking the AVESHRINKS
column
Hands-on
In this exercise you will learn how to: tune rollback or undo
segments, set the UNDO_SEGMENT parameter to AUTO, calculate an
estimate of UNDO spaces to meet the UNDO retention requirement, tune
the MANUAL UNDO segments, list block contention, calculate the UNDO
segment Hit Ratio, and more.
So, let's connect to SQLPlus as the SYSTEM/MANAGER user.
SQL> CONNECT system/manager AS SYSDBA
View UNDO parameters
First, check the UNDO_MANAGEMENT parameter.
SQL> SHOW PARAMETER undo
Always set the UNDO_MANAGEMENT parameter to AUTO. In the AUTO option,
the database takes control of how to manage the UNDO segments. The
UNDO_RETENTION parameter indicates the number of seconds that the
database keeps the UNDO segments. The UNDO_TABLESPACE parameter
indicates the UNDO tablespace.
Spaces to meet UNDO
retention
Use the V$UNDOSTAT view to
calculate an estimate of undo spaces to meet the undo retention
requirement for 15 minutes.
SQL> SELECT (xx*(ups*overhead)
+ overhead) AS "Bytes"
FROM (SELECT value AS xx
FROM v$parameter WHERE name
= 'undo_retention'),
(SELECT (SUM(undoblks)/SUM((end_time-begin_time)*86400))
AS ups
FROM v$undostat),
(SELECT value AS overhead
FROM v$parameter
WHERE name = 'db_block_size')
/
The result of this query shows how much UNDO space we need
to meet the UNDO retention requirement.
List all UNDO segments
Query the V$ROLLNAME dictionary view to list the UNDO segments.
SQL> SELECT * FROM v$rollname
/
Shrinking UNOD segments
Query the V$ROLLSTAT, and V$ROLLNAME dictionary views where the
number of SHRINKS are more than 1. This is only applicable if you are
using UNDO segments manually.
SQL> SELECT a.name, b.extents, b.optsize,
b.shrinks,
b.aveshrink, writes
FROM v$rollname a, v$rollstat b
WHERE a.usn = b.usn
AND b.shrinks > 1
/
The WRITES column indicates the number of bytes written in
the rollback segment. Notice that if the UNDO segment size is not big
enough you may get the following error message: ORA-01555: snapshot
too old.
If the UNDO_MANAGEMENT is
AUTO ignore the optimal size.
If it is MANUAL, then be sure that the UNDO segments have an optimal
size.
If the SHRINKS value is HIGH and the AVESHRINKS value is HIGH then
increase the Optimal size.
If the SHRINKS value is HIGH and the AVESHRINKS value is LOW then
increase the Optimal size.
If the SHRINKS value is LOW and the AVESHRINKS value is LOW then
decrease the Optimal size.
If the SHRINKS value is LOW and the AVESHRINKS value is HIGH then the
Optimal size is okay. |
Block contention statistics
Query the V$WAITSTAT view to list block contention statistics.
SQL> SELECT *
FROM v$waitstat
WHERE class LIKE '%undo%'
/
Note the UNDO header.
DB BLOCK GETS and
CONSISTENT GETS values
Then, query the V$SYSSTAT to gather statistics for the DB BLOCK
GETS and the CONSISTENT GETS parameters.
SQL> COL name FORMAT a40
SQL> SELECT name, value
FROM v$sysstat
WHERE name in ('db block gets','consistent gets')
/
Note the DB BLOCK GETS, and CONSISTENT GETS values.
Calculate Cache Buffer Hit
Ratio
Calculate the Hit Ratio
from following formula.
| Hit Ratio = (db block gets + consistent gets - undo header) /(db block gets + consistent gets) |
If the UNDO_MANAGEMENT parameter is MANUAL and the Hit Ratio is less
than 99 you may have problem with Rollback Segment contentions. You
may have to increase the number of Rollback Segments. This is not
applicable when the UNDO_MANAGEMENT parameter is set to AUTO.
View UNOD activities
Query the V$SYSSTAT directory view to gather statistics for the
Rollback and UNDO activities.
SQL> COL name FORMAT a60
SQL> SELECT name, value
FROM v$sysstat
WHERE name LIKE '%roll%'
OR name LIKE '%undo%'
/
|
"Life is a zoo in a
jungle." Peter De Vries |
Questions:
Q: How do you tune the UNDO
segments?
Q: Describe the
UNDO_MANAGEMENT parameter?
Q: Describe the
UNDO_RETENTION parameter?
Q: Describe the
UNDO_TABLESPACE parameter?
Q: Describe the V$UNDOSTAT
view?
Q: Describe the V$ROLLNAME
view?
Q: Describe the V$ROLLSTAT
view?
Q: How do you monitor the
DB_BLOCK_GETS, and CONSISTENT GETS parameters?
Q: When do you monitor the
SHRINKS and AVESHRINS columns in the V$ROLLSTAT dictionary view?
Q: How do you calculate an
estimate of undo spaces to meet the undo retention requirement for 15
minutes?
Q: How do you get a list of
UNDO segments?
Q: What does the WRITES
column indicate in the V$ROLLSTAT dictionary view?
Q: When do you get the
following undo segment error message?
ORA-01555: snapshot too
old.
Q: What is an optimal size
when you use an undo segments manually?
Q: What does the following
SQL statement?
SQL> SELECT name, value
FROM v$sysstat
WHERE name in ('db block gets','consistent gets');
|