Scripts to check UNDO tablespace stats

posted Sep 13, 2010, 8:19 AM by Sachchida Ojha   [ updated Sep 13, 2010, 8:22 AM ]
set linesize 145
set pagesize 1000
set trimout on
set trimspool on
Set Feedback off
set timing off
set verify off
Set Heading Off
Set Termout Off
Column Var_DB_BLOCK_SIZE new_value Var_DB_BLOCK_SIZE noprint
Column Var_UNDO_RETENTION new_value Var_UNDO_RETENTION noprint

Select block_size Var_DB_BLOCK_SIZE
from dba_tablespaces
where tablespace_name in (select value from V$PARAMETER where name='undo_tablespace');

select value Var_UNDO_RETENTION
from V$PARAMETER
where name='undo_retention';

Set Termout On
Set Heading On

prompt
prompt -- ----------------------------------------------------------------------- ---
prompt -- Undo Segment Stats ---
prompt -- ----------------------------------------------------------------------- ---
prompt

column Begin_Time heading "Begin|Time" format A16
column End_Time heading "End|Time" format A16
column UndoTSN heading "Tsn" format 999
column UndoBLKS heading "Blks" format 99999
column TxnCount heading "TxnCount" format 99999999
column MAXQUERYLEN heading "Max|Query|Len" format 9999
column MAXCONCURRENCY heading "Max|Conc." format 9999
column UNXPSTEALCNT heading "Un|Xpsteal|Cnt" format 9999
column UNXPBLKRELCNT heading "UnXp|Blkrel|Cnt" format 9999
column UNXPBLKREUCNT heading "UnXp|Blkreu|Cnt" format 9999
column EXPSTEALCNT heading "Exp|Steal|Cnt" format 9999
column EXPBLKRELCNT heading "Exp|Blkrel|Cnt" format 9999
column EXPBLKREUCNT heading "Exp|Blkreu|Cnt" format 9999
column SSOLDERRCNT heading "Sso|Lderr|Cnt" format 9999
column UNXPBLKREUCNT heading "UnXp|Blkreu|Cnt" format 9999
column NOSPACEERRCNT heading "No|Space|Errcnt" format 9999

Select To_Char(Begin_Time,'DD-MM-YYYY HH24:MI') Begin_Time
, To_Char(End_Time,'DD-MM-YYYY HH24:MI') End_Time
, UndoTSN
, UndoBLKS
, TxnCount
, MAXQUERYLEN
, MAXCONCURRENCY
, UNXPSTEALCNT
, UNXPBLKRELCNT
, UNXPBLKREUCNT
, EXPSTEALCNT
, EXPBLKRELCNT
, EXPBLKREUCNT
, SSOLDERRCNT
, NOSPACEERRCNT
From V$UNDOSTAT;
where begin_time > sysdate - 1/24
Order by begin_time
;

Select To_Char(Begin_Time,'DD-MM-YYYY HH24:MI') Begin_Time
, To_Char(End_Time,'DD-MM-YYYY HH24:MI') End_Time
, UndoTSN
, UndoBLKS
, TxnCount
, MAXQUERYLEN
, MAXCONCURRENCY
, UNXPSTEALCNT
, UNXPBLKRELCNT
, UNXPBLKREUCNT
, EXPSTEALCNT
, EXPBLKRELCNT
, EXPBLKREUCNT
, SSOLDERRCNT
, NOSPACEERRCNT
From V$UNDOSTAT;
where begin_time > sysdate - 1/24*3
Order by begin_time
;

prompt -- Undo blocks / Second
prompt -- ----------------------------------------------------------------------- ---
prompt
column UndoBLKS600 heading "Blks|Second" format 99999

Select To_Char(Begin_Time,'DD-MM-YYYY HH24:MI') Begin_Time
, To_Char(End_Time,'DD-MM-YYYY HH24:MI') End_Time
, UndoBLKS/600 UndoBLKS600
From V$UNDOSTAT;
Order by Begin_Time
;

prompt -- Max Undo blocks / Second
prompt -- ----------------------------------------------------------------------- ---
prompt
column maxUndoBLKS600 heading "Max Blks|Second" format 99999

Select max(UndoBLKS/600) maxUndoBLKS600
From V$UNDOSTAT
;

prompt -- Avg Undo blocks / Second
prompt -- ----------------------------------------------------------------------- ---
prompt
column AvgUndoBLKS600 heading "Avg Blks|Second" format 99999

Select avg(UndoBLKS/600) AvgUndoBLKS600
From V$UNDOSTAT
;

prompt -- Avg Undo blocks / Second (median value of the last hour)
prompt -- ----------------------------------------------------------------------- ---
prompt
column SumUndoBLKS600 heading "Avg Blks|Second" format 99999

Select
count(*)
, sum(UndoBLKS)/3600 SumUndoBLKS600
From V$UNDOSTAT
where begin_time > sysdate - 1/24
;

column Sumsize600 heading "Avg space|Second (Mb)" format 99999

Select count(*)
, sum(UndoBLKS)/3600*&Var_DB_BLOCK_SIZE./1024/1024*&Var_UNDO_RETENTION. Sumsize600
From V$UNDOSTAT
where begin_time > sysdate - 1/24
;

prompt -- Avg Undo blocks / Second (median value of the last day)
prompt -- ----------------------------------------------------------------------- ---
prompt
column SumUndoBLKS600 heading "Avg Blks|Second" format 99999

Select count(*), sum(UndoBLKS)/(3600*24) SumUndoBLKS600
From V$UNDOSTAT
where begin_time > sysdate - 1
;

column Sumsize600 heading "Avg space|Second (Mb)" format 99999
Select count(*), sum(UndoBLKS)/(3600*24)*&Var_DB_BLOCK_SIZE./1024/1024*&Var_UNDO_RETENTION. Sumsize600
From V$UNDOSTAT
where begin_time > sysdate - 1
;

prompt -- Avg Undo blocks / Second (median value of the last three days)
prompt -- ----------------------------------------------------------------------- ---
prompt
column SumUndoBLKS600 heading "Avg Blks|Second" format 99999

Select count(*), sum(UndoBLKS)/(3600*24*3) SumUndoBLKS600
From V$UNDOSTAT
where begin_time > sysdate - 3
;

column Sumsize600 heading "Avg space|Second (Mb)" format 99999999

Select count(*), sum(UndoBLKS)/(3600*24*3)*&Var_DB_BLOCK_SIZE./1024/1024*&Var_UNDO_RETENTION. Sumsize600
From V$UNDOSTAT
where begin_time > sysdate - 3
;

prompt -- Max space used
prompt -- ----------------------------------------------------------------------- ---
prompt

column spaceused heading "Max Space used (Mb)" format a40
Prompt

Select ' Since start DB : '||To_Char(round(max(UndoBLKS)/600*&Var_UNDO_RETENTION.*&Var_DB_BLOCK_SIZE./1024/1024,2)) spaceused
From V$UNDOSTAT
Union
Select ' Last hour : '||To_Char(round(max(UndoBLKS)/600*&Var_UNDO_RETENTION.*&Var_DB_BLOCK_SIZE./1024/1024,2)) spaceused
From V$UNDOSTAT
where begin_time > sysdate - 1/24
Union
Select ' Last day : '||To_Char(round(max(UndoBLKS)/600*&Var_UNDO_RETENTION.*&Var_DB_BLOCK_SIZE./1024/1024,2)) spaceused
From V$UNDOSTAT
where begin_time > sysdate - 1
Union
Select ' Last three days : '||To_Char(round(max(UndoBLKS)/600*&Var_UNDO_RETENTION.*&Var_DB_BLOCK_SIZE./1024/1024,2)) spaceused
From V$UNDOSTAT
where begin_time > sysdate - 3
;

prompt -- Avg space used
prompt -- ----------------------------------------------------------------------- ---
prompt
column spaceused heading "Avg Space used (Mb)" format a40


Prompt

Select ' Since start DB : '||To_Char(round(Avg(UndoBLKS)/600*&Var_UNDO_RETENTION.*&Var_DB_BLOCK_SIZE./1024/1024,2)) spaceused
From V$UNDOSTAT
Union
Select ' Last hour : '||To_Char(round(Avg(UndoBLKS)/600*&Var_UNDO_RETENTION.*&Var_DB_BLOCK_SIZE./1024/1024,2)) spaceused
From V$UNDOSTAT
where begin_time > sysdate - 1/24
Union
Select ' Last day : '||To_Char(round(Avg(UndoBLKS)/600*&Var_UNDO_RETENTION.*&Var_DB_BLOCK_SIZE./1024/1024,2)) spaceused
From V$UNDOSTAT
where begin_time > sysdate - 1
Union
Select ' Last three days : '||To_Char(round(Avg(UndoBLKS)/600*&Var_UNDO_RETENTION.*&Var_DB_BLOCK_SIZE./1024/1024,2)) spaceused
From V$UNDOSTAT
where begin_time > sysdate - 3
;

prompt -- Was the desired undo retention retained?
prompt -- ----------------------------------------------------------------------- ---
prompt
Prompt -- Remedy: Increasing the size of the undo-tablespace or reducing the undo retention.
prompt

select to_char(begin_time,'YYYY-MM-DD HH24:MI:SS') "Begin",
to_char(end_time,'YYYY-MM-DD HH24:MI:SS') "End ",
undoblks "UndoBlocks",
unxpstealcnt "Unexpired"
from V$UNDOSTAT
where UNXPSTEALCNT > 0 or
UNXPBLKRELCNT > 0 or
UNXPBLKREUCNT > 0;

prompt -- How often and when does "Snapshot too old" (ORA-01555) occur?
prompt -- ----------------------------------------------------------------------- ---
prompt
Prompt -- Depending on the result: Increase the undo retention
prompt

select to_char(begin_time,'YYYY-MM-DD HH24:MI:SS') "Begin",
to_char(end_time,'YYYY-MM-DD HH24:MI:SS') "End ",
undoblks "UndoBlocks",
SSOLDERRCNT "ORA-1555"
from V$UNDOSTAT
where SSOLDERRCNT > 0;

prompt -- When and how often was the undo-tablespace too small?
prompt -- ----------------------------------------------------------------------- ---
prompt
Prompt -- Remedy: Making more space available for the undo-tablespace.
prompt

select to_char(begin_time,'YYYY-MM-DD HH24:MI:SS') "Begin",
to_char(end_time,'YYYY-MM-DD HH24:MI:SS') "End ",
undoblks "UndoBlocks",
nospaceerrcnt "Space Err"
from V$UNDOSTAT
where nospaceerrcnt > 0;

prompt -- How long did the longest database query take (in seconds) during the period covered by v$undostat?
prompt -- ----------------------------------------------------------------------- ---
prompt
Prompt -- Remedy: Making more space available for the undo-tablespace.
prompt

Select max(MAXQUERYLEN)
From V$UNDOSTAT
;

Select
To_Char(Begin_Time,'DD-MM-YYYY HH24:MI') Begin_Time
, To_Char(End_Time,'DD-MM-YYYY HH24:MI') End_Time
, UndoTSN
, UndoBLKS
, TxnCount
, MAXQUERYLEN
, MAXCONCURRENCY
, UNXPSTEALCNT
, UNXPBLKRELCNT
, UNXPBLKREUCNT
, EXPSTEALCNT
, EXPBLKRELCNT
, EXPBLKREUCNT
, SSOLDERRCNT
, NOSPACEERRCNT
From V$UNDOSTAT
Order By MAXQUERYLEN
;

Select
To_Char(Begin_Time,'DD-MM-YYYY HH24:MI') Begin_Time
, To_Char(End_Time,'DD-MM-YYYY HH24:MI') End_Time
, UndoTSN
, UndoBLKS
, TxnCount
, MAXQUERYLEN
, MAXCONCURRENCY
, UNXPSTEALCNT
, UNXPBLKRELCNT
, UNXPBLKREUCNT
, EXPSTEALCNT
, EXPBLKRELCNT
, EXPBLKREUCNT
, SSOLDERRCNT
, NOSPACEERRCNT
From V$UNDOSTAT
Order By UndoBLKS
;
Comments