The Undo Advisor, however, relies on another feature new to Oracle 10g??”the Automatic
Workload Repository (AWR). The Automatic Workload Repository, built into every Oracle
database, contains snapshots of all key statistics and workloads in the database at 60-minute
intervals by default. The statistics in the AWR are kept for seven days, after which the oldest
statistics are dropped. Both the snapshot intervals and the retention period can be adjusted to
suit your environment, however. The AWR maintains the historical record of how the database
is being used over time and helps to diagnose and predict problems long before they can cause
a database outage.
To set up Undo Advisor to analyze undo space usage, we will use an anonymous PL/SQL
block similar to what we used for Segment Advisor. Before we can use Segment Advisor, however,
we need to determine the timeframe to analyze. The data dictionary view DBA_HIST_SNAPSHOT
contains the snapshot numbers and date stamps; we will look for the snapshot numbers from 8:00
P.M. Saturday, July 21, 2007 through 9:30 P.M. Saturday, July 21, 2007:
SQL> select snap_id, begin_interval_time, end_interval_time
2 from DBA_HIST_SNAPSHOT
3 where begin_interval_time > '21-Jul-07 08.00.00 PM' and
4 end_interval_time < '21-Jul-07 09.31.00 PM'
5 order by end_interval_time desc;
SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- --------------------------- ---------------------------
8 21-JAN-07 09.
Pages:
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343