Using Undo Advisor

Undo Advisor helps you to size the undo tablespace based on the workload history. By default, automatic undo retention tuning is enabled, and Oracle determines the optimal undo retention time depending on the undo tablespace size. Oracle Database 10g remembers these optimal undo settings and avoids any performance problems during instance restart, database migration, undo tablespace switching, and so on. After a database restart or undo tablespace switch, Oracle Database 10g determines the number of undo segments to be placed online based on data stored in the AWR. The Undo Advisor analyzes the undo usage over a period of time and suggests the optimal undo tablespace size to support the longest-running queries. Altering the collection interval and retention period for AWR statistics as explained in Chapter 6 can affect the precision and recommendations given by the Undo Advisor.

The V$UNDOSTAT view has the necessary statistics for monitoring and tuning undo space. Oracle utilizes these statistics to tune undo usage in the system. Using this view, you also can get a better estimate of the amount of undo space required for the current workload. The DBA_HIST_UNDOSTAT view contains statistical snapshots of V$UNDOSTAT information. The V$ROLLSTAT view has information about the behavior of undo segments in the undo tablespace. The V$trANSACTION view has undo segment information, while the DBA_UNDO_EXTENTS gives the status and size of each extent in the undo tablespace.

You can use a fixed-size undo tablespace or a tablespace with auto-extensible on. If you choose a fixed-size undo tablespace, use the Undo Advisor to estimate the needed sizing. Undo Advisor can be invoked through Oracle Enterprise Manager or through the DBMS_ADVISOR PL/SQL package. OEM is the preferred method for accessing the Undo Advisor.

Undo Advisor will not give any useful information unless the database has been up and running for a long enough time to gather sufficient statistics.

Undo Management Using OEM

To manage undo space using Oracle Enterprise Manager, click the Advisor Central link in the Database Instance page. Then click the Undo Management link in the Advisor Central page. See Figure 7.1 for details.

Figure 7.1. Undo management from the OEM.

In addition to an Undo Advisor button, which you can click to access the Undo Advisor, the Undo Management page includes the following sections:

• Configuration. The Configuration section displays details about the auto-tuned undo retention period, the minimum undo retention period, and the name and size of the undo tablespace. It also shows whether the undo tablespace has the auto-extensible feature enabled. With the auto-extensible feature enabled, Oracle automatically increases the size of the undo tablespace with space needs. Using the auto-extensible feature along with auto-tuned undo retention, the DBA can provide enough undo space for longest-running queries and ensure the successful completion of batch cycles. When the auto-extensible feature is disabled, you must manually extend the undo tablespace as needed. If you get an undo tablespace alert (warning or critical), or if you get alerts such as "query too long" or "snapshot too old," use the Undo Advisor to get recommendations and make changes to the sizing.

• Recommendations. Using the Recommendations section, the DBA can choose a time period and ask Oracle for system-activity analysis. If there are any problems or any recommendations for the current undo configuration, they will be displayed here. You can update the analysis by clicking the Update Analysis button.

• System Activity and Tablespace Usage. This section shows the longest-running query and a graphical depiction of both undo tablespace usage and undo generation rates.

Making the Most of Undo Advisor

Click the Undo Advisor button to get undo recommendations. Under the Advisor section, you can choose a new undo retention time and new undo retention periods. The Analysis section shows all the database-analysis results of your undo setup. The graph helps the DBA to visualize the undo tablespace size and the undo retention period. See Figure 7.2 for details.

Figure 7.2. The Undo Advisor.

The maximum undo retention time corresponds to your longest-running query. The best possible undo retention time period should be longer than your longest-running query for the undo tablespace to be adequately configured.

You should combine the length of your expected longest-running query and the longest interval needed for flashback operations to look up the required undo tablespace size on the Undo Advisor graph.

You can also use Undo Advisor to calculate undo retention time for flashback operations. For using flashback operations and to maintain an effective flashback recovery strategy, the DBA has to set the low threshold parameter optimally for automatic undo tuning. Oracle always keeps the minimum undo retention time over this threshold parameter (expressed in minutes), so if your database requires a flashback recovery period of six hours, set the low threshold to six hours. On the Undo Advisor page, enter the threshold value in the New Undo Retention field and click OK.

The database must be in archive log mode to enable flashback operations. Flashback logs are similar to redo logs and can be written to a file, a directory, or an ASM disk group.

You can also set the UNDO_RETENTION parameter from the Initialization Parameters page, as shown in Figure 7.3.

Figure 7.3. Changing the UNDO_RETENTION parameter