BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation









  Preparing the System for Capturing the Workload

Oracle 11g New Features Tips by Burleson Consulting
June 27, 2008

Oracle 11g SQL New Features Tips

First, capture directory object in the database:

Lutz @ orcl11g as sysRemote DBA SQL> CREATE DIRECTORY workload_dir AS '/home/oracle/my_workload_dir';

M           The capture directory must be empty!

In a RAC configuration with local installations on each node, it is possible to capture the workload of each instance into local directories. The capture files in this case must be manually copied into one common location for processing and replay.

The testing system which will be used for the replay of the captured workload must be in a state that is as close as possible to the state of the system where the capture was made. Therefore, make sure that capturing is not started while user transactions are still ongoing.

M If the application is using timestamps for DML, it is necessary to reset the system clock of the testing system to the same time when capture started.

Note the current_scn from v$database before capture starts. This would be the offset SCN for the replay.

If capturing is started without having restricted the database an error will be encountered:

ERROR at line 1:
ORA-15504: cannot start workload capture because instance 1 not present in
ORA-06512: at "SYS.DBMS_WORKLOAD_CAPTURE", line 723
ORA-06512: at line 1
Database Replay

It is strongly recommended to start the database in restricted mode before workload capture starts. If restricted mode is not possible, use the argument no_restart_mode=TRUE, though this is not advisable.

Lutz @ orcl11g as sysRemote DBA SQL> SHUTDOWN IMMEDIATE
Lutz @ orcl11g as sysRemote DBA SQL> STARTUP RESTRICT

Capturing the workload

The workload capture can be named in order to identify it later on:

Lutz @ orcl11g as sysRemote DBA SQL> exec dbms_workload_capture.start_capture - ('my_capture_1','WORKLOAD_DIR'); -- name of the directory must be UPPER CASE!

After starting the capturing, Oracle automatically disables restricted session mode again. From now on, the workload happening on the database will be recorded into capture files in the capture directory. It is possible to filter out parts of the workload by either specifying what to exclude or what to include in the capture.  Here is a description of the ADD_FILTER procedure:

dbms_workload_capture.ADD_FILTER( fname             IN VARCHAR2,
                                  fattribute IN VARCHAR2,
                                  fvalue            IN VARCHAR2)

And here is a code example:

Lutz @ orcl11g as sysRemote DBA SQL> exec dbms_workload_capture.ADD_FILTER - ('hr_filter','USER','HR');

This would include only work done by HR into the workload capture:

Lutz @ orcl11g as sysRemote DBA SQL> exec dbms_workload_capture.start_capture - ('my_capture_2','WORKLOAD_DIR',default_action='INCLUDE');This would exclude all work done by HR from the capture:

Lutz @ orcl11g as sysRemote DBA SQL> exec dbms_workload_capture.start_capture - ('my_capture_2','WORKLOAD_DIR',default_action='EXCLUDE');

M Caution! The default value for default_action is EXCLUDE!

Possible filter attributes are


  • USER





The DELETE_FILTER procedure can be used to remove filters again from the capture.

OMSs and agents are filters by default:

Lutz @ orcl11g as sysRemote DBA SQL>  SELECT * FROM Remote DBA_workload_filters

TYPE    ID STATUS NAME                                ATTRIBUTE       VALUE
------- -- ------ ----------------------------------- --------------- ---------

The capture period should include a representative workload for testing.

This is the syntax for stopping the capturing after the representative workload is finished:

Lutz @ orcl11g as sysRemote DBA SQL> exec dbms_workload_capture.finish_capture()

The data dictionary holds in detail information about the workload captures:

Lutz @ orcl11g as sysRemote DBA SQL> SELECT id,
                               FROM  Remote DBA_workload_captures
                               WHERE id = (SELECT MAX(id)
                                            FROM Remote DBA_workload_captures) ;

It is possible to review a report about the capture by using the report function from the dbms_workload_capture package:

Lutz @ orcl11g as sysRemote DBA SQL> SELECT dbms_workload_capture.report(1,'TEXT')
                               FROM dual;

Database Capture Report For ORCL11G

DB Name         DB Id    Release      RAC Capture Name               Status
------------ ----------- -----------  --- -------------------------- ----------
ORCL11G         2825011450 NO  my_capture_2             COMPLETED

                   Start time: 07-Feb-08 13:18:19 (SCN = 1587787)
                     End time: 07-Feb-08 13:23:29 (SCN = 1685135)
                     Duration: 5 minutes 10 seconds
                 Capture size: 3.81 KB
             Directory object: WORKLOAD_DIR
               Directory path: /home/oracle/my_workload_dir
      Directory shared in RAC: FALSE
                 Filters used: 0

Captured Workload Statistics                        DB: ORCL11G  Snaps: 125-126
-> 'Value' represents the corresponding statistic aggregated
      across the entire captured database workload.
-> '% Total' is the percentage of 'Value' over the corresponding
      system-wide aggregated total.

Statistic Name                                   Value   % Total
---------------------------------------- ------------- ---------
DB time (secs)                                   18.92     22.61
Average Active Sessions                           0.08
User calls captured                                 15     17.65
User calls captured with Errors                      2
Session logins                                       2     11.00
Transactions                                        24    100.00
Workload Filters                                    DB: ORCL11G  Snaps: 125-126 

                  No data exists for this section of the report.

oracle@rhas4 ~]$ ls -la /home/oracle/my_workload_dir 

-rw-r--r--  1 oracle oinstall     1125 Feb 11 13:17 wcr_4gc1wu40025vf.rec
-rw-r-----  1 oracle oinstall     1182 Feb 11 13:17 wcr_4gc1wu40026k6.rec
-rw-r--r--  1 oracle oinstall     1181 Feb 11 13:17 wcr_4gc1wu80026k9.rec
-rw-r--r--  1 oracle oinstall     1111 Feb 11 13:17 wcr_4gc1wuc00268n.rec
-rw-r--r--  1 oracle oinstall      904 Feb 11 13:17 wcr_4gc1wuh0025vj.rec
-rw-r--r--  1 oracle oinstall     1114 Feb 11 13:17 wcr_4gc1wun0025vu.rec
-rw-r--r--  1 oracle oinstall     1114 Feb 11 13:17 wcr_4gc1wv0002688.rec
-rw-r--r--  1 oracle oinstall     4505 Feb 11 13:19 wcr_4gc1wv00026kj.rec
-rw-r--r--  1 oracle oinstall     1115 Feb 11 13:17 wcr_4gc1wvc0025vy.rec
-rw-r--r--  1 oracle oinstall     1111 Feb 11 13:17 wcr_4gc1wvn0025uy.rec
-rw-r--r--  1 oracle oinstall      899 Feb 11 13:17 wcr_4gc1wvn00269v.rec
-rw-r--r--  1 oracle oinstall      918 Feb 11 13:18 wcr_4gc1wzs00268x.rec
-rw-r--r--  1 oracle oinstall     1115 Feb 11 13:18 wcr_4gc1x3s0025wf.rec
-rw-r--r--  1 oracle oinstall     1114 Feb 11 13:18 wcr_4gc1x3s0025wj.rec
-rw-r--r--  1 oracle oinstall     1113 Feb 11 13:18 wcr_4gc1x3s0025x6.rec
-rw-r--r--  1 oracle oinstall     4034 Feb 11 13:20 wcr_4gc1xan0026mz.rec
-rw-r--r--  1 oracle oinstall     1115 Feb 11 13:19 wcr_4gc1xbh0025wm.rec
-rw-r--r--  1 oracle oinstall     1114 Feb 11 13:19 wcr_4gc1xbh0025yf.rec
-rw-r--r--  1 oracle oinstall     1114 Feb 11 13:19 wcr_4gc1xbh0025yh.rec
-rw-r--r--  1 oracle oinstall     1127 Feb 11 13:20 wcr_4gc1xp40025vw.rec
-rw-r--r--  1 oracle oinstall     1020 Feb 11 13:22 wcr_4gc1xxs0026q6.rec
-rw-r-----  1 oracle oinstall 11673600 Feb 11 13:25 wcr_ca.dmp
-rw-r--r--  1 oracle oinstall    16135 Feb 11 13:25 wcr_ca.log
-rw-r-----  1 oracle oinstall    12288 Feb 11 13:26 wcr_conn_data.extb
-rw-r--r--  1 oracle oinstall    52386 Feb 11 13:22 wcr_cr.html
-rw-r--r--  1 oracle oinstall    25536 Feb 11 13:22 wcr_cr.text
-rw-r--r--  1 oracle oinstall      272 Feb 11 13:22 wcr_fcapture.wmd
-rw-r-----  1 oracle oinstall      156 Feb 11 13:26 wcr_login.pp
-rw-r-----  1 oracle oinstall       35 Feb 11 13:26 wcr_process.wmd

During the capture, MMON has created a performance snapshot from the SGA and stored them into the Automatic Workload Repository (AWR).

The view Remote DBA_workload_captures shows which ones were created:

Lutz @ orcl11g as sysRemote DBA SQL> SELECT id,
                               FROM Remote DBA_workload_captures;

---------- -------------- ------------
         1            125          126

To get a detailed report for the snapshot range, run the script $ORACLE_HOME/rdbms/admin/awrrpt.sql. These snapshots can be compared against the snapshots which will be created for the replay of the captured workload on the testing system.

This is an excerpt from the new book Oracle 11g New Features: Expert Guide to the Important New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J. Jain, Brian Carr.

You can buy it direct from the publisher for 30% off.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter