Oracle DBMS_WORKLOAD_CAPTURE_I
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 Internal supporting package supporting DBMS_WORKLOAD_CAPTURE for capturing transactions for real application testing.
AUTHID DEFINER
Dependencies
DBA_HIST_PARAMETER DBMS_STANDARD SYS_IXMLAGG
DBA_HIST_SNAPSHOT DBMS_SWRF_INTERNAL V$DATABASE
DBA_SQLSET DBMS_SWRF_REPORT_INTERNAL V$INSTANCE
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 PROPS$  
Documented No
First Available 18.1.0.0
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvtwrr.plb
Subprograms
 
ADD_FILTER (new 18.1)
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 (new 18.1)
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 (new 18.1)
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 (new 18.1)
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 (new 18.1)
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 (new 18.1)
Undocumented dbms_workload_capture_i.export_uc_graph(capture_id IN NUMBER);
exec dbms_workload_capture_i.export_uc_graph(6);
 
FINISH_CAPTURE (new 18.1)
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 (new 18.1)
Undocumented dbms_workload_capture_i.finish_capture_help(
timeout IN NUMBER,
reason  IN VARCHAR2);
TBD
 
GET_CAPTURE_INFO (new 18.1)
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 (new 18.1)
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 (new 18.1)
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
 
GET_STATE (new 18.1)
Returns 1 if a session is being capture: Else 0 dbms_workload_capture_i.get_state RETURN BINARY_INTEGER;
SQL> SELECT dbms_workload_capture_i.get_state
  2  FROM dual;

 GET_STATE
----------
         0
 
HANDLE_ENCRYPTION (new 18.1)
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 (new 18.1)
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 (new 18.1)
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 (new 18.1)
Undocumented dbms_workload_capture_i.import_uc_graph(capture_id IN NUMBER);
TBD
 
REPORT (new 18.1)
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 (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_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 (new 18.1)
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 (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_i.switch_bucket(FALSE);
 
USER_CALLS_GRAPH (new 18.1)
Undocumented dbms_workload_capture_i.user_calls_graph(capture_id IN NUMBER)
RETURN uc_graph_table PIPELINED;
TBD

Related Topics
Built-in Functions
Built-in Packages
DBMS_WORKLOAD_CAPTURE
DBMS_WORKLOAD_REPLAY
DBMS_WORKLOAD_REPLAY_I
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