Troubleshooting Issues with Undo Tablespace

Monday, 11 June 2018

Troubleshooting Issues with Undo Tablespace

Commonly seen problems with the undo tablespace are of the following nature:

These errors can be caused by many different issues, such as incorrect sizing of the undo tablespace or poorly written SQL or PL/SQL code.

• ORA-01555: snapshot too old

• ORA-30036: unable to extend segment by ... in undo tablespace 'UNDO1'

Causes :

Frequent commits can be the cause of ORA-1555. It's all about read consistency. The time you start a query oracle records a before image. So the result of your query is not altered by DML that takes place in the meantime (your big transaction). The before image uses the rollback segments to get the values of data that is changed after the before image is taken. By committing in your big transaction you tell oracle the rollback data of that transaction can be overwritten. If your query need data from the rollback segments that is overwritten you get this error. The less you commit the less chance you have that the rollback data you need is overwritten. Typically this occurs when users are executing the PL/SQL procedures and code commits inside a cursor.

Actions :

1. Check if Undo Is Correctly Sized:

The below query checks for issues that have occurred within the last day :

select to_char(begin_time,'MM-DD-YYYY HH24:MI') begin_time
,ssolderrcnt ORA_01555_cnt, nospaceerrcnt no_space_cnt
,txncount max_num_txns, maxquerylen max_query_len
,expiredblks blck_in_expired
from v$undostat where begin_time > sysdate - 1 order by begin_time;

Output :

BEGIN_TIME ORA_01555_CNT NO_SPACE_CNT MAX_NUM_TXNS BLCK_IN_EXPIRED

---------------- ------------- ------------ ------------ ---------------

06-10-2018 14:52 0 0 42 0

02-10-2018 07:24 0 0 0 0

If this column reports a non-zero value, you need to do one or more of the following tasks:

The most effective way is to “Increase the UNDO_RETENTION initialization parameter”.

2. Below are the resolutions that can be taken hence forth

• Commit less often, commit at the end only

• Ensure that code does not contain COMMIT statements within cursor loops.

• Re-schedule long-running queries when the system has less DML load or Off-peak hours

• Check the SQL’s that are consuming more undo and try to tune the SQL statement throwing the errors.

• Finally, you may proceed to add extra rollback segments (undo logs) to make more transaction slots available.

NOTE : A maximum of 4 days’ worth of information is stored in the V$UNDOSTAT view. The statistics are gathered every 10 minutes, for a maximum of 576 rows in the table. If you’ve stopped and started your database within the last 4 days, this view will only contain information from the time you last started your database.

The following query displays the current undo size and the recommended size for an undo tablespace with recommended retention in seconds:

select sum(bytes)/1024/1024 cur_mb_size,
dbms_undo_adv.required_undo_size(900) req_mb_size
from dba_data_files
where tablespace_name = (select
value from v$parameter where name = 'undo tablespace');

Output:

CUR_MB_SIZE REQ_MB_SIZE

----------- -----------

51200 35840

The output shows that the undo tablespace currently has size of 50GB allocated to it.

In the prior query, you used 900 seconds as the amount of time to retain information in the undo tablespace. To retain undo information for 900 seconds, the Oracle Undo Advisor estimates that the undo tablespace should be around 35G . In this example the undo tablespace is sized adequately. If it were not sized adequately, you would have to either add space to an existing data file or add a data file to the undo tablespace.

Here is perfect query to get the Current undo retention and optimal undo retention from site Akadia

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'
/

Output :

ACTUAL UNDO SIZE [MByte]

------------------------

51200

UNDO RETENTION [Sec]

--------------------

10800

OPTIMAL UNDO RETENTION [Sec]

----------------------------

14580

Find the sessions using view - v$session and v$transaction to get sessions consuming UNDO Segments :

select s.sid, s.serial#, s.osuser, s.logon_time ,s.status, s.machine
,t.used_ublk, t.used_ublk*16384/1024/1024 undo_usage_mb
from v$session s ,v$transaction t where t.addr = s.taddr;

You can use below query using view – v$SQL to get SQL statement associated with a user/session consuming undo space.

select s.sid, s.serial#, s.osuser, s.logon_time, s.status ,s.machine, t.used_ublk ,
t.used_ublk*16384/1024/1024 undo_usage_mb ,q.sql_text from v$session s,
v$transaction t ,v$sql q where t.addr = s.taddr and s.sql_id = q.sql_id;