Optimal setting of undo retention in Oracle database for OLTPs:
There are lots of doubts to set value for UNDO retention and UNDO tablespace sizing. All most all DBAs are familiar with "ORA-01555". So, we will discuss optimizing oracle UNDO parameters to avoid all doubts. Transaction undo information is stored in rollback segments until a commit or rollback statement was executed. Sometimes we get an error “ORA-01555:Snapshot Too Old” on long running queries. In case of online recover scenario also you may see the above error. This error occures when we set UNDO parameters incorrectly.
You can find ORA-1555 error count that you’re wondering in the last few from the moment our database is opened with the following query;
SQL> select '"ORA-01555 (Snapshot too old error )" Found : ' || sum(ssolderrcnt) ||' Times '
from v$undostat;
With UNDO_RETENTION parameter, automatic undo management allows to specify how long undo information must be retained after commit. The default value of this parameter is 900s. (15 min). If you set UNDO_MANAGEMENT parameter to AUTO and create a UNDO tablespace then ORACLE will manage it.
So how should UNDO_RETENTION up period? The answer to this question is proportional to transcend size UNDO Tablespace. Criticality of data and according to the disk, you can give an area for the UNDO tablespace. Calculating the optimum size of the amount of time you should set this parameter UNDO_RETENTION.
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 (15 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.
However it is worth to tune the following important parameters
1. The size of the UNDO tablespace
2. The UNDO_RETENTION parameter
You can choose to allocate a specific size for the UNDO tablespace and then set the UNDO_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:
You can use below query to find optimal value to set undo tablespace size:
SELECT (UR * (UPS * DBS)) / 1024 / 1024 AS "MBytes"
FROM (SELECT 36000 AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT undoblks / ((end_time - begin_time) * 86400) AS UPS
FROM v$undostat
WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),
(SELECT block_size AS DBS
FROM dba_tablespaces
WHERE tablespace_name =
(SELECT UPPER(value)
FROM v$parameter
WHERE name = 'undo_tablespace'));
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#;
output:
UNDO_SIZE
----------
6170869760
Undo Blocks per Second :
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;
------------------
24.865
DB Block Size:
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';
output:
DB_BLOCK_SIZE [KByte]
---------------------
8192
So as per the formula, the Optimal Undo Retention will be :
SQL> select 6170869760/(24.865*8192) from dual;
output:
30294 Sec
Using Inline Views, you can do all in one 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';
outputs:
ACTUAL UNDO SIZE [MByte]
----------------------------------
5885
UNDO RETENTION [Sec]
------------------------------
900
OPTIMAL UNDO RETENTION [Sec]
-------------- ----------------------------
30295
Calculate required UNDO Size(aprox) for given Database Activity :
If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:
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';
There are lots of doubts to set value for UNDO retention and UNDO tablespace sizing. All most all DBAs are familiar with "ORA-01555". So, we will discuss optimizing oracle UNDO parameters to avoid all doubts. Transaction undo information is stored in rollback segments until a commit or rollback statement was executed. Sometimes we get an error “ORA-01555:Snapshot Too Old” on long running queries. In case of online recover scenario also you may see the above error. This error occures when we set UNDO parameters incorrectly.
You can find ORA-1555 error count that you’re wondering in the last few from the moment our database is opened with the following query;
SQL> select '"ORA-01555 (Snapshot too old error )" Found : ' || sum(ssolderrcnt) ||' Times '
from v$undostat;
With UNDO_RETENTION parameter, automatic undo management allows to specify how long undo information must be retained after commit. The default value of this parameter is 900s. (15 min). If you set UNDO_MANAGEMENT parameter to AUTO and create a UNDO tablespace then ORACLE will manage it.
So how should UNDO_RETENTION up period? The answer to this question is proportional to transcend size UNDO Tablespace. Criticality of data and according to the disk, you can give an area for the UNDO tablespace. Calculating the optimum size of the amount of time you should set this parameter UNDO_RETENTION.
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 (15 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.
However it is worth to tune the following important parameters
1. The size of the UNDO tablespace
2. The UNDO_RETENTION parameter
You can choose to allocate a specific size for the UNDO tablespace and then set the UNDO_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:
You can use below query to find optimal value to set undo tablespace size:
SELECT (UR * (UPS * DBS)) / 1024 / 1024 AS "MBytes"
FROM (SELECT 36000 AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT undoblks / ((end_time - begin_time) * 86400) AS UPS
FROM v$undostat
WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),
(SELECT block_size AS DBS
FROM dba_tablespaces
WHERE tablespace_name =
(SELECT UPPER(value)
FROM v$parameter
WHERE name = 'undo_tablespace'));
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#;
output:
UNDO_SIZE
----------
6170869760
Undo Blocks per Second :
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;
output:
UNDO_BLOCK_PER_SEC------------------
24.865
DB Block Size:
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';
output:
DB_BLOCK_SIZE [KByte]
---------------------
8192
So as per the formula, the Optimal Undo Retention will be :
SQL> select 6170869760/(24.865*8192) from dual;
output:
30294 Sec
Using Inline Views, you can do all in one 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';
outputs:
ACTUAL UNDO SIZE [MByte]
----------------------------------
5885
UNDO RETENTION [Sec]
------------------------------
900
OPTIMAL UNDO RETENTION [Sec]
-------------- ----------------------------
30295
Calculate required UNDO Size(aprox) for given Database Activity :
If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:
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';
outputs:
ACTUAL UNDO SIZE [MByte]
----------------------------------
5885
UNDO RETENTION [Sec]
------------------------------
900
NEEDED UNDO SIZE [MByte]
-------------- --------------------
31398.6
This is the best practice to set UNDO retention and UNDO tablespace size setting. The previous query may return a "NEEDED UNDO SIZE" that is less than the "ACTUAL UNDO SIZE". If this is the case, you may be wasting space. You can choose to resize your UNDO tablespace to a lesser value or increase your UNDO_RETENTION parameter to use the additional space.
I hope this article may help you.
byeeee.
ACTUAL UNDO SIZE [MByte]
----------------------------------
5885
UNDO RETENTION [Sec]
------------------------------
900
NEEDED UNDO SIZE [MByte]
-------------- --------------------
31398.6
This is the best practice to set UNDO retention and UNDO tablespace size setting. The previous query may return a "NEEDED UNDO SIZE" that is less than the "ACTUAL UNDO SIZE". If this is the case, you may be wasting space. You can choose to resize your UNDO tablespace to a lesser value or increase your UNDO_RETENTION parameter to use the additional space.
I hope this article may help you.
byeeee.
No comments:
Post a Comment