Undo Tablespace Management

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

UNDO Tablespace filling up - pre 10G

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

1. This ticket will be raised as a result of an UNDO tablespace apparently having space pressure. This may be a false alarm. 

2. Run the query below :

a. select  ssolderrcnt "Snapshot Too Old Errors",
nospaceErrCnt "Out of Space Errors"
from  v$undostat
where ( ssolderrcnt != 0 or  nospaceErrCnt != 0 );

3. If no rows are returned, no actions are required, check source of alert.

4. If this returns one or more rows, we may need to Tune the undo retention and/or increase the size of the UNDO tablespace.

5. Your should set the UNDO_RETENTION to    NO LESS THAN     than  the result of the query below:

a. select round(( max( maxQueryLen )* 1.2 ),0) 
as "Max Query Length in Seconds"

from v$undostat;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

UNDO Tablespace filling up - 10g and later

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
col status format a15
col description format a40 word_wrap
set serverout on size 999999 lines 150 verify off pages 50 echo off trimspool on
break on report
compute Sum LABEL SUM of undosize_MB on report
compute Sum LABEL SUM of block_count on report

col undo_alloc new_value undo_alloc noprint
col undo_pct format 9999
spool undo_info.log
SELECT SUM(bytes)/1024/1024 undo_alloc
FROM DBA_data_files
WHERE tablespace_name =
(SELECT UPPER(value)
FROM v$parameter
WHERE name = 'UNDO');

ttitle left '*********** REPORT 1 - Undo Block Status and Free Space Analysis ********************' skip 2

col Description format a40 word_wrap
col undo_pct format a5 head "UNDO|PCTGE"
SELECT status,
DECODE(status,'ACTIVE','UNDO BEING USED','EXPIRED','UNDO AVAILABLE FOR USE','UNEXPIRED','UNDO BEING RETAINED TO SUPPORT UNDO RETENTION. CAN BECOME EXPIRED IF SPACE BECOMES TIGHT') Description,
count(*) block_count,
round(sum(bytes)/1024/1024) undosize_MB,
round((sum(bytes)/1024/1024)/&undo_alloc*100)||'%' undo_pct
FROM dba_undo_extents
GROUP BY status
UNION
SELECT 'FREE SPACE',
'UNDO FREE SPACE AVAILABLE FOR USE',
COUNT(*),
ROUND(SUM(bytes)/1024/1024),
round((sum(bytes)/1024/1024)/&undo_alloc*100)||'%'
FROM dba_free_space
WHERE tablespace_name =
(SELECT value
FROM v$parameter
WHERE name = 'undo_tablespace')
GROUP BY 1
/

ttitle off
ttitle left '*********** REPORT 2 - Undo Health Check ********************' skip 2
DECLARE
tablespaceName varchar2(30);
tablespaceSize number;
autoExtend boolean;
autoExtendtf char(5);
undoRetention number;
retentionGuarantee boolean;
retentionGuaranteetf char(5);
autotuneEnabled boolean;
autotuneEnabledtf char(5);
longestQuery number;
longestQueryFormatted varchar2(30);
requiredRetention number;
requiredRetentionFormatted varchar2(20);
bestPossibleRetention number;
bestPossibleRetentionFormatted varchar2(20);
requireUndoSize number;
--
problem varchar2(100);
recommendation varchar2(100);
rationale varchar2(100);
retention number;
utbsize number;
nbr number;
undoAdvisor varchar2(100);
instanceNumber number;
ret boolean;
rettf char(5);
undoRetentionFormatted varchar2(50);
Recommended_undo_size number;
--
--
BEGIN

ret := sys.dbms_undo_adv.undo_info (tableSpaceName, tableSpaceSize, autoExtend, undoRetention, retentionGuarantee);

if ret
then rettf := 'TRUE';
else rettf := 'FALSE';
end if;

if autoextend
then autoextendtf := 'TRUE';
else autoextendtf := 'FALSE';
end if;

if retentionguarantee
then retentionguaranteetf := 'TRUE';
else retentionguaranteetf := 'FALSE';
end if;

SELECT to_char(trunc( max(undoRetention)/(60*60))||' hrs ')
|| trunc(to_char( ( max(undoRetention) - (3600 * trunc(max(undoRetention)/3600) ) )/60)) ||' mins '
INTO undoRetentionFormatted
FROM Dual;

longestquery := dbms_undo_adv.longest_query(sysdate-1,sysdate);
SELECT to_char(trunc( max(longestQuery)/(60*60))||' hrs ')
|| trunc(to_char( ( max(longestQuery) - (3600 * trunc(max(longestQuery)/3600) ) )/60)) ||' mins '
INTO longestQueryFormatted
FROM Dual;

-- dbms_output.put_line(' ');
-- dbms_output.put_line('--------------------------------------------------');
-- dbms_output.put_line('* UNDO Health *');
-- dbms_output.put_line
dbms_output.put_line( '*********** REPORT 2 - Undo Health Check ********************');
dbms_output.put_line(' ');
dbms_output.put_line(' ');
nbr := dbms_undo_adv.undo_health (problem, recommendation, rationale, retention, utbsize);

SELECT DECODE(utbsize,0,tableSpaceSize,utbsize)
INTO Recommended_undo_size
FROM dual;

dbms_output.put_line (RPAD('Problem',35,CHR(0))||' : '||problem);
dbms_output.put_line (RPAD('Recommendation',35,CHR(0))||' : '||recommendation);
dbms_output.put_line (RPAD('Rationale',35,CHR(0))||' : '||rationale);
dbms_output.put_line (RPAD('undo_retention (secs)',35,CHR(0))||' : '||undoRetention);
dbms_output.put_line (RPAD('undo_retention (hrs/mins)',35,CHR(0))||' : '||undoRetentionFormatted);
dbms_output.put_line (RPAD('Guaranteed Retention',35,CHR(0))||' : '||retentionGuaranteetf);
dbms_output.put_line (RPAD('Longest Run Query (secs)',35,CHR(0))||' : '||longestQuery );
dbms_output.put_line (RPAD('Longest Run Query (hrs/mins)',35,CHR(0))||' : '||longestQueryFormatted );
dbms_output.put_line (RPAD('Recommended Undo T/S Size (MB)',35,CHR(0))||' : '||Recommended_undo_size);
-- dbms_output.put_line (RPAD('Recommended Undo T/S Size (MB)',35,CHR(0))||' : '||utbsize);
dbms_output.put_line (RPAD('Current Undo T/S Size (MB)',35,CHR(0))||' : '||tableSpaceSize);
END;
/

col SSOLDERRCNT format 999999999 HEAD "SNAPSHOT|TOO OLD|ERROR|COUNT"
col NOSPACEERRCNT format 9999999 HEAD "NOSPACE|ERROR|COUNT"
ttitle off
ttitle left '*********** REPORT 3 - Current Undo Stats ********************' skip 2

SELECT BEGIN_TIME,
END_TIME,
UNDOBLKS,
MAXQUERYLEN,
MAXQUERYID,
SSOLDERRCNT,
NOSPACEERRCNT,
TUNED_UNDORETENTION
FROM v$undostat
WHERE BEGIN_TIME> sysdate-.090
ORDER BY 1;

ttitle off
ttitle left '*********** REPORT 4 - Undo Datafiles ********************' skip 2
col filename format a65
col TSPACENAME format a12
col CURRENT_SIZE_MB format 999,999 head 'CURRENT|SIZE(MB)'
col AUTOEXTEND_UP_TO_SIZE format 9,999,999 head 'AUTOEXTEND UP|TO SIZE(MB) '

SELECT d.file_name "FILENAME",
d.bytes/1024/1024 CURRENT_SIZE_MB,
d.maxbytes/1024/1024 AUTOEXTEND_UP_TO_SIZE,
t.tablespace_name "TSPACENAME",
CASE
WHEN d.autoextensible='YES' AND d.bytes>=d.maxbytes THEN 'WORKROUND IN PLACE'
WHEN d.autoextensible='YES' AND d.bytes WHEN d.autoextensible='NO' THEN 'NO AUTOEXTEND'
END AUTOEXTEND
FROM dba_data_files d,
dba_tablespaces t,
v$parameter p
WHERE d.tablespace_name = t.tablespace_name
AND d.tablespace_name = UPPER(p.value)
AND p.name = 'undo_tablespace'
/
ttitle off

ttitle left '*************** REPORT 5 - Current Undo Activity **************' skip 2
col dummy noprint
SELECT * from dual;

col userdet heading "OSUSER : |USERNAME" format A15
col procid heading "SID:SERIAL - |SPID" format A15
col terminal heading "TTY#" format A15
col program heading "PROGRAM NAME" format A20
col status heading "STATUS" format A10
col name heading "UNDO|SEGMENT" format a15
col sql_text heading "CURRENT SQL STATEMENT" format a100 word_wrap
col used_ublk heading "USED|UNDO" format a10
col start_date heading "START DATE"
SELECT unique
RPAD(vs.osuser,13,' ')||': '|| vs.username userdet,
RPAD(vs.sid||':'|| vs.serial#,13,' ') ||'- '|| vp.spid procid,
-- vs.terminal ,
vs.program ,
vs.status ,
vr.name ,
vt.used_ublk * TO_NUMBER(ts.block_size)/1024||'K' used_ublk,
vt.start_date ,
vsql.sql_text
FROM v$rollname vr,
v$transaction vt,
v$sql vsql,
v$process vp,
v$session vs,
(
SELECT dt.block_size
FROM v$parameter vp,
dba_tablespaces dt
WHERE vp.value = dt.tablespace_name
AND vp.name = 'undo_tablespace'
) ts
WHERE vs.paddr = vp.addr
-- AND NVL(vs.sql_id,vs.prev_sql_id) = vsql.sql_id
AND vs.sql_id = vsql.sql_id(+)
AND vs.taddr = vt.addr
AND vt.xidusn = vr.usn;
ttitle off

spool off