Undo Advisor
Oracle 11g??™s Undo Advisor automates a lot of the tasks necessary to fine-tune the amount of space
required for an undo tablespace. In Chapter 6, we reviewed two examples of using the Undo
Advisor: via the EM Database Control interface and using the PL/SQL DBMS_ADVISOR packages
within the Automatic Workload Repository (AWR) to programmatically choose a time period to
analyze and perform the analysis.
The Automatic Undo Management GUI screen is shown in Figure 7-6.
UNDO_RETENTION is currently set to 15 minutes and the size of the active undo tablespace
(UNDO_BATCH) is 500MB. In this example, if we want a read-consistent view of table data for
720 minutes, clicking the Run Analysis button tells us that we only need an undo tablespace size
of 165MB (and ideally three times this amount) to support workload fluctuations. Therefore, our
undo tablespace is sized adequately at 500MB.
FIGURE 7-6 Tablespace characteristics
222 Oracle Database 11g DBA Handbook
Controlling Undo Usage
As of Oracle9i, Oracle??™s Database Resource Manager can help to control undo space usage by
user or by group of users within a resource consumer group via the UNDO_POOL directive. Each
consumer group can have its own undo pool; when the total undo generated by a group exceeds
the assigned limit, the current transaction generating the undo is terminated and generates the
error message ???ORA-30027: Undo quota violation??”failed to get number (bytes).
Pages:
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381