Home > Optimize Oracle UNDO Parameters, Performace Tuning > Optimize Oracle UNDO Parameters

Optimize Oracle UNDO Parameters

Data Concurrency and Read Consistency

ROLLBACK or UNDO is the backbone of the READ CONSISTENCY mechanism provided by Oracle. Multi-User Data Concurrency and Read Consistency mechanism make Oracle stand tall in Relational Database Management Systems (RDBMS) world.

Starting in Oracle9i, rollback segments are re-named undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued, at which point it was made available for overlaying.

Best of all, 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.

UNDO_RETENTION is a parameter in the init.ora initialization parameters file that specifies the time period in seconds for which a system retains undo data for committed transactions. The flashback query can go upto the point of time specified as a value in the UNDO_RETENTION parameter.

The properties of the UNDO_RETENTION parameter are mentioned below:

  • Parameter type – Integer
  • Default value – 900
  • Range of values – 0 to 232 – 1
  • Syntax – ALTER SYSTEM SET UNDO_RETENTION = < seconds >;

However it is worth to tune the following important parameters

1. The size of the UNDO tablespace
2. The UNDO_RETENTION parameter

Calculate UNDO_RETENTION for given UNDO Tabespace

You can choose to allocate a specific size for the UNDO tablespace and then set the UNDO_RETENTION parameter to an optimal value according to the UNDO size and the database activity. If your disk space is limited and you do not want to allocate more space than necessary to the UNDO tablespace, this is the way to proceed. The following query will help you to optimize the UNDO_RETENTION parameter:

Optimal Undo Retention =
           Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC)

Because these following queries use the V$UNDOSTAT statistics, run the queries only after the database has been running with UNDO for a significant and representative time.

Actual Undo Size

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#;

Undo Blocks per Second

SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"
FROM v$undostat;

DB Block Size

SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';

Optimal Undo Retention Calculation

Formula:
Optimal Undo Retention = 
           Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC)

Using Inline Views, you can do all calculation in one query

SQL Code:
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

If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:

Formula:
Undo Size = Optimal Undo Retention × DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC

Using Inline Views, you can do all calculation in one query

SQL Code:
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'

Automatic Undo Retention Tuning

Oracle 10g automatically tunes undo retention to reduce the chances of “snapshot too old” errors during long-running queries. The UNDO_RETENTION parameter is used to set a low retention time threshold which the system will attempt to achieve. In the event of any undo space constraints the system will prioritize DML operations over undo retention meaning the low threshold may not be achieved. If the undo retention threshold must be guaranteed, even at the expense of DML operations, the RETENTION GUARANTEE clause can be set against the undo tablespace during or after creation:

Reset the undo low threshold.

ALTER SYSTEM SET UNDO_RETENTION = 2400;

Guarantee the minimum threshold is maintained.

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
 
SELECT tablespace_name, retention FROM dba_tablespaces;
 
TABLESPACE_NAME                RETENTION
------------------------------ -----------
SYSTEM                         NOT APPLY
UNDOTBS1                       GUARANTEE
SYSAUX                         NOT APPLY
TEMP                           NOT APPLY
USERS                          NOT APPLY
 
5 rows selected.

Switch back to the default mode.

ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;
 
TABLESPACE_NAME                RETENTION
------------------------------ -----------
SYSTEM                         NOT APPLY
UNDOTBS1                       NOGUARANTEE
SYSAUX                         NOT APPLY
TEMP                           NOT APPLY
USERS                          NOT APPLY
 
5 rows selected.

The Undo Advisor PL/SQL Interface

Oracle Database provides an Undo Advisor that provides advice on and helps automate the establishment of your undo environment. You activate the Undo Advisor by creating an undo advisor task through the advisor framework. The following example creates an undo advisor task to evaluate the undo tablespace. The name of the advisor is ‘Undo Advisor‘. The analysis is based on Automatic Workload Repository snapshots, which you must specify by setting parameters START_SNAPSHOT and END_SNAPSHOT. In the following example, the START_SNAPSHOT is “1” and END_SNAPSHOT is “2”.

DECLARE
   tid    NUMBER;
   tname  VARCHAR2(30);
   oid    NUMBER;
   BEGIN
   DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');
   DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, 'null', oid);
   DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);
   DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 1);
   DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 2);
   DBMS_ADVISOR.execute_task(tname);
end;
Source : http://www.oracle.com/technology/oramag/code/tips2004/032904.html

Automatic Undo Retention Tuning

Oracle 10g automatically tunes undo retention to reduce the chances of “snapshot too old” errors during long-running queries. The UNDO_RETENTION parameter is used to set a low retention time threshold which the system will attempt to achieve. In the event of any undo space constraints the system will prioritize DML operations over undo retention meaning the low threshold may not be achieved. If the undo retention threshold must be guaranteed, even at the expense of DML operations, the RETENTION GUARANTEE clause can be set against the undo tablespace during or after creation:

Reset the undo low threshold.

ALTER SYSTEM SET UNDO_RETENTION = 2400;

Guarantee the minimum threshold is maintained.

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
 
SELECT tablespace_name, retention FROM dba_tablespaces;
 
TABLESPACE_NAME                RETENTION
------------------------------ -----------
SYSTEM                         NOT APPLY
UNDOTBS1                       GUARANTEE
SYSAUX                         NOT APPLY
TEMP                           NOT APPLY
USERS                          NOT APPLY
 
5 rows selected.

Switch back to the default mode.

ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;
 
TABLESPACE_NAME                RETENTION
------------------------------ -----------
SYSTEM                         NOT APPLY
UNDOTBS1                       NOGUARANTEE
SYSAUX                         NOT APPLY
TEMP                           NOT APPLY
USERS                          NOT APPLY
 
5 rows selected.
Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: