Tuesday, March 12, 2013

0 Database replay from OEM

Database replay is Oracle 11g new feature and it is part of Oracle Real application testing. Another important feature of the Real Application testing is SQL Performance Analyzer (SPA) . The main difference between SPA and database replay is that we can customize the SQL set within the SPA and tune the SQL base on the SQL statement. Database replay is base on the entire system and consider the concurrence.

Create the test environment

mkdir /u01/app/oracle/db_replay
 
 
CREATE OR REPLACE DIRECTORY db_replay_dir AS '/u01/app/oracle/db_replay';
CREATE USER db_replay1 IDENTIFIED BY db_replay1 QUOTA UNLIMITED ON users;
GRANT CONNECT, CREATE TABLE TO db_replay1;

image

Capture the workload

Database Replay is under the “Software and Support

image_thumb[1]

check the acknowledge box and click next.

image

Because it is for testing only, I will just skip bouncing server and not capture SQL tuning set

image

Select the directory name form the drop down.

image

Make sure the job name is the one you want and fill in the Host credentials.

image

Click submit the job.

 

image

image

Lets prepare some work load.

CREATE TABLE tb_db_replay1 (
  id           NUMBER,
  description  VARCHAR2(50),
  CONSTRAINT PK_tb_db_replay1 PRIMARY KEY (id)
);
 
BEGIN
  FOR i IN 1 .. 300000 LOOP
    INSERT INTO tb_db_replay1 (id, description)
    VALUES (i, 'Desc for ' || i);
  END LOOP;
  COMMIT;
END;
/

We can monitor the workload progress.

image

image

After the processes completed, just click to stop the workload capture.

image

image

Preprocess Captured Workload

Copy the work load files to the target host

mkdir /u01/app/oracle/db_replay
CREATE OR REPLACE DIRECTORY db_replay_dir AS '/u01/app/oracle/db_replay';
scp -r /u01/app/oracle/db_replay/.  panda1:/u01/app/oracle/db_replay

 

Preprocesses the workload file. The process work is resource intensive, so it is better to run on non-production system.

image

image

image

image

image

Replay the workload

image

image

image

image

image

image

image

Start the workload client

$ORACLE_HOME/bin/wrc system/XXXXX@oradb1 mode=replay replaydir=/u01/app/oracle/db_replay

image

image

image

image

image

image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates