| UNDO Setup Issue [message #616835] |
Sat, 21 June 2014 05:28  |
 |
VIP2013
Messages: 91
Registered: June 2013
|
Member |
|
|
Hi, i am using Release 11.2.0.3.0 version of oracle. its 2 node RAC
I got below error in my log,
Error:
-------
SQL state [72000]; error code [1555]; ORA-01555: snapshot too old: rollback segment number '' with name "_SYSS..." too small ;
i can see that the query which was running and it was long running one. but i just want to make sure that the UNDO_RETENTION and UNDO tablespace size is set properly. I have below setup
NAME VALUE
--------------------------------------
undo_management AUTO
undo_retention 1800
UNDO tablespaces details all ONLINE:
--------------------------------
tablespace_name Allocated_Space(IN GB) used_space
----------------------------------------------------------------
UNDOTBS4 7.81 122 MB
UNDOTBS2 95.55 2.4 GB
UNDOTBS3 47.81 94 MB
UNDOTBS1 87.27 1.4 GB
but i can see from gv$parameter for the name undo_tablespace i have UNDOTBS1 for inst_id -1 and UNDOTBS2 for inst_id - 2. So perhaps these are the ones actually used. Also there exists one of the datafiles belongs to each 4 undo tablespaces having AUTOEXTEND ON others fix size. And retention for alll the tablespaces are 'NOGUARANTEE'.
I got to know, below calculation for setting up the retention period
UNDO SIZE = UNDO RETENTION * DB_BLOCK_SIZE * UNDO_BLOCK_PER_SEC
DB_BLOCK_SIZE= 8192 Bytes
for calculating UNDO_BLOCK_PER_SEC
SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"
FROM gv$undostat
-------
283.4
but checking same from DBA_HIST_UNDOSTAT gives below value
select MAX(undoblks/((end_time-begin_time)*3600*24)) from DBA_HIST_UNDOSTAT
------------
1143.3
so in any of the case i think the undo_retention seems to be set as very less so as per the calculation it should be
UNDO RETENTION =(UNDO_SIZE in GB)*1024*1024*1024/(DB_BLOCK_SIZE * UNDO_BLOCK_PER_SEC)
= (7.81+95.55+47.81+87.27)*1024*1024*1024/(1143*8192) = ~27342 which is very much high than current set value.
so should we set the UNDO retention value to ~27342 sec that is 7.5 hr or is there some other thing which needs to be taken care in the anlysis?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: UNDO Setup Issue [message #616921 is a reply to message #616907] |
Mon, 23 June 2014 00:55  |
John Watson
Messages: 8966
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
Why do you not simply raise your undo_retention to 30000? This will do no harm, it might help, and will take you 5 seconds to do.
|
|
|
|
| Re: UNDO Setup Issue [message #616946 is a reply to message #616921] |
Mon, 23 June 2014 10:03 |
|
VIP2013
Messages: 91
Registered: June 2013
|
Member |
|
|
Thanks John.
just for understanding, If i have ~80GB+ of UNDO space alloted with above mentioned setup(retention ~30 minutes) .
Update started on a table tab1 and committed too
Now after 45 minutes i.e EXPIRED UNDO, so will oracle retain these UNDO for tab1 till the total 80GB space is fully used and then it will overwrite( so it will can use those extents for other active transactions)?
or
it will overwrite(use those extents) for other active transactions at anypoint(after 30 minutes) without waiting for the UNDO tablespace to gets filled up?
Just checked below query ,total_usage always <15GB, and ~80% is always expiredblk, ~20% UNEXPIREDBLKS and ~2% ACTIVEBLKS
select TO_CHAR (begin_time, 'DD-MON-RR HH24:MI') bet,
(sum(ACTIVEBLKS+UNEXPIREDBLKS+expiredblks)*8192)/1024/1024/1024 total_usage,
from dba_hist_undostat
group by TO_CHAR (begin_time, 'DD-MON-RR HH24:MI')
order by TO_CHAR (begin_time, 'DD-MON-RR HH24:MI');
[Updated on: Mon, 23 June 2014 10:04] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
