For read consistency oracle uses undo tablespace. In this post I will mention auto tuned undo management for a 184.108.40.206 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;
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.
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.
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 ;
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;
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;