Home » RDBMS Server » Performance Tuning » Which is correct solution for ORA-01555 - Increase Undo size or Undo retention? (Oracle 10.2.0.3.0 on AIX)
Which is correct solution for ORA-01555 - Increase Undo size or Undo retention? [message #582748] Mon, 22 April 2013 06:49 Go to next message
sysdba007
Messages: 28
Registered: November 2012
Junior Member
Hello

I have got the following error yesterday

		  
ORA-01555 caused by SQL statement below (SQL ID: fdxcyoin67ty8t, Query Duration=380128 sec, SCN: 0x0229.ff00afd0):

following are the existing settings

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     96000
undo_tablespace                      string          undo


SQL> select RETENTION from dba_tablespaces where TABLESPACE_NAME='UNDO';

RETENTION
-----------
NOGUARANTEE

SQL> select distinct AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME like 'UNDO%';

AUT
---
NO

SQL> select sum(bytes)/1024/1024  "size_in_mb" from dba_data_files where tablespace_name='UNDO';

SIZE_IN_MB
--------------------
  216511


following are the details from v$undostat

select begin_time, end_time, undotsn, undoblks, maxquerylen, maxqueryid, activeblks, unexpiredblks, expiredblks, tuned_undoretention from v$undostat 
where trunc(begin_time)=trunc(sysdate)-1 order by begin_time;

BEGIN_TIME     END_TIME          UNDOTSN   UNDOBLKS MAXQUERYLEN MAXQUERYID    ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
-------------- -------------- ---------- ---------- ----------- ------------- ---------- ------------- ----------- -------------------
21-04-13 00:08 21-04-13 00:18          1      12733      378446 duqnawh32hp4u      91152       7068448      225440              345600
21-04-13 00:18 21-04-13 00:28          1       8951      379047 duqnawh32hp4u      99344       7072800      225440              345600
21-04-13 00:28 21-04-13 00:38          1      14073      379650 duqnawh32hp4u      90128       7075872      234656              345600
21-04-13 00:38 21-04-13 00:48          1       6109        2651 2hgnkv6xxncmq      90128       6919328      399520              345600
21-04-13 00:48 21-04-13 00:58          1      10088        3253 2hgnkv6xxncmq      89104       6768928      525472              345600
21-04-13 00:58 21-04-13 01:08          1      69593        3854 2hgnkv6xxncmq      90128       6664736      564384              345600
21-04-13 01:08 21-04-13 01:18          1     163304        4457 2hgnkv6xxncmq     122896       6753824      541856              345600
21-04-13 01:18 21-04-13 01:28          1     172593        5058 2hgnkv6xxncmq     121872       6909472      482464              345600
21-04-13 01:28 21-04-13 01:38          1     171075        5660 2hgnkv6xxncmq     119824       7080480      480416              345600
21-04-13 01:38 21-04-13 01:48          1      84882        6262 2hgnkv6xxncmq      98320       7270048      478368              345600
21-04-13 01:48 21-04-13 01:58          1       1622        6863 2hgnkv6xxncmq      72720       7330464      476320              345600
21-04-13 01:58 21-04-13 02:08          1       2075        6335 2hgnkv6xxncmq      64528       7337632      477344              345600
21-04-13 02:08 21-04-13 02:18          1       1491         236 a4mhnydhzsv0y      64528       7336608      478368              345600
21-04-13 02:18 21-04-13 02:28          1        884         795 62yh0pbb4p9f2      63504       7325472      490656              345600



Following are the details in AWR report (00:00 til 01:00 of 21-Apr-2013) .... not thet the error was produced at 00:42

Undo Segment Summary                DB/Inst: DBCPY/dbcpy01  Snaps: 18853-18854
-> Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
-> STO - Snapshot Too Old count,  OOS - Out of Space count
-> Undo segment block stats:
-> uS - unexpired Stolen,   your - unexpired Released,   uU - unexpired reUsed
-> eS - expired   Stolen,   eR - expired   Released,   eU - expired   reUsed

Undo   Num Undo       Number of  Max Qry   Max Tx Min/Max   STO/     uS/uR/uU/
 TS# Blocks (K)    Transactions  Len (s) Concurcy TR (mins) OOS      eS/eR/eU
---- ---------- --------------- -------- -------- --------- ----- --------------
   1      156.1          41,962  379,650       38 5760/5760 1/0   0/0/0/0/0/0
          -------------------------------------------------------------

		  Undo Segment Stats                   DB/Inst: DBCPY/dbcpy01  Snaps: 18853-18854
-> Most recent 35 Undostat rows, ordered by Time desc

                Num Undo    Number of Max Qry  Max Tx Tun Ret STO/    uS/uR/uU/
End Time          Blocks Transactions Len (s)   Concy  (mins) OOS     eS/eR/eU
------------ ----------- ------------ ------- ------- ------- ----- ------------
21-Apr 01:08      69,593        4,871   3,854      37   5,760 0/0   0/0/0/0/0/0
21-Apr 00:58      10,088        7,261   3,253      37   5,760 0/0   0/0/0/0/0/0
21-Apr 00:48       6,109        4,110   2,651      35   5,760 1/0   0/0/0/0/0/0
21-Apr 00:38      14,073        4,168 #######      36   5,760 0/0   0/0/0/0/0/0
21-Apr 00:28       8,951        4,031 #######      36   5,760 0/0   0/0/0/0/0/0
21-Apr 00:18      12,733        5,979 #######      36   5,760 0/0   0/0/0/0/0/0
21-Apr 00:08      34,602       11,542 #######      38   5,760 0/0   0/0/0/0/0/0
          -------------------------------------------------------------
		  


Undo Advisor information taken 'now' is as following

SQL> select dbms_undo_adv.longest_query(sysdate-2,sysdate) from dual;

DBMS_UNDO_ADV.LONGEST_QUERY(SYSDATE-2,SYSDATE)
----------------------------------------------
                                        379650

SQL> select dbms_undo_adv.required_retention from dual;

REQUIRED_RETENTION
------------------
            379650

SQL> select dbms_undo_adv.best_possible_retention from dual;

BEST_POSSIBLE_RETENTION
-----------------------
                1169169

SQL> select dbms_undo_adv.required_undo_size(379650) from dual;

DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(379650)
----------------------------------------
                                   80655

SQL> select dbms_undo_adv.required_undo_size(379650,sysdate-3,sysdate) from dual;

DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(379650,SYSDATE-3,SYSDATE)
----------------------------------------------------------
                                                     56028



In above situation what should be my first choice (assuming increasing space is not an issue) - increase undo tablespace or increase undo retention?

If latter is the choice then what should be the value?
Because as I understand present 96000 value is taken as lower limit and because of auto tuning the actual value (TUNED_UNDORETENTION) being used was 345600
In that case shall I set it to something > max(maxquerylen) i.e 379,650 + X?
Or I shall increase the undo tablespace size?

From Undo Advisor output it looks to me that even if I increase the undo retention to 379650 current undo size will be able to support it (may be at the expense of DMLs)
Is that right?

Thanks in advance
sysdba007
Re: Which is correct solution for ORA-01555 - Increase Undo size or Undo retention? [message #582749 is a reply to message #582748] Mon, 22 April 2013 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 58643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The first point is to investigate that no one is committing inside a loop.

Regards
Michel
Re: Which is correct solution for ORA-01555 - Increase Undo size or Undo retention? [message #582750 is a reply to message #582748] Mon, 22 April 2013 07:05 Go to previous messageGo to next message
nlnkapardi
Messages: 26
Registered: April 2013
Location: India
Junior Member

Hello Sysdba

General calculation :-

TUNED_UNDORETENTION = MAXQUERYLEN + 300 Sec.

If UNDO_RETENTION is set > 0, the value specified acts as a minimum setting.

If UNDO_MANAGEMENT=AUTO automatic undo tuning will be used.
MMON will calculate the MAXQUERYLEN every 30 sec interval. Based upon the MAXQUERYLEN, MMON decides the
TUNED_UNDORETENTION. That means the new UNDO RETENTION will be set to TUNED_UNDORETENTION.

For optimal undo management, rather than
tuning based on 100% of the tablespace size, the database tunes the undo retention period based
on 85% of the tablespace size.

Automatic tuning will help to avoid ORA-01555, but if your UNDO tablespace has autoextend off, then you might get into a situation
where active DML needs more space--not reusing expired UNDO segments. The database will be under space pressure and Oracle will give
higher priority to finishing the DML, and not queries.

So Better set Auto

Regards,

Kapardi




[Updated on: Mon, 22 April 2013 07:06]

Report message to a moderator

Re: Which is correct solution for ORA-01555 - Increase Undo size or Undo retention? [message #582751 is a reply to message #582750] Mon, 22 April 2013 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 58643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
That means the new UNDO RETENTION will be set to TUNED_UNDORETENTION.


In 10g?

Quote:
Automatic tuning will help to avoid ORA-01555,


Not if the error is in the application side, only if it is a wrong sizing.

Quote:
then you might get into a situation where active DML needs more space--not reusing expired UNDO segments. The database will be under space pressure and Oracle will give higher priority to finishing the DML, and not queries.


Where do you get that? There is no priority at all in the database engine, none.
And DML will reuse expired UNDO (unless the undo retention is guaranteed which is not the case here), taking expired in the meaning not necessary to rollback some DML and not in the meaning of older than the retention, in this latter case it is always reused if needed.
In addition, queries has nothing to do with pressure on the undo tablespace. Only DML writes into it.

Regards
Michel
Re: Which is correct solution for ORA-01555 - Increase Undo size or Undo retention? [message #582752 is a reply to message #582749] Mon, 22 April 2013 07:18 Go to previous messageGo to next message
sysdba007
Messages: 28
Registered: November 2012
Junior Member
Hello Michel

Thanks for your reply

We have already communicated to the application team on the following :
reduce number of commit, processing against a range of data rather than the whole table, fetching across commits, checking outer select does not revisit the same block at different times during the processing

In this case if the error (ora-01555) is produced despite above precaustions and code changes are not possible then in the following situation which option shall be implemented and to what value?

Thanks and Regards
sysdba007
Re: Which is correct solution for ORA-01555 - Increase Undo size or Undo retention? [message #582753 is a reply to message #582752] Mon, 22 April 2013 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 58643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unfortunately there is little you can do against commits in a loop. Commit says to Oracle I no more care about the undo, you can reuse the space.
So, change your undo to GUARANTEE, set UNDO_RETENTION to the longest batch you have and size your tablespace for this time (accordingly to the advisor).

Regards
Michel
Re: Which is correct solution for ORA-01555 - Increase Undo size or Undo retention? [message #582754 is a reply to message #582748] Mon, 22 April 2013 07:27 Go to previous messageGo to next message
sysdba007
Messages: 28
Registered: November 2012
Junior Member
Hello Michel

Quote:

change your undo to GUARANTEE, set UNDO_RETENTION to the longest batch you have and size your tablespace for this time (accordingly to the advisor).


From the information shared below do you think tablespace size will need to be increased alongwith undo_retention?

sorry I am confused and that's why confirming it again!

Thanks and Regards
sysdba007
Re: Which is correct solution for ORA-01555 - Increase Undo size or Undo retention? [message #582756 is a reply to message #582754] Mon, 22 April 2013 07:38 Go to previous messageGo to next message
Michel Cadot
Messages: 58643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to increase undo_retention to at least 379650; the undo tablespace size seems correct but the most important point is you have to recreate it (or a new one) with GUARANTEE option.

Regards
Michel
Re: Which is correct solution for ORA-01555 - Increase Undo size or Undo retention? [message #582771 is a reply to message #582748] Mon, 22 April 2013 08:46 Go to previous messageGo to next message
sysdba007
Messages: 28
Registered: November 2012
Junior Member
Hello Michel

While max(maxquerlen) of v$undostat is 379650 and the query which caused ora-01555 might execute again, shall I set undo_retention to little higher than 379650?

The query if would have executed 'might' have taken more than 379650 seconds; right?

Regarding the 'GURANTEE' option, as of now I am holding back on this change. It will cause a noticeable change to DMLs (may be undesirable), isn't it?

Thanks and Regards
sysdba007
Re: Which is correct solution for ORA-01555 - Increase Undo size or Undo retention? [message #582772 is a reply to message #582771] Mon, 22 April 2013 08:53 Go to previous messageGo to next message
Michel Cadot
Messages: 58643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The query if would have executed 'might' have taken more than 379650 seconds; right?


Right.

Quote:
Regarding the 'GURANTEE' option, as of now I am holding back on this change. It will cause a noticeable change to DMLs (may be undesirable), isn't it?


No, it will just need your undo tablespace to be bigger.
Note: this is MANDATORY for you to prevent your ORA-01555 error.
You have no choice, you MUST do it (or live with your error).

Regards
Michel

[Updated on: Mon, 22 April 2013 08:54]

Report message to a moderator

Re: Which is correct solution for ORA-01555 - Increase Undo size or Undo retention? [message #582773 is a reply to message #582771] Mon, 22 April 2013 08:54 Go to previous messageGo to next message
BlackSwan
Messages: 22555
Registered: January 2009
Senior Member
>ORA-01555 caused by SQL statement below (SQL ID: fdxcyoin67ty8t, Query Duration=380128 sec, SCN: 0x0229.ff00afd0):
the noted SQL ID did not CAUSE the ORA-01555 error. SQL ID: fdxcyoin67ty8t was victim of a problem caused else where.

If COMMIT exist within LOOP, the only way to stop this error is to (re)move the COMMIT.
Diddling with other parameters is an exercise in futility.
Re: Which is correct solution for ORA-01555 - Increase Undo size or Undo retention? [message #582782 is a reply to message #582748] Mon, 22 April 2013 09:30 Go to previous messageGo to next message
sysdba007
Messages: 28
Registered: November 2012
Junior Member
Hello Michel and BlackSwan

Thank you for your replies

@BlackSwan
It doesn't look like 'commit' in loop and at the same time the sql_id mentioned seems to be the culprit
(I remember in the past I had to query prev_sql_id to get actual sql creating this issue)

I checked that the sql pertaining to this particular sql_id was executing for 9.5 hours before it failed - single execution


select snap_id,instance_number,session_id,session_serial#,sample_time from dba_hist_active_sess_history where sql_id='fdxcyoin67ty8t' and sample_time>sysdate-2 order by session_id,session_serial#,sample_time
   SNAP_ID INSTANCE_NUMBER SESSION_ID SESSION_SERIAL# SAMPLE_TIME
---------- --------------- ---------- --------------- ---------------------------------------------------------------------------
18845 				1        919           39329 20-APR-13 15.05.43.317
......................................
..........................................
18854              	1        919           39329 21-APR-13 00.42.14.458


select executions_delta,executions_total from dba_hist_sqlstat where snap_id between 18845 and 18854 and  sql_id='fdxcyoin67ty8t';
EXECUTIONS_DELTA EXECUTIONS_TOTAL
---------------- ----------------
               0                1
               0                1
               0                1
               0                1
               0                1
               0                1
               0                1
               0                1
               0                1
               0                1


Thanks and Regards
sysdba007
Re: Which is correct solution for ORA-01555 - Increase Undo size or Undo retention? [message #582783 is a reply to message #582782] Mon, 22 April 2013 09:35 Go to previous messageGo to next message
BlackSwan
Messages: 22555
Registered: January 2009
Senior Member
>It doesn't look like 'commit' in loop and at the same time the sql_id mentioned seems to be the culprit
>(I remember in the past I had to query prev_sql_id to get actual sql creating this issue)
>I checked that the sql pertaining to this particular sql_id was executing for 9.5 hours before it failed - single execution
>select snap_id,instance_number,session_id,session_serial#,sample_time from dba_hist_active_sess_history

Other/ different session WAS doing DML against dba_hist_active_sess_history & doing COMMIT while SELECT above was executing!
Re: Which is correct solution for ORA-01555 - Increase Undo size or Undo retention? [message #582814 is a reply to message #582783] Mon, 22 April 2013 19:28 Go to previous message
Kevin Meade
Messages: 1934
Registered: December 1999
Location: Connecticut USA
Senior Member
As I recall, ora-01555 is SNAPSHOT TOO OLD. Since there seems to be an overwhelming amount of misinformation being posted I figure it is safe for me to add some more. I really don't like it when people make me have to look up error message text.

SNAPSHOT TOO OLD does not require a cursor to be open across commits although this is often part of the problem. It only requires you to run out of undo space (although this can't happen without transactions changing data). The basic issue with SNAPSHOT TOO OLD is that you have a long running query that needs to recreate old versions of rows. By far the most common reason why a query needs to read old versions of rows is because someone is modifying the data that the query needs to read while the query is running.

The first and best way to fix this problem is to get rid of the need for a long running query to read old versions of the data.

Quote:
Patient: Doctor! Doctor! it hurts when I do this!
Doctor: So don't do that.


This is normally done in one of the following ways:

1. tune the query so that it gets done way faster.
2. modify the query so that you can tune the query so that it gets done way faster.
3. move the job that is modifying the data so that data is not being modified while the query is running.
4. move the query so that you are not reading the data while it is being modified.
5. modify the update job so that it updates way less data (you may have a poor update process).
6. add more undo.


Notice the ADD MORE UNDO is the last place you should go. This is typically just delaying the inevitable that comes with bad design. If you choose to add more undo because you are in a hurry for whatever reason (sometimes being in a hurry is legit), then the question becomes... how much should we add? I always tell people that if you are not going to do 1-5 first then double the amount of undo. If that does not work, keep doubling until it does work or until you feel you are adding so much undo that it looks stupid, at which point go back and do one or more of 1-5.

Good luck. Kevin
Previous Topic: Identify performance for a query manually i.e. without STATPACK, AWR, ADDM etc.
Next Topic: Query Tuning -indexing
Goto Forum:
  


Current Time: Fri Aug 01 12:18:24 CDT 2014

Total time taken to generate the page: 0.06769 seconds