Oracle

Undo Remains Unexpired

problems23

For read consistency oracle uses undo tablespace. In this post I will mention auto tuned undo management for a 11.2.0.4 database. We had space issues on our undo tablespaces. The extents remained unexpired and not recyled back to unexpired extends or free space. When we hit this space pressure point we had row cache waits. The quick solution was adding more space which was not much pretty solution for us. I will give our solution at the end first a little bit warm up.

 select count(*) , trunc(sum(bytes)/1024/1024) MB, status, tablespace_name
from dba_undo_extents
group by status, tablespace_name order by status, tablespace_name; 
COUNT(*) MB STATUS TABLESPACE_NAME
31 211 ACTIVE UNDOTBS1
22 167 ACTIVE UNDOTBS2
1653 6989 EXPIRED UNDOTBS1
2518 8536 EXPIRED UNDOTBS2
7887 36975 UNEXPIRED UNDOTBS1
6038 25260 UNEXPIRED UNDOTBS2

What is Active Extend ? 

This extend is used by an acitve transaciton. A transaction what is still not committed or rolledback. This Extend is required for rolling back the transaction if needed.

What is Unexpired Extend ? 

After a transaction is commited. The previous copies of the changed blocks are stored in these extends inside undo tablespace. These are required for sometime If any other transaction needs to see the previous copies. They are expired after sometime. For expiration period, UNDO_RETENTION may play a factor,  but in auto tuned undo you need to check the V$UNDOSTAT.TUNED_UNDORETENTION  column. Oracle says “hey boss I have plenty of space, I can retain as much as possible so that no one gets a ORA-1555  🙂 ”

What is Expired Extend ? 

These are extends are used by transactions that have exceed the UNDO_RETENTION time to be more precise it is the V$UNDOSTAT.TUNED_UNDORETENTION  column.
Our Problem
When we check the tuned undo retainn in v$undostat it was something like 200 thousand seconds which is more than 2 days and it was not required for us. The extends did not expire.

we found a support document “Undo Remains Unexpired When Using Non-autoextensible Datafiles For The Undo Tablespace (Doc ID 1112431.1)

It gives 4 options
1) Disable automatic tuning of undo by setting _undo_autotune=false.
Not a big fan of underscore parameters. we eliminated this option. Tested before and had bad experiences.

2) Turn on autoextensibility of the undo tablespace datafiles.
We don’t want unto to grow exponantially.

3) Allocate more space to the undo tablespace and/or reduce the threshold level used for computation of the tuned undo retention value….
The first part is a definete NO,  I will show how the second part solved our problem

4) set the _first_spare_parameter (10.2) or _highthreshold_undoretention (11.x) instance parameter to a value limiting the tuned undo retention value. See Note 742035.1 for more details….
Still Not a fan of underscore parameters so  we eliminated this option.

SOLUTION HISTORY

To monitor the datbase we were using EM12c, on the monitoring screen the thresholds were entered very low values. We never thought that would affect the auto-tune configuration. We were wrong. After we set a high value for the threshold as described in the Support Document, undo tablespace free is increased but not as much as we expected.

(do it for all undo tablespaces )

 begin
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id = dbms_server_alert.tablespace_pct_full,
warning_operator = dbms_server_alert.operator_ge,
warning_value = '50', 
critical_operator = dbms_server_alert.operator_ge,
critical_value = '60',
observation_period = 1,
consecutive_occurrences = 1,
instance_name = NULL,
object_type = dbms_server_alert.object_type_tablespace,
object_name = 'UNDOTBS1');
end;
/ 

The above setting advised by the support document helped us but the big diffence came after setting a a threshold value for “Tablespace Bytes Free” then we got excellent free space.

begin
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id = dbms_server_alert.TABLESPACE_BYT_FREE,
warning_operator = dbms_server_alert.operator_le,
warning_value = '20000000', 
critical_operator = dbms_server_alert.operator_le,
critical_value = '10000000',
observation_period = 1,
consecutive_occurrences = 1,
instance_name = NULL,
object_type = dbms_server_alert.object_type_tablespace,
object_name = 'UNDOTBS3');
end;
/

Just Afer we set the “Tablespace Bytes Free” for the undo tablespace.

SELECT ACTIVEBLKS,UNEXPIREDBLKS,EXPIREDBLKS, TUNED_UNDORETENTION
FROM V$UNDOSTAT ;
ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
115952 12729184 17610552 82010
141424 12711392 18083384 82023
92272 12821728 18371128 81984 <<<<<<<
159856 19033912 12045280 291425
99440 16951608 14109152 291988
93424 16866488 14100960 277414
130160 16763576 14110816 277990
217200 16630712 14113632 278436

How to check the thresholds on the undo tablespaces and  the Fınal configuration for the threshold values.

SELECT object_name AS tablespace_name,
metrics_name,
warning_operator,
warning_value,
critical_operator,
critical_value
FROM dba_thresholds
WHERE object_type = 'TABLESPACE' and object_name like 'UNDO%'
ORDER BY object_name;
TABLESPACE_NAME METRICS_NAME WARNING
OPERATOR
WARNING
VALUE
CRITICAL
OPERATOR
CRITICAL
VALUE
UNDOTBS1 Tablespace Bytes
Space Usage
LE 20000000 LE 10000000
UNDOTBS1 Tablespace GE 50 GE 60
UNDOTBS2 Tablespace Bytes
Space Usage
LE 20000000 LE 10000000
UNDOTBS2 Tablespace GE 50 GE 60

More usefull undo scripts

How to check free space :

select tablespace_name, trunc(sum(bytes) / 1024 / 1024) free_mb
from dba_free_space where tablespace_name like 'UNDO%'
group by tablespace_name order by 1 ;

To find long running transaction which will also acffect the undo retention time :

SELECT s.username username,
s.module module,
s.machine machine,
s.inst_id inst_id,
s.sid sid,
s.serial# serial,
t.start_date start_date,
TRUNC ( (SYSDATE - t.start_date) * 24 * 60) run_duration
FROM gv$transaction t, gv$session s
WHERE t.inst_id = s.inst_id
AND t.ses_addr = s.saddr
ORDER BY t.start_time;
Advertisements

4 thoughts on “Undo Remains Unexpired

  1. It is a very inspring post. Thanks a lot.
    I have a question which can be related to the topic.
    Our total expired undo block size is about 100GB.
    However the free size of the undo tablespace is seen as 5GB, which is far less than the total expired undo size.
    What is the possible reason of the huge difference between these values? Any idea would be appriacated. Thank you again.

    1. It should not be a problem. Oracle will steal automatically from expired blocks first. The key point is to have enough expired blocks. If it is not enough it will steal from unexpired blocks, which may lead long running queries to face with “ORA-01555 Snapshot Too Old” error. If your system is under heavy transaction this stealing process may lead the other sessions to wait on “enq: US – contention” or “row cache lock”.

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