189 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_REPLAY package.
This chapter contains the following topics:
See Also:
Oracle Database Testing Guide for more information about database replay
189.1 DBMS_WORKLOAD_CAPTURE Overview
Since the capture infrastructure is instance wide (and also within an Oracle Real Application Clusters (Oracle RAC)), 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.
189.2 DBMS_WORKLOAD_CAPTURE Security Model
The security model describes the privileges needed for using DBMS_WORKLOAD_CAPTURE
.
The following code describes the minimal set of privileges required to:
-
Create directory objects
-
Operate the interface provided by the
DBMS_WORKLOAD_CAPTURE
andDBMS_WORKLOAD_REPLAY
packages -
Act as a replay client user (wrc
someuser
/somepassword
or wrcUSER=someuser
PASSWORD=somepassword
)
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 are used for the replay clients and be able to access the file system appropriately to copy the capture to the replay clients' hosts if required.
189.3 Summary of DBMS_WORKLOAD_CAPTURE Subprograms
This table lists the DBMS_WORKLOAD_CAPTURE
package subprograms in alphabetical order.
Table 189-1 DBMS_WORKLOAD_CAPTURE Package Subprograms
Subprogram | Description |
---|---|
Adds a specified filter |
|
This procedure decrypts sensitive data in workload capture that was encrypted using various advanced encryption standards such as |
|
Deletes the rows in the |
|
Deletes a specified filter |
|
This procedure encrypts sensitive data in workload capture using various advanced encryption standards such as |
|
Exports the AWR snapshots associated with a given capture ID |
|
Finalizes the workload capture by signaling all connected sessions to stop capture, and stops future requests to the database from being captured |
|
Retrieves all the information regarding a workload capture present in the stipulated directory, imports the information into the |
|
Imports the AWR snapshots associated with a given capture ID |
|
Returns a report on the workload capture under consideration using one or more different sources |
|
Initiates workload capture on all instances |
189.3.1 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 189-2 ADD_FILTER Procedure Parameters
Parameter | Description |
---|---|
|
Name for the filter to be added. Can be used to delete the filter later if it is not required. (Mandatory) |
|
Specifies the attribute on which the filter needs to be applied (Mandatory). The possible values are:
|
|
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 |
Usage Notes
-
The workload capture filters work in either the
DEFAULT
INCLUSION
or theDEFAULT
EXCLUSION
mode as determined by thedefault_action
input to the START_CAPTURE Procedure. -
ADD_FILTER
adds a new filter that affects the next workload capture, and whether the filters are considered asINCLUSION
filters orEXCLUSION
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. -
You can capture the workload for a particular PDB by specifying a filter of PDB type.
Examples
-
By default, a capture works in an
INCLUSION
mode, which records everything except for those requests that satisfy conditions of specified filters. For example, if you want to exclude all requests fromSCOTT
, you can add the following filter before starting a capture.EXEC DBMS_WORKLOAD_CAPTURE.ADD_FILTER ('filter user1', 'USER', 'SCOTT');
-
Multiple filters are evaluated according to the logical disjunction operator
OR
. Therefore, if you want to record workload for bothSCOTT
andJOHN
, you add an additional filter:EXEC DBMS_WORKLOAD_CAPTURE.ADD_FILTER ('filter user2', 'USER', 'JOHN');
-
In a CDB, you exclude the workload of a particular PDB by the filter:
EXEC DBMS_WORKLOAD_CAPTURE.ADD_FILTER ('filter pdb workload', 'PDB_NAME', 'CDB1_PDB1');
-
To use DBMS_APPLICATION_INFO to identify workload that is issued to the database:
DBMS_APPLICATION_INFO.SET_MODULE('ORDER_ENTRY', NULL); -- run some SQL here DBMS_APPLICATION_INFO.SET_ACTION('ORDER_ENTRY_LOG'); -- run logging SQL
-
If having captured workload, you want to exclude the logging SQL from the captured, specify a filter for capture:
DBMS_WORKLOAD_CAPTURE.ADD_FILTER('filter logging operations', 'ACTION', 'ORDER_ENTRY_LOG');
-
To filter out the full order entry transaction, define a filter:
DBMS_WORKLOAD_CAPTURE.ADD_FILTER('filter order entry', 'MODULE', 'ORDER_ENTRY');
189.3.2 DECRYPT_CAPTURE Procedure
This procedure decrypts sensitive data in workload capture that was encrypted using various advanced encryption standards such as AES128, AES192, or AES256.
Syntax
DBMS_WORKLOAD_CAPTURE.DECRYPT_CAPTURE ( src_dir IN VARCHAR2, dst_dir IN VARCHAR2);
Parameters
Table 189-3 DECRYPT_CAPTURE Procedure Parameters
Parameter | Description |
---|---|
|
A directory object pointing to the workload capture to be decrypted. This parameter is case sensitive. |
|
A directory object pointing to an OS path that has write permissions. The decrypted capture files will be written to this directory This parameter is case sensitive. |
Usage Notes
This procedure relies on a software keystore. The identifier is oracle.rat.database_replay.encryption
(case-sensitive).
189.3.3 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 189-4 DELETE_CAPTURE_INFO Procedure Parameters
Parameter | Description |
---|---|
|
ID of the workload capture that needs to be deleted. Corresponds to |
Usage Notes
Passing the ID of a capture that is in progress will first automatically stop that capture.
189.3.4 DELETE_FILTER Procedure
This procedure deletes a specified filter.
Syntax
DBMS_WORKLOAD_CAPTURE.DELETE_FILTER ( filter_name IN VARCHAR2(40) NOT NULL);
Parameters
Table 189-5 DELETE_FILTER Procedure Parameters
Parameter | Description |
---|---|
|
Filter to be deleted |
Usage Notes
The DELETE_FILTER
Procedure only affects filters that have not been used by any previous capture. Consequently, filters can be deleted only if they have been added using the ADD_FILTER Procedures after any capture has been completed. Filters that have been added using ADD_FILTER
before a START_CAPTURE
and FINISH_CAPTURE
cannot be deleted anymore
using this subprogram.
189.3.5 ENCRYPT_CAPTURE Procedure
This procedure encrypts sensitive data in workload capture using various advanced encryption standards such as AES128, AES192, or AES256. To encrypt capture on the fly, use encryption parameter in START_CAPTURE procedure.
Syntax
DBMS_WORKLOAD_CAPTURE.ENCRYPT_CAPTURE src_dir IN VARCHAR2, dst_dir IN VARCHAR2, encryption IN VARCHAR2 DEFAULT 'AES256');
Parameters
Table 189-6 ENCRYPT_CAPTURE Procedure Parameters
Parameter | Description |
---|---|
|
A directory object pointing to the workload capture to be encrypted. The parameter value is case sensitive. |
|
A directory object pointing to an OS path that has write permissions. The encrypted capture files will be written to this directory. The parameter value is case sensitive. |
|
Specifies if sensitive data in workload capture is encrypted or not. The possible values are: The default value is |
Usage Notes
For encrypted capture, the ENCRYPT_CAPTURE
Procedure relies on a software keystore. The identifier is oracle.rat.database_replay.encryption
(case-sensitive).
189.3.6 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 189-7 EXPORT_AWR Procedure Parameters
Parameter | Description |
---|---|
|
ID of the capture whose AWR snapshots are to be exported. (Mandatory) |
Usage Notes
This procedure works 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.
189.3.7 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 timeout IN NUMBER DEFAULT 30 reason IN VARCHAR2 DEFAULT NULL);
Parameters
Table 189-8 FINISH_CAPTURE Procedure Parameters
Parameter | Description |
---|---|
|
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 |
|
Specifies a reason for calling the procedure. The reason appears in the column |
Usage Notes
-
By default,
FINISH_CAPTURE
waits 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, flush their buffers and send back their acknowledgement toFINISH_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 does not send it's acknowledgement toFINISH_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 runningFINISH_CAPTURE
or send new database requests to those sessions duringFINISH_CAPTURE
.
189.3.8 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 189-9 GET_CAPTURE_INFO Function Parameters
Parameter | Description |
---|---|
|
Name of the |
Usage Notes
If an appropriate row describing the capture in the stipulated directory already exists in DBA_WORKLOAD_CAPTURES
, the GET_CAPTURE_INFO Function simply returns that row's DBA_WORKLOAD_CAPTURES
.ID
. If no existing row matches the capture present in the stipulated directory a new row is inserted to DBA_WORKLOAD_CAPTURES
and that row's ID
is returned.
189.3.9 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, force_cleanup IN BOOLEAN DEFAULT FALSE) RETURN NUMBER;
Parameters
Table 189-10 IMPORT_AWR Function Parameters
Parameter | Description |
---|---|
|
ID of the capture whose AWR snapshots should be imported. (Mandatory) |
|
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 |
|
Values:
|
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
fails 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
.
Related Topics
189.3.10 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 189-11 REPORT Function Parameters
Parameter | Description |
---|---|
|
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 |
|
Specifies the report format. Valid values are |
Return Values
The report body in the desired format returned as a CLOB
.
Table 189-12 Constants Used by Report Function
Constant | Type | Value | Description |
---|---|---|---|
|
|
'HTML' |
Generates the HTML version of the report |
|
|
'TEXT' |
Used as input to the |
189.3.11 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, capture_sts IN BOOLEAN DEFAULT FALSE, sts_cap_interval IN NUMBER DEFAULT 300, plsql_mode IN VARCHAR2 DEFAULT 'TOP_LEVEL', encryption IN VARCHAR2 DEFAULT NULL);
Parameters
Table 189-13 START_CAPTURE Procedure Parameters
Parameter | Description |
---|---|
|
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 is preserved along with the captured workload actions. (Mandatory) |
|
Name of the |
|
Optional input to specify the duration (in seconds) for which the workload needs to be captured. |
|
Can be either
|
|
Can be either
|
|
If this parameter is If starting SQL set capture fails, workload capture is stopped. The reason is stored in |
|
Specifies the capture interval of the SQL set capture from the cursor cache in seconds. The default value is 300. |
|
Specifies the PL/SQL capture mode:
|
|
Specify if sensitive data in workload capture is encrypted or not. The possible values are:
The default value is |
Usage Notes
-
All user requests sent to database after a successful invocation of
START_CAPTURE
are recorded in the givendir
directory for the given duration provided that one was specified. If no duration was specified, the capture lasts indefinitely until the FINISH_CAPTURE Procedure is executed. -
A workload capture once started continues 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 are 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) is not 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 usingSTARTUP
RESTRICT
areUNRESTRICTED
upon a successful invocation ofSTART_CAPTURE
UseFALSE
for theauto_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 are not replayed properly in subsequent database replays, since only that part of the transaction whose calls were executed afterSTART_CAPTURE
are replayed. -
For encrypted capture, the
START_CAPTURE
Procedure relies on a software keystore. The identifier isoracle.rat.database_replay.encryption
(case-sensitive). -
You must configure a software keystore in
auto-login
mode. Otherwise, if the database is bounced during capture, the capture is automatically terminated.