Performance

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:

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.

Use the V$UNDOSTAT view to calculate an estimate of undo spaces to meet the undo retention requirement for 15 minutes.

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 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%'
/
 

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');