The AWR is essentially a job-based, scheduled collection of statistics, gathered and stored in the Oracle database, containing information about the database itself. By default, this metadata repository is kept in an extra tablespace called SYSAUX. When creating a new database, the job of gathering statistics is automatically created as part of the database creation. By default, statistics collection will run every 60 minutes, gathering information from dynamic performance views (v$ Views) within the database on statistics such as I/O waits and other wait events, CPU used per session, sorts, I/O rates on various datafiles, and so on. For this to occur, the parameter STATISTICS_LEVEL must be set to ALL or TYPICAL (the default). At the end of a run, the repository is updated with current information for that period. Reports can then be generated, using begin and end values defined by the user, to measure activity during a given period of time.
Since AWR runs are scheduled by default when the database is created, you need do nothing special to enable this functionality. As mentioned, AWR runs at 60-minute intervals, collecting stats and storing that information. However, as you can imagine, this can lead to a rather large repository over time, so by default the data is purged after seven days. Should you want to change these defaults, either the frequency of the runs or how soon the repository is purged, you can do so easily using OEM. Should you want to disable AWR altogether, you can do that as well; however, we strongly recommend that you do not disable AWR. The overhead for AWR is minimal, and the gains achieved by allowing the statistic gathering can come in handy at the most unexpected times.
Viewing an AWR report
The Workload Repository is populated every 60 minutes with statistics, and this happens automatically. Then what? What do you do with this information? You define times, outlined by AWR runs, in which to turn this information into a report. When creating an AWR report, you define the beginning and ending interval for the reports using snapshot IDs from the Workload Repository; your report will begin at one snapshot and end at a later snapshot.
An AWR report can be viewed in OEM from the Administration page. From there, select Workload Repository, and then click Snapshots. You can also change the interval for AWR snapshots here. As mentioned, you can determine the interval of the reports, with the beginning of the interval going back as far as the repository keeps the data. Reports can be viewed using a full report, or you can view the data with pertinent information summarized in the details view.
Creating baselines for comparing the workload
Evaluating AWR reports is also made easier if you have a comparison point. You may suspect, as you evaluate sections of the AWR report, that something “doesn’t look right”—for example, the Top SQL may show SQL statements with an excessive number of buffer gets. It would be valuable to be able to compare the report to a similar point in time in the past, when similar work was being done and performance was better. For example, perhaps the same SQL was run at points in the past, but the buffer gets for that same statement were much lower. This might indicate that an index has been dropped, or that the data distribution of the tables queried has somehow changed.
A baseline (also called a preserved snapshot set) is used as a comparison point of reference if and when performance suffers. The basic idea, for example, is that on a Monday morning, when poor MAA DBA Max is stumbling into work after a long weekend road trip to see his favorite football team, and all hell breaks loose with the database, Max can compare the current performance (or, say, the performance over the past hour) to the baseline that was set up for that timeframe. This will give him an immediate idea of what is different from last week and a head start on where to look for the solution to the problem.
Periods for baseline creation
As you can probably tell already, it is necessary to anticipate periods when you might expect to have performance problems. For this, you must understand the nuances of your particular business. For example, you may experience different peaks at different times of the day—for example, a busy period may occur between 8:30 A.M. and 1:00 P.M., and another peak from 2:30 P.M. to 4:30 P.M. Fortunately, you can create multiple baselines, each covering different points in time. Therefore, you may decide to create a different, separate baseline for each of those periods.
Leave a Reply