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

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • 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. Oracle Interview Questions – Flash Recovery Area
  2. Oracle Database 12c : Creating a Scheduler Job in a Multitenant Database
  3. How To Setup UDEV Rules For RAC OCR And Voting Devices on Partitions
  4. Beginners Guide to Working with spfile in Oracle ASM
  5. Beginners Guide to RMAN compression for backups
  6. How to configure Partitioned Block Devices (Non-ASMLIB) And Assign Them To ASM
  7. Oracle Tablespace Transport for a Single Partition
  8. Recommendation for the Oracle Real Application Cluster Interconnect and Jumbo Frames
  9. How to Convert STANDARD ASM to FLEX ASM in 12C
  10. How to Switch to a New Undo Tablespace in Oracle Database

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ACPI in CentOS/RHEL 7
  • How to Use real-time query to access data on a physical standby database
  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary