Oracle 11g:
How
to use the Database Replay at Oracle 11g database
What is the database replay?
Simply, the database replay process captures a sequence of Oracle DML in
a file in a capture directory. Then you can replay the file against the
same database or a different one to execute the same DML.
Why do I need to do that?
You may use this to test performance problem at your environment.
Assuming that you want to change a parameter at your production database
but you have no idea about its result. You can capture a peak time DML
from your production and replay it as many you want at your test
environment.
How do I do that?
Create capture directory
$ cd /home
$ mkdir dircapture
Create a directory object in the database
SQL>
CREATE DIRECTORY dircapture AS "/home/dirdirectory";
One hour before your peak time
start capture
using the following procedure. We are using NULL for duration so we stop
the capture when we want.
SQL> BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE (NAME => "dml_capture",
DIR => "DIRCAPTURE",
DURATION => NULL);
END;
/
Once you pass the peak time or you are satisfy. You can
stop the capture
using the following procedure.
SQL> BEGIN
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE;
END;
/
If replay performs on different server, FTP the file into created replay
directory.
Assuming that you have FTP the
dml_capture
file with a new name
dml_replay:
To create replay directory:
$ cd /home
$ mkdir
dirreplay
Create a directory object in the database
SQL>
CREATE DIRECTORY
dirreplay
AS "/home/dirreplay";
Tell your database where that file is:
Notice that your directory must be upper case.
SQL> BEGIN
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY (REPLAY_NAME=>"dml_reply",
REPLAY_DIR => "DIRREPLAY";
END;
/
Then start
replay
process.
SQL> EXEC DBMS_WORKLOAD_REPLAY.START_REPLAY();
Check your performance and compare your benchmark.
Good Luck!
|