Capture using the DBMS_WORKLOAD_CAPTURE Package
The DBMS_WORKLOAD_CAPTURE package provides a set of procedures and functions to control the capture process. Before we can initiate the capture process we need an empty directory on the “ORCL” database server to hold the capture logs.
[oracle@localhost ~]$ mkdir /u01/app/oracle/paw_db_replay_capture
Next, we create a directory object pointing to the new directory.
SQL> CONN sys/sys@ORCL AS SYSDBA
Connected.
SQL> CREATE OR REPLACE DIRECTORY paw_db_replay_capture_dir AS ‘/u01/app/oracle/paw_db_replay_capture/’;
Directory created.
- Make sure existing processes are complete.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2217992 bytes
Variable Size 616564728 bytes
Database Buffers 213909504 bytes
Redo Buffers 6590464 bytes
Database mounted.
Database opened.
Notice the inclusion of a shutdown and startup of the database. This is not necessary, but Oracle suggest it as a good way to make sure any outstanding processes are complete before starting the capture process.
The combination of the ADD_FILTER procedure and the DEFAULT_ACTION parameter of the START_CAPTURE procedure allow the workload to be refined by including or excluding specific work based on the following attributes:
- INSTANCE_NUMBER
- USER
- MODULE
- ACTION
- PROGRAM
- SERVICE
For simplicity let’s assume we want to capture everything, so we can ignore this and jump straight to the START_CAPTURE procedure. This procedure allows us to name a capture run, specify the directory the capture files should be placed in, and specify the length of time the capture process should run for. If the duration is set to NULL, the capture runs until it is manually turned off using the FINISH_CAPTURE procedure.
SQL>
BEGIN
DBMS_WORKLOAD_CAPTURE.start_capture (name => ‘paw_test_capture_1’,
dir => ‘PAW_DB_REPLAY_CAPTURE_DIR’,
duration => NULL);
END;
/
PL/SQL procedure successfully completed.
Now, we need to do some work to capture. First, we create a test user.
SQL> CREATE USER paw_db_replay_user IDENTIFIED BY paw_db_replay_user QUOTA UNLIMITED ON users;
User created.
SQL> GRANT CONNECT, RESOURCE, DBA TO paw_db_replay_user;
Grant succeeded.
Next, we create a table in “ paw_db_replay_user “ and insert some data in this table.
SQL> CONN paw_db_replay_user/paw_db_replay_user@ORCL
Connected.
SQL>
CREATE TABLE paw_db_replay_tab
(
id NUMBER,
description VARCHAR2(50),
CONSTRAINT paw_db_replay_tab_pk PRIMARY KEY (id)
);
Table created.
SQL>
BEGIN
FOR i IN 1 .. 500000 LOOP
INSERT INTO paw_db_replay_tab (id, description)
VALUES (i, ‘Description for ‘ || i);
END LOOP;
COMMIT;
END;
/
PL/SQL procedure successfully completed.
Once the work is complete we can stop the capture using the FINISH_CAPTURE procedure.
SQL> CONN sys/sys@ORCL AS SYSDBA
Connected.
SQL>
BEGIN
DBMS_WORKLOAD_CAPTURE.finish_capture;
END;
/
PL/SQL procedure successfully completed.
If we check out the capture directory, we can see that some files have been generated there.
[oracle@localhost oracle]$ cd /u01/app/oracle/paw_db_replay_capture
[oracle@localhost paw_db_replay_capture]$ ll
total 8
drwxr-xr-x 2 oracle oinstall 4096 Feb 26 14:34 cap
drwxr-xr-x 3 oracle oinstall 4096 Feb 26 14:31 capfiles
[oracle@localhost capfiles]$ cd ..
[oracle@localhost paw_db_replay_capture]$ cd cap
[oracle@localhost cap]$ ll
total 52
-rw-r–r– 1 oracle oinstall 29160 Feb 26 14:34 wcr_cr.html
-rw-r–r– 1 oracle oinstall 10900 Feb 26 14:34 wcr_cr.text
-rw-r–r– 1 oracle oinstall 211 Feb 26 14:34 wcr_fcapture.wmd
-rw-r–r– 1 oracle oinstall 103 Feb 26 14:31 wcr_scapture.wmd
[oracle@localhost cap]$ cd ..
[oracle@localhost paw_db_replay_capture]$ cd capfiles/
[oracle@localhost capfiles]$ ll
total 4
drwxr-xr-x 12 oracle oinstall 4096 Feb 26 14:31 inst1
[oracle@localhost capfiles]$ cd inst1/
[oracle@localhost inst1]$ ll
total 40
drwxr-xr-x 2 oracle oinstall 4096 Feb 26 14:34 aa
drwxr-xr-x 2 oracle oinstall 4096 Feb 26 14:31 ab
drwxr-xr-x 2 oracle oinstall 4096 Feb 26 14:31 ac
drwxr-xr-x 2 oracle oinstall 4096 Feb 26 14:31 ad
drwxr-xr-x 2 oracle oinstall 4096 Feb 26 14:31 ae
drwxr-xr-x 2 oracle oinstall 4096 Feb 26 14:31 af
drwxr-xr-x 2 oracle oinstall 4096 Feb 26 14:31 ag
drwxr-xr-x 2 oracle oinstall 4096 Feb 26 14:31 ah
drwxr-xr-x 2 oracle oinstall 4096 Feb 26 14:31 ai
drwxr-xr-x 2 oracle oinstall 4096 Feb 26 14:31 aj
[oracle@localhost inst1]$ cd aa
[oracle@localhost aa]$ ll
total 12
-rw-r–r– 1 oracle oinstall 1988 Feb 26 14:32 wcr_d054dh0000006.rec
-rw-r–r– 1 oracle oinstall 4062 Feb 26 14:34 wcr_d0557h0000009.rec
-rw-r–r– 1 oracle oinstall 1651 Feb 26 14:34 wcr_d058ph000000h.rec
[oracle@localhost aa]$ cd ..
[oracle@localhost inst1]$ cd ab
[oracle@localhost ab]$ ll
total 0
[oracle@localhost ab]$ cd ..
[oracle@localhost inst1]$ cd ac
[oracle@localhost ac]$ ll
total 0
[oracle@localhost ac]$ cd ..
[oracle@localhost inst1]$ cd ad
[oracle@localhost ad]$ ll
total 0
[oracle@localhost ad]$ cd ..
[oracle@localhost inst1]$ cd ae
[oracle@localhost ae]$ ll
total 0
[oracle@localhost ae]$ cd ..
[oracle@localhost inst1]$ cd af
[oracle@localhost af]$ ll
total 0
[oracle@localhost af]$ cd ..
[oracle@localhost inst1]$ cd ag
[oracle@localhost ag]$ ll
total 0
[oracle@localhost ag]$ cd ..
[oracle@localhost inst1]$ cd ah
[oracle@localhost ah]$ ll
total 0
[oracle@localhost ah]$ cd ..
[oracle@localhost inst1]$ cd ai
[oracle@localhost ai]$ ll
total 0
[oracle@localhost ai]$ cd ..
[oracle@localhost inst1]$ cd aj
[oracle@localhost aj]$ ll
total 0
[oracle@localhost aj]$ cd ..
[oracle@localhost inst1]$
We can retrieve the ID of the capture run by passing the directory object name to the GET_CAPTURE_INFO function, or by querying the DBA_WORKLOAD_CAPTURES view.
[oracle@localhost inst1]$ sqlplus sys/sys@ORCL as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 26 15:00:52 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL> SELECT DBMS_WORKLOAD_CAPTURE.get_capture_info(‘PAW_DB_REPLAY_CAPTURE_DIR’)FROM dual;
DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO(‘PAW_DB_REPLAY_CAPTURE_DIR’)
——————————————————————-
1
1 row selected.
SQL> COLUMN name FORMAT A30
SQL> SELECT id, name FROM dba_workload_captures;
ID NAME
———- ——————————
1 paw_test_capture_1
1 row selected.
The DBA_WORKLOAD_CAPTURES view contains information about the capture process. This can be queried directly, or a report can be generated in text or HTML format using the REPORT function.
SQL>
SQL> DECLARE
l_report CLOB;
BEGIN
l_report := DBMS_WORKLOAD_CAPTURE.report(capture_id => 1,
format => DBMS_WORKLOAD_CAPTURE.TYPE_HTML);
END;
/
PL/SQL procedure successfully completed.
The capture ID can be used to export the AWR snapshots associated with the specific capture run.
SQL>
BEGIN
DBMS_WORKLOAD_CAPTURE.export_awr (capture_id => 1);
END;
/
PL/SQL procedure successfully completed.
A quick look at the capture directory shows a dump file, associated log file and a graph file have been produced.
[oracle@localhost inst1]$ cd /u01/app/oracle/paw_db_replay_capture
[oracle@localhost paw_db_replay_capture]$ ll
total 8
drwxr-xr-x 2 oracle oinstall 4096 Feb 26 15:07 cap
drwxr-xr-x 3 oracle oinstall 4096 Feb 26 14:31 capfiles
[oracle@localhost paw_db_replay_capture]$ cd cap
[oracle@localhost cap]$ ll
total 5612
-rw-r—– 1 oracle oinstall 5648384 Feb 26 15:07 wcr_ca.dmp
-rw-r–r– 1 oracle oinstall 18639 Feb 26 15:07 wcr_ca.log
-rw-r—– 1 oracle oinstall 12288 Feb 26 15:07 wcr_cap_uc_graph.extb
-rw-r–r– 1 oracle oinstall 29160 Feb 26 14:34 wcr_cr.html
-rw-r–r– 1 oracle oinstall 10900 Feb 26 14:34 wcr_cr.text
-rw-r–r– 1 oracle oinstall 211 Feb 26 14:34 wcr_fcapture.wmd
-rw-r–r– 1 oracle oinstall 103 Feb 26 14:31 wcr_scapture.wmd
Thank you for Reading…This is Airy… Enjoy 🙂