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  |
 |
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 #582750 is a reply to message #582748] |
Mon, 22 April 2013 07:05  |
 |
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 |
 |
Michel Cadot
Messages: 68641
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 #582772 is a reply to message #582771] |
Mon, 22 April 2013 08:53 |
|
Michel Cadot
Messages: 68641
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 #582814 is a reply to message #582783] |
Mon, 22 April 2013 19:28 |
 |
Kevin Meade
Messages: 2103
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
|
|
|
|
Goto Forum:
Current Time: Sat Apr 20 02:45:47 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
