Oracle | DBMS_WORKLOAD_CAPTURE

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	FINISH_CAPTURE_HELP	IMPORT_PERFORMANCE_DATA
DELETE_CAPTURE_INFO	GET_CAPTURE_INFO	IMPORT_UC_GRAPH
DELETE_FILTER	GET_CAPTURE_PATH	REPORT
EXPORT_AWR	GET_PERF_DATA_EXPORT_STATUS	START_BATCH_CAPTURE
EXPORT_PERFORMANCE_DATA	HANDLE_ENCRYPTION	START_CAPTURE
EXPORT_UC_GRAPH	IMPORT_AWR	SWITCH_BUCKET
FINISH_CAPTURE

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);