Undo Tips from the Trenches


For best practices and to control the retention of undo records, Oracle recommends that your database undo retention period be at least as long as your longest-running query. If you plan to use flashback features to recover your database from human errors, the undo retention period should be equal to or higher than the maximum time duration you want to go back in the past. You must judiciously compare the time needed for flashback operations as well as for longest-running queries and determine an optimal value for the undo retention period. This will in turn help you determine the undo tablespace sizing.

For certain databases, you may want to limit the undo space by establishing user quotas. You can use the Database Resource Manager directive UNDO_POOL to limit the amount of undo space consumed by a group of users (under Resource Consumer Group, using OEM or Oracle-supplied packages). More details on using the Database Resource Manager can be found in Oracle Database Administrator's Guide 10g. An undo pool will control the amount of total undo that can be generated by a consumer group (measured in kilobytes). When the total undo generated by the consumer group exceeds its limit, the current transaction generating the undo will be terminated. Members of the consumer group cannot perform any further updates until undo space is freed from this pool. The default value for UNDO_POOL size is UNLIMITED.



    Oracle Database 10g Insider Solutions
    SUSE LINUX Enterprise Server 9 Administrators Handbook
    ISBN: 672327910
    EAN: 2147483647
    Year: 2006
    Pages: 214

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net