What is Undo Tablespace and its importance
Behavior of undo tablespace is different from Normal Tablspace, It store undo record mean old image of data, if we update record from 35 to 40 , it will hold 35 to maintain data consistency
in case same record accessed by other sessions or users, it will fetch old record until unless it is committed .
Therefore usage of undo tablespace cannot be consided based on usages , There is different type of segment inside undo tablespace ,expired ,unexpired ,active ,expired segment will be recycled and will be available for new transaction .
Oracle maintains information to nullify changes made to the database. Such information consists of records of the actions of transactions, collectively known as undo. Oracle uses the undo to do the following:
- Rollback an active transaction
- Recover a terminated transaction
- Provide read consistency
- Recovery from logical corruptions
UNDO_MANAGEMENT Initialization Parameter
UNDO_MANAGEMENT specifies which undo space management mode the system should use. When set to AUTO, the instance starts in automatic undo management mode. In manual undo management mode, undo space is allocated externally as rollback segments.
By default, this parameter is set to MANUAL. Set this parameter to AUTO to enable automatic undo management mode.
This is a static parameter and cannot be modified dynamically using alter system command.
So if you wish to switch between Rollback Segments and AUM, then you need to restart the instance.
UNDO_RETENTION Initialization Parameter
This parameter specifies (in seconds) the low threshold value of undo retention.
The UNDO_RETENTION parameter can only be honored if the current undo tablespace has enough space. If an active transaction requires undo space and the undo tablespace does not have available space, then the system starts reusing unexpired undo space. This action can potentially cause some queries to fail with a "snapshot too old" message.
The amount of time for which undo is retained for the Oracle Database for the current undo tablespace can be obtained by querying the TUNED_UNDORETENTION column of the V$UNDOSTAT dynamic performance view.
To check undo Tablespace usages
-------------------------------------
COL TABLESPACE_NAME FORMAT A40
col "Tablespace" for a50
col "USED_IN_MB" for 9999999
col "FREE_IN_MB" for 99999
select
a.TABLESPACE_NAME,
a.TOTAL_IN_MB,
to_char(nvl((b.used),0.00),'999990.99') USED_IN_MB,
to_char(nvl((c.free),0.00),'999990.99') FREE_IN_MB,
to_char(nvl(((b.used/a.TOTAL_IN_MB)*100),0.00),'99990.99') PCT_USED
from
(select TABLESPACE_NAME,
to_char(sum(bytes)/(1024*1024),'9999990.99') TOTAL_IN_MB
from sys.dba_data_files
group by TABLESPACE_NAME) a,
(select TABLESPACE_NAME,bytes/(1024*1024) used
from sys.SM$TS_USED) b,
(select TABLESPACE_NAME,bytes/(1024*1024) free
from sys.SM$TS_free) c
where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+) and
a.TABLESPACE_NAME=c.tablespace_name(+) and c.tablespace_name='&TSPACE' ;
set num 5
COL file_name FORMAT A60
select file_name,bytes/1024/1024 mb ,AUTOEXTENSIBLE from dba_data_files where tablespace_name='&TSPAC' ;
select file_name,bytes/1024/1024 mb from dba_data_files where tablespace_name='&TSPAC' ;
select file_name from dba_data_files where tablespace_name='&TSPAC' ;
Transaction Status of the undo in the extent can be any of the following:
SQL> select distinct status ,sum(bytes/1024/1024),count(*) from dba_undo_extents group by status;
STATUS SUM(BYTES/1024/1024) COUNT(*)
--------- -------------------- ----------
EXPIRED 11192.3125 1016
UNEXPIRED 20382.25 1591
ACTIVE 192 3
ACTIVE - Undo Extent is Active, Used by a transaction.
EXPIRED - Undo Extent is expired (Exceeded the Undo Retention).
UNEXPIRED - Undo Extent will be required to honor UNDO_RETENTION.
To check undo datafile
select file_name,bytes/1024/1024 mb,AUTOEXTENSIBLE from dba_data_files where tablespace_name='&TSPAC'
To extend datafile to add additional space
alter database datafile '/ora/gnsisprd/001/oradata/gnsisprd/undo_05.dbf' resize 7000m
V$TRANSACTION lists the active transactions in the system.
(a) The following columns together points to a transaction. (ie) The combination of the following should give unique transaction id for that database.
XIDUSN - Undo segment number
XIDSLOT - NUMBER Slot number
XIDSQN - NUMBER Sequence number
(b) The following columns explains the number of undo blocks / undo records used per transaction.
USED_UBLK - Number of undo blocks used
USED_UREC - Number of undo records used
In the case of transaction rollback, the above columns will give estimation about the number of undo blocks that needs to be rolled back.
The number of undo records and undo blocks (USED_UREC and USED_UBLK) decrease while the transaction is rolling back. When they reach 0, the transaction disappears from v$transaction.
The following query can be used to monitor the transaction rollback.
SQL> select a.sid,a.username,b.xidusn,b.used_urec,b.used_ublk from v$session a, v$transaction b where a.saddr=b.ses_addr;
SID USERNAME XIDUSN USED_UREC USED_UBLK
---------- ------------------------------ ---------- ---------- ----------
87 OPS$ETRD359 12 1 1
2354 OPS$LMER046 258 1 1
1137 OPS$TPRD001 11 1 1
1807 G2_PROD_SUPPORT 14 1 1
SQL> select xidusn,ses_addr,status from v$transaction;
XIDUSN SES_ADDR STATUS
---------- ---------------- ----------------
12 070001024A88A4F0 ACTIVE
14 0700010252B7F8A0 ACTIVE
To understand state of the extents, space-used in the current undo tablespace.
select distinct status,sum(bytes/1024/1024),count(*) from dba_undo_extents group by status;
You need to find out what user and which SQL statement is eating up all the UNDO space.
SQL> select s.sql_text from v$sql s, v$undostat u where u.maxqueryid=s.sql_id;
You can also use following SQL to find out most undo used by a session for a currently executing transaction.
SQL> select s.sid,s.username,t.used_urec,t.used_ublk
from v$session s, v$transaction t
where s.saddr = t.ses_addr
order by t.used_ublk desc;
To find out which session is currently using the most UNDO,
SQL>select s.sid, t.name, s.value
from v$sesstat s, v$statname t
where s.statistic#=t.statistic#
and t.name='undo change vector size'
order by s.value desc;
SQL>select sql.sql_text, t.used_urec records, t.used_ublk blocks,
(t.used_ublk*8192/1024) kb from v$transaction t,
v$session s, v$sql sql
where t.addr=s.taddr
and s.sql_id = sql.sql_id
and s.username ='&USERNAME';
select
s.sid,
s.serial#,
s.username,
u.segment_name,
count(u.extent_id) "Extent Count",
t.used_ublk,
t.used_urec,
s.program
from
v$session s,
v$transaction t,
dba_undo_extents u
where
s.taddr = t.addr
and
u.segment_name like '_SYSSMU'||t.xidusn||'_%$'
and
u.status = 'ACTIVE'
group by
s.sid,
s.serial#,
s.username,
u.segment_name,
t.used_ublk,
t.used_urec,
s.program
order by
t.used_ublk desc,
t.used_urec desc,
s.sid,
s.serial#,
s.username,
s.program;
Here is another version of this query:
select
s.username,
s.sid,
s.serial#,
s.logon_time,
t.xidusn, t.ubafil,
t.ubablk,
t.used_ublk,
t.start_date, t.status
from
v$session s,
v$transaction t
where
s.saddr = t.ses_addr;
This query will also display UNDO for a user session:
select
s.sid,
s.username,
r.name "RBS name",
t.start_time,
t.used_ublk "Undo blocks",
t.used_urec "Undo recs"
from
v$session s,
v$transaction t,
v$rollname r
where
t.addr = s.taddr
and
r.usn = t.xidusn;
Here is a script to display UNDO changes by a session:
select
b.sid,
b.value,
a.name
from
v$statname a,
v$sesstat b
where
a.statistic# = b.statistic#
and
lower(a.name) = lower ('undo change vector size')
and
b.sid = nnn;
Here is yet another script to display UNDO by session:
set lines 200;
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"
from
sys.v_$rollname r,
sys.v_$session s,
sys.v_$transaction t,
sys.v_$parameter x
where
s.taddr = t.addr
AND
r.usn = t.xidusn(+)
AND
x.name = 'db_block_size';
SQL> select sid,serial#,machine,module,username,SQL_ID,to_char(logon_time,'dd-mon:hh24:mi') from v$session where sid= 255;
SID SERIAL# MACHINE MODULE USERNAME SQL_ID TO_CHAR(LOGO
----- ------- ---------------------------------------------------------------- ---------------------------------------------------------------- --------------- ------------- ------------
255 28547 PC73183DD SQL Developer G2_PROD_SUPPORT 87ju09164fhvk 10-aug:09:53
SQL>
SELECT a.sql_text FROM v$sqlarea a WHERE a.sql_id = '87ju09164fhvk' ;
No comments:
Post a Comment