Oracle Create_Baseline

Oracle Database Tips by Donald Burleson

Create_baseline is both a function and a procedure that defines a baseline by either its starting snapshot ID or time and its ending snapshot ID or time. As a function, it returns the snapshot ID number.

Create_baseline_template is an overloaded procedure, meaning it  has two forms, that defines a template for how future baselines are to be created. The first form, which creates a baseline as the period between two snapshots, is as follows:

The second form of Create_baseline_template, which creates a baseline as the period from a user defined start time and duration, is as follows:

 

Argument	Type	In / Out	Default Value
DAY_OF_WEEK	VARCHAR2	IN
HOUR_IN_DAY	NUMBER	IN
DURATION	NUMBER	IN
START_TIME	DATE	IN
END_TIME	DATE	IN
BASELINE_NAME_PREFIX	VARCHAR2	IN
TEMPLATE_NAME	VARCHAR2	IN
EXPIRATION	NUMBER	IN
DBID	NUMBER	IN	NULL

Create_snapshot is both a function and a procedure that defines a new snapshot of performance data that is to be collected. As a function, it simply returns the snapshot ID number. The valid inputs are TYPICAL (default) and ALL. Also remember that calling this action consumes disk space in the SYSAUX tablespace.

Drop_baseline is a procedure that deletes an existing baseline. When CASCADE is set to TRUE, it also deletes the pair of associated snapshots for that baseline. Otherwise, the snapshots will remain until as such time as their date and time fall outside the retention period and are then cleaned up automatically.

Drop_baseline_template is a procedure that simply deletes an existing baseline template.

Drop_snapshot_range is a procedure that deletes an existing range of snapshots. The space thus freed can be utilized by future snapshots.

Modify_snapshot_settings is a procedure that permits control of three important aspects of snapshots: the frequency of the collection interval, the retention or persistence period, and the number of top SQL captured. While this call does not consume any space per se in the SYSAUX tablespace, it nonetheless defines how often snapshots are collected and for how long they are retained. Therefore, care should be taken when setting these parameters. The retention time is expressed in minutes and must be from 1 day to 100 years, where zero means keep forever. The interval is also expressed in minutes, and must be between 10 minutes and 1 year. The further apart the snapshots are spread, the harder it becomes to diagnose problems, so set accordingly.

The constants for these minimum and maximum values are as follows:

Modify_baseline_window_size is a procedure that permits one to redefine the window size of a sliding or moving window baseline. The window size is expressed in number of days and must be less than or equal to the retention period.

Rename_baseline is a procedure that simply renames an existing baseline.

Although PL/SQL provides this wonderful programmatic interface, most people will probably use the Oracle Enterprise Manager (OEM) screens for managing snapshots and baselines. Figure 7.1 below shows an example of deleting a snapshot range.