using baseline

I've generated some baselines for the critical batch jobs which you can view from dba_hist_baseline so that you would be aware of which range of statistics to be extracted.

These will serve to be useful whenever we need to troubleshoot as we need to have baselines to work from. For last week's issue on the filesystem, I worked

from a base set of performance numbers before I concluded that there is something wrong at the system.

Numbers that I looked at include the average response time and absolute time for the background events, response table for the tablespaces, transaction rate for I/O related statistics. Let me know if you are unsure.

--CREATE_BASELINE Procedure Parameters
Parameter Description
START_SNAP_ID Lowest snapshot ID in the range
END_SNAP_ID Highest snapshot ID in the range
BASELINE_NAME Unique name for the baseline
DBID Optional database ID

For example, if you want to baseline the job that creates nightly reports, you could use the following
example, assuming that snapshots 42 and 43 mark the beginning and ending of the job:
BEGIN
Exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(START_SNAP_ID =>42,
➥END_SNAP_ID =>43,
BASELINE_NAME => ’REPORTS’);
END


--Dropping Baselines
--DROP_BASELINE Procedure Parameters
Parameter Description
BASELINE_NAME Name of the baseline to be dropped
CASCADE Boolean to determine whether the associated snapshots will be dropped
(default = FALSE)
DBID Optional database ID

Exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(‘REPORTS’,FALSE);

To query existing baselines, can use below command.

chk_baselines.sql"
set pages 1000
column BASELINE_NAME format a20
column START_SNAP_TIME format a30
column END_SNAP_TIME format a30

select BASELINE_ID,BASELINE_NAME,START_SNAP_TIME,END_SNAP_TIME from dba_hist_baseline order by baseline_id;