Saturday, February 20, 2010

Real Application Testing Database Replay

Apply all Recommended Patches for Database Replay

1. For 11.1.0.6 a patch is recommended for proper database replay functionality. The patch can be download for 11.1.0.6 for you platform from Metalink. For Unix the patch number is 6865809 and the patch for windows 32-bit 7044721
** For other database platforms see Appendix A: Real Application Testing for Earlier Releases

2. Shutdown all databases and listeners running within the ORACLE_HOME being patched.

3. Ensure that opatch utility version 11.1.0.6.3 is installed prior to installing patch set

C:\Oracle\product\11.1.0\db_1\OPatch>.\opatch version
Invoking OPatch 11.1.0.6.2

OPatch Version: 11.1.0.6.2

OPatch succeeded.

4. Change directory to the location where the 7044721 patch was extracted to

5. Apply the patch using the opatch utility
opatch apply

** Ensure that all patch instructions are followed completely including post apply steps. See specific patch notes.



6. Bring all databases and listeners back up.


7. Verify database connectivity and listener status
Setup Source Database for Database Replay Capture
Database Releases prior to 11g can be utilized for capturing a workload for replay on an 11g and above database, however there is some setup that is required. For database release 10.2.0.4 there are no required patches for the capture functionality however for releases prior to 10.2.0.4 a patch is required.
** See Appendix A: Real Application Testing for Earlier Releases, for Versions prior to 10.2.0.4

Run the wrrenbl.sql script, only required for 10.2.0.4 and is optional for other releases prior to 11g:
Script will enable the PRE_11G_ENABLE_CAPTURE database initialization parameter.

a. cd $ORACLE_HOME/rdbms/admin
b. sqlplus / as sysdba
c. SQL> @wrrenbl.sql



Prepare to Capture Workload

On the source database where we will be capturing the workload for replay we need to prepare the database for capture.

1. Create a directory location where the workload capture file can be written.
a. C:\Oracle>mkdir dbcapture

2. Create a directory within the source database where workload is to be captured.
a. SQL> create directory dbcapture as 'c:\oracle\dbcapture' ;

3. Determine the workload to be captured. In this example the workload is artificially generated using a script process.
** see Appendix B: Script that Generates an Artificial Workload



Capture Workload


1. start the capture process
a. SQL> exec dbms_workload_capture.start_capture(’NRML_ACTIVITY’, ’DBCAPTURE’) ;

2. Run the workload
a. This is where the script for the artificial workload is executed

3. When the workload is complete or you have captured the desired workload stop the capture process.
a. SQL> exec dbms_workload_capture.finish_capture() ;

4. Execute a report on the Capture.
** See Appendix C: Generate Workload Capture Report (HTML)



Prepare the Workload for Replay

** This database that processes the captured workload must be an 11.1.0.6 or above database as the packages to process the captured workload are not available in prior releases.

1. Create a directory location for the captured workload to be paced for processing and replay on both the processing database and the replay database if the processing and replay databases are different. Best practice is to process the workload on the same database that will execute the replay.
a. C:\Oracle> mkdir Replay

2. Copy the captured workload files to another directory if on same host as database where workload was captured or to destination system location to be processed.
a. copy C:\Oracle\dbcapture\* C:\Oracle\Replay

3. Create directory in the oracle database that will process the captured workload.
a. SQL> create directory REPLAY as ‘c:\Oracle\Replay’ ;


Process the Workload for Replay
** This is a resource intensive process therefore it should be done on a non-production system. Therefore if you captured the workload on your production system move the capture files to a test or development system to prepare them for replay.

1. Process the captured workload
a. SQL> exec dbms_workload_replay.process_capture ('REPLAY') ;

The process will create the following files in the REPLAY directory during the processing of the captured workload.

wcr_login.pp
WCR_SCN_ORDER.EXTB
WCR_SEQ_DATA.EXTB
WCR_CONN_DATA.EXTB
wcr_process.wmd

Prepare to Replay the Workload
1. Go to database where replay is to be executed if different from database the processed workload. If the replay database is the same as the database that processed the workload skip to step 3 otherwise process step 2.

2. If not done so already go to Prepare workload for Replay Section and execute the steps on the database that will replay the workload.

3. Initialize the replay
a. SQL> exec dbms_workload_replay.initialize_replay ('TEST_REPLAY','REPLAY') ;

4. Prepare for Workload Replay
a. SQL> exec dbms_workload_replay.prepare_replay(synchronization=>TRUE) ;

5. Calibrate the Workload to determine the number of workload replay clients.
a. At the command line change to the replay directory
i. cd C:\Oracle\Replay
ii. c:\Oracle\Replay> wrc mode=calibrate

Workload Replay Client: Release 11.1.0.6.0 - Production on Thu Jul 24 10:38:41 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Report for Workload in: .
-----------------------

Recommendation:
Consider using at least 1 clients divided among 1 CPU(s).

Workload Characteristics:
- max concurrency: 9 sessions
- total number of sessions: 46

Assumptions:
- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE


Execute the Workload Replay
Now that the workload has been captured and processed the workload is now ready to be replayed.

1. Take the number of workload replay clients indicated in the previous section that will be needed to execute the processed workload.

2. Open up a window for each workload replay client that will be needed as these will each be separate executions.
** Only Need 1 client as indicated in the pervious step for the example workload

3. From the command line enter the replay command with the wrc utility for the number of replay clients needed, should be one command in each window.
a. C:\Oracle\proccapture>wrc system/xxxxx@orcl11g replaydir=c:\Oracle\Replay

4. Start each of the replay clients needed.

C:\Oracle\proccapture>wrc system/xxxxx@orcl11g replaydir=c:\Oracle\Replay

Workload Replay Client: Release 11.1.0.6.0 - Production on Thu Jul 24 15:09:15 2
008

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Wait for the replay to start (15:09:15)
Replay started (15:09:40)
Replay finished (16:01:41)


5. AWR information will be available for the time period where the workload apply was executed, it is generally recommended that the workload apply that is executed at least cover 1 complete snap period to provide the most useful data or that a snap is taken prior to the start and immediately after the workload execution. (this is optional)

SQL> execute DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

6. In another window login to the database where the workload apply is to be execute and start the workload apply process. You will see an indication that the workload has been started.

$ sqlplus /@orcl11g

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jul 24 13:55:39 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exec dbms_workload_replay.start_replay ;

PL/SQL procedure successfully completed.


7. Monitor the workload apply until completed. AWR information will be available for the time period where the workload apply was executed, it is generally recommended that the workload apply that is executed at least cover 1 complete snap period to provide the most useful data or that a snap is taken prior to the start and immediately after the workload execution.


8. Once the workload apply is complete optionally take another AWR snaphot.

SQL> execute DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();


9. Execute workload replay report extraction and evaluate the results from the report.
** See Appendix D: Generate Workload Replay Report (HTML)




Appendix A: Real Application Testing for Earlier Releases

See Metalink Note: 560977.1


Appendix B: Script that Generates an Artificial Workload

-- create_workload.sql
-- put in sql script to be executed to generate workload.
create table orders tablespace users as select * from oe.orders ;

create table order_items tablespace users as select * from oe.order_items ;


-- insert more rows into orders and order items to bulk up amount of data for
-- a more true high volume test.
declare
cursor my_cur is
select * from oe.orders ;
lcnt number ;
v_order_id oe.orders.order_id%type ;
v_order_date oe.orders.order_date%type ;
v_order_mode oe.orders.order_mode%type ;
v_customer_id oe.orders.customer_id%type ;
v_order_status oe.orders.order_status%type ;
v_order_total oe.orders.order_total%type ;
v_sales_rep_id oe.orders.sales_rep_id%type ;
v_promotion_id oe.orders.promotion_id%type ;
cursor my_cur_2 is
select line_item_id, product_id, unit_price, quantity
from oe.order_items where order_id = v_order_id ;
v_line_item_id oe.order_items.line_item_id%type ;
v_product_id oe.order_items.product_id%type ;
v_unit_price oe.order_items.unit_price%type ;
v_quantity oe.order_items.quantity%type ;

begin
select max(order_id) + 1 into lcnt from orders ;

loop
if lcnt = 10000 then
exit ;
end if ;

open my_cur ;
loop
fetch my_cur into v_order_id, v_order_date, v_order_mode, v_customer_id,
v_order_status, v_order_total, v_sales_rep_id, v_promotion_id ;
exit when my_cur%notfound ;

insert into orders values (lcnt, v_order_date, v_order_mode, v_customer_id,
v_order_status, v_order_total, v_sales_rep_id, v_promotion_id) ;
commit ;

open my_cur_2 ;
loop
fetch my_cur_2 into v_line_item_id, v_product_id, v_unit_price, v_quantity ;
exit when my_cur_2%notfound ;

insert into order_items values (lcnt, v_line_item_id, v_product_id, v_unit_price, v_quantity) ;
end loop ;
close my_cur_2 ;

lcnt := lcnt + 1 ;
end loop ;

close my_cur ;
end loop ;
end ;
/


Appendix C: Generate Workload Capture Report (HTML)


-- Capture Report
DECLARE
cap_id NUMBER;
cap_rpt CLOB;
buffer VARCHAR2(32767);
buffer_size CONSTANT BINARY_INTEGER := 32767;
amount BINARY_INTEGER;
offset NUMBER(38);
file_handle UTL_FILE.FILE_TYPE;
directory_name CONSTANT VARCHAR2(80) := 'DBCAPTURE';
v_filename CONSTANT VARCHAR2(80) := 'Capture.html';

BEGIN
cap_id := DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO(dir=>directory_name);
cap_rpt := DBMS_WORKLOAD_CAPTURE.REPORT(capture_id => cap_id,
format => DBMS_WORKLOAD_CAPTURE.TYPE_HTML);

-- Write Report to file
DBMS_OUTPUT.ENABLE(100000);

-- --------------------------------
-- OPEN NEW XML FILE IN WRITE MODE
-- --------------------------------
file_handle := UTL_FILE.FOPEN(location => directory_name,
filename => v_filename,
open_mode => 'w',
max_linesize => buffer_size);

amount := buffer_size;
offset := 1;

WHILE amount >= buffer_size
LOOP
DBMS_LOB.READ(lob_loc => cap_rpt,
amount => amount,
offset => offset,
buffer => buffer);

offset := offset + amount;

UTL_FILE.PUT(file => file_handle,
buffer => buffer);

UTL_FILE.FFLUSH(file => file_handle);
END LOOP;

UTL_FILE.FCLOSE(file => file_handle);
END;
/



Appendix D: Generate Workload Replay Report (HTML)

-- Replay Report
DECLARE
cap_id NUMBER;
rep_id NUMBER;
rep_rpt CLOB;
buffer VARCHAR2(32767);
buffer_size CONSTANT BINARY_INTEGER := 32767;
amount BINARY_INTEGER;
offset NUMBER(38);
file_handle UTL_FILE.FILE_TYPE;
directory_name CONSTANT VARCHAR2(80) := 'PROCCAPTURE';
v_filename CONSTANT VARCHAR2(80) := 'Replay.html';

BEGIN
cap_id := DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(dir => 'PROCCAPTURE');

select max(id)
into rep_id
from dba_workload_replays
where capture_id = cap_id ;

rep_rpt := DBMS_WORKLOAD_REPLAY.REPORT(replay_id => rep_id,
format => DBMS_WORKLOAD_REPLAY.TYPE_HTML);


-- Write Report to file
DBMS_OUTPUT.ENABLE(100000);

-- --------------------------------
-- OPEN NEW XML FILE IN WRITE MODE
-- --------------------------------
file_handle := UTL_FILE.FOPEN(location => directory_name,
filename => v_filename,
open_mode => 'w',
max_linesize => buffer_size);

amount := buffer_size;
offset := 1;

WHILE amount >= buffer_size
LOOP
DBMS_LOB.READ(lob_loc => rep_rpt,
amount => amount,
offset => offset,
buffer => buffer);

offset := offset + amount;

UTL_FILE.PUT(file => file_handle,
buffer => buffer);

UTL_FILE.FFLUSH(file => file_handle);
END LOOP;

UTL_FILE.FCLOSE(file => file_handle);
END;
/

16 comments:

Anonymous said...

I am trying to test Real Application for my work to do evaluation.
I got confuse. The source database has to has the same schema/tables as the destionation database right?
For example, I want to do capture in source db, I have to say have schema "ABC" and 30 tables with data.
Then the destination db has to have schema "ABC" and that exact 30 tables with exact data right?

Mike Messina said...

For Real Application Testing the database that will replay the workload should be a copy of the database at a point in time prior to the the execution of the workload captured at the source.

Anonymous said...

Thanks a lot for clearing my concept.

Anonymous said...

Hi Michael,
For Real application testing db replay,
it has to have 2 machines right?
It can't be done by using one machine only. My boss is asking me this question.

Thanks,

Mike Messina said...

Technically you could capture and replay on the same server. if you choose to use the same server there are a couple of ways you could approach this. Backup the databases, Execute a workload and capture it. Then recover the database prior to the start of the execution of the workload captured and then replay the workload against the recovered database. Another approach would be to duplicate the database on the same host prior to execute of workload to be captured. Execute the workload and capture it, the process and execute the workload on the caopy of the database you made. Now keep in mind that an additional database will consume more resources on the server which could skew your results so take that into consideration are make sure that the resource use is consistent for the replay as it was for the capture.

Anonymous said...

Hi Mike,
Thanks for your help, I make my Real application work on same 11g to 11g database. Now, I like to try out the 10g(capture in 10g) and then replay in oracle 11g. (have not done this one yet).
Now, I understand more on the capture and replay workload.
But I read about something call
SQL performancer analyzer, now I get confuse. It is another feature ??

I try it too, it work.
But somehow, I still don't understand when to use the capture-and-replay and when to use the SQL performancer analyzer?

Thanks

Dominica

Anonymous said...

Hi Mike,

I figure it out now.
Ok, I got the different and when to use capture/replay and when to use
sql performancer analyzer.

now there is new question about
if I use the API to generate a workload capture report. I saw you have some "code" to write to a file.
I guess I have to "write that report to file " myself, Oracle won't do it ??

Pretty confusing.



Thanks,

Dominica

Anonymous said...

I just try out your code to generate the workload capture report and dump it out.
It works.

Thank you,


Dominica

Mike Messina said...

Glad the code worked for you and you were able to work out the differences in Replay and SQL Performance Analyzer as they are both Part of the Real Application Testing with 11g.

Anonymous said...

Thanks Mike,
Now, I am curious, I would like to do "capture" on the primary of the dataguard pair, I have not tested it yet.
I think I will make sure, if I create subdirectory in the os level.
will immediately create in the standby dbserver,otherwise, oracle might error out. when I execute "create subdirectory as '/home/oracle/testworkload';

Because I did read other people 's document or step-by-step, everyone is talk about a standalone oracle (on the capture process).


Dominica

Mike Messina said...

I have not tried a capture on a primary of a dataguard configuration, but based on what I understand of the capture process the standby should not be affected by a capture on the primary. Now the Oracle Directory you create will goto the standby and I would make sure that directory existing on my standby server as a precaution.

Anonymous said...

Thanks a lot for your help/reply.
By the way, now I got my SQL performance analyzer testing also working very well now.

Thanks

Dominica L

Anonymous said...

Hi Mike,

So the SQL performance analyzer does not really "run" sql statement right ?
Do I understand correctly, it is only a statisical benchmarking tool for the SQL in a database??



Dominica L

Mike Messina said...

Based on the testing I have done with SQL Performance Analyzer it does execute sql. You collect a SQL tuning Set and then execute that SQL tuning Set to see the different results. I like to look at the SPA as a targeted way of analyzing the SQL differences where the Database Replay is for your entire workload.

Anonymous said...

Hi Mike,

I know why now.
if the execution_type => "test execute" is used, then it will actually run SQL statement,
There are other 3 options for this parameter: "explain plan, compare performance, convert sqlset". If I use "explain plan", it will not run the SQL.

thanks,

Dominica

Mike Messina said...

I never use just the compare of explain plans using SQL Performance Analyzer as we been able to to that long before SQL Performance analyzer, what I find most valuable is user response differences as those will always be the judge of good performance and that only really comes from execution.