Undo Management


 
 optimal undo_retention parameter = actual undo size / (db_block_size/undo_block_per_sec)
 
 -- **********************
 -- manual undo management
 -- **********************
 
 
 create rollback segment r01 tablespace rbs;
 create public rollback_segment r01 tablespace rbs;
 alter rollback segment r01 offline;
 drop rollback segment r01;
 
 -- *************************
 -- automatic undo management
 -- *************************
 
 alter system set undo_management = auto;
 alter system set undo_tablespace = <ts_name>;
 alter system set undo_suppress_errors = true|false ;
 alter system set undo_retention = 900; --seconds
 select name, value from v$parameter where name like 'undo_management'
 select name, value from v$parameter where name like 'undo_tablespace'
 select name, value from v$parameter where name like 'undo_retention'
 
 -- creating undo tablespace
 
 create undo tablespace ts_undo datafile 'C:\ts_undo_01.dbf' size 20m;
 alter tablespace ts_undo add datafile 'C:\ts_undo_02.dbf' size 40m;
 alter system set undo_tablespace = ts_undo;
 drop tablespace ts_undo;
 alter tablespace ts_undo retention guarantee;
 select retention, tablespace_name from dba_tablespaces;
 select begin_time, end_time, tuned_undoretention from v$undostat;
 
 -- calculating undo space
 
 select
 ceil(((UR * (UPS * DBS)) + (DBS * 24))/1024/1024) as MB
 from
 (select value as UR from v$parameter where name='undo_retention'),
 (select (sum(undoblks)/sum(((end_time - begin_time) * 84600))) as UPS
 from v$undostat),
 (select value as DBS from v$parameter where name = 'db_block_size');
 set transaction read only;
 set transaction isolation level serializable; -- possible negative impact on performance
 
 -- Actual Undo Size
 
 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#;
 
 -- Undo Blocks per Second
 
 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
 "UNDO_BLOCK_PER_SEC"
 FROM v$undostat;
 
 -- DB Block Size
 
 SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
 FROM v$parameter
 WHERE name = 'db_block_size';
 
 -- optimal undo retention
 
 SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
 SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
 ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec)))
 "OPTIMAL UNDO RETENTION [Sec]"
 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 e,
 v$parameter f,
 ( SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
 undo_block_per_sec FROM v$undostat ) g
 WHERE e.name = 'undo_retention'
 AND f.name = 'db_block_size';
 
 -- optimal undo size
 
 SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
 SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
 (TO_NUMBER(e.value) * 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 e,
 v$parameter f,
 (
 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
 undo_block_per_sec
 FROM v$undostat
 ) g
 WHERE e.name = 'undo_retention'
 AND f.name = 'db_block_size';
 
 -- undo consumer sessions
 
 SELECT
 TO_CHAR (s.SID) || ',' || TO_CHAR (s.serial#) sid_serial,
 NVL (s.username, 'None') orauser,
 s.program,
 r.NAME undoseg,
 t.used_ublk * TO_NUMBER (x.VALUE) / 1024 || 'K' "Undo",
 t1.tablespace_name
 FROM
 SYS.v_$rollname r,
 SYS.v_$session s,
 SYS.v_$transaction t,
 SYS.v_$parameter x,
 dba_rollback_segs t1
 WHERE
 s.taddr = t.addr
 AND r.usn = t.xidusn(+)
 AND x.NAME = 'db_block_size'
 AND t1.segment_id = r.usn
 AND t1.tablespace_name = 'UNDOTBS1';

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: