Computing the Minimum Undo Tablespace Size Using the Undo Advisor

If you must change the undo tablespace to a fixed size, then use the Undo Advisor to help determine the minimum required size. You can also use the Undo Advisor to set the minimum undo retention period.

To configure the undo tablespace to have a fixed size, Oracle suggests that you first allow enough time after database creation to run a full workload, thus allowing the undo tablespace to grow to its minimum required size to handle the workload. Then, you can use the Undo Advisor to determine the best size to configure the undo tablespace to allow for future long-running queries and Oracle Flashback operations.

To compute the minimum undo tablespace size using the Undo Advisor:

1. Go to the Automatic Undo Management page.

   See "Viewing Undo Information".

2. Decide whether you want to compute the minimum size of the undo tablespace based on statistics gathered over a designated time period (such as the last 7 days), or based on an undo retention period of a duration that you choose.

   The automatically gathered statistics include the duration of the longest-running query and the undo generation rate. Computing the minimum undo tablespace size based on these statistics is advisable if you do not use Oracle Flashback features or if you do not expect future long-running queries to exceed the duration of previous long-running queries.

   If you prefer to choose and enter the duration of a desired undo retention period, then the duration must be based on your expectations of the duration of future long-running queries or Oracle Flashback operations.

3. To compute the minimum undo tablespace size based on statistics gathered over a period, complete the following steps:

   1. In the Analysis Period section, in the Analysis Time Period list, select the desired analysis time period.

      If you select Customize Time Period, then a page appears that enables you to enter the starting and ending date and time for the period.

   2. Select Automatically chosen based on longest query in analysis period.

   3. Click Run Analysis.

      The minimum required undo tablespace size is displayed in the Analysis Results section.

   4. (Optional) Click Show Graph to view a graph of the analysis results.

4. To compute the minimum undo tablespace size based on a duration that you enter, complete the following steps:

   1. In the Analysis Period section, select Specified manually to allow for longer duration queries or flashback.

   2. In the Duration field, enter the desired duration of the undo retention period in seconds, minutes, hours, or days.

      You may compute this duration as follows:

      • Determine the duration of the expected longest-running query for your workload.

        You can base your determination on the longest-running query recorded during a previous workload period. This information is available from the System Activity subpage of the Automatic Undo Management page. You choose the analysis period for this subpage by selecting from the Analysis Time Period list on the General subpage. You can choose a custom analysis period that corresponds to your typical workload period.

      • Determine the longest duration that is expected for an Oracle Flashback operation.

      • Take the maximum of these two durations.

      
      Description of the illustration undo_mgmt_activity_112.gif
      
      

   3. In the Analysis Period section of the General subpage, click Run Analysis.

      The minimum required undo tablespace size is displayed in the Analysis Results section.

   4. (Optional) Click Show Graph to view a graph of the analysis results.

5. (Optional) Click the tick-mark, or point, on the curve that corresponds to the desired undo retention period.

   The Duration field changes to match the selected undo retention period, and the Minimum Required Undo Tablespace Size field above the graph changes to reflect the matching required size.

6. (Optional) Click Apply.

   The minimum undo retention period is set to the value of the Duration field.