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;
"Do something (anything). If you
don't do anything, you won't get anywhere.
Make it your hobby, not a chore, but above
all have fun!" 😊
