• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How To Size UNDO Tablespace For Automatic Undo Management

by admin

Sizing an UNDO tablespace requires three pieces of data.

  1. (UR) UNDO_RETENTION in seconds
  2. (UPS) Number of undo data blocks generated per second
  3. (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.

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:

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'));

Filed Under: oracle, Oracle 10g, Oracle 11g

Some more articles you might also be interested in …

  1. MAX_STRING_SIZE Parameter in Oracle Database
  2. Unable to export realm protected table using data pump
  3. ORA-19554: error allocating device, device type: SBT_TAPE, device name:
  4. Shell Script to run DataPump Import Export
  5. How to recreate an ASM disk group
  6. Smart scan for ASM disk group in Exadata
  7. New Background Processes In Oracle 11g
  8. ORA-12547: TNS:lost Contact (Oracle 12c2)
  9. What are Lexical Units in a PL/SQL Block
  10. How to Restore and Recover files over network in Oracle 12c

You May Also Like

Primary Sidebar

Recent Posts

  • protonvpn-cli Command Examples in Linux
  • protonvpn-cli connect Command Examples
  • procs Command Examples in Linux
  • prlimit: command not found

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright