How to deal with UNDO space usage alert
How to deal
with UNDO space usage alert
Oracle Database creates and manages information that is used to
roll back, or undo, changes to the database. Such information consists of
records of the actions of transactions, primarily before they are committed. These
records are collectively referred to as undo.
Undo records are used to:
- Roll back transactions when a ROLLBACK statement is issued
- Recover the database
- Provide read consistency
- Analyze data as of an earlier point in time by using Oracle Flashback Query
- Recover from logical corruptions using Oracle Flashback features
When a ROLLBACK statement is issued, undo records are used to
undo changes that were made to the database by the uncommitted transaction.
During database recovery, undo records are used to undo any uncommitted changes
applied from the redo log to the datafiles. Undo records provide read
consistency by maintaining the before image of the data for users who are
accessing the data at the same time that another user is changing it.
Whenever you get an UNDO space usage alert, do not go for
immediately increasing the UNDO tablespace size. Check how many expired extents
and free extents are left in the undo tablespace. These extents can be reused
and hence even though if the undo tablespace shows near 100% utilized, you
don’t need to add space to the tablespace as there are segments to get reused. If
the sum of (expired extent + free space) is low or you are getting ORA-1555 Snapshot
too old error (in the alert log) then you need to think about it and go for
increasing space in undo tablespace.
SQL Query for
checking UNDO tablespace usage [Free Space]:
set pages 999
col tablespace_name format a40
col "size MB" format 999,999.99
col "free MB" format 999,999.99
col "% Used" format 999.99
select tsu.tablespace_name,
ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb),
NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 -
ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 -
ceil(tsf.free_mb/tsu.used_mb*100)) "% Used"
from (select
tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union
all
select tablespace_name || ' **TEMP**'
, sum(bytes)/1024/1024 used_mb
from dba_temp_files group by tablespace_name) tsu
, (select
tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name
= tsf.tablespace_name (+)
and tsu.tablespace_name = 'APPS_UNDOTS1'
order by 4
/
[Specify
the name of Undo Tablespace that you are using.]
SQL Query for checking UNDO extent(s) status [Expired
Extents]:
set linesize 200 pagesize 200
select
tablespace_name, status segment_status,
count(extent_id)
"Extent Count", sum(blocks) "Total Blocks",
sum(blocks)*8/(1024)
"Total Space in MB"
from
dba_undo_extents
group by
tablespace_name, status
order by
tablespace_name
/
TABLESPACE_NAME
SEGMENT_S Extent Count Total Blocks Total Space in MB
------------------------------ --------- ------------
------------ -----------------
APPS_UNDOTS1
ACTIVE
20
9496 74.1875
APPS_UNDOTS1
EXPIRED
761 336312
2627.4375
APPS_UNDOTS1
UNEXPIRED
2513 686448
5362.875
Note:
All the
above said are valid only when the undo is in auto extend off.
The best
practice is to have it in autoextend off.
Comments
Post a Comment