The DBMS_WORKLOAD_CAPTURE
package configures the Workload Capture system and produce the workload capture data.
This chapter contains the following topics:
Overview
Security Model
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
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.
This table list the package subprograms in alphabetical order.
Table 118-1 DBMS_WORKLOAD_CAPTURE Package Subprograms
Subprogram | Description |
---|---|
Adds a specified filter |
|
Deletes the rows in the |
|
Deletes a specified filter |
|
Exports the AWR snapshots associated with a given capture ID |
|
Depending on current state, it either finalizes the workload capture or unrestricts the database and puts it back in the "Normal" mode |
|
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 the series of actions that are part of the capture process |
This procedure adds a filter to capture a subset of the workload.
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);
Table 118-2 ADD_FILTER Procedure Parameters
Parameter | Description |
---|---|
|
A 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 |
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.
This procedure deletes the rows in the DBA_WORKLOAD_CAPTURES
and DBA_WORKLOAD_FILTERS
views that corresponds to the given workload capture ID.
DBMS_WORKLOAD_CAPTURE.DELETE_CAPTURE_INFO capture_id IN NUMBER);
Table 118-3 DELETE_CAPTURE_INFO Procedure Parameters
Parameter | Description |
---|---|
|
ID of the workload capture that needs to be deleted. Corresponds to |
Passing the ID of a capture that is in progress will first automatically stop that capture.
This procedure deletes a specified filter.
DBMS_WORKLOAD_CAPTURE.DELETE_FILTER ( filter_name IN VARCHAR2(40) NOT NULL);
Table 118-4 DELETE_FILTER Procedure Parameters
Parameter | Description |
---|---|
|
The filter to be deleted |
This procedure exports the AWR snapshots associated with a given capture ID.
DBMS_WORKLOAD_CAPTURE.EXPORT_AWR ( capture_id IN NUMBER);
Table 118-5 EXPORT_AWR Procedure Parameters
Parameter | Description |
---|---|
|
ID of the capture whose AWR snapshots are to be exported. (Mandatory) |
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.
This procedure signals all connected sessions to stop the workload capture and then stops future requests to the database from being captured.
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE timneout IN NUMBER DEFAULT 30 reason IN VARCHAR2 DEFAULT NULL);
Table 118-6 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 will appear in the column |
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
.
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
DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO dir IN VARCHAR2) RETURN NUMBER;
Table 118-7 GET_CAPTURE_INFO Function Parameters
Parameter | Description |
---|---|
|
Name of the |
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.
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.
DBMS_WORKLOAD_CAPTURE.IMPORT_AWR ( capture_id IN NUMBER, staging_schema IN VARCHAR2) RETURN NUMBER;
Table 118-8 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 |
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.
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
.
This function generates a report on the stipulated workload capture.
DBMS_WORKLOAD_CAPTURE.REPORT ( capture_id IN NUMBER, format IN VARCHAR2) RETURN CLOB;
Table 118-9 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 |
The report body in the desired format returned as a CLOB
Table 118-10 Constants Used by Report Function
Constant | Type | Value | Description |
---|---|---|---|
|
|
'HTML' |
Generates the HTML version of the report |
|
|
'TEXT' |
Use this as input to the |
This procedure initiates a database wide workload capture.
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);
Table 118-11 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 will be 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
|
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.