Automatic Tuning of Undo Retention in Oracle

Back to: Oracle DBA Tutorials

Automatic Tuning of Undo Retention in Oracle

In this article, we will learn about the Automatic Tuning of Undo Retention in Oracle with Examples. Please read our previous article discussing Flashback Table and Flashback Query in Oracle with examples.

Automatic Tuning of Undo Retention in Oracle

Oracle database automatically tunes the undo retention period based on how the undo tablespace is configured. If the undo tablespace is configured with the AUTOEXTEND option, the database dynamically tunes the undo retention period to be somewhat longer than the longest-running active query on the system. If we set the undo_retention to 5 seconds, and the query running is 15 seconds, then the Oracle will the undo retention to the longest query on the system. However, this retention period may be insufficient to accommodate Oracle Flashback operations.

Oracle Flashback operations resulting in snapshot too old errors indicate that you must intervene to ensure that sufficient undo data is retained to support these operations. To better accommodate the Oracle flashback features, you can either set the UNDO_RETENTION parameter to a value equal to the longest expected Oracle flashback operation or change the undo tablespace to a fixed size.

Note: If the undo tablespace is fixed size, Oracle ignores the value of undo_Retention unless you enable a retention guarantee. The database dynamically tunes the undo retention period for the best possible retention for that tablespace size and the current system load. This best possible retention time is typically significantly greater than the duration of the longest-running active query.

If you decide to change the undo tablespace to a fixed size, you must choose a sufficiently large tablespace size. If you choose an undo tablespace size that is too small, the following error could occur:

• DML could fail because there is insufficient space to accommodate undo for new transactions.
• Long-running queries could fail with a snapshot too old error, meaning there was insufficient undo data for read consistency.

We will try to perform an exercise and then learn more about automatic undo retention. If the undo tablespace is configured with the AUTOEXTEND option, the database dynamically tunes the undo retention period to be somewhat longer than the longest-running active query on the system. If we set the undo_retention to 5 seconds, and the query running is 15 seconds, then the Oracle will the undo retention to the longest query on the system. In this exercise, we will be using two sessions.

Step 1: Connect to the pluggable database

Connect to the pluggable database

Command: show con_name

Step 2: Check the parameter undo and the tablespaces

Statement: show parameter undo

The undo_management is AUTO, and the undo_retention is mentioned as 900, which means uncommitted data will be sorted for 900 seconds. Here, the Database used UNDOTBS1 as the default undo tablespace.

Note: Active undo will never be replaced by Oracle. If we make the update for 10000 rows and forget to commit the data, then the data in the undo segment will never be replaced. It will stay in the undo segment as long as you commit the data. Let’s check the tablescapes.

Step 2: Check the tablespaces

Select * from dba_Tablespaces;

We can see all the tablespaces and the undo tablespace. The retention of the UNDO tablespace is NOGUARANTEE. Oracle will not look for the values in the undo retention. According to the space in the tablespace, Oracle might look for a period of more than 900 seconds or less than 900 seconds. Let’s now check the metadata for the tablespace UNDO.

Statement: SELECT DBMS_METADATA.GET_DDL(‘TABLESPACE’,’UNDOTBS1′) FROM dual;

Here, we can see the metadata for the tablespace UNDOTBS1. We can see that the data file is auto-extended. So, Oracle will adjust the undo_retention automatically for active queries. If a query needs to run for 20 minutes, then Oracle will adjust the undo_retention for that query accordingly. But for the flashback operations, it may or may not work. Let’s try to set the undo_retention to 1.

Statement: alter system set undo_retention=1;

So, we have updated the undo_retetnion to 1 sec. This means that Oracle will unexpired data and undo data for one second. But this is not logical if we put the value as 1. If the query requires 5 minutes, Oracle will not handle the undo_retention. To test this, let’s create a new table with two columns, id and value.

create table xyz
(id number primary key, val number);

So, the table is now created. Let’s try inserting 500000 records into it using the for loop.

begin
for i in 1..500000
loop
insert into xyz values (i,500 );
end loop;
commit;
end;

So, 500000 records were inserted successfully. Now, let’s check the values of the table xyz.

Statement: select * from xyz order by 1;

So, we can see the records are present in table XYZ. The column ID will start at one and end at 500000. Let’s try to update the table using an update statement without the commit option.

update xyz
set val=0
where id =500000;

So, the table xyz is updated for the ID 500000, and the value is 0. Let’s create a new session and connect to the same user. Here, we will create another table called xyz_copy. In this table, we will add all the data to the table xyz_copy.

create table xyz_copy
(id number primary key, val number );

So, the table XYZ_COPY has been created. The table xyz_copy will have the data from the xyz table. The value of the table will still be 500 because the user didn’t commit yet.

begin
for i in (select * from xyz)
loop
insert into xyz_copy values (i.id, i.val );
end loop;
commit;
end;

Here, we mentioned select * from the XYZ table in a loop and inserting all the values from the table XYZ into the new table xyz_copy. Let’s execute the code.

When the code is executed, open the first session and make the commit transaction. As the new records are inserted, the old value will be stored in the table xyz_copy. Let’s now check the value of xyz_copy for the ID 500000.

Statement: select * from xyz_copy where id=500000;

We can see the value of the ID 500000 is still 500. Let’s now try to check value of xyz table. This session has an active query or DML/ While the code is executed, the first session commits the data so that the committed data will be as unexpired undo. We said before that unexpired undo is used by active query or flashback. Oracle is supposed to keep the unexpired undo for 1 second. However, Oracle will not listen to the value because Oracle needs to guarantee read consistency. Now, let’s check the flashback query for the table XYZ.

select versions_starttime,versions_endtime, val from xyz
versions between scn minvalue and maxvalue
where id=500000;

Oracle will adjust the undo retention when the tablespace is auto-extended and will adjust the value according to the query. In this article, I explain the automatic tuning of undo retention in Oracle with examples. I hope you enjoy this article on Automatic Tuning of Undo Retention in Oracle.