| General Information |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmswrr.sql |
| First Available |
11.1.0.6 |
| 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_SWRF_REPORT_INTERNAL |
PROPS$ |
| DBA_WORKLOAD_CAPTURES |
DBMS_WORKLOAD_CAPTURE_LIB |
PRVT_SMGUTIL |
| DBMS_ADVISOR |
DBMS_WORKLOAD_REPLAY |
UTL_FILE |
| DBMS_LOB |
DBMS_WORKLOAD_REPOSITORY |
V$DATABASE |
| DBMS_RANDOM |
DBMS_WRR_INTERNAL |
WRR$_CAPTURES |
| DBMS_SCHEDULER |
GV$INSTANCE |
WRR$_CAPTURE_UC_GRAPH |
| DBMS_SWRF_INTERNAL |
PLITBLM |
|
|
| 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); |
| TBD |
| |
EXPORT_UC_GRAPH (new 11.2.0.1)  |
| Undocumented |
dbms_workload_capture.export_uc_graph(capture_id IN NUMBER); |
| TBD |
| |
| 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 (new 11.2.0.1) |
| Returns the full path to the 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;
/ |
| |
| 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 |
| |
| PORT_UC_GRAPH (new 11.2.0.1) |
| Undocumented |
dbms_workload_capture.import_uc_graph(capture_id IN 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 (new 11.2.0.2 parameters)  |
| 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);
SELECT name, ?
FROM dba_workload_captures; |
| |
| USER_CALLS_GRAPH (new 11.2.0.1) |
| 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 / 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
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
-- it will fail |
exec dbms_workload_capture.start_capture('UWCapture', 'CTEMP', 3000);
-- now start the new SQL*Plus session
conn uwclass/uwclass
exec captest; |
DECLARE
n NUMBER;
BEGIN
n := dbms_workload_capture.get_capture_info('CTEMP');
dbms_output.put_line(n);
END;
/ |
