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