Database Replay Enhanced PL/SQL Support
APPLIES TO:
Oracle Database – Enterprise Edition – Version 12.2.0.1 and later
Information in this document applies to any platform.
PURPOSE
Introduce and demonstrate the new Database Replay enhanced PL/SQL Support in 12.2. This feature is available in version 12.2 and onward.
DETAILS
Database Replay has always had the ability to capture and replay PL/SQL. It has done so through capturing and replaying the top level PL/SQL calls. As of Oracle Database 12c Release 2 (12.2), Database Replay gives you the choice of replaying the top level PL/SQL calls or the recursive SQL called within the PL/SQL procedures. Depending on the workload, the new replay mode can perform replays with more accuracy and less divergence.
Enhanced PL/SQL support gives DBAs the ability to perform database replays with less divergence, allowing faster, easier, and more complete testing of workloads having a lot of PL/SQL.
— Create and populate the testcase dependent objects:
create table t1_dml (c1 varchar2(50) not null, c2 number not null);
create table t2_select (c1 varchar2(50) not null, c2 number not null);
declare
v_sql varchar2(50);
v_num number;
v_vc varchar2(50);
begin
for i in 1..10000 loop
v_sql := ‘insert into t2_select values(:p0, :p1)’;
v_num := i;
v_vc := ‘Test’ || i;
execute immediate v_sql using v_vc, v_num;
end loop;
commit;
end;
/
— PLSQL – Capture Side
CREATE OR REPLACE DIRECTORY CAP_122_PLSQLON AS ‘/u02/testcase/jenny/plsqlon’;
BEGIN
DBMS_WORKLOAD_CAPTURE.ADD_FILTER (fname => ‘USER_SCOTT’, fattribute => ‘USER’, fvalue => ‘SCOTT’);
END;
/
— plsqlmode extended
BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => ‘TESTPLSQLEXTENDED’, dir => ‘CAP_122_PLSQLON’, default_action =>’EXCLUDE’, plsql_mode=>’EXTENDED’);
END;
/
— Simulate PLSQL Workload:
declare
v_sql varchar2(50);
v_num number;
v_vc varchar2(50);
begin
for i in 1..1000000 loop
v_sql := ‘insert into t1_dml values(:p0, :p1)’;
v_num := i;
v_vc := ‘Test’ || i;
execute immediate v_sql using v_vc, v_num;
end loop;
commit;
end;
/
declare
v_sql varchar2(500);
v_num_out number;
v_num_in number;
begin
for i in 1..10000 loop
v_sql := ‘select c2 from t2_select where c2 = :1’;
v_num_in := i;
execute immediate v_sql into v_num_out using v_num_in;
end loop;
end;
/
BEGIN
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE();
END;
/
PLSQL – Replay Side
——————-
— Process Capture
BEGIN
DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (capture_dir => ‘CAP_122_PLSQLON’, plsql_mode=>’EXTENDED’);
END;
/
— Initialize with plsqlmode extended with capture that is extended and processed as extendedBEGIN
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY (replay_name => ‘TESTPLSQLEXTENDED’, replay_dir => ‘CAP_122_PLSQLON’, plsql_mode=>’EXTENDED’);
END;
/
— PrepareBEGIN
DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (synchronization => FALSE);
END;
/
— Start the wrc client(s)
wrc system/xxxxx mode=replay replaydir=/u02/testcase/jenny/plsqlon CONNECTION_OVERRIDE=TRUE
— Start Replay
BEGIN
DBMS_WORKLOAD_REPLAY.START_REPLAY;
END;
/
— Once the Replay completes generate replay report
— Replay with Extended PL/SQL Support against Extended Capture
set long 100000000 longchunksize 100000000 linesize 200 head off feedback off echo off TRIMSPOOL ON TRIM ON
SPOOL /u02/testcase/jenny/plsqlon/replay_report_122_extended.html
select DBMS_WORKLOAD_REPLAY.REPORT(replay_id => 1, format => ‘HTML’) from dual;
SPOOL OFF
Observations:
The Replay report has two new rows int he Replay Statistics section to show both PL/SQL user calls and PL/SQL sub-calls.
Common Errors:
If a workload was captured with plsql_mode EXTENDED but the workload was not processed with plsql_mode EXTENDED, any attempt to initialize a replay with plsql_mode EXTENDED will return the following error:
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY (replay_name => ‘TESTPLSQLEXTENDED’, replay_dir => ‘CAP_122_PLSQLON’, plsql_mode=>’EXTENDED’);
END;
/
12:18:15 2 12:18:15 3 12:18:15 4 BEGIN
*
ERROR at line 1:
ORA-20223: Error: Invalid Input. Directory “CAP_122_PLSQLON” does not contain a valid processed workload capture
ORA-06512: at “SYS.DBMS_WORKLOAD_REPLAY”, line 3451
ORA-06512: at “SYS.DBMS_WORKLOAD_REPLAY”, line 3481
ORA-06512: at line 2
If a workload was captured with plsql_mode TOP_LEVEL or the default and processed, any attempt to initialize a replay with plsql_mode=extended will return the following error:
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY (replay_name => ‘TESTPLSQLEXTENDED’, replay_dir => ‘CAP_122_PLSQLOFF’, plsql_mode=>’EXTENDED’);
END;
/10:00:29 2 10:00:29 3 10:00:29 4
BEGIN
*
ERROR at line 1:
ORA-20223: Error: Invalid Input. Capture does not have subcall information for PL/SQL calls. Specify ‘EXTENDED’ at START_CAPTURE
ORA-06512: at “SYS.DBMS_WORKLOAD_REPLAY”, line 3451
ORA-06512: at “SYS.DBMS_WORKLOAD_REPLAY”, line 3481
ORA-06512: at line 2
WikiDBA 