118 DBMS_WORKLOAD_CAPTURE

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

Using DBMS_WORKLOAD_CAPTURE

Overview
Security Model

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

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)

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

The workload capture filters work in either the DEFAULT INCLUSION or the DEFAULT EXCLUSION mode as determined by the default_action input to the START_CAPTURE Procedure.
ADD_FILTER adds a new filter that will affect the next workload capture, and whether the filters will be considered as INCLUSION filters or EXCLUSION filters depends on the value of the default_action input to START_CAPTURE Procedure.
Filters once specified are valid only for the next workload capture. If the same set of filters need to be used for subsequent capture, they need to be specified each time before the START_CAPTURE Procedure is executed.
All the filters are listed in the DBA_WORKLOAD_FILTERS view.

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

By default, FINISH_CAPTURE will wait for 30 seconds to receive a successful acknowledgement from all sessions in the database cluster before timing out.
All sessions that either were in the middle of executing a user request or received a new user request, while FINISH_CAPTURE was waiting for acknowledgements, will flush their buffers and send back their acknowledgement to FINISH_CAPTURE.
If a database session remains idle (waiting for the next user request) throughout the duration of FINISH_CAPTURE, the session might have unflushed capture buffers and will not send it's acknowledgement to FINISH_CAPTURE.

To avoid this, do not have sessions that remain idle (waiting for the next user request) while invoking FINISH_CAPTURE. Either close the database session(s) before running FINISH_CAPTURE or send new database requests to those sessions during FINISH_CAPTURE.

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

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)

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

All user requests sent to database after a successful invocation of START_CAPTURE will be recorded in the given dir directory for the given duration provided that one was specified. If no duration was specified, the capture will last indefinitely until the FINISH_CAPTURE Procedure is executed.
A workload capture once started will continue to record user requests across database instance shutdowns and startups for the specified duration, or until FINISH_CAPTURE is executed, whichever occurs first.
One can use workload filters (as described with regard to the ADD_FILTER Procedures) to capture only a subset of the user requests sent to the database. By default, when no workload filters are defined, all user requests will be captured.
Workload that is initiated from Oracle Database background processes (such as SMON, PMON, MMON) and Oracle Database Scheduler Jobs (as detailed in the DBMS_SCHEDULER package) will not be captured, no matter how the workload filters are defined. These activities should happen automatically on an appropriately configured replay system.
By default, all database instances that were started up in RESTRICTED mode using STARTUP RESTRICT will be UNRESTRICTED upon a successful invocation of START_CAPTURE Use FALSE for the auto_unrestrict input parameter, if you do not want this behavior.
It is important to have a well-defined starting point for the workload so that the replay system can be restored to that point before initiating a replay of the captured workload. To have a well-defined starting point for the workload capture, it is preferable not to have any active user sessions when START_CAPTURE is executed. If ongoing sessions have ongoing transactions, those transactions will not be replayed properly in subsequent database replays, since only that part of the transaction whose calls were executed after START_CAPTURE will be replayed.