Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
11g Release 1 (11.1)

B28419-03
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

144 DBMS_WORKLOAD_CAPTURE

The DBMS_WORKLOAD_CAPTURE package configures the Workload Capture system and produce the workload capture data. Replay of this capture is implemented by way of the DBMS_WORKLOAD_REPLAYpackage.

See Also:

Oracle Database Real Application Testing User's 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

The following code describes the minimal set of privileges required to

DROP USER rom1 CASCADE;
CREATE USER rom1 IDENTIFIED BY rom1;
 
GRANT EXECUTE ON DBMS_WORKLOAD_CAPTURE TO rom1;
GRANT EXECUTE ON DBMS_WORKLOAD_REPLAY TO rom1;
 
GRANT CREATE SESSION TO rom1;
GRANT CREATE ANY DIRECTORY TO rom1;
GRANT SELECT_CATALOG_ROLE TO rom1;
GRANT BECOME USER TO rom1;

Appropriate OS permissions are required to access and manipulate files and directories on both the capture and replay system. This means that the Oracle process(es) and the OS user performing the capture or replay must be able to access and manipulate at least one common directory accessible from the host where the instance is running. Additionally, the OS user performing the replay should be able to execute wrc on hosts that will be used for the replay clients and be able to access the file system appropriately to be able to copy the capture to the replay clients' hosts if required.


Summary of DBMS_WORKLOAD_CAPTURE Subprograms

This table list the package subprograms in alphabetical order.

Table 144-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

Finalizes the worload capture by signaling all connected sessions to stop capture, and stops future requests to the database from being captured

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

PROCESS_CAPTURE Procedure

Processes the workload capture found in capture_dir in place, analyzes it, and creates new workload replay specific metadata files that are required to replay the given workload capture.

REPORT Function

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

START_CAPTURE Procedure

Initiates workload capture on all instances



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 144-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 144-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 144-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 144-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 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 144-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 144-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 144-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.


PROCESS_CAPTURE Procedure

This procedure processes the workload capture found in capture_dir in place, analyzes it, and creates new workload replay specific metadata files that are required to replay the given workload capture.

Syntax

DBMS_WORKLOAD_CAPTURE.PROCESS_CAPTURE (
   capture_dir        IN VARCHAR2,   parallel_level     IN NUMBER DEFAULT NULL);

Parameters

Table 144-9 PROCESS_CAPTURE Procedure Parameters

Parameter Description

capture_dir

Name of the workload capture directory object (case sensitive).The directory object must point to a valid OS directory that has appropriate permissions. New files will be added to this directory. (Mandatory)

parallel_level

Number of oracle processes used to process the capture in a parallel fashion. The NULL default value will auto-compute the parallelism level, whereas a value of 1 will enforce serial execution.


Usage Notes

This procedure can be run multiple times on the same capture directory, which is useful when it encounters unexpected errors or is cancelled by the user.

Once this procedure runs successfully, the capture_dir can be used as input to INITIALIZE_REPLAY Procedure in the DBMS_WORKLOAD_REPLAY package in order to replay the captured workload present in capture_dir.


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 144-10 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 144-11 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 workload capture on all instances.

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 144-12 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