Staring with Oracle 10g, Oracle
introduced database
replay where you can avoid using artificial test data by grabbing
real-world workloads. This allows the DBA more flexibility than ever
before, and increases the validity of pre-production testing.
Workload-based optimization is an important
part of
global SQL optimization,
and Oracle codified the "holistic" approach to Oracle tuning, most directly in
the
11g SQL Performance Analyzer,
called "SPA". See these related notes on workload-based testing:
Workload management is a major new approach
for Oracle, away from contrived test cases, and into testing with empirical,
real-world workloads.
Capturing a workload
First, see
11g SQL Performance
Analyzer tips for a detailed discussion of capturing a SQL tuning set.
Ahmed Baraka notes these steps in
capturing a SQL workload in 11g:
Preparing for Capturing Workload
Before you capture the workload, perform the following steps:
1. Backup database data that you want to test. Use either RMAN,
user-managed online backup, Data Pump utilities or a snapshot standby. Output
files of this backup will be used for the replay process.
2. Any transaction that is underway when you start capturing the
workload may not be captured. If you want to capture all transactions, you can
restart the database in restricted mode, start the capture process, then open
the database for users.
3. Create directory object for storing captured workload.
Create directory WORKLOAD_DIR as
'C:\Oracle\admin\ora11g\workload';
4. Decide whether some of the user sessions should not be
captured. You may not need to capture DBA sessions, Oracle Enterprise Manager
sessions or any sessions created by third party sessions. To achieve this task,
use
DBMS_WORKLOAD_CAPTURE
package as in the following guidelines:
a. Use
ADD_FILTER
procedure to add any eliminating sessions
based on USER,
MODULE,
ACTION,
PROGRAM,
SERVICE
or
INSTANCE_NUMBER:
BEGIN
DBMS_WORKLOAD_CAPTURE.ADD_FILTER(
FNAME => 'FILTER_DBA1',
FATTRIBUTE => 'USER',
FVALUE => 'SYSTEM,DBSNMP' );
END;
b. Use
DELETE_FILTER
procedure to delete any existing filter:
EXEC DBMS_WORKLOAD_CAPTURE.DELETE_FILTER( FNAME =>
'FILTER_DBA1');
Capturing Workload
Use
START_CAPTURE
procedure in
DBMS_WORKLOAD_CAPTURE
package to start capturing the workload:
BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE(
NAME => '1JAN_WORKLOAD',
DIR => 'WORKLOAD_DIR',
DURATION => 40); -- duration in minutes
END;
To stop the capture process before ending of duration period,
issue the following command:
BEGIN
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE;
END;
After capture process finishes, you can issue query about
workload captures using the following command:
SELECT ID, NAME, STATUS, ERROR_MESSAGE FROM
DBA_WORKLOAD_CAPTURES;
You can generate a report about the workload capture you have
made:
DECLARE
v_capture_id number;
v_capture_rpt clob;
BEGIN
v_capture_id := DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO(DIR => 'WORKLOAD_DIR');
v_capture_rpt := DBMS_WORKLOAD_CAPTURE.REPORT( CAPTURE_ID =>
v_capture_id , FORMAT => DBMS_WORKLOAD_CAPTURE.TYPE_TEXT); -- format could also
be TYPE_HTML
-- display contents of v_capture
END;
Alternatively, you can use the following statements:
SELECT id, name, status FROM DBA_WORKLOAD_CAPTURES؛
SELECT DBMS_WORKLOAD_CAPTURE.REPORT(1, 'HTML') FROM DUAL;
If you want to delete from its data dictionary views, used the procedure
DELETE_CAPTURE_INFO.
However, this procedure does not delete the workload capture files in its
directory. If you want to take a new workload capture with the same name, you
should manually get rid of its files otherwise an error will be returned when
you execute START_CAPTURE
procedure.
See my related notes on Oracle workload techniques:
