CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_WORKLOAD_CAPTURE
Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/dbmswrr.sql
First Available 11.1
Dependencies
dba_hist_snapshot dbms_swrf_internal gv$instance
dba_workload_captures dbms_swrf_report_internal plitblm
dbms_advisor dbms_workload_capture_lib props$
dbms_lob dbms_workload_replay utl_file
dbms_random dbms_workload_repository v$database
dbms_scheduler dbms_wrr_internal wrr$_captures
Security Model Execute is granted to EXECUTE_CATALOG_ROLE and DBA roles
 
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
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);
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)
SELECT 
FROM 

exec dbms_workload_capture.export_awr(
 
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;
 
IMPORT_AWR
Imports the AWR snapshots from a given capture, provided those AWR snapshots were exported earlier from the original capture system using DBMS_WORKLOAD_CAPTURE EXPORT_AWR. In order 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
 
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);
exec dbms_workload_capture.start_capture('UWCapture', 'CTEMP', 300);

SELECT name, ?
FROM dba_workload_captures;
 
Capture Demo

Go to
DBMS_WORKLOAD_REPLAY to complete the demo.
conn / as sysdba

SELECT COUNT(*)
FROM dba_workload_captures;

CREATE OR REPLACE DIRECTORY ctemp AS 'c: emp';

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

-- do not know where filters are stored

DECLARE
 n NUMBER;
BEGIN
  n := dbms_workload_capture.get_capture_info('CTEMP');
  dbms_output.put_line(n);
END;
/
conn uwclass/uwclass

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
 WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';

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

-- it will fail

exec dbms_workload_capture.start_capture('UWCapture', 'CTEMP', 3000);

-- now start the new SQL*Plus session

conn uwclass/uwclass

exec captest;

x

 
Related Topics
DBMS_APPLICATION_INFO
DBMS_WORKLOAD_REPLAY
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [276 users online]    © 2010 psoug.org