11G
Database Replay using the command line
User Rating: / 0
- Details
-
Parent Category: Articles
-
Created on Wednesday, 24 March 2010 22:58
-
Last Updated on Monday, 12 March 2012 13:54
-
Published on Wednesday, 24 March 2010 22:58
-
Written by Guy Lambregts
-
Hits: 6201
Database Replay using the command line interface
1. Capture workload towards a directory
2. Process and optionally move the captured workload towards another environment
3. Have the workload replayed
4. Compare performance statistics between source configuration and target configuration. Source and target are obviously different enviornments. The difference can be.
a) a change in Oracle version ( test impact upgrade )
b) a change in one of some of the many different Oracle instance initialization parameters affecting performance.
c) a change of optimizer statistics
The idea is to become aware of the possible impact of a configuration change and this in time.
It is time for some workload gym. ( fasten seatbelts ). The initial configuration is an instance with a limited SGA + PGA of 300 Mb and a db_file_multiblock_read_count of 16
SQL> startup restrict;
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1347020 bytes
Variable Size 192938548 bytes
Database Buffers 113246208 bytes
Redo Buffers 6328320 bytes
Database mounted.
Database opened.
SQL> create or replace directory WORKLOAD as 'C:\oracle\Workload'
SQL> grant read write on directory workload to public;
SQL> create restore point before_workload_07 guarantee flashback database;
Restore point created.
SQL> show parameter memory_target
NAME TYPE VALUE
memory_target big integer 300M
SQL> show parameter db_file_multiblock_read_count;
NAME TYPE VALUE
db_file_multiblock_read_count integer 16
We are not going to capture the entire load of our database, in stead we will apply a filter. With filter we can exclude or include particular sessions, modules, actions, services, users and programs.
If INCLUDE is used, by default all user requests to the database will be captured, except for the part of the workload defined by the filters. If EXCLUDE is used, by default no user request to the database will be captured, except for the part of the workload defined by the filters.
begin
dbms_workload_capture.add_filter(fname=>'SYSMAN',fattribute=>'USER',fvalue=>'PM');
end;
/
PL/SQL procedure successfully completed.
SQL> select * from dba_workload_filters;
CAPTURE 1 USED
SYSMAN
USER
SYSMAN
CAPTURE 12 USED
SYSMAN
USER
SYSMAN
CAPTURE 12 USED
ORACLE MANAGEMENT SERVICE (DEFAULT)
PROGRAM
OMS
CAPTURE 12 USED
ORACLE MANAGEMENT AGENT (DEFAULT)
PROGRAM
emagent%
CAPTURE 40 USED
SYSMAN
USER
SYSMAN
CAPTURE 30 USED
SYSMAN
USER
SYSMAN
CAPTURE 61 USED
SYSMAN
USER
SYSMAN
CAPTURE 62 USED
SYSMAN
USER
SYSMAN
CAPTURE 63 USED
SYSMAN
USER
SYSMAN
CAPTURE 0 NEW
SYSMAN
USER
PM
We start the workload
begin
dbms_workload_capture.start_capture (
name => 'WORKLOAD_06',
dir=>'WORKLOAD',
duration=>null,
default_action=>'EXCLUDE',
auto_unrestrict=>TRUE);
end;
/
PL/SQL procedure successfully completed.
SQL> select id,name,directory,status from dba_workload_captures;
1
My First Workload Capture
WORKLOAD COMPLETED
30
WORKLOAD_01
WORKLOAD COMPLETED
61
WORKLOAD_03
WORKLOAD COMPLETED
63
WORKLOAD_05
WORKLOAD COMPLETED
40
WORKLOAD_02
WORKLOAD COMPLETED
64
WORKLOAD_06
WORKLOAD IN PROGRESS
12
CAPTURE-PLATINUM-20090706210047
WORKLOAD COMPLETED
62
WORKLOAD_04
WORKLOAD COMPLETED
8 rows selected.
To make myself familiar with the workload capture and replay I have defined a sample fantasie schema and a workload_capture.sql script which I used to simulate some database load.
See Database Replay Schema
Obviously in real database situations the idea is to capture a representative database load ( and to have it replayed afterwards ). To enable performance comparisons some AWR snapshots are automatically taken.
From multiple clients we launch
SQL> start C:\Data\Support\WEBSITE\workload_capture.sql;
SQL> select id,AWR_BEGIN_SNAP,AWR_END_SNAP from dba_workload_captures;
1
30 94 96
61 101 102
63
40
64 103
12
62
We manuallly stop the capturing process
BEGIN
DBMS_WORKLOAD_CAPTURE.finish_capture;
END;
/
PL/SQL procedure successfully completed.
SQL> select id,AWR_BEGIN_SNAP,AWR_END_SNAP from dba_workload_captures;
1
30 94 96
61 101 102
63
40
64 103 104
12
62
SQL> set pagesize 9999
SQL> select id,name,directory,status from dba_workload_captures order by id;
1
My First Workload Capture
WORKLOAD COMPLETED
12
CAPTURE-PLATINUM-20090706210047
WORKLOAD COMPLETED
30
WORKLOAD_01
WORKLOAD COMPLETED
40
WORKLOAD_02
WORKLOAD COMPLETED
61
WORKLOAD_03
WORKLOAD COMPLETED
62
WORKLOAD_04
WORKLOAD COMPLETED
63
WORKLOAD_05
WORKLOAD COMPLETED
64
WORKLOAD_06
WORKLOAD COMPLETED
Below what i call with my own words the post capturing info
SQL> select dbms_workload_capture.get_capture_info('WORKLOAD') from dual;
DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO('WORKLOAD')
64
DECLARE
l_report CLOB;
BEGIN
l_report := DBMS_WORKLOAD_CAPTURE.report(capture_id => 64,
format => DBMS_WORKLOAD_CAPTURE.TYPE_HTML);
END;
/
PL/SQL procedure successfully completed.
SQL> exec dbms_workload_capture.export_awr(capture_id=>64);
PL/SQL procedure successfully completed.
I flashback the database back to the restore point I created and I change the environment. ( I increase the memory allocation )
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 644468736 bytes
Fixed Size 1349236 bytes
Variable Size 167774604 bytes
Database Buffers 469762048 bytes
Redo Buffers 5582848 bytes
Database mounted.
SQL> flashback database to restore point before_workload_06;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> alter system set memory_target=1G scope=spfile;
System altered.
SQL> shutdown immediate;
SQL> startup;
We pre process, initialize and prepare the replay
SQL> BEGIN
2 DBMS_WORKLOAD_REPLAY.process_capture('WORKLOAD');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_WORKLOAD_REPLAY.initialize_replay (replay_name => 'WORKLOAD_02', replay_dir => 'WORKLOAD');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_WORKLOAD_REPLAY.prepare_replay;
3 end;
4 /
PL/SQL procedure successfully completed.
replay session 1
c:\oracle\Workload>wrc mode=calibrate replaydir=C:\oracle\Workload
Workload Replay Client: Release 11.1.0.7.0 - Production on Wed Jul 8 22:37:00 20
09
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Report for Workload in: C:\oracle\Workload
Recommendation:
Consider using at least 1 clients divided among 1 CPU(s)
You will need at least 45 MB of memory per client process.
If your machine(s) cannot match that number, consider using more clients.
Workload Characteristics:
- max concurrency: 12 sessions
- total number of sessions: 13
Assumptions:
- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- 256 KB of memory cache per concurrent session
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE
c:\oracle\Workload>wrc system/
[email protected] mode=replay replaydir=C:\oracle\Workload
Workload Replay Client: Release 11.1.0.7.0 - Production on Thu Jul 9 22:37:26 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Wait for the replay to start (22:37:26)
in another session we start the replay
SQL> begin
2 dbms_workload_replay.start_replay;
3 end;
4 /
PL/SQL procedure successfully completed.
Back to session 1
c:\oracle\Workload>wrc system/
[email protected] mode=replay replaydir=C:\oracle\Workload
Workload Replay Client: Release 11.1.0.7.0 - Production on Thu Jul 9 22:37:26 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Wait for the replay to start (22:37:26)
Replay started (22:38:50) >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Automatically added when replay is started in other session
We can monitor the replay progress
SQL> select name,status from dba_workload_replays;
REPLAY-PLATINUM-20090706214153
COMPLETED
WORKLOAD_02
IN PROGRESS
SQL> select name,status from dba_workload_replays;
REPLAY-PLATINUM-20090706214153
COMPLETED
WORKLOAD_02
COMPLETED
Back to session 1
c:\oracle\Workload>wrc system/
[email protected] mode=replay replaydir=C:\oracle\Workload
Workload Replay Client: Release 11.1.0.7.0 - Production on Thu Jul 9 22:37:26 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Wait for the replay to start (22:37:26)
Replay started (22:38:50)
Replay finished (22:59:08)
The comparison report can be made as indicated below
SQL> select id,name,status from dba_workload_replays;
5
WORKLOAD_02
COMPLETED
DECLARE
l_report CLOB;
BEGIN
l_report := DBMS_WORKLOAD_REPLAY.report(replay_id => 5,
format => DBMS_WORKLOAD_REPLAY.TYPE_HTML);
END;
/
SQL> spool c:\temp\replay_report.html
SQL> select dbms_workload_replay.report(5,'HTML') from dual;
SQL> spool off