The Real Application testing, Database Replay process

The Real Application testing, Database Replay process

The Real Application testing feature, which consists of two separate tools, Database Replay and the SQL performance Analyzer, is arguably the most significant new feature in the Oracle Database 11.1 Release. With this process we can simulate a real production workload on a test system; we can apply some patches, change data storage and see how our performance changes on test system.
In this post we step by step review Database replay process

1. Restart prod DB and startup restrict mode (this in optional)
2. We can use optional workload filters to restrict the workload capture to only a part of the actual production workload:
BEGIN
DBMS_WORKLOAD_CAPTURE.ADD_FILTER(FNAME => 'FILTER_NAME',FATTRIBUTE => 'USER',FVALUE => 'SCOTT');
END;
We can find filter’s in view DBA_WORKLOAD_FILTERS;
3. Start Workload capture, means start process which collect workload to the some files, which then will apply on test DB:
BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE(NAME => '2010OCT',DIR => 'DATA_PUMP_DIR',DURATION => 1200);
END;
Duration is number of seconds for which workload will be captured this is optional parameter.
DIR is oracle directory which must be empty!
We can manually stop capture process with this statement:
BEGIN
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE();
END;
We can see status of process in view DBA_WORKLOAD_CAPTURES
4. Preprocess capture data means that capture data will procedure the metadata for captured workload and transform the captured workload datafiles into reply streams called reply files that you can now replay on the test system:
BEGIN
DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(CAPTURE_DIR => 'DATA_PUMP_DIR');
END;
5. Now we must create test DB which should have same data structure as prod DB, we can use rman scripts(backup, restore), snapshot standby DB, Data pump utilities or simple cold backup for creating test DB
6. Check that oracle directory in test DB link on directory where replay files are located, in this scenario we create directory TEST_DIR
7. Initializing the replay Data means initialize the data, which loads the metadata into tables required by workload process:
BEGIN
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(REPLAY_NAME => 'TEST_REPLAY',REPLAY_DIR => 'TEST_DIR');
END;
8. Then we mush remap connections with statement:
BEGIN
DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION(CONNECTION_ID => 111);
END;
9. Prepare the Workload for Replay with statement :
BEGIN
DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY();
END;
There we run procedure with default parameters, for example we can use parameter synchronization, by default true, meaning that the commit order of captured workload will be preserved during the workload replay
10. Set up Reply Clients. The reply driver is a special application that consumes the captured workload by sending replay requests to the test DB. The reply client in essence simulates the production system on the test DB. To Find optimal number of clients we must run this statement on test server in terminal window up to directory where is test_dir
wrc system/a mode=calibrate replay_dir=./test_dir
if we should run with 2 or more clients we must open new terminals and run process in other windows.
Run client with this statement:
wrc system/a mode=replay replaydir=./test_dir
11. Now we can start replay process with statement:
BEGIN
DBMS_WORKLOAD_REPLAY.START_REPLAY();
END:
You must start minimum of one wrc client before you can start the workload replay.
You can manually stop Replay process by statement, this is optional, because replay process will finished itself:
BEGIN
DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY();
END;
Status and progress of replay process you can see in view : DBA_WORKLOAD_REPLAYS
12. Report and result of replay process you can see with this statement :



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) := 'TEST_DIR';
V_FILENAME CONSTANT VARCHAR2(80) := 'REPLAY.HTML';
BEGIN
CAP_ID := DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(DIR => 'TEST_DIR');
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 => 'TEST_DIR',
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;

increase session_cached_cursors

Hello all,

We have performance problem on prod DB, and Oracle ADDM advice us increase size of session_cached_cursors parameter.

We decide find reason of problem.

Here you can find statement which you should run where problem appear.

create table t1 as select sql_text from v$sqlarea;

alter table t1 add sql_text_wo_constants varchar2(3000);

create or replace function
remove_constants( p_query in varchar2 ) return varchar2
as
l_query long;
l_char varchar2(1);
l_in_quotes boolean default FALSE;
begin
for i in 1 .. length( p_query )
loop
l_char := substr(p_query,i,1);
if ( l_char = '''' and l_in_quotes )
then
l_in_quotes := FALSE;
elsif ( l_char = '''' and NOT l_in_quotes )
then
l_in_quotes := TRUE;
l_query := l_query || '''#';
end if;
if ( NOT l_in_quotes ) then
l_query := l_query || l_char;
end if;
end loop;
l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
for i in 0 .. 8 loop
l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
end loop;
return upper(l_query);
end;
/
update t1 set sql_text_wo_constants = remove_constants(sql_text);



now we can see result on statement



select sql_text_wo_constants, count(*)
from t1
group by sql_text_wo_constants
having count(*) > 100
order by 2 desc
/

we can see selects which is reason of performance problem.

In these select please use bind variables.