UNDO Tablespace
The undo tablespace, has undo segments.
The each undo segment is composed by undo extents.
When transactions hit a database and they need undo space the allocation happens in the following sequence:
1. Allocate an extent in an undo segment which has no active transaction. Oracle tries to distribute transactions over all undo segments.
2. If no undo segment found then oracle tries to online an off-line undo segment and use it.
3. If no undo segments to online, then we create a new undo segment and use it.
4. If space does not permit creation of undo segment, then we try to reuse an expired extent from the existing undo segments.
For a running transaction associated with undo segment/ extent, if it needs more undo space then:
1. If the current extent has more free blocks then use the next free block that is all ready allocated to the extent.
2. If the current extent does not have free blocks and if the next extent of the segment has expired then wrap in the the next extent and return the first block.
3. If the next extent has not expired then get space from the UNDO tablespace. If a free extent is available then allocate it to the undo segment and return the first block in the new extent.
4. If there is no free extent available then steal from an offline undo segment. Deallocate the extent from the offline undo segment and add it to the current undo segment. Return the first free block of the extent.
5. Steal from online undo segment. Deallocate the extent from the online undo segment and add it to the current undo segment. Return the first free block of the extent.
6. Extend the file in the UNDO tablespace. If the file can be extended then add an extent to the current undo segment then return the block.
7. Otherwise try to reuse unexpired extents from own undo segment. If all extents are currently busy(they contains uncommitted information) go to the step 8. Otherwise wrap into the next extent.
8. Randomly steal unexpired extents from offline undo segments. If this fails then try to online undo segments for reuse.
9. If all the above fails then return ORA-30036 unable to extend segment by %s in undo tablespace '%s'
Can we clear undo tablespace?
Deleting an unused oracle UNDO tablespace is similar to dropping any other tablespace. If you have multiple datafiles associated with an UNDO tablespace, delete those first. First, get a list of all the datafile for your UNDO tablespace. select file_name from dba_data_files where tablespace_name = 'UNDOTBS';
What happens if undo tablespace is corrupted?
If the Oracle UNDO table space is corrupted, it may be possible to get the error as follows. When UNDO segments corrupted, the following should be done: The undo_management parameter must be MANUAL. A new UNDO tablespace must be created.
How do I reclaim space from undo tablespace?
The simplest way to reclaim space from the undo tablespace is to create a new undo tablespace, make it the database undo tablespace and drop the old tablespace. In the following example I've used autoextend, but you may wish to remove this if you want manual control over the datafile size.
Use the following queries to view undo tablespaces and rollback segments:
SELECT * FROM DBA_TABLESPACES WHERE CONTENTS = 'UNDO'; SELECT * FROM V$PARAMETER WHERE NAME = 'undo_tablespace'; ...
Use the following queries to view undo statistics:
SELECT * FROM V$ROLLSTAT; SELECT * FROM V$UNDOSTAT;
Below are some of the SQLs that can help.
To check UNTO tablepsace associated:
SQL> select inst_id, name, value from gv$parameter where name='undo_tablespace' order by inst_id;
INST_ID NAME VALUE
---------- ------------------------------ ------------------------------
1 undo_tablespace UNDOTBS1
2 undo_tablespace UNDOTBS2
3 undo_tablespace UNDOTBS3
4 undo_tablespace UNDOTBS4
To check UNDO is set to guarantee or not
SQL> select tablespace_name, retention from dba_tablespaces where tablespace_name like '%UNDO%';TABLESPACE_NAME RETENTION
------------------------------ -----------
UNDOTBS1 GUARANTEE
UNDOTBS2 GUARANTEE
UNDOTBS3 NOGUARANTEE
UNDOTBS4 NOGUARANTEE
check the overall usage of UNDO by this query as below
SQL> column tablespace format a20;
SQL> column sum_in_mb format 999999.99;
SQL> select tablespace_name tablespace, status, sum(bytes)/1024/1024 sum_in_mb, count(*) counts from dba_undo_extents group by tablespace_name, status order by 1,2;
TABLESPACE STATUS SUM_IN_MB COUNTS
-------------------- --------- ---------- ----------
UNDOTBS1 ACTIVE 1.00 1
UNDOTBS1 EXPIRED 1063.19 1015
UNDOTBS1 UNEXPIRED 241.94 152
UNDOTBS2 ACTIVE 2.00 2
UNDOTBS2 EXPIRED 12523.14 1456
UNDOTBS2 UNEXPIRED 357.10 265
The information in the status column in the above query also indicates whether the corresponding undo information will be truncated. If its status is ACTIVE, this means that undo is being used in an active process and will not be truncated in any way. If its status is UNEXPIRED, this undo information belongs to a completed transaction, indicating that it has not yet exceeded the time specified by undo_retention. It can be truncated if required. If the status is EXPIRED, this undo information belongs to a completed transaction and indicates that it has exceeded the period specified by undo_retention. This means that this is the first undo information to be truncated. You can use the following query to control user-based undo usage.
The space is not full yet, and lots of EXPIRED extents in UNDOTBS2 which is an UNDO tablespace dedicated for node 2 instance in a RAC environment. Normally, once System Monitor (SMON) detects space pressure, it either autoextend file size or reclaim EXPIRED extents.
Another SQL
UNDO:
======
col allocated for 999,999.999
col free for 999,999.999
col used for 999,999.999
select
( select sum(bytes)/1024/1024 from dba_data_files
where tablespace_name like ‘%UND%’ ) allocated_MB,
( select sum(bytes)/1024/1024/1024 from dba_free_space
where tablespace_name like ‘%UND%’) free_GB,
( select sum(bytes)/1024/1024/1024 from dba_undo_extents
where tablespace_name like ‘%UND%’) Used_GB
from dual
/
User UNDO Tablespace Usage
--------------------------
SQL> select u.tablespace_name tablespace, s.username, u.status, sum(u.bytes)/1024/1024 sum_in_mb, count(u.segment_name) seg_cnts from dba_undo_extents u left join v$transaction t on u.segment_name = '_SYSSMU' || t.xidusn || '$' left join v$session s on t.addr = s.taddr group by u.tablespace_name, s.username, u.status order by 1,2,3;
TABLESPACE USERNAME STATUS SUM_IN_MB SEG_CNTS
-------------------- --------------- --------- ---------- ----------
UNDOTBS1 SCOTT ACTIVE 8.00 1
UNDOTBS1 SCOTT EXPIRED 120.12 66
UNDOTBS1 EXPIRED 5476.18 1962
UNDOTBS1 UNEXPIRED 305.40 85
UNDOTBS2 EXPIRED 1743.67 3252
It's pretty easy to identify undo tablespace usage and who is using undo space just now You can ask users who consume a lot of undo to commit or rollback more frequently in their transactions. In this way, there will be no active use of undo for a long time.
UNDO BY SESSION LEVE WISE:
==========================
select s.sid,s.status,
s.username,
sum(ss.value) / 1024 / 1024 as undo_size_mb
from v$sesstat ss
join v$session s on s.sid = ss.sid
join v$statname stat on stat.statistic# = ss.statistic#
where stat.name = ‘undo change vector size’
and s.type ‘BACKGROUND’
and s.username IS NOT NULL
group by s.sid,s.username,s.status;
To show UndoRetention Value
SQL> Show parameter undo_retention
Undo retention in hours
col "Retention" for a30
col name for a30
col value for a50
select name "Retention",value/60/60 "Hours" from v$parameter where name like '%undo_retention%';
To check space related statistics of UndoTablespace from stats$undostat of 90 days
select UNDOBLKS,BEGIN_TIME,MAXQUERYLEN,UNXPSTEALCNT,EXPSTEALCNT,NOSPACEERRCNT from stats$undostat where BEGIN_TIME between sysdate-90 and sysdate and UNXPSTEALCNT > 0;
To check space related statistics of UndoTablespace from v$undostat
select
sum(ssolderrcnt) "Total ORA-1555s",
round(max(maxquerylen)/60/60) "Max Query HRS",
sum(unxpstealcnt) "UNExpired STEALS",
sum(expstealcnt) "Expired STEALS"
from v$undostat
order by begin_time;
To check for Active Transactions
set head on
select usn,extents,round(rssize/1048576)
rssize,hwmsize,xacts,waits,optsize/1048576 optsize,shrinks,wraps
from v$rollstat where xacts>0
order by rssize;
Undo Space Utilization by each Sessions
set lines 200
col sid for 99999
col username for a10
col name for a15
select s.sid,s.serial#,username,s.machine,
t.used_ublk ,t.used_urec,rn.name,(t.used_ublk *8)/1024/1024 SizeGB
from v$transaction t,v$session s,v$rollstat rs, v$rollname rn
where t.addr=s.taddr and rs.usn=rn.usn and rs.usn=t.xidusn and rs.xacts>0;
List of long running queries since instance startup
set head off
select 'LIST OF LONG RUNNING - QUERY SINCE INSTANCE STARTUP' from dual;
set head on
select *
from
(select to_char(begin_time, 'DD-MON-YY hh24:mi:ss') BEGIN_TIME ,
round((maxquerylen/3600),1) Hours
from v$undostat
order by maxquerylen desc)
where rownum < 11;
Undo Space used by all transactions
set lines 200
col sid for 99999
col username for a10
col name for a15
select s.sid,s.serial#,username,s.machine,
t.used_ublk ,t.used_urec,rn.name,(t.used_ublk *8)/1024/1024 SizeGB
from v$transaction t,v$session s,v$rollstat rs, v$rollname rn
where t.addr=s.taddr and rs.usn=rn.usn and rs.usn=t.xidusn and rs.xacts>0;
List of All active Transactions
select sid,username,
t.used_ublk ,t.used_urec
from v$transaction t,v$session s
where t.addr=s.taddr;
To list all Datafile of UndoTablespace
select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%UNDO%'
or tablespace_name like '%RBS%'
order by tablespace_name,file_name;
select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%UNDOTBS2%'
order by tablespace_name,file_name;
col file_name for a40
set pagesize 100
select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%APPS_UNDOTS1%'
order by tablespace_name,file_name;
select file_name,tablespace_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible
from dba_data_files where file_name like '%undo%' order by file_name;
Session Details Consuming Undo
SELECT s.inst_id,
r.name rbs,
nvl(s.username, ‘None’) oracle_user,
s.osuser client_user,
p.username unix_user,
to_char(s.sid)||’,’||to_char(s.serial#) as sid_serial,
p.spid unix_pid,
TO_CHAR(s.logon_time, ‘mm/dd/yy hh24:mi:ss’) as login_time,
t.used_ublk * 8192 as undo_BYTES,
st.sql_text as sql_text
FROM gv$process p,
v$rollname r,
gv$session s,
gv$transaction t,
gv$sqlarea st
WHERE p.inst_id=s.inst_id
AND p.inst_id=t.inst_id
AND s.inst_id=st.inst_id
AND s.taddr = t.addr
AND s.paddr = p.addr(+)
AND r.usn = t.xidusn(+)
AND s.sql_address = st.address
AND t.used_ublk * 8192 > 1073741824
ORDER
BY undo_BYTES desc
Comments
Post a Comment