11G

Database Replay using the command line

User Rating:  / 0
PoorBest 
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: 5849

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