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 .
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 we can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time. The flashback query can go upto the point of time specified as a value in the UNDO_RETENTION parameter.
Why ORA-01555 error occur?
1.) Oracles does this by reading the "before image" of changed rows from the online undo segments. If we have lots of updates, long running SQL i.e , rollback records needed by a reader fo consistent read are overwritten by other writers.
To solve this issues we need to increase the undo tablepsace and undo retention period. Now the issue is how much should be the optimal value of undo retention and undo tablespace. For this we use the advisor. By using OEM, it is quite easy to estimate the size and time duration of undo.
The following query will help us to optimize the UNDO_RETENTION parameter :
Optimal Undo Retention = Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_SEC)
To calculate Actual Undo Size :
SQL> SELECT SUM(a.bytes)/1024/1024 "UNDO_SIZE_MB"
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 :
SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"
FROM v$undostat ;
DB Block Size :
SQL> SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [Byte]" FROM v$parameter
WHERE name = 'db_block_size';
We can do all in one query as
SQL> SELECT d.undo_size/(1024*1024) “ACT_UNDO_SIZE [MB]“,
SUBSTR(e.value,1,25) “ UNDO_RTN [Sec] “,
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) “OPT_UNDO_RET[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'
/
ACT_UNDO_SIZE [MB] UNDO_RTN [Sec] OPT_UNDO_RET[Sec]
------------------------ ---------------------- ----------------------------
50 900 24000
Calculate Needed UNDO Size :
If we are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for us (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity :
Here again we can find in a single :
SQL> 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'
/
We can avoid ORA-01555 error as follows :
1.) Do not run discrete transactions while sensitive queries or transactions are running, unless we are confident that the data sets required are mutually exclusive.
2.) Schedule long running queries and transactions out of hours, so that the consistent gets will not need to rollback changes made since the snapshot SCN. This also reduces the work done by the server, and thus improves performance.
3.) Code long running processes as a series of restartable steps.
4.) Shrink all rollback segments back to their optimal size manually before running a sensitive query or transaction to reduce risk of consistent get rollback failure due to extent deallocation.
5.) Use a large optimal value on all rollback segments, to delay extent reuse.
6.) Don't fetch across commits. That is, don't fetch on a cursor that was opened prior to the last commit, particularly if the data queried by the cursor is being changed in the current session.
7.) Commit less often in tasks that will run at the same time as the sensitive query, particularly in PL/SQL procedures, to reduce transaction slot reuse.
Enjoy :-)
1 comment:
very nice.. keep it up...... :)
Post a Comment