Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
10g Release 2 (10.2)

Part Number B14258-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

118 DBMS_WORKLOAD_CAPTURE

The DBMS_WORKLOAD_CAPTURE package configures the Workload Capture system and produce the workload capture data.

See Also:

Oracle Database Performance Tuning Guide for more information about "Database Replay"

This chapter contains the following topics:


Using DBMS_WORKLOAD_CAPTURE


Overview

Since the capture infrastructure is instance wide (and RAC-wide) only one workload capture is being produced at any point in time. Thus capture interfaces do not need a state object passed in as a parameter since there is one single state at any point in time. This means that all subprograms cannot be methods of an object but are package wide PL/SQL subprograms


Security Model

Use of the package is restricted to users with DBA role or EXECUTE_CATALOG_ROLE.Additionally, the user of the package must have access to a host directory that can also be accessed by the RDBMS to store/retrieve the workload capture data files.


Summary of DBMS_WORKLOAD_CAPTURE Subprograms

This table list the package subprograms in alphabetical order.

Table 118-1 DBMS_WORKLOAD_CAPTURE Package Subprograms

Subprogram Description

ADD_FILTER Procedures

Adds a specified filter

DELETE_CAPTURE_INFO Procedure

Deletes the rows in the DBA_WORKLOAD_CAPTURES and DBA_WORKLOAD_FILTERS views that corresponds to the given workload capture ID

DELETE_FILTER Procedure

Deletes a specified filter

EXPORT_AWR Procedure

Exports the AWR snapshots associated with a given capture ID

FINISH_CAPTURE Procedure

Depending on current state, it either finalizes the workload capture or unrestricts the database and puts it back in the "Normal" mode

GET_CAPTURE_INFO Function

Retrieves all the information regarding a workload capture present in the stipulated directory, imports the information into the DBA_WORKLOAD_CAPTURES and DBA_WORKLOAD_FILTERS views, and returns the appropriate DBA_WORKLOAD_CAPTURES.ID

IMPORT_AWR Function

Imports the AWR snapshots associated with a given capture ID

REPORT Function

Returns a report on the workload capture under consideration using one or more different sources

START_CAPTURE Procedure

Initiates the series of actions that are part of the capture process



ADD_FILTER Procedures

This procedure adds a filter to capture a subset of the workload.

Syntax

DBMS_WORKLOAD_CAPTURE.ADD_FILTER (
   fname           IN   VARCHAR2 NOT NULL, 
   fattribute      IN   VARCHAR2 NOT NULL, 
   fvalue          IN   VARCHAR2 NOT NULL);
DBMS_WORKLOAD_CAPTURE.ADD_FILTER (
   fname           IN   VARCHAR2 NOT NULL, 
   fattribute      IN   VARCHAR2 NOT NULL, 
   fvalue         IN   NUMBER NOT NULL);

Parameters

Table 118-2 ADD_FILTER Procedure Parameters

Parameter Description

fname

A name for the filter to be added. Can be used to delete the filter later if it is not required. (Mandatory)

fattribute

Specifies the attribute on which the filter needs to be applied (Mandatory). The possible values are:

  • INSTANCE_NUMBER - type NUMBER

  • USER - type STRING

  • MODULE - type STRING

  • ACTION - type STRING

  • PROGRAM - type STRING

  • SERVICE - type STRING

fvalue

Specifies the value to which the given attribute should be equal to for the filter to be considered active. Wildcards like '%' are acceptable for all attributes that are of type STRING. This means that the filter for a NUMBER attribute will be parsed as "attribute = value", with the filter for a STRING attribute parsed as "attribute like value" (Mandatory)


Usage Notes


DELETE_CAPTURE_INFO Procedure

This procedure deletes the rows in the DBA_WORKLOAD_CAPTURES and DBA_WORKLOAD_FILTERS views that corresponds to the given workload capture ID.

Syntax

DBMS_WORKLOAD_CAPTURE.DELETE_CAPTURE_INFO
   capture_id     IN   NUMBER);

Parameters

Table 118-3 DELETE_CAPTURE_INFO Procedure Parameters

Parameter Description

capture_id

ID of the workload capture that needs to be deleted. Corresponds to DBA_WORKLOAD_CAPTURES.ID. (Mandatory)


Usage Notes

Passing the ID of a capture that is in progress will first automatically stop that capture.


DELETE_FILTER Procedure

This procedure deletes a specified filter.

Syntax

DBMS_WORKLOAD_CAPTURE.DELETE_FILTER (
   filter_name           IN   VARCHAR2(40) NOT NULL);

Parameters

Table 118-4 DELETE_FILTER Procedure Parameters

Parameter Description

filter_name

The filter to be deleted



EXPORT_AWR Procedure

This procedure exports the AWR snapshots associated with a given capture ID.

Syntax

DBMS_WORKLOAD_CAPTURE.EXPORT_AWR (
   capture_id     IN NUMBER);

Parameters

Table 118-5 EXPORT_AWR Procedure Parameters

Parameter Description

capture_id

ID of the capture whose AWR snapshots are to be exported. (Mandatory)


Usage Notes

This procedure will work only if the corresponding workload capture was performed in the current database (meaning that the corresponding row in DBA_WORKLOAD_CAPTURES was not created by calling the GET_CAPTURE_INFO Function) and the AWR snapshots that correspond to the original capture time period are still available.


FINISH_CAPTURE Procedure

This procedure signals all connected sessions to stop the workload capture and then stops future requests to the database from being captured.

Syntax

DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE
   timneout     IN   NUMBER  DEFAULT 30
   reason       IN   VARCHAR2 DEFAULT NULL);

Parameters

Table 118-6 FINISH_CAPTURE Procedure Parameters

Parameter Description

timeout

Specifies in seconds for how long the procedure should wait before it times out. Pass 0 if you want to cancel the current workload capture and not wait for any sessions to flush it's capture buffers. Default value: 30 seconds

reason

Specifies a reason for calling the procedure. The reason will appear in the column ERROR_MESSAGE of the view DBA_WORKLOAD_CAPTURES.


Usage Notes


GET_CAPTURE_INFO Function

This procedure retrieves all information regarding a workload capture present in the stipulated directory, imports the information into the DBA_WORKLOAD_CAPTURES and DBA_WORKLOAD_FILTERS views, and returns the appropriate DBA_WORKLOAD_CAPTURES.ID

Syntax

DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO
   dir     IN   VARCHAR2)
  RETURN NUMBER;

Parameters

Table 118-7 GET_CAPTURE_INFO Function Parameters

Parameter Description

dir

Name of the DIRECTORY object (case sensitive) where all the workload capture files are located (Mandatory)


Usage Notes

If an appropriate row describing the capture in the stipulated directory already exists in DBA_WORKLOAD_CAPTURES, the GET_CAPTURE_INFO Function will simply return that row's DBA_WORKLOAD_CAPTURES.ID. If no existing row matches the capture present in the stipulated directory a new row will be inserted to DBA_WORKLOAD_CAPTURES and that row's ID will be returned.


IMPORT_AWR Function

This procedure imports the AWR snapshots associated with a given capture ID provided those AWR snapshots were exported earlier from the original capture system using the EXPORT_AWR Procedure.

Syntax

DBMS_WORKLOAD_CAPTURE.IMPORT_AWR (
   capture_id      IN  NUMBER,
   staging_schema  IN  VARCHAR2)
 RETURN NUMBER;

Parameters

Table 118-8 IMPORT_AWR Function Parameters

Parameter Description

capture_id

ID of the capture whose AWR snapshots should be imported. (Mandatory)

staging_schema

Name of a valid schema in the current database which can be used as a staging area while importing the AWR snapshots from the capture directory to the SYS AWR schema.The SYS schema is not a valid input. (Mandatory, Case sensitive).


Return Values

Returns the new randomly generated database ID that was used to import the AWR snapshots. The same value can be found in the AWR_DBID column in the DBA_WORKLOAD_CAPTURES view.

Usage Notes

IMPORT_AWR will fail if the staging_schema provided as input contains any tables with the same name as any of the AWR tables, such as WRM$_SNAPSHOT or WRH$_PARAMETER. Please drop any such tables in the staging_schema before invoking IMPORT_AWR.


REPORT Function

This function generates a report on the stipulated workload capture.

Syntax

DBMS_WORKLOAD_CAPTURE.REPORT (
   capture_id      IN   NUMBER,
   format          IN   VARCHAR2)
 RETURN CLOB;

Parameters

Table 118-9 REPORT Function Parameters

Parameter Description

capture_id

ID of the workload capture whose capture report is required. (Mandatory)

This relates to the directory that contains the workload capture on which the Report needs to be generated. Should be a valid DIRECTORY object that points to a valid directory in the host system that contains a workload capture.

format

Specifies the report format. Valid values are DBMS_WORKLOAD_CAPTURE.TYPE_TEXT and DBMS_WORKLOAD_CAPTURE.TYPE_HTML.(Mandatory)


Return Values

The report body in the desired format returned as a CLOB

Table 118-10 Constants Used by Report Function

Constant Type Value Description

TYPE_HTML

VARCHAR2(4)

'HTML'

Generates the HTML version of the report

TYPE_TEXT

VARCHAR2(4)

'TEXT'

Use this as input to the format argument to generate the text version of the report.



START_CAPTURE Procedure

This procedure initiates a database wide workload capture.

Syntax

DBMS_WORKLOAD_CAPTURE.START_CAPTURE (
   name              IN  VARCHAR2,
   dir               IN  VARCHAR2,
   duration          IN  NUMBER   DEFAULT NULL,
   default_action    IN  VARCHAR2 DEFAULT 'INCLUDE',
   auto_unrestrict   IN  BOOLEAN  DEFAULT TRUE);

Parameters

Table 118-11 START_CAPTURE Procedure Parameters

Parameter Description

name

Name of the workload capture. Allows the workload capture to be given a label, such as "Thanksgiving weekend" or "Christmas peak workload" for future reference. The workload capture's name will be preserved along with the captured workload actions. (Mandatory)

dir

Name of the DIRECTORY object (case sensitive) where all the workload capture files will be stored. Should contain enough space to hold all the workload capture files. (Mandatory)

duration

Optional input to specify the duration (in seconds) for which the workload needs to be captured. DEFAULT is NULL which means that workload capture will continue until the user executes DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE

default_action

Can be either INCLUDE or EXCLUDE. Determines whether, by default, every user request should be captured or not. Also determines whether the workload filters specified should be considered as INCLUSION filters or EXCLUSION filters.

  • If INCLUDE, by default all user requests to the database will be captured, except for the part of the workload defined by the filters. In this case, all the filters specified using the ADD_FILTER Procedures will be treated as EXCLUSION filters, determining the workload that will not be captured. (DEFAULT, and so all the filters specified are assumed to be EXCLUSION filters.)

  • If EXCLUDE, by default no user request to the database will be captured, except for the part of the workload defined by the filters. In this case, all the filters specified using the ADD_FILTER Procedures will be treated as INCLUSION filters, determining the workload that will be captured.

auto_unrestrict

Can be either TRUE or FALSE.

  • If TRUE, all instances started up in RESTRICTED mode using STARTUP RESTRICT will be automatically unrestricted upon a successful START_CAPTURE. (DEFAULT)

  • If FALSE, no database instance will be automatically unrestricted.


Usage Notes