Performing Baselining
| We alluded to a baseline before, and will define and detail it in this section. An AWR baseline is a collection of snapshots usually taken over a representative time period. For example, you might record a baseline where the system is performing well at peak load. You can then use this baseline as a way of comparing statistics captured during a period of bad performance. This baseline comparison can help pinpoint certain statistics that have changed significantly as compared to the period when the database was performing well. You can then analyze the statistics that differ vastly to determine the cause of the problem. Baselines can also be used with SQL tuning sets; we talk about this in detail in a Chapter 13, "Effectively Using the SQL Advisors." AWR supports the capture of baseline data by allowing you to specify and preserve a range or even the minimum of a pair of AWR snapshots as a baseline. You should, however, carefully consider the time period you choose as a baseline. This period should be a good representation of the normal, expected load on the system. In the future, you can compare these baselines with snapshots captured during periods of poor performance for comparison purposes. Note that the snapshots that are linked to baselines are never purged from the AWR repository even if they are past the purge period. For this reason, baselines are also known as preserved snapshot sets. Current baselines can be viewed using the DBA_HIST_BASELINE view or the WRM$_BASELINE view at a lower level. Capturing BaselinesSimilar to the CREATE_SNAPSHOT object, the CREATE_BASELINE object can be called as a procedure or as a function from within the DBMS_WORKLOAD_REPOSITORY package to create a baseline. The required input parameters include a starting and ending SNAPSHOT_ID corresponding to the period of the baseline as well as a descriptive name for the baseline. The kernel internally assigns a baseline ID, which can be seen from the OEM Preserved Snapshot Set screen shown in Figure 11.4. The commands to create these baselines are shown in Listing 11.7. Listing 11.7. Using the DBMS_WORKLOAD_REPOSITORY Package to Create BaselinesSQL> begin 2 dbms_workload_repository.create_baseline(start_snap_id=>715, - 3 end_snap_id=>717, baseline_name=>'New Backup baseline' ); 4 end; 5 / PL/SQL procedure successfully completed. SQL> select 2 dbms_workload_repository.create_baseline 3 (703, 711, 'Good Batch baseline') 4 from dual; DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(703,711,'GOODBATCHBASELINE') --------------------------------------------------------------------- 3 Figure 11.4. The Preserved Snapshot Sets screen. Note that the snapshots associated with a baseline will not be purged even if they are past their retention time, as long as the baseline exists. They are purged only when the baseline is dropped. Dropping BaselinesA previously captured baseline can be dropped using the DROP_BASELINE procedure, as shown in Listing 11.8. Listing 11.8. Using DROP_BASELINE to Drop Existing BaselinesSQL> begin 2 dbms_workload_repository.drop_baseline(baseline_name =>, - 3 'New Backup baseline', cascade=> TRUE ); 4 end; 5 / PL/SQL procedure successfully completed. The cascade option is optional and defaults to FALSE. When defaulted, the underlying snapshots for that baseline will not be deleted. If, however, cascade is set to trUE, the associated snapshots are dropped along with the baseline. When you drop a baseline without specifying the deletion of the associated snapshots, and the time period for the purging of the baseline has passed, the snapshots will not be deleted immediately, but will be purged in the next purge cycle.
|
EAN: 2147483647
Pages: 214