ORAC LE TRANS AC TION MA NAG EMENT in Font

Making Code39 in Font ORAC LE TRANS AC TION MA NAG EMENT

a new transaction, Oracle will use an unexpired undo extent a transaction can t be stopped, after all. This is a last-resort event, but you should be aware that it is a possibility. The key is to make the undo tablespace big enough so that it can support your undo retention interval, thus helping Oracle retain undo information for the specified period. You can set the undo retention size by specifying it in the initialization file as follows: UNDO_RETENTION = 1800 The default for the UNDO_RETENTION parameter is 900 seconds.

Your undo tablespace must be able to accommodate any increase in the undo retention period. If the undo tablespace can t keep undo records for the required time, you run the risk of queries failing with the snapshot-tooold error.

If you wish to change the amount of time the database should retain the undo information, you can dynamically change the UNDO_RETENTION parameter as follows: SQL> ALTER SYSTEM SET UNDO_RETENTION = 7200 /* two hours There is no one ideal UNDO_RETENTION time interval. Your retention time interval will depend on how long you estimate your longest transactions may run. Based on the information about the maximum length of transactions in your database, you can arrive at an approximate time to assign for the UNDO_RETENTION parameter. The V$UNDOSTAT table provides an indicator for helping figure out the undo retention interval. Query the V$UNDOSTAT view as follows: SQL> SELECT MAX(maxquerylen) FROM v$undostat; MAX(MAXQUERYLEN) ---------------210 The maxquerylen column of the V$UNDOSTAT view tells you the length of the longest executed query (in seconds) during the past 24 hours. The time set in the UNDO_RETENTION parameter should be at least as long as the time indicated in the maxquerylen column. This, by itself, won t guarantee that a new long-running query will definitely be accommodated, but you ll have a reasonable chance that your longest transactions will have read consistency when using the undo tablespace. Oracle provides the following guidelines for setting the undo retention interval for a new database: OLTP: 15 minutes Mixed: 1 hour DSS: 3 hours Flashback Query: 24 hours If you think all of this undo retention business is too much work, take the easy way out and let Oracle automatically tune undo in your database. Oracle automatically tunes the undo retention period for the longest-running query and collects query-duration information every 30 seconds. Depending on your workload characteristics, Oracle will adjust the length of the undo retention period. For example, during the day, shorter transactions may mean a shorter undo retention period, and during the nightly batch jobs, you d need a much longer undo retention period to avoid the snapshot-too-old errors. If you don t set a value for the UNDO_RETENTION parameter (or if you set a value of 0), Oracle automatically tunes undo with 900 seconds as the minimum value.

Here s a summary of automatic undo retention in Oracle Database 11g Release 1: If you use an auto-extensible undo tablespace (using an AUTOEXTEND datafile), Oracle will treat any UNDO_RETENTION value you specify as the low threshold value and retain undo for at least this time period. If you set an undo retention period of 30 minutes, Oracle will adjust the retention period upward of 30 minutes if needed, but never let it go below 30 minutes (unless faced with a lack of space in the undo tablespace). The database will tune the undo retention to take care of the undo needs of the longest queries in your database. Thus, in the case of auto-extensible undo tablespaces, Oracle will Retain undo data a little longer than the longest query in your database, if space allows it Retain undo data at least as long as the low threshold of undo retention, subject to space limitations If you use a fixed-size undo tablespace, Oracle will ignore any UNDO_RETENTION value you may have set. The database will automatically tune undo with the goal of achieving the maximum possible retention period, given the undo tablespace size and its usage history. Of course, if you use the guaranteed undo retention feature, as explained later in this chapter, Oracle will have to honor any UNDO_RETENTION period you set. If you ve specified any Flashback requirements, Oracle will satisfy them as well. If you re considering a fixed size and an auto-extensible tablespace of the same size, know that the fixed-size tablespace will provide you with a slightly longer undo retention period. Even if you do set a value for the UNDO_RETENTION parameter, Oracle will still auto-tune undo, with the value you specified as the minimum value. Note that the value you assign for the UNDO_RETENTION parameter is treated by Oracle as a requested minimum. If Oracle determines, through its automatic tuning, that the undo retention period should be longer than this requested minimum to accommodate long transactions, it will retain undo data for the longer retention period.