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 OEMTo 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:
Making the Most of Undo AdvisorClick 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 |