Oracle RAT’s Workload Capture With Duration Set Does Not Stop Automatically

One of most exciting features of Oracle 11g is the Oracle Real Application Testing (RAT). Fortunately, Oracle extends support of this feature back to the previous versions. Even though the workload replay is only possible on Oracle 11g, the workload capture is now available in Oracle 9i and 10g. See the Metalink note: 560977.1 – Real Application Testing for Earlier Releases for more details.

I’ve tried the capture on Oracle 9.2.0.8 with all required patches mentioned in above Metalink note. I used the scripts as provided by Oracle.

I’ve found that everything works except for one minor issue which was later identified by Oracle support as an unpublished bug. This issue was that the workload capture initiated by the DBMS_WORKLOAD_CAPTURE.START_CAPTURE does not stop automatically even when the duration parameter is specified. For your reference, the bug number is 6068696 – “Gen V111 (74) CAPTURE WITH DURATION SET DOES NOT STOP AUTOMATICALLY.” And as expected, there will be no fix backported to 9.2.0.8. The only workaround is to manually stop it.

-- Check Date/Time before start
SQL> !date
Fri Apr 17 09:43:39 CDT 2009

-- Check for any existing capture
SQL> select NAME, DBNAME, DBVERSION, STATUS, START_TIME from DBA_WORKLOAD_CAPTURES
where STATUS <> 'COMPLETED';

no rows selected

-- Start capture
SQL> BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => 'TSDW_CAPTURE_TEST',
dir => 'CAPTURE_DIR_FA_TSDW',
duration => 30); -- duration in seconds
END;
/

PL/SQL procedure successfully completed.

-- Verify that capture is running
SQL> select NAME, DBNAME, DBVERSION, STATUS, START_TIME from DBA_WORKLOAD_CAPTURES
where STATUS <> 'COMPLETED';

NAME               DBNAME DBVERSION   STATUS        START_TIME
------------------ -----  ----------- ------------- --------------------
TSDW_CAPTURE_TEST  TSDW   9.2.0.8.0   IN PROGRESS   Apr 17 2009 09:43:47

-- Check point after about one minute
SQL> !date
Fri Apr 17 09:44:42 CDT 2009

-- Capture is still running - ok, let's give some more time...
SQL> select NAME, DBNAME, DBVERSION, STATUS, START_TIME from DBA_WORKLOAD_CAPTURES
where STATUS <> 'COMPLETED';

NAME               DBNAME DBVERSION   STATUS        START_TIME
------------------ -----  ----------- ------------- --------------------
TSDW_CAPTURE_TEST  TSDW   9.2.0.8.0   IN PROGRESS   Apr 17 2009 09:43:47

-- Next check point - almost 2 minutes past
SQL> !date
Fri Apr 17 09:45:48 CDT 2009

-- Still running
SQL> select NAME, DBNAME, DBVERSION, STATUS, START_TIME from DBA_WORKLOAD_CAPTURES
where STATUS <> 'COMPLETED'; 

NAME               DBNAME DBVERSION   STATUS        START_TIME
------------------ -----  ----------- ------------- --------------------
TSDW_CAPTURE_TEST  TSDW   9.2.0.8.0   IN PROGRESS   Apr 17 2009 09:43:47

-- Use finish_capture manually
SQL> BEGIN
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE ();
END;
/

PL/SQL procedure successfully completed.

-- Gone. No more capture.
SQL> select NAME, DBNAME, DBVERSION, STATUS, START_TIME from DBA_WORKLOAD_CAPTURES
where STATUS <> 'COMPLETED';

no rows selected