Oracle DBMS_WORKLOAD_CAPTURE_I
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose Internal supporting package supporting DBMS_WORKLOAD_CAPTURE for capturing transactions for real application testing.
AUTHID DEFINER
Dependencies
DBA_HIST_PARAMETER DBMS_STANDARD PROPS$
DBA_HIST_SNAPSHOT DBMS_SWRF_INTERNAL SYS_IXMLAGG
DBA_SQLSET DBMS_SWRF_REPORT_INTERNAL V$DATABASE
DBA_WORKLOAD_CAPTURES DBMS_WORKLOAD_CAPTURE WRR$_CAPTURES
DBA_WORKLOAD_FILTERS DBMS_WORKLOAD_CAPTURE_LIB WRR$_CAPTURE_BUCKETS
DBMS_ADVISOR DBMS_WORKLOAD_REPLAY_I WRR$_CAPTURE_FILES
DBMS_ASSERT DBMS_WRR_INTERNAL WRR$_CAPTURE_SQLTEXT
DBMS_LOB DUAL XMLAGG
DBMS_REPORT PLITBLM XMLTYPE
DBMS_SCHEDULER    
Documented No
First Available 18c
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvtwrr.plb
Subprograms
 
ADD_FILTER
Adds a filter to capture only a subset of the workload

Overload 1
dbms_workload_capture_i.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_i.add_filter('UWFilter', 'User', 'UWCLASS');
Overload 2 dbms_workload_capture_i.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_i.add_filter('UWFilter', 'Instance', 2);
 
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_i.delete_capture_info(capture_id IN NUMBER);
SELECT id, name
FROM dba_workload_captures;

exec dbms_workload_capture_i.delete_capture_info(6);

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

exec dbms_workload_capture_i.delete_filter('UWFilter');
 
EXPORT_AWR
Exports the AWR snapshots associated with a given capture_id dbms_workload_capture_i.export_awr(capture_id IN NUMBER);
exec dbms_workload_capture_i.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_i.export_performance_data(capture_id IN NUMBER);
exec dbms_workload_capture_i.export_performance_data(6);
 
EXPORT_UC_GRAPH
Undocumented dbms_workload_capture_i.export_uc_graph(capture_id IN NUMBER);
exec dbms_workload_capture_i.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_i.finish_capture(
timeout IN NUMBER   DEFAULT 30,
reason  IN VARCHAR2 DEFAULT NULL);
exec dbms_workload_capture_i.finish_capture(20, 'Demo Complete');

SELECT name, error_message
FROM dba_workload_captures;
 
FINISH_CAPTURE_HELP
Undocumented dbms_workload_capture_i.finish_capture_help(
timeout IN NUMBER,
reason  IN VARCHAR2);
TBD
 
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_i.get_capture_info(dir IN VARCHAR2)
RETURN NUMBER;
set serveroutput on

DECLARE
 n NUMBER;
BEGIN
  n := dbms_workload_capture_i.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_i.get_capture_path(capture_id IN NUMBER)
RETURN VARCHAR2;
set serveroutput on

DECLARE
 dirpath VARCHAR2(100);
BEGIN
  dirpath := dbms_workload_capture_i.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_i.get_perf_data_export_status(
capture_id IN  NUMBER,
awr_data   OUT VARCHAR2,
sts_data   OUT VARCHAR2);
TBD
 
HANDLE_ENCRYPTION
Based on the specified action will encrypt or decrypt the identified capture dbms_workload_capture_i.handle_encryption(
action     IN BINARY_INTEGER,
src_dir    IN VARCHAR2,
dst_dir    IN VARCHAR2,
encryption IN VARCHAR2);  -- options: 'AES128', 'AES192', 'AES256'
TBD
 
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_i.import_awr(
cpature_id     IN NUMBER
staging_schema IN VARCHAR2
force_cleanup  IN BOOLEAN)
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_i.import_performance_data(
capture_id     IN NUMBER,
staging_schema IN VARCHAR2,
force_cleanup  IN BOOLEAN)
RETURN NUMBER;
TBD
 
IMPORT_UC_GRAPH
Undocumented dbms_workload_capture_i.import_uc_graph(capture_id IN NUMBER);
TBD
 
REPORT
Generates a report on the given workload capture dbms_workload_capture_i.report(
capture_id IN NUMBER,
format     IN VARCHAR2);
SELECT id, name, status
FROM dba_workload_captures;

SELECT dbms_workload_capture_i.report(1, 'HTML');
 
START_BATCH_CAPTURE
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_i.start_batch_capture(
name             IN VARCHAR2,
dir              IN VARCHAR2,
duration         IN NUMBER,
default_action   IN VARCHAR2,
auto_unrestrict  IN BOOLEAN,
capture_sts      IN BOOLEAN,
sts_cap_interval IN NUMBER,
plsql_mode       IN VARCHAR2,
encryption       IN VARCHAR2);
TBD
 
START_CAPTURE
Initiates a database wide workload capture dbms_workload_capture_i.start_capture(
name             IN VARCHAR2,
dir              IN VARCHAR2,
duration         IN NUMBER,
default_action   IN VARCHAR2,
auto_unrestruct  IN BOOLEAN,
CAPTURE_STS      IN BOOLEAN,
STS_CAP_INTERVAL IN NUMBER,
PLSQL_MODE       IN VARCHAR2,
ENCRYPTION       IN VARCHAR2);
TBD
 
SWITCH_BUCKET
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_i.switch_bucket(FALSE);

Related Topics
Built-in Functions
Built-in Packages
DBMS_AWRHUB
DBMS_AWRHUB_SERVER
DBMS_AWRHUB_SOURCE
DBMS_RAT_MASK
DBMS_REPLAYHUB
DBMS_WORKLOAD_CAPTURE
DBMS_WORKLOAD_REPLAY
DBMS_WORKLOAD_REPLAY_I
DBMS_WRR_INTERNAL
DBMS_WRR_STATE_BASE
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx