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

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.

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

sqlplus / as sysdba

CREATE UNDO TABLESPACE UNDOTBS2;

ALTER SYSTEM SET undo_tablespace=UNDOTBS2 SCOPE=BOTH;

DROP TABLESPACE undotbs1;

• 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;

UNDO Advisor

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

Bibliography