How To Size UNDO Tablespace For Automatic Undo Management (Doc ID 262066.1)
How to monitor Undo Tablespace Usage and the Free Space (Doc ID 1951400.1)
一、
How To Size UNDO Tablespace For Automatic Undo Management (Doc ID 262066.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.4 [Release 9.2 to 11.2]
GOAL
To assist Database Administrators in sizing an UNDO Tablespace for automatic undo management.
SOLUTION
Sizing an UNDO tablespace requires three pieces of data.
(UR) UNDO_RETENTION in seconds
(UPS) Number of undo data blocks generated per second
(DBS) Overhead varies based on extent and file size (db_block_size)
The undo space needed is calculated as:
UndoSpace = UR * (UPS * DBS)
Two of the pieces of information can be obtained from the instance configuration: UNDO_RETENTION and DB_BLOCK_SIZE. The third piece of the formula requires a query being run against the database. The maximum number of undo blocks generated per second can be acquired from V$UNDOSTAT.
Note: Overall consideration for peak/heavy vs. normal system activity should be taken into account when peforming the calculations. Autoextend OFF vs. ON will change the behavior for UNDO_RETENTION growth and use of UNEXPIRED extents. See Note 461480.1 for more information.
The following formula calculates the peak undo blocks generated per second:
SQL> SELECT undoblks/((end_time-begin_time)*86400) "Peak Undo Block Generation" FROM v$undostat WHERE undoblks=(SELECT MAX(undoblks) FROM v$undostat);
Column END_TIME and BEGIN_TIME are DATE data types. When DATE data types are subtracted, the resulting value is the # of days between both dates. To convert days to seconds, you multiply by 86400, the number of seconds in a day (24 hours * 60 minutes * 60 seconds).
The following query calculates the number of bytes needed to handle a peak undo activity:
SQL> SELECT (UR * (UPS * DBS)) AS "Bytes"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT undoblks/((end_time-begin_time)*86400) AS UPS
FROM v$undostat
WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),
(SELECT block_size AS DBS
FROM dba_tablespaces
WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'));
For 10g and Higher Versions where Tuned undo retention is being used,please use below query:
----zpli注：即 _undo_autotune=true 时，使用该计算公式
SQL>SELECT (UR * (UPS * DBS)) AS "Bytes"
FROM (select max(tuned_undoretention) AS UR from v$undostat),
(SELECT undoblks/((end_time-begin_time)*86400) AS UPS
FROM v$undostat
WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),
(SELECT block_size AS DBS
FROM dba_tablespaces
WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'));
二、
IF: How to monitor Undo Tablespace Usage and the Free Space (Doc ID 1951400.1)
In this Document
Symptoms
Changes
Cause
Solution
APPLIES TO:
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
SYMPTOMS
This document provides the various queries that can be used to monitor Undo space usage.
The Undo Space once allocated wont be deallocated to the OS, by default. But the space can be reused by other transactions once the UNDO_RETENION (or TUNED_UNDORETENTION period) is met.
CHANGES
CAUSE
None
SOLUTION
- To check the current size of the Undo tablespace:
select sum(a.bytes) as 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#;
- To check the free space (unallocated) space within Undo tablespace:
select sum(bytes)/1024/1024 "mb" from dba_free_space where tablespace_name ='
3.To Check the space available within the allocated Undo tablespace:
select tablespace_name , sum(blocks)*8/(1024) reusable_space from dba_undo_extents where status='EXPIRED' group by tablespace_name;
- To Check the space allocated in the Undo tablespace:
select tablespace_name , sum(blocks)*8/(1024) space_in_use from dba_undo_extents where status IN ('ACTIVE','UNEXPIRED') group by tablespace_name;
Alternatively, below one SQL can be used as well:
with free_sz as
(select tablespace_name, sum(f.bytes) / 1048576 / 1024 free_gb
from dba_free_space f
group by tablespace_name),
a as
(select tablespace_name,
sum(case
when status = 'EXPIRED' then
blocks
end) * 8 / 1048576 reusable_space_gb,
sum(case
when status in ('ACTIVE', 'UNEXPIRED') then
blocks
end) * 8 / 1048576 allocated_gb
from dba_undo_extents
where status in ('ACTIVE', 'EXPIRED', 'UNEXPIRED')
group by tablespace_name),
undo_sz as
(select tablespace_name, df.user_bytes / 1048576 / 1024 user_sz_gb
from dba_tablespaces ts
join dba_data_files df
using (tablespace_name)
where ts.contents = 'UNDO'
and ts.status = 'ONLINE')
select tablespace_name,
user_sz_gb,
free_gb,
reusable_space_gb,
allocated_gb,
free_gb + reusable_space_gb + allocated_gb total
from undo_sz
join free_sz
using (tablespace_name)
join a
using (tablespace_name);