Oracle | DBMS_WORKLOAD

Oracle DBMS_WORKLOAD_CAPTURE 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 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_WORKLOAD_CAPTURES	DBMS_WORKLOAD_CAPTURE_I	DBMS_WRR_PROTECTED
DBMS_RAT_MASK	DBMS_WRR_INTERNAL	WRR$_CAPTURE_UC_GRAPH

Documented Yes

First Available 11.1

Security Model Owned by SYS with EXECUTE granted to the DBA and EXECUTE_CATALOG_ROLE 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

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

-- we have looked, 
      but nof found, where workload capture filters are stored

      

      exec dbms_workload_capture.delete_filter('UWFilter');

ENCRYPT_CAPTURE

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;

      /

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

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

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

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;

SELECT * FROM TABLE(dbms_workload_capture.user_calls_graph(42));

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;

      /