Oracle DBMS_WORKLOAD_CAPTURE
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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).
AUTHID DEFINER
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);
 
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(filter_name IN VARCHAR2);
-- do not know where filters are stored

exec dbms_workload_capture.delete_filter('UWFilter');
 
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 (new 12.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.export_performance_data(capture_id IN NUMBER);
exec dbms_workload_capture.export_performance_data(6);
 
EXPORT_UC_GRAPH
Undocumented 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 (new 12.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.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;
/
 
IMPORT_AWR
Imports ab AWR snapshots 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 (new 12.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.import_performance_data(
capture_id     IN NUMBER,
staging_schema IN VARCHAR2,
force_cleanup  IN BOOLEAN DEFAULT FALSE )
RETURN NUMBER;
TBD
 
IMPORT_UC_GRAPH
Undocumented 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;

Available format parameter values
TYPE_HTML VARCHAR2(4) 'HTML'
TYPE_TEXT VARCHAR2(4) 'TEXT'
SELECT id, name, status
FROM dba_workload_captures;

SELECT dbms_workload_capture.report(1, 'HTML');
 
START_CAPTURE
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);
exec dbms_workload_capture.start_capture('UWCapture', 'CTEMP', 300);
 
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
DBMS_APPLICATION_INFO
DBMS_RAT_MASK
DBMS_WORKLOAD_REPLAY
DIRECTORIES
Packages

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