Oracle UNDO

  • One undo segment can protect many transactions (although Oracle will try to avoid this)

  • One transaction is protected by one undo segment

  • One database can have many undo tablespaces (although only one will be made active at any moment by the instance)

  • One instance can only use one undo tablespace (in RAC, each instance must have its own undo tablespace)

  • One undo segment can span multiple datafiles (just like any other segment)

  • Undo segments and rollback segments can coexist but a database can only use one or the other

Rollback Progress

If a user kills a long running transaction then there will likely be rollback activity. This could take some time to happen in the background. You can check progress using this query. If you note the time of each execution you can work out how many blocks are processed in the time period to calculate an estimate of how much time is remaining.

SET TIME ON

SELECT s.username,

SUBSTR(s.program, 1, 30) command,

t.used_ublk

FROM v$session s,

v$transaction t

WHERE s.saddr = t.ses_addr

/

UNDO Usage By Session

SELECT s.sid,

s.serial#,

s.username,

SUBSTR(s.program, 1, 30) command,

t.used_urec used_undo_record,

t.used_ublk used_undo_blocks

FROM v$session s,

v$transaction t

WHERE s.saddr=t.ses_addr

/

Enter the SID from the above query into the one on the right to get the SQL Text related to the session...

SELECT DISTINCT s.sid,

s.serial#,

s.username,

SUBSTR(s.program, 1, 30) command,

sql.sql_text,

t.used_urec used_undo_record,

t.used_ublk used_undo_blocks

FROM v$session s,

v$transaction t,

v$sql sql

WHERE s.saddr=t.ses_addr

AND s.sql_id = sql.sql_id

AND s.sid = &SID

/

UNDO Usage History

Statistics over 10 minute periods. 144 rows = 1 day...

SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,

TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,

UNDOTSN,

UNDOBLKS,

TXNCOUNT,

MAXCONCURRENCY AS "MAXCON"

FROM v$UNDOSTAT

WHERE rownum <= 144;

Sizing UNDO Tablespace

To determine the optimal size for the undo tablespace...

  • Take the largest figure for UNDOBLKS (in v$undostat).

  • Divide by 600 to get undo generation in blocks per second (v$undostat reports in 10 minute chunks).

  • Multiply by the db_block_size to get the figure in bytes.

  • Multiply by the largest figure for MAXQUERYLEN (in v$undostat).

  • This gives the space needed if the highest rate of undo generation coincided with the longest query

  • Divide by 1 billion to get the answer in GB.

SELECT ROUND((((ubs/Secs)*dbs)*mql)/1024/1024/1024,2) GB

FROM

(SELECT value dbs

FROM v$parameter

WHERE name = 'db_block_size'),

(SELECT MAX((((end_time - begin_time)*24)*60)*60) Secs

FROM v$undostat

WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)

OR maxquerylen = (SELECT MAX(maxquerylen) FROM v$undostat)),

(SELECT MAX(undoblks) ubs

FROM v$undostat

WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)

OR maxquerylen = (SELECT MAX(maxquerylen) FROM v$undostat)),

(SELECT MAX(maxquerylen) mql

FROM v$undostat

WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)

OR maxquerylen = (SELECT MAX(maxquerylen) FROM v$undostat))

/


GB

----------

1.55

In this example...

  • The largest figure for UNDOBLKS was 38544

  • The db_block_size was 8192

  • The largest figure for MAXQUERYLEN was 3168

(((38544/600)*8192)*3168)/1024/1024/1024 = 1.55GB

To work out how big the UNDO tablespace needs to be in order to satisfy a retention period of a specific number of hours use the following query:

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",

&&hours*60*60 "UNDO RETENTION [Sec]",

(&&Hours*60*60 * TO_NUMBER(f.value) * g.undo_block_per_sec) / (1024*1024) "NEEDED UNDO SIZE [MByte]"

FROM (

SELECT SUM(a.bytes) undo_size

FROM v$datafile a,

v$tablespace b,

dba_tablespaces c

WHERE c.contents = 'UNDO'

AND c.status = 'ONLINE'

AND b.name = c.tablespace_name

AND a.ts# = b.ts#

) d,

v$parameter f,

(

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))

undo_block_per_sec

FROM v$undostat

) g

WHERE f.name = 'db_block_size'

/


Enter value for hours: 24

old 2: &&hours*60*60 "UNDO RETENTION [Sec]",

new 2: 24*60*60 "UNDO RETENTION [Sec]",

old 3: (&&Hours*60*60 * TO_NUMBER(f.value) * g.undo_block_per_sec) / (1024*1024) "NEEDED UNDO SIZE [MByte]"

new 3: (24*60*60 * TO_NUMBER(f.value) * g.undo_block_per_sec) / (1024*1024) "NEEDED UNDO SIZE [MByte]"


ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] NEEDED UNDO SIZE [MByte]

------------------------ -------------------- ------------------------

24915 86400 92340

Moving UNDO

This procedure may be need, for example, if you wanted to move UNDO from ASM to filesystem, or vice versa. This procedure does NOT require an outage.

These steps assume that a /u02 filesystem has been created at the correct size with the correct ownership and permissions.

ALTER SYSTEM SET db_create_file_dest = '/u02' SCOPE=BOTH;

In a Dataguard environment, be sure to do this on both Primary and Standby databases.

sqlplus / as sysdba

CREATE UNDO TABLESPACE UNDOTBS2;

ALTER SYSTEM SET undo_tablespace=UNDOTBS2 SCOPE=BOTH;

DROP TABLESPACE undotbs1;

This procedure will work automatically in a Dataguard environment as long as standby_file_management = auto

Automated Undo Management

  • UNDO_MANAGEMENT - If AUTO or null, enables automatic undo management. If MANUAL, sets manual undo management mode. The default is AUTO.

  • UNDO_TABLESPACE - allows you to specify a specific UNDO tablespace at database startup. This could be useful if you have multiple UNDO tablespaces sized for different workloads (e.g. daytime OLTP and overnight Batch)

  • UNDO_RETENTION - sets a target for undo retention. This is not guaranteed unless you set RETENTION GUARANTEE on the Undo tablespace...

ALTER TABLESPACE undo RETENTION GUARANTEE;

ALTER TABLESPACE undo RETENTION NOGUARANTEE;

Setting RETENTION GUARANTEE can cause transactions to fail if there is not enough UNDO to satisfy them. Use with caution.

UNDO Advisor

Identify Snapshots...

@/u01/dba/tune/awrsnaps.sql

set linesize 280

set pagesize 80

column begin_interval_time format a25

column end_interval_time format a25

column startup_time format a25

column flush_elapsed format a20

column snap_timezone format a20


SELECT *

FROM dba_hist_snapshot

ORDER BY begin_interval_time;

Run the Advisor...

DECLARE

tid NUMBER;

tname VARCHAR2(30);

oid NUMBER;

BEGIN

DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');

DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, 'null', oid);

DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);

DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', &snap1);

DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', &snap2);

DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'INSTANCE', 1);

DBMS_ADVISOR.execute_task(tname);

END;

/

View Results...

  • DBA_ADVISOR_TASKS

  • DBA_ADVISOR_OBJECTS

  • DBA_ADVISOR_FINDINGS

  • DBA_ADVISOR_RECOMMENDATIONS