Home » RDBMS Server » Performance Tuning » UNDO Setup Issue
UNDO Setup Issue [message #616835] Sat, 21 June 2014 05:28 Go to next message
VIP2013
Messages: 84
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 #616844 is a reply to message #616835] Sat, 21 June 2014 08:00 Go to previous messageGo to next message
BlackSwan
Messages: 22837
Registered: January 2009
Senior Member
http://www.orafaq.com/node/1888
Re: UNDO Setup Issue [message #616892 is a reply to message #616844] Sun, 22 June 2014 08:02 Go to previous messageGo to next message
VIP2013
Messages: 84
Registered: June 2013
Member
Thanks.

In my case i verified from DBA_ALERT_HISTORY, the exact sql_id which got errored out and the tablespace was UNDOTBS1. Also verified that the sql which errored out was normally runs more than an hour and also does FTS on some big tables. Then i verified the tablespace usage trend for the UNDOTBS1, but i saw the max usage was ~6GB that too in a different day, and the size allocated for UNDOTBS1 is lot more than that(its ~90GB+) so i am just wondering , how this query got errored out even if space was available that time.

I find from below oracle doc, it says, with undo management AUTO , "If the undo tablespace is configured with the AUTOEXTEND option, the database dynamically tunes the undo retention period to be somewhat longer than the longest-running active query on the system."

http://docs.oracle.com/cd/E16655_01/server.121/e17633/logical.htm#CNCPT1080

oracle should take care of same as my setup is Auto extend on in tablespace level+undo management auto...
Re: UNDO Setup Issue [message #616894 is a reply to message #616892] Sun, 22 June 2014 08:54 Go to previous messageGo to next message
BlackSwan
Messages: 22837
Registered: January 2009
Senior Member
>also does FTS on some big tables
FTS (SELECT) does not generate UNDO.

in some, many, most cases of ORA-01555 error the session reporting this error is the victim; not the culprit.
The culprit is usually a different session that does DML against same table as the SELECT & does "frequent" COMMIT.
The usual solution is to do fewer (only 1) COMMIT or do less DML while the SELECT is running.
Re: UNDO Setup Issue [message #616907 is a reply to message #616894] Sun, 22 June 2014 13:46 Go to previous messageGo to next message
VIP2013
Messages: 84
Registered: June 2013
Member
Thanks. Yes i will take care of the DML/COMMIT during running heavy SELECTS.

But is the undo_retention is optimum in this case? Till how much time would it be guaranteed to store the committed undos in my DB considering above setup? I am suspecting the small value of UNDO_RETENTION is the culprit somehow,considering the space available in the undo tablespace and its AUTOEXTEND ON option and AUTO undo management. dont you?

Re: UNDO Setup Issue [message #616909 is a reply to message #616907] Sun, 22 June 2014 15:03 Go to previous messageGo to next message
BlackSwan
Messages: 22837
Registered: January 2009
Senior Member
>dont you?
no.

>Yes i will take care of the DML/COMMIT during running heavy SELECTS.
after you do so, then no more ORA-01555 will be thrown.
Re: UNDO Setup Issue [message #616921 is a reply to message #616907] Mon, 23 June 2014 00:55 Go to previous messageGo to next message
John Watson
Messages: 4609
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 Go to previous messageGo to next message
VIP2013
Messages: 84
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

Re: UNDO Setup Issue [message #616949 is a reply to message #616946] Mon, 23 June 2014 10:17 Go to previous messageGo to next message
BlackSwan
Messages: 22837
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/
Re: UNDO Setup Issue [message #617397 is a reply to message #616949] Sun, 29 June 2014 04:32 Go to previous message
Flyby
Messages: 144
Registered: March 2011
Location: Belgium
Senior Member
you can specifiy "UNDO RETENTION GUARANTEE"
Previous Topic: Debug Procedure
Next Topic: Table Shrink Space and Performance
Goto Forum:
  


Current Time: Tue Sep 30 01:12:33 CDT 2014

Total time taken to generate the page: 0.08940 seconds