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
RESTRICTED SESSION mode
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:
-
SESSION_ID
-
USER
-
MODULE
-
ACTION
-
PROGRAM
-
SERVICE
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
------- -- ------ -----------------------------------
--------------- ---------
CAPTURE 1 USED ORACLE MANAGEMENT SERVICE (DEFAULT)
PROGRAM OMS
CAPTURE 1 USED ORACLE MANAGEMENT AGENT (DEFAULT)
PROGRAM emagent%
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,
name,
status,
start_time,
end_time,
connects,
user_calls,
dir_path
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
11.1.0.6.0 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,
AWR_BEGIN_SNAP,
AWR_END_SNAP
FROM Remote DBA_workload_captures;
ID AWR_BEGIN_SNAP AWR_END_SNAP
---------- -------------- ------------
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. |
