Tuning Your Database for Availability - Creating Baselines for Comparing the Workload
(Page 2 of 7 )
Evaluating the reports is also made easier if there is a comparison point. We may suspect, as we evaluate sections of the AWR report, that something does not look right—the top SQL may show SQL statements with an excessive number of buffer gets, as an example. It would be invaluable 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 the data distribution of the tables queried has somehow changed.
A baseline (also called a Preserved Snapshot Set) is used to have a comparison point to refer back to if and when performance suffers. The basic idea is that on a Monday morning (or any other time), when out of the blue the HA DBA is barraged with users advising that the database performance is less than exemplary, the first thing that he or she can do is compare the current performance (or, say, the performance over the past hour) to a baseline that was previously set up for that time frame. This will give the HA DBA an immediate idea of what is different from last week (or whatever point in the past you are comparing to) 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 have an understanding of the nuances of your particular business. For example, you may experience different peaks at different times of the day—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, it is possible to create multiple baselines, each covering different points in time. Therefore, you may decide to create a different, separate baseline for each of those periods.
On the other hand, it is difficult to create a baseline for every possible period. If performance problems occur with no appropriate baseline having been created, don’t fret. Depending on how long the retention time is for the Workload Repository, you can go back and create the baseline from a similar time frame in the past. For that reason, we recommend that you increase the workload retention time from the default of seven days. You may want to consider two weeks, or even a value of just over a month, depending on your storage capacity and the overall impact this has on your system. Keep in mind that this information will be stored in the SYSAUX tablespace, so size the SYSAUX tablespace accordingly.
Consider our scenario at the beginning of this section. Let’s say the DBA did not have a baseline for the early Monday morning period when performance was bad. Let’s assume that the problem began at 5:30 A.M. In this shop’s line of work, Monday mornings are rather unique, but it is unusual for the system to be loaded this early. The DBA would like to compare this morning’s workload to last week at the same time. However, with the default retention time of seven days, stats from last Monday at 5:30 A.M. would have already been purged from the database. If the retention time were set to eight days, or fourteen days, the DBA could go back in time in the Workload Repository, create a quick baseline covering last Monday morning, and then compare the current day’s snapshot to last week at the same time.
Comparing Current Stats to Baseline
The advantage of creating baselines is twofold. First, the baseline data remains in the repository indefinitely, until it is explicitly deleted, so it is not subject to being aged out like the rest of the snapshots. This means that the data will still be there in a month to refer back to and compare. Hence the term “Preserved Snapshot Set.” The second advantage is the ability to quickly run comparisons of a snapshot to a baseline.
It is easiest to do the comparison using EM. From the Workload Repository section, you can select the option to Compare Timelines. This will allow you to first select the time frame that you wish to compare (presumably a period of time with poor performance), and then you can select a preserved snapshot set (or any other period of time within your retention range) to compare that to. The output will give you such fodder as comparison of block change rates for each period, physical reads, parse time, user commits, and so on. The display will show the timelines side by side, in graphical format, allowing you to quickly see the differentiators between the two timelines for the given periods.
----------------------------------------------------------------------
HA Workshop: Exploring the Workload Repository
Workshop Notes
The Workload Repository is best understood by using Enterprise Manager, giving a graphical view of the snapshots available and masking the complexity of the APIs used. This example walks through using EM to view reports, edit the retention policies, and create baselines for comparison.
Step 1. Log in to EM using the following URL, substituting in the hostname of the database server for <server_name>:
http://<server_name>:5500/em
If using Enterprise Manager Grid Control (discussed in Chapter 5), use the machine name for the management repository database, with port 7777, as such:
http://<repository_server>:7777/em
Navigate to the Workload Repository by clicking on the Administration link for your particular database, and then, under Workload, click the Automatic Workload Repository link.
Step 2. Note the Snapshot Retention time (in days), the Snapshot Interval, and the Collection Level, under the General section. Click on Edit to change these. Set the Retention Period value to 30 days, and the System Snapshot Interval to 30 minutes, as shown in Figure 3-1, and then click OK.
Step 3. Back on the main Workload Repository page, in the Snapshots section, click on the underlined number next to Snapshots. This link will propel you into the Snapshots screen, where you can view the times of all AWR snapshots that currently reside in the repository.
Step 4. To view a report, navigate to the box titled Actions and change the selection to View Report. Click on the radio button next to an existing snap ID (not the last one) and then click Go. The next page will display all snapshots created
Figure 3-1. Changing default snapshot retention time
after the beginning snap ID that was previously selected. Select the one created directly after the beginning snap ID (this will give us a 30-minute interval), and then click OK. After a few seconds, you will see the Snapshot Details screen, with the ability to see in report format or details format. Save it by clicking on the Save to File button in the Report screen.
Step 5. Next, to create a baseline, return to the Snapshots screen by clicking on the Snapshots link across the top. For this exercise, we will create a baseline for the time period of 9:00 A.M. to 12:00 P.M., on a weekday, so select the radio button next to an existing snapshot with a capture time as near as possible to 9:00 A.M. on a weekday. Ensure that Create Preserved Snapshot Set is selected in the Actions box, and choose Go.
Step 6. On the Create Preserved Snapshot Set page, give the Snapshot Set a name of Weekday Morning and then select the Ending Snapshot to be the snapshot ID closest to 12 P.M. on the same day. Click OK to create the baseline (preserved snapshot set).
Step 7. Now, to compare a report interval to the baseline you have just created, return to the Snapshots screen by clicking on the Workload Repository link and then the Snapshots link again (see Step 3). In the Actions box, select Compare Timelines and, for the beginning snap ID, choose a snapshot from 9:00 A.M. on a weekday other than the baseline you just created.
Step 8. Choose the ending snapshot of 12:00 P.M. on that same day and choose Go.
Step 9. On the Second Timeline Start screen, choose the Select a Preserved Snapshot Set option and then select the weekday morning baseline that was created in Step 6. Click Next, and then click Finish to get the Compare Timelines: Results screen. This screen will give you a graphical comparison between the baseline (second timeline) and the date that you had selected for comparison.
NOTEYou do not have to have a preserved snapshot set to do the comparison—you can compare intervals between any two periods for which you still have snapshots in the repository, based on your snapshot retention period.
ADDM (Automatic Database Diagnostic Monitor)
Well, after going through all of the information available in a Workload Repository report, it can seem a bit daunting to interpret. Further, you may ask yourself, geez—do I need to read through these reports every 60 minutes? Once a day? Once a week? The answer to that depends on your social life. However, the good news is that you do not have to read these reports on an hourly basis. The Automatic Database Diagnostic Monitor will do that for you.
ADDM is constantly running in the background, monitoring information that is collected in the Workload Repository. ADDM uses this data, as well as data from the active session history (ASH), and automatically analyzes this information to provide proactive recommendations on tuning and performance issues. The home screen in Enterprise Manager has an Advice section, with links to ADDM Findings, as well as an ADDM Analysis section where this information is displayed.
ADDM essentially can be used in two separate modes—what we would refer to as a proactive and a reactive mode. The proactive mode is defined by the automatic diagnosis mentioned above. Aside from these automatic ADDM checks, additional ADDM tasks can be created to look for specific problems, or to go back in time and look at a period in time when a problem was observed or reported. This is the reactive mode usage of ADDM.
Next: Viewing the ADDM Reports >>
More Oracle Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter three of Oracle Database 10g High Availability with RAC, Flashback and Data Guard, written by Matthew Hart and Scott Jesse (McGraw-Hill/Osborne, 2004; ISBN: 0072254289). Check it out at your favorite bookstore. Buy this book now.
|
|
/ 11 
