View the Total Size of Undo:-
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name where
tablespace_name = 'UNDO_TS';
TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
UNDO_TS 13206
View used Size space of Undo :-
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_segments where tablespace_name = 'UNDO_TS'
group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
UNDO_TS 12864.6172
View undo Segment statistics based on status:-
SQL> select tablespace_name, status, sum(blocks) * 8192/1024/1024 "MB" from dba_undo_extents group by tablespace_name, status;
TABLESPACE_NAME STATUS MB
------------------------------ --------- ----------
UNDO_TS EXPIRED 7.1796875
UNDO_TS UNEXPIRED 12863.4609
View the No. Undo block required per second:-
SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC" FROM v$undostat;
UNDO_BLOCK_PER_SEC
------------------
180.633333
To know the Optimal undo_rention seconds:-
Optimal Undo Retention =
Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REQ_PSC)
SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size';
ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] OPTIMAL UNDO RETENTION [Sec]
------------------------ ------------------------- ----------------------------
13206 18000 9358
To know the undo space required for the current database load/activity:-
SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size' 24 ;
ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] NEEDED UNDO SIZE [MByte]
------------------------ ------------------------- ------------------------
13206 18000 25401.5625
Undo space Estimate Formula :-
Undo_rettention * undo_blcoks_per_sec * db_block_size
Optimal undo retention size Formual :-
Actual undo size
___________________
undo_blcoks_per_sec * db_block_size
===============
oracle 9i undo usage:-
set linesize 120
set pagesize 60
alter session set nls_date_format = "dd-Mon-yyyy hh24:mi:ss";
COL TXNCOUNT FOR 99,999,999 HEAD 'Txn. Cnt.'
COL MAXQUERYLEN FOR 99,999,999 HEAD 'Max|Query|Sec'
COL MAXCONCURRENCY FOR 9,999 HEAD 'Max|Concr|Txn'
COL bks_per_sec FOR 99,999,999 HEAD 'Blks per|Second'
COL kb_per_second FOR 99,999,999 HEAD 'KB per|Second'
COL undo_mb_required FOR 999,999 HEAD 'MB undo|Needed'
COL ssolderrcnt FOR 9,999 HEAD 'ORA-01555|Count'
COL nospaceerrcnt FOR 9,999 HEAD 'No Space|Count'
break on report
compute max of txncount -
maxquerylen -
maxconcurrency -
bks_per_sec -
kb_per_second -
undo_mb_required on report
compute sum of -
ssolderrcnt -
nospaceerrcnt on report
SELECT begin_time,
decode(txncount,0,lag(txncount) over (order by end_time), txncount) - lag(txncount) over (order by end_time) as txncount,
maxquerylen,
maxconcurrency,
undoblks/((end_time - begin_time)*86400) as bks_per_sec,
(undoblks/((end_time - begin_time)*86400)) * t.block_size/1024 as kb_per_second,
((undoblks/((end_time - begin_time)*86400)) * t.block_size/1024) * TO_NUMBER(p2.value)/1024 as undo_MB_required,
ssolderrcnt,
nospaceerrcnt
FROM v$undostat s,
dba_tablespaces t,
v$parameter p,
v$parameter p2
WHERE t.tablespace_name = UPPER(p.value)
AND p.name = 'undo_tablespace'
AND p2.name = 'undo_retention'
ORDER BY begin_time;
show parameter undo
clear computes
Which queries are using undo segments
select a.name, b.xacts, c.sid, c.serial#, c.username, d.sql_text
from v$rollname a, v$rollstat b, v$session c, v$sqltext d,
v$transaction e
where a.usn = b.usn
and b.usn = e.xidusn
and c.taddr = e.addr
and c.sql_address = d.address
and c.sql_hash_value = d.hash_value
order by a.name, c.sid, d.piece;
ouput:-
name xacts sid serial# username sql_text
RB1 1 5 33 USER1 delete from test1;
RB2 1 7 41 USER9 update orders set items = 5 where orderno = 555;
Undo Management in oracle:-
How undo Tablespace Shrinks ---
Explanation by Thomas Kyte.
Question:-
Just few days ago, the UNDOTBS tablespace is about 99.8% used
(UNDOTBS size is 16G in one file with autoextend 'NO', database size is 450G).
The database was running huge work. I tried to add one datafile to the UNDOTBS.
My colleague told me that we don't need to do that because Oracle will automatically
handle it, if add more space to it, the UNDOTBS will continue to grow,so I did not
add a file to it. Maybe one hour or maybe two hours later, the UNDBTBS tablespace
shrink to about 14G, is less than 76% used.
I am wondering how Oracle handle UNDOTBS tablespace size if 100% used.
Answer by Thomas Kyte:-
The undo tablespace is a series of circular queues, a set of undo segments (made up of extents).
The undo tablespace will have a set of undo segments in it, each of these is a circular queue, the extents point to each other, the last extent in the segment points to the first extent.
The size of this queue is controlled by you indirectly via your setting of undo_retention.
So, let's say you set undo_retention to 1 hour. We will ATTEMPT to hold undo in the undo segments for at least one hour.
So, as you are aware, as we are processing transactions we write UNDO to these segments.
Now, as you generate undo, we get to the end of an extent and want to advance into the next extent (the last extent remember considers the FIRST extent to be its NEXT extent - the circular queue concept).
Before we advance into the next extent - it must not contain any active transactions - if it does, we cannot advance and would simply add another new extent into this queue. We would get that extent from
a) free space in the undo tablespace OR
b) by extending the datafiles for the undo tablespace to get more free space
(not in your case, you disabled that) OR
c) by stealing an extent from some other undo segment - finding its oldest unused extent and stealing it.
If all of that failed, you would receive an error and the statement you were executing would be rolled back.
Now, let's say the next extent is not active - before we advance into it, we'll check the age of the data in it. If the age of the data is more than 1 hour (our retention period) - we'll advance into it. If the age of the data is less than one hour we will try
a) add extent from free space so as to not overwrite the data
b) add extent from free space after autoextending a file
c) to steal an extent from another undo segment that is not active and has data over an hour old.
If none of that works, we'll have to advance into that extent and prematurely "expire" this undo (eg: violate your undo retention request). We will not fail (unless you set the undo retention guarantee)
You can monitor all of that in v$undostat
Now, after a while, some extents in your undo tablespace might become older than one hour hold (data is no longer needed), we can and do release these extents over time as we online and offline undo segments and such.
That is the "shrinking" you saw - we just freed up data we didn't need anymore, putting it back into the free space so that when we need to grow again - it is there.
Free space in Undo Tablespace is the totalsize of the undo minus the sum of bytes irrespecitve of status.
SQL> select tablespace_name, status, sum(bytes)/1024/1024 from dba_undo_extents group by tablespace_name, status;
TABLESPACE_NAME STATUS SUM(BYTES)/1024/1024
------------------------------ --------- --------------------
UNDO_TS ACTIVE 256
UNDO_TS EXPIRED 75.1171875
UNDO_TS UNEXPIRED 7106.03125
SQL> select tablespace_name, sum(bytes)/1024/1024 from dba_free_space where tablespace_name = 'UNDO_TS' group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
UNDO_TS 5768.3125
SQL> select tablespace_name, sum(bytes)/1024/1024 from dba_Data_files where tablespace_name = 'UNDO_TS' group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
UNDO_TS 13206
SQL> select tablespace_name, sum(bytes)/1024/1024 from dba_segments where tablespace_name = 'UNDO_TS' group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
UNDO_TS 7461.14844
SQL> select totalsize - usedextents "Free Space in Undo Tablespace" from
( select sum(bytes)/1024/1024 usedextents from dba_undo_extents),
( select sum(bytes)/1024/1024 totalsize from dba_data_files where tablespace_name = 'UNDO_TS' group by tablespace_name);
Free Space in Undo Tablespace
-----------------------------
5563.85156
To find the No. of transaction utilizing undo segments.
SQL> select
substr(lkd.os_user_name,1,8) "OS User",
substr(lkd.oracle_username,1,8) "DB User",
substr(obj.owner,1,8) "Schema",
substr(obj.object_name,1,20) "Object Name",
substr(obj.object_type,1,10) "Type",
substr(rbs.segment_name,1,5) "RBS",
substr(trn.used_urec,1,12) "# of Records"
from
v$locked_object lkd,
dba_objects obj,
dba_rollback_segs rbs,
v$transaction trn,
v$session ses
where
obj.object_id = lkd.object_id
and rbs.segment_id = lkd.xidusn
and trn.xidusn = lkd.xidusn
and trn.xidslot = lkd.xidslot
and trn.xidsqn = lkd.xidsqn -- added for completeness
and ses.taddr = trn.addr
;
============================================================================
For more info check the below ..
How to determine undo usage in Oracle
Overview
Undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued. Automatic undo management allows the DBA to specify how long undo information should be retained after commit, preventing “snapshot too old” errors on long running queries.
This is done by setting the UNDO_RETENTION parameter. The default is 900 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time. Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle take care of the rest. Turning on automatic undo management is easy. All you need to do is create an undo tablespace and set UNDO_MANAGEMENT = AUTO.
With the below information gathered by SQL query, you may have a idea to prevent undo space is running out so quickly.
Undo Segments
In Undo Segments there are three types of extents, they are
Unexpired Extents – Undo data whose age is less than the undo retention period.
Expired Extents – Undo data whose age is greater than the undo retention period.
Active Extents – Undo data that is part of the active transaction.
The sequence for using extents is as follows,
1. A new extent will be allocated from the undo tablespace when the requirement arises. As undo is being written to an undo segment, if the undo reaches the end of the current extent and the next extent contains expired undo then the new undo (generated by the current transaction) will wrap into that expired extent, in preference to grabbing a free extent from the undo tablespace free extent pool.
2. If this fails because of no available free extents and we cannot autoextend the datafile, then Oracle tries to steal an expired extent from another undo segment.
3. If it still fails because there are no extents with expired status then Oracle tries to reuse an unexpired extent from the current undo segment.
4. If even that fails, Oracle tries to steal an unexpired extent from another undo segment.
5. If all the above fails, an Out-Of-Space error will be reported.
Check the overall status for undos.
select tablespace_name, status, sum(blocks) * 8192/1024/1024/1024 GB from dba_undo_extents group by tablespace_name, status;
TABLESPACE_NAME STATUS GB
------------------------------ --------- ----------
UNDOTBS1 UNEXPIRED 2.29626465
UNDOTBS2 UNEXPIRED 11.0892944
UNDOTBS1 EXPIRED 7.20245361
UNDOTBS2 EXPIRED 1.80932617
UNDOTBS2 ACTIVE .015625
Undo Blocks per Second
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;
Optimal Undo Retention
209'715'200 / (3.12166667 * 4'096) = 16'401 [Sec]
Using Inline Views, you can do all in one query!
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
Calculate Needed UNDO Size for given Database Activity
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
------------------------------------
http://www.orafaq.com/node/61
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name where
tablespace_name = 'UNDO_TS';
TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
UNDO_TS 13206
View used Size space of Undo :-
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_segments where tablespace_name = 'UNDO_TS'
group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
UNDO_TS 12864.6172
View undo Segment statistics based on status:-
SQL> select tablespace_name, status, sum(blocks) * 8192/1024/1024 "MB" from dba_undo_extents group by tablespace_name, status;
TABLESPACE_NAME STATUS MB
------------------------------ --------- ----------
UNDO_TS EXPIRED 7.1796875
UNDO_TS UNEXPIRED 12863.4609
View the No. Undo block required per second:-
SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC" FROM v$undostat;
UNDO_BLOCK_PER_SEC
------------------
180.633333
To know the Optimal undo_rention seconds:-
Optimal Undo Retention =
Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REQ_PSC)
SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size';
ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] OPTIMAL UNDO RETENTION [Sec]
------------------------ ------------------------- ----------------------------
13206 18000 9358
To know the undo space required for the current database load/activity:-
SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size' 24 ;
ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] NEEDED UNDO SIZE [MByte]
------------------------ ------------------------- ------------------------
13206 18000 25401.5625
Undo space Estimate Formula :-
Undo_rettention * undo_blcoks_per_sec * db_block_size
Optimal undo retention size Formual :-
Actual undo size
___________________
undo_blcoks_per_sec * db_block_size
===============
oracle 9i undo usage:-
set linesize 120
set pagesize 60
alter session set nls_date_format = "dd-Mon-yyyy hh24:mi:ss";
COL TXNCOUNT FOR 99,999,999 HEAD 'Txn. Cnt.'
COL MAXQUERYLEN FOR 99,999,999 HEAD 'Max|Query|Sec'
COL MAXCONCURRENCY FOR 9,999 HEAD 'Max|Concr|Txn'
COL bks_per_sec FOR 99,999,999 HEAD 'Blks per|Second'
COL kb_per_second FOR 99,999,999 HEAD 'KB per|Second'
COL undo_mb_required FOR 999,999 HEAD 'MB undo|Needed'
COL ssolderrcnt FOR 9,999 HEAD 'ORA-01555|Count'
COL nospaceerrcnt FOR 9,999 HEAD 'No Space|Count'
break on report
compute max of txncount -
maxquerylen -
maxconcurrency -
bks_per_sec -
kb_per_second -
undo_mb_required on report
compute sum of -
ssolderrcnt -
nospaceerrcnt on report
SELECT begin_time,
decode(txncount,0,lag(txncount) over (order by end_time), txncount) - lag(txncount) over (order by end_time) as txncount,
maxquerylen,
maxconcurrency,
undoblks/((end_time - begin_time)*86400) as bks_per_sec,
(undoblks/((end_time - begin_time)*86400)) * t.block_size/1024 as kb_per_second,
((undoblks/((end_time - begin_time)*86400)) * t.block_size/1024) * TO_NUMBER(p2.value)/1024 as undo_MB_required,
ssolderrcnt,
nospaceerrcnt
FROM v$undostat s,
dba_tablespaces t,
v$parameter p,
v$parameter p2
WHERE t.tablespace_name = UPPER(p.value)
AND p.name = 'undo_tablespace'
AND p2.name = 'undo_retention'
ORDER BY begin_time;
show parameter undo
clear computes
Which queries are using undo segments
select a.name, b.xacts, c.sid, c.serial#, c.username, d.sql_text
from v$rollname a, v$rollstat b, v$session c, v$sqltext d,
v$transaction e
where a.usn = b.usn
and b.usn = e.xidusn
and c.taddr = e.addr
and c.sql_address = d.address
and c.sql_hash_value = d.hash_value
order by a.name, c.sid, d.piece;
ouput:-
name xacts sid serial# username sql_text
RB1 1 5 33 USER1 delete from test1;
RB2 1 7 41 USER9 update orders set items = 5 where orderno = 555;
Undo Management in oracle:-
How undo Tablespace Shrinks ---
Explanation by Thomas Kyte.
Question:-
Just few days ago, the UNDOTBS tablespace is about 99.8% used
(UNDOTBS size is 16G in one file with autoextend 'NO', database size is 450G).
The database was running huge work. I tried to add one datafile to the UNDOTBS.
My colleague told me that we don't need to do that because Oracle will automatically
handle it, if add more space to it, the UNDOTBS will continue to grow,so I did not
add a file to it. Maybe one hour or maybe two hours later, the UNDBTBS tablespace
shrink to about 14G, is less than 76% used.
I am wondering how Oracle handle UNDOTBS tablespace size if 100% used.
Answer by Thomas Kyte:-
The undo tablespace is a series of circular queues, a set of undo segments (made up of extents).
The undo tablespace will have a set of undo segments in it, each of these is a circular queue, the extents point to each other, the last extent in the segment points to the first extent.
The size of this queue is controlled by you indirectly via your setting of undo_retention.
So, let's say you set undo_retention to 1 hour. We will ATTEMPT to hold undo in the undo segments for at least one hour.
So, as you are aware, as we are processing transactions we write UNDO to these segments.
Now, as you generate undo, we get to the end of an extent and want to advance into the next extent (the last extent remember considers the FIRST extent to be its NEXT extent - the circular queue concept).
Before we advance into the next extent - it must not contain any active transactions - if it does, we cannot advance and would simply add another new extent into this queue. We would get that extent from
a) free space in the undo tablespace OR
b) by extending the datafiles for the undo tablespace to get more free space
(not in your case, you disabled that) OR
c) by stealing an extent from some other undo segment - finding its oldest unused extent and stealing it.
If all of that failed, you would receive an error and the statement you were executing would be rolled back.
Now, let's say the next extent is not active - before we advance into it, we'll check the age of the data in it. If the age of the data is more than 1 hour (our retention period) - we'll advance into it. If the age of the data is less than one hour we will try
a) add extent from free space so as to not overwrite the data
b) add extent from free space after autoextending a file
c) to steal an extent from another undo segment that is not active and has data over an hour old.
If none of that works, we'll have to advance into that extent and prematurely "expire" this undo (eg: violate your undo retention request). We will not fail (unless you set the undo retention guarantee)
You can monitor all of that in v$undostat
Now, after a while, some extents in your undo tablespace might become older than one hour hold (data is no longer needed), we can and do release these extents over time as we online and offline undo segments and such.
That is the "shrinking" you saw - we just freed up data we didn't need anymore, putting it back into the free space so that when we need to grow again - it is there.
Free space in Undo Tablespace is the totalsize of the undo minus the sum of bytes irrespecitve of status.
SQL> select tablespace_name, status, sum(bytes)/1024/1024 from dba_undo_extents group by tablespace_name, status;
TABLESPACE_NAME STATUS SUM(BYTES)/1024/1024
------------------------------ --------- --------------------
UNDO_TS ACTIVE 256
UNDO_TS EXPIRED 75.1171875
UNDO_TS UNEXPIRED 7106.03125
SQL> select tablespace_name, sum(bytes)/1024/1024 from dba_free_space where tablespace_name = 'UNDO_TS' group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
UNDO_TS 5768.3125
SQL> select tablespace_name, sum(bytes)/1024/1024 from dba_Data_files where tablespace_name = 'UNDO_TS' group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
UNDO_TS 13206
SQL> select tablespace_name, sum(bytes)/1024/1024 from dba_segments where tablespace_name = 'UNDO_TS' group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
UNDO_TS 7461.14844
SQL> select totalsize - usedextents "Free Space in Undo Tablespace" from
( select sum(bytes)/1024/1024 usedextents from dba_undo_extents),
( select sum(bytes)/1024/1024 totalsize from dba_data_files where tablespace_name = 'UNDO_TS' group by tablespace_name);
Free Space in Undo Tablespace
-----------------------------
5563.85156
To find the No. of transaction utilizing undo segments.
SQL> select
substr(lkd.os_user_name,1,8) "OS User",
substr(lkd.oracle_username,1,8) "DB User",
substr(obj.owner,1,8) "Schema",
substr(obj.object_name,1,20) "Object Name",
substr(obj.object_type,1,10) "Type",
substr(rbs.segment_name,1,5) "RBS",
substr(trn.used_urec,1,12) "# of Records"
from
v$locked_object lkd,
dba_objects obj,
dba_rollback_segs rbs,
v$transaction trn,
v$session ses
where
obj.object_id = lkd.object_id
and rbs.segment_id = lkd.xidusn
and trn.xidusn = lkd.xidusn
and trn.xidslot = lkd.xidslot
and trn.xidsqn = lkd.xidsqn -- added for completeness
and ses.taddr = trn.addr
;
============================================================================
For more info check the below ..
How to determine undo usage in Oracle
Overview
Undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued. Automatic undo management allows the DBA to specify how long undo information should be retained after commit, preventing “snapshot too old” errors on long running queries.
This is done by setting the UNDO_RETENTION parameter. The default is 900 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time. Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle take care of the rest. Turning on automatic undo management is easy. All you need to do is create an undo tablespace and set UNDO_MANAGEMENT = AUTO.
With the below information gathered by SQL query, you may have a idea to prevent undo space is running out so quickly.
Undo Segments
In Undo Segments there are three types of extents, they are
Unexpired Extents – Undo data whose age is less than the undo retention period.
Expired Extents – Undo data whose age is greater than the undo retention period.
Active Extents – Undo data that is part of the active transaction.
The sequence for using extents is as follows,
1. A new extent will be allocated from the undo tablespace when the requirement arises. As undo is being written to an undo segment, if the undo reaches the end of the current extent and the next extent contains expired undo then the new undo (generated by the current transaction) will wrap into that expired extent, in preference to grabbing a free extent from the undo tablespace free extent pool.
2. If this fails because of no available free extents and we cannot autoextend the datafile, then Oracle tries to steal an expired extent from another undo segment.
3. If it still fails because there are no extents with expired status then Oracle tries to reuse an unexpired extent from the current undo segment.
4. If even that fails, Oracle tries to steal an unexpired extent from another undo segment.
5. If all the above fails, an Out-Of-Space error will be reported.
Check the overall status for undos.
select tablespace_name, status, sum(blocks) * 8192/1024/1024/1024 GB from dba_undo_extents group by tablespace_name, status;
TABLESPACE_NAME STATUS GB
------------------------------ --------- ----------
UNDOTBS1 UNEXPIRED 2.29626465
UNDOTBS2 UNEXPIRED 11.0892944
UNDOTBS1 EXPIRED 7.20245361
UNDOTBS2 EXPIRED 1.80932617
UNDOTBS2 ACTIVE .015625
Undo Blocks per Second
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;
Optimal Undo Retention
209'715'200 / (3.12166667 * 4'096) = 16'401 [Sec]
Using Inline Views, you can do all in one query!
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
Calculate Needed UNDO Size for given Database Activity
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
------------------------------------
http://www.orafaq.com/node/61
No comments:
Post a Comment