ORA-01555 caused by SQL statement below (SQL ID: fdxcyoin67ty8t, Query Duration=380128 sec, SCN: 0x0229.ff00afd0):
Following are the existing settings
Code:
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
Code:
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
Code:
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
Code: [Select all] [Show/ hide]
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?
~~~~~
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
~~~~~~~~~~
_____________________________________________________________________________________________
Data Concurrency and Read Consistency
ROLLBACK or UNDO is the backbone of the READ CONSISTENCY mechanism provided by Oracle. Multi-User Data Concurrency and Read Consistency mechanism make Oracle stand tall in Relational Database Management Systems (RDBMS) world.
Starting in Oracle9i, rollback segments are re-named undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued, at which point it was made available for overlaying.
Best of all, automatic undo management allows the DBA to specify how long undo information should be retained after commit, preventing “snapshot too old” errors on long running queries.
This is done by setting the UNDO_RETENTION parameter. The default is 900 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time.
Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle take care of the rest. Turning on automatic undo management is easy. All you need to do is create an undo tablespace and set UNDO_MANAGEMENT = AUTO.
UNDO_RETENTION is a parameter in the init.ora initialization parameters file that specifies the time period in seconds for which a system retains undo data for committed transactions. The flashback query can go upto the point of time specified as a value in the UNDO_RETENTION parameter.
The properties of the UNDO_RETENTION parameter are mentioned below:
Parameter type – Integer
Default value – 900
Range of values – 0 to 232 – 1
Syntax – ALTER SYSTEM SET UNDO_RETENTION = < seconds >;
However it is worth to tune the following important parameters
1. The size of the UNDO tablespace
2. The UNDO_RETENTION parameter
Calculate UNDO_RETENTION for given UNDO Tabespace
You can choose to allocate a specific size for the UNDO tablespace and then set theUNDO_RETENTION parameter to an optimal value according to the UNDO size and the database activity. If your disk space is limited and you do not want to allocate more space than necessary to the UNDO tablespace, this is the way to proceed. The following query will help you to optimize the UNDO_RETENTION parameter:
Optimal Undo Retention =
Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC)
Because these following queries use the V$UNDOSTAT statistics, run the queries only after the database has been running with UNDO for a significant and representative time.
Actual Undo Size
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#;
Undo Blocks per Second
SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"
FROM v$undostat;
DB Block Size
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';
Optimal Undo Retention Calculation
Formula:
Optimal Undo Retention =
Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC)
Using Inline Views, you can do all calculation in one query
SQL Code:
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'
Calculate Needed UNDO Size for given Database Activity
If you are not limited by disk space, then it would be better to choose theUNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:
Formula:
Undo Size = Optimal Undo Retention × DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC
Using Inline Views, you can do all calculation in one query
SQL Code:
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
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'
Automatic Undo Retention Tuning
Oracle 10g automatically tunes undo retention to reduce the chances of “snapshot too old”errors during long-running queries. The UNDO_RETENTION parameter is used to set a low retention time threshold which the system will attempt to achieve. In the event of any undo space constraints the system will prioritize DML operations over undo retention meaning the low threshold may not be achieved. If the undo retention threshold must be guaranteed, even at the expense of DML operations, the RETENTION GUARANTEE clause can be set against the undo tablespace during or after creation:
Reset the undo low threshold.
ALTER SYSTEM SET UNDO_RETENTION = 2400;
Guarantee the minimum threshold is maintained.
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
SELECT tablespace_name, retention FROM dba_tablespaces;
TABLESPACE_NAME RETENTION
------------------------------ -----------
SYSTEM NOT APPLY
UNDOTBS1 GUARANTEE
SYSAUX NOT APPLY
TEMP NOT APPLY
USERS NOT APPLY
5 rows selected.
Switch back to the default mode.
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;
TABLESPACE_NAME RETENTION
------------------------------ -----------
SYSTEM NOT APPLY
UNDOTBS1 NOGUARANTEE
SYSAUX NOT APPLY
TEMP NOT APPLY
USERS NOT APPLY
5 rows selected.
The Undo Advisor PL/SQL Interface
Oracle Database provides an Undo Advisor that provides advice on and helps automate the establishment of your undo environment. You activate the Undo Advisor by creating an undo advisor task through the advisor framework. The following example creates an undo advisor task to evaluate the undo tablespace. The name of the advisor is ‘Undo Advisor‘. The analysis is based on Automatic Workload Repository snapshots, which you must specify by setting parameters START_SNAPSHOT and END_SNAPSHOT. In the following example, the START_SNAPSHOT is “1” and END_SNAPSHOT is “2”.
DECLARE
tid NUMBER;
tname VARCHAR2(30);
oid NUMBER;
BEGIN
DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');
DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, 'null', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 1);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 2);
DBMS_ADVISOR.execute_task(tname);
end;
_____________________________________________________________________________________________
To check the optimum size, please excute this query:
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';
Following are the existing settings
Code:
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
Code:
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
Code:
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
Code: [Select all] [Show/ hide]
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?
~~~~~
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
~~~~~~~~~~
_____________________________________________________________________________________________
Data Concurrency and Read Consistency
ROLLBACK or UNDO is the backbone of the READ CONSISTENCY mechanism provided by Oracle. Multi-User Data Concurrency and Read Consistency mechanism make Oracle stand tall in Relational Database Management Systems (RDBMS) world.
Starting in Oracle9i, rollback segments are re-named undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued, at which point it was made available for overlaying.
Best of all, automatic undo management allows the DBA to specify how long undo information should be retained after commit, preventing “snapshot too old” errors on long running queries.
This is done by setting the UNDO_RETENTION parameter. The default is 900 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time.
Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle take care of the rest. Turning on automatic undo management is easy. All you need to do is create an undo tablespace and set UNDO_MANAGEMENT = AUTO.
UNDO_RETENTION is a parameter in the init.ora initialization parameters file that specifies the time period in seconds for which a system retains undo data for committed transactions. The flashback query can go upto the point of time specified as a value in the UNDO_RETENTION parameter.
The properties of the UNDO_RETENTION parameter are mentioned below:
Parameter type – Integer
Default value – 900
Range of values – 0 to 232 – 1
Syntax – ALTER SYSTEM SET UNDO_RETENTION = < seconds >;
However it is worth to tune the following important parameters
1. The size of the UNDO tablespace
2. The UNDO_RETENTION parameter
Calculate UNDO_RETENTION for given UNDO Tabespace
You can choose to allocate a specific size for the UNDO tablespace and then set theUNDO_RETENTION parameter to an optimal value according to the UNDO size and the database activity. If your disk space is limited and you do not want to allocate more space than necessary to the UNDO tablespace, this is the way to proceed. The following query will help you to optimize the UNDO_RETENTION parameter:
Optimal Undo Retention =
Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC)
Because these following queries use the V$UNDOSTAT statistics, run the queries only after the database has been running with UNDO for a significant and representative time.
Actual Undo Size
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#;
Undo Blocks per Second
SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"
FROM v$undostat;
DB Block Size
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';
Optimal Undo Retention Calculation
Formula:
Optimal Undo Retention =
Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC)
Using Inline Views, you can do all calculation in one query
SQL Code:
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'
Calculate Needed UNDO Size for given Database Activity
If you are not limited by disk space, then it would be better to choose theUNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:
Formula:
Undo Size = Optimal Undo Retention × DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC
Using Inline Views, you can do all calculation in one query
SQL Code:
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
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'
Automatic Undo Retention Tuning
Oracle 10g automatically tunes undo retention to reduce the chances of “snapshot too old”errors during long-running queries. The UNDO_RETENTION parameter is used to set a low retention time threshold which the system will attempt to achieve. In the event of any undo space constraints the system will prioritize DML operations over undo retention meaning the low threshold may not be achieved. If the undo retention threshold must be guaranteed, even at the expense of DML operations, the RETENTION GUARANTEE clause can be set against the undo tablespace during or after creation:
Reset the undo low threshold.
ALTER SYSTEM SET UNDO_RETENTION = 2400;
Guarantee the minimum threshold is maintained.
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
SELECT tablespace_name, retention FROM dba_tablespaces;
TABLESPACE_NAME RETENTION
------------------------------ -----------
SYSTEM NOT APPLY
UNDOTBS1 GUARANTEE
SYSAUX NOT APPLY
TEMP NOT APPLY
USERS NOT APPLY
5 rows selected.
Switch back to the default mode.
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;
TABLESPACE_NAME RETENTION
------------------------------ -----------
SYSTEM NOT APPLY
UNDOTBS1 NOGUARANTEE
SYSAUX NOT APPLY
TEMP NOT APPLY
USERS NOT APPLY
5 rows selected.
The Undo Advisor PL/SQL Interface
Oracle Database provides an Undo Advisor that provides advice on and helps automate the establishment of your undo environment. You activate the Undo Advisor by creating an undo advisor task through the advisor framework. The following example creates an undo advisor task to evaluate the undo tablespace. The name of the advisor is ‘Undo Advisor‘. The analysis is based on Automatic Workload Repository snapshots, which you must specify by setting parameters START_SNAPSHOT and END_SNAPSHOT. In the following example, the START_SNAPSHOT is “1” and END_SNAPSHOT is “2”.
DECLARE
tid NUMBER;
tname VARCHAR2(30);
oid NUMBER;
BEGIN
DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');
DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, 'null', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 1);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 2);
DBMS_ADVISOR.execute_task(tname);
end;
_____________________________________________________________________________________________
To check the optimum size, please excute this query:
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';
No comments:
Post a Comment