The advisor results in Figure 6-12 indicate that the table EMPLOYEES_SEARCH would benefit
from a shrink operation, potentially improving access to the table and freeing up space in the
USERS tablespace. To implement the recommendation, you can click the Shrink button in the
Recommendation column.
Undo Advisor
To start the Automatic Undo Management Advisor, start at the page in Figure 6-11, and click the
Automatic Undo Management link at the top of the page. In Figure 6-13, you see the current
settings for the undo tablespace UNDOTBS1.
FIGURE 6-11 Advisors and Tasks
Chapter 6: Monitoring Space Usage 205
FIGURE 6-12 Segment Advisor results
FIGURE 6-13 Undo Advisor current settings and options
206 Oracle Database 11g DBA Handbook
Given the recent SQL load in this database, the current size of the undo tablespace (265MB)
is sufficient (with AUTOEXTEND set at 5MB increments) to satisfy the undo data needs for similar
queries in the future. However, you??™re expecting to add some data warehouse tables and you may
have long-running queries that may exceed the current 15-minute undo retention window, and
you want to maintain overall system performance by avoiding frequent extensions to the existing
undo tablespace. Therefore, you probably need to increase the size of the undo tablespace; in
Figure 6-13, specify 45 minutes in the Duration text box and click the Run Analysis button. The
analysis is performed immediately; at the bottom of Figure 6-14, you see that the minimum
required undo tablespace size is 545MB.
Pages:
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361