Hands-On 10 (Tuning rollback or undo segment)

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 _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

SISTENT GETS parameter

Checking the SHRINKS column

Checking the AVESHRINKS column

-- Hands-On 10 (Tuning rollback or undo segments)
-- Tuning rollback or undo segment
-- Preparation
set echo on
connect system/manager as sysdba
SET linesize 1000 pagesize 55
COL name FORMAT a40
col parameter format a40
col username format a10
pause

--Start

CLEAR SCR
-- 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.

pause

CONNECT system/manager AS SYSDBA

pause


CLEAR SCR
-- First, check the UNDO_MANAGEMENT parameter.

pause

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.

pause

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

pause

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.


pause

CLEAR SCR
-- Query the V$ROLLNAME dictionary view to list the UNDO segments.

pause

SELECT * FROM v$rollname
/

pause

CLEAR SCR
-- Query the V$ROLLSTAT, and V$ROLLNAME dictionary views where the
-- number of SHRINKS are more than 1.

pause

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.
pause

CLEAR SCR
-- Query the V$WAITSTAT view to list block contention statistics.

pause

SELECT *
FROM v$waitstat
WHERE class LIKE '%undo%'
/

-- Note the UNDO header.

pause

CLEAR SCR
-- Then, query the V$SYSSTAT to gather statistics for the DB BLOCK GETS 
-- and the CONSISTENT GETS parameters.

pause

COL name FORMAT a40

SELECT name, value 
FROM v$sysstat
WHERE name in ('db block gets','consistent gets')
/

-- Note the DB BLOCK GETS, and CONSISTENT GETS values.

pause

CLEAR SCR
-- Calculate the Hit Ratio from following formula.

-- (db block gets + consistent gets - undo header)
-- Hit Ratio = -----------------------------------------------
-- (db block gets + consistent gets)

pause



-- 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.

pause

CLEAR SCR
-- Query the V$SYSSTAT directory view to gather statistics for the Rollback and
-- UNDO activities.

pause

COL name FORMAT a60

SELECT name, value 
FROM v$sysstat
WHERE name LIKE '%roll%'
OR name LIKE '%undo%'
/

pause


CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause