Oracle DBMS_WORKLOAD_CAPTURE
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
Purpose To capture (record and save) real-time workloads on a database so that they can be replayed repeatedly for purposes of testing and debugging.

According to Oracle: "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." which means, we think, that Bryn Llewellyn wrote the paragraph and little more as almost no working DBA will understand it. Suffice it to say that Capture is a database-level, not an instance-level event so attempts perform multiple runs, in parallel, will fail (or should).

Experience indicates that RAT capture will likely add 5-6% cpu overhead plus an amount of I/O dependent upon the volume of the data being written.
AUTHID CURRENT_USER
Constants
Name Data Type Value
Report
TYPE_HTML VARCHAR2(4) 'HTML'
TYPE_TEXT VARCHAR2(4) 'TEXT'
TYPE_XML VARCHAR2(3) 'XML'
 
REGULAR_CAPTURE BINARY_INTEGER 0
BATCH_CAPTURE BINARY_INTEGER 1
Data Types TYPE uc_graph_record IS RECORD(time NUMBER, user_calls NUMBER, flags NUMBER);

TYPE uc_graph_table IS TABLE OF uc_graph_record;
Dependencies
DBA_HIST_SNAPSHOT DBMS_SCHEDULER PROPS$
DBA_SQLSET DBMS_STANDARD SYS_IXMLAGG
DBA_WORKLOAD_CAPTURES DBMS_SWRF_INTERNAL V$DATABASE
DBA_WORKLOAD_FILTERS DBMS_SWRF_REPORT_INTERNAL V$INSTANCE
DBMS_ADVISOR DBMS_WORKLOAD_CAPTURE_LIB WRR$_CAPTURES
DBMS_LOB DBMS_WORKLOAD_REPLAY WRR$_CAPTURE_UC_GRAPH
DBMS_RANDOM DBMS_WRR_INTERNAL XMLAGG
DBMS_RAT_MASK DUAL XMLTYPE
DBMS_REPORT PLITBLM  
Documented Yes
First Available 11.1.0.6
Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE and DBA roles
Source {ORACLE_HOME}/rdbms/admin/dbmswrr.sql
Subprograms
 
ADD_FILTER
Adds a filter to capture only a subset of the workload

Overload 1
dbms_workload_capture.add_filter(
fname      IN VARCHAR2 NOT NULL,
fattribute IN VARCHAR2 NOT NULL,
fvalue     IN VARCHAR2 NOT NULL);

Attribute Data Type
Action String
Instance Number
Module String
Program String
Service String
User String
conn sys@pdbdev as sysdba

exec dbms_workload_capture.add_filter('UWFilter', 'User', 'UWCLASS');
Overload 2 dbms_workload_capture.add_filter(
fname      IN VARCHAR2 NOT NULL,
fattribute IN VARCHAR2 NOT NULL,
fvalue     IN NUMBER   NOT NULL);
conn sys@pdbdev as sysdba

exec dbms_workload_capture.add_filter('UWFilter', 'Instance', 2);
 
DECRYPT_CAPTURE (new 18.1)
Decrypt an encrypted workload capture dbms_workload_capture.decrypt_capture(
src_dir IN VARCHAR2,
dst_dir IN VARCHAR2);
exec dbms_workload_capture.decrypt_capture('SRCDIR', 'TGTDIR');
 
DELETE_CAPTURE_INFO
Deletes the rows in DBA_WORKLOAD_CAPTURES and DBA_WORKLOAD_FILTERS that corresponds to the given workload capture id dbms_workload_capture.delete_capture_info(capture_id IN NUMBER);
SELECT id, name
FROM dba_workload_captures;

exec dbms_workload_capture.delete_capture_info(6);

SELECT id, name
FROM dba_workload_captures;
 
DELETE_FILTER
Deletes the filter with the given name dbms_workload_capture.delete_filter(fname IN VARCHAR2);
-- do not know where filters are stored

exec dbms_workload_capture.delete_filter('UWFilter');
 
ENCRYPT_CAPTURE (new 18.1)
Encrypt an un-encrypted workload capture dbms_workload_capture.encrypt_capture(
src_dir    IN VARCHAR2,
dst_dir    IN VARCHAR2,
encryption IN VARCHAR2 DEFAULT 'AES256'); -- options: 'AES128', 'AES192', 'AES256'
exec dbms_workload_capture.encrypt_capture('SRCDIR', 'TGTDIR', 'AES256');
 
EXPORT_AWR
Exports the AWR snapshots associated with a given capture_id dbms_workload_capture.export_awr(capture_id IN NUMBER);
exec dbms_workload_capture.export_awr(6);
 
EXPORT_PERFORMANCE_DATA
Exports the AWR snapshots associated with a given capture_id as well as the SQL set that may have been captured along with the workload dbms_workload_capture.export_performance_data(capture_id IN NUMBER);
exec dbms_workload_capture.export_performance_data(6);
 
EXPORT_UC_GRAPH
Undocumented (Internal) dbms_workload_capture.export_uc_graph(capture_id IN NUMBER);
exec dbms_workload_capture.export_uc_graph(6);
 
FINISH_CAPTURE
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(
timeout IN NUMBER DEFAULT 30,
reason  IN VARCHAR2 DEFAULT NULL);
exec dbms_workload_capture.finish_capture(20, 'Demo Complete');

SELECT name, error_message
FROM dba_workload_captures;
 
GET_CAPTURE_INFO
Looks into the workload capture present in the given directory and retrieves all the information regarding that capture, 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;
set serveroutput on

DECLARE
 n NUMBER;
BEGIN
  n := dbms_workload_capture.get_capture_info('CTEMP');
  dbms_output.put_line(n);
END;
/

SELECT name, start_scn, end_scn, duration_secs, filters_used, capture_size
FROM dba_workload_captures;
 
GET_CAPTURE_PATH
Returns the full path to the capture files directory dbms_workload_capture.get_capture_path(capture_id IN NUMBER) RETURN VARCHAR2;
set serveroutput on

DECLARE
 dirpath VARCHAR2(100);
BEGIN
  dirpath := dbms_workload_capture.get_capture_path(6);
  dbms_output.put_line(dirpath);
END;
/
 
GET_PERF_DATA_EXPORT_STATUS
Populates awr_data and sts_data with the filenames of the exported performance data. If no data exists, NULL is set to the appropriate output variable dbms_workload_capture.get_perf_data_export_status(
capture_id IN  NUMBER,
awr_data   OUT VARCHAR2,
sts_data   OUT VARCHAR2);
DECLARE
 ad_out  VARCHAR2(30);
 sts_out VARCHAR2(30);
BEGIN
  dbms_workload_capture.get_perf_data_export_status(6, ad_out, sts_out);
  dbms_output.put_line(ad_out);
  dbms_output.put_line(sts_out);
END;
/
 
GET_STATE (new 18.1)
Returns 1 if a session is being capture: Else 0 dbms_workload_capture.get_state RETURN BINARY_INTEGER;
SQL> SELECT dbms_workload_capture.get_state
  2  FROM dual;

 GET_STATE
----------
         0
 
IMPORT_AWR
Imports an AWR snapshot that was exported earlier from the original capture system using DBMS_WORKLOAD_CAPTURE EXPORT_AWR. To avoid DBID conflicts, this function will generate a random DBID and use that DBID to populate the SYS AWR schema. The value used for DBID can be found in DBA_WORKLOAD_CAPTURES AWR_DBID. dbms_workload_capture.import_awr(
capture_id     IN NUMBER,
staging_schema IN VARCHAR2,
force_cleanup  IN BOOLEAN DEFAULT FALSE)
RETURN NUMBER;
TBD
 
IMPORT_PERFORMANCE_DATA
Imports the AWR snapshots from a given replay, provided those AWR snapshots were successfully exported earlier from the original replay system dbms_workload_capture.import_performance_data(
capture_id     IN NUMBER,
staging_schema IN VARCHAR2,
force_cleanup  IN BOOLEAN DEFAULT FALSE)
RETURN NUMBER;
TBD
 
IMPORT_UC_GRAPH
Undocumented (Internal) dbms_workload_capture.import_uc_graph(capture_id IN NUMBER);
exec dbms_workload_capture.import_uc_graph(6);
 
REPORT
Generates a report on the given workload capture dbms_workload_capture.report(
capture_id IN NUMBER,
format     IN VARCHAR2)
RETURN CLOB;
SELECT id, name, status
FROM dba_workload_captures;

SELECT dbms_workload_capture.report(1, 'HTML');
 
START_BATCH_CAPTURE (new 18.1)
Start workload capture and store data in different buckets. For instance, workload in 9AM - 10AM will be stored in bucket 1 while workload in 10AM - 12PM will be stored in bucket 2. dbms_workload_capture.start_batch_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);
TBD
 
START_CAPTURE (new 18.1 parameters)
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,
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
);
exec dbms_workload_capture.start_capture('UWCapture', 'CTEMP', 300);
 
SWITCH_BUCKET (new 18.1)
Signals all connected sessions to store workload captures into a new bucket. By default, SWITCH_BUCKET will create an AWR snapshot for the workload captured in the current bucket. dbms_workload_capture.switch_bucket(create_snapshot IN BOOLEAN DEFAULT TRUE);
exec dbms_workload_capture.switch_bucket(FALSE);
 
USER_CALLS_GRAPH
Undocumented dbms_workload_capture.user_calls_graph(capture_id IN NUMBER)
RETURN uc_graph_table PIPELINED;
TBD
 
Capture Demo
Go to DBMS_WORKLOAD_REPLAY to complete the demo conn sys@pdbdev as sysdba

SELECT COUNT(*)
FROM dba_workload_captures;

CREATE OR REPLACE DIRECTORY ctemp AS 'c:\temp';

set linesize 121
col owner format a10
col directory_path format a60

SELECT *
FROM dba_directories;

exec dbms_workload_capture.add_filter('UWFilter', 'User', 'UWCLASS');

desc dba_workload_filters

col type format a15
col attribute format a15
col name format a15

SELECT *
FROM dba_workload_filters;

conn uwclass/uwclass@pdbdev

CREATE TABLE capture AS
SELECT *
FROM all_objects
WHERE 1=2;

CREATE OR REPLACE PROCEDURE captest IS
 CURSOR ao_cur IS
 SELECT *
 FROM all_objects;

 etime DATE := SYSDATE + 5/1440;
BEGIN
  WHILE SYSDATE < etime LOOP
    FOR ao_rec IN ao_cur LOOP
      INSERT INTO capture
      VALUES ao_rec;
    END LOOP;
    user_lock.sleep(100);
    COMMIT;
  END LOOP;
  sys.dbms_workload_capture.finish_capture(5, 'Demo Complete');
END captest;
/

shutdown immediate;

startup restrict;

-- start a separate SQL*Plus session and try this:

conn uwclass/uwclass@pdbdev

-- it will fail

exec dbms_workload_capture.start_capture('UWCapture', 'CTEMP', 3000);

-- now start the new SQL*Plus session

conn uwclass/uwclass@pdbdev

exec captest;

DECLARE
 n NUMBER;
BEGIN
  n := dbms_workload_capture.get_capture_info('CTEMP');
  dbms_output.put_line(n);
END;
/

Related Topics
AS_REPLAY
Built-in Functions
Built-in Packages
DBMS_APPLICATION_INFO
DBMS_RAT_MASK
DBMS_WORKLOAD_CAPTURE_I
DBMS_WORKLOAD_REPLAY
DBMS_WORKLOAD_REPLAY_I
DIRECTORIES
What's New In 12cR2
What's New In 18cR3

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved