utPLSQL Unit Test Manager is Very Slow

Toad for Oracle
1

When I run the Unit Test Manager for utPLSQL testing, it seems very slow. Using one of my test packages as an example - it has 80 unit tests in it. From the Toad for Oracle Unit Test Manager it takes 40 seconds of real time to complete and the Elapsed Time show is 1.2 seconds. And that is without trying to retrieve the report output. But when I run the same package from the command line it takes 1.2 seconds of real time and 0.92 seconds of elapsed time.

I ran several different packages and did multiple runs to eliminate caching differences. The results are consistent.

Do you know why it is taking so long for Toad to run these packages?

Toad for Oracle Xpert Edition 15.0.97
utPLSQL version v3.1.11.3559

ut.run command:
begin
ut.run('UT_DUR_MISC', ut_documentation_reporter(), a_color_console=>true);
end;
/

Thanks,
Alan

2

Don't have much exposure with the utPLSQL framework, but here's something you might want to try to troubleshoot. Within Toad, and before you initiate the utPLSQL unit test, turn on SQL Spooling (Tools-->Spool SQL-> To Screen.... or similar... going from memory). Then initiate your unit testing. Any SQL that Toad executes on your behalf will be shown in the Output window.

Make sure that you toggle on Elapsed time, as this will help you identify which SQL statement/s consumes the most resources or takes the longest to run. Report back to the forum if you see something taking the lion's share of that 40+ seconds!

image

3

Hi Gary,

Thanks for the suggestion. I ran it with spooling but it doesn't show much. The bulk of the time is in the last 3 entries at the bottom which look like connects and disconnects.

Any thoughts?
Thanks,
Alan

-- Timestamp: 15:26:31.748
Connect: MY_SCHEMA@(DESCRIPTION=(ADDRESS=(PORT=1234)(HOST=xx.xx.xx.xx)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=mycomputer)))

-- Timestamp: 15:26:31.748
Connect: MY_SCHEMA@(DESCRIPTION=(ADDRESS=(PORT=1234)(HOST=xx.xx.xx.xx)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=mycomputer)))

-- Session: MY_SCHEMA@(DESCRIPTION=(ADDRESS=(PORT=1234)(HOST=xx.xx.xx.xx)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=mycomputer)))
-- Timestamp: 15:26:32.926
DECLARE
l_reporter UT_REALTIME_REPORTER := UT_REALTIME_REPORTER ();
BEGIN
l_reporter.set_reporter_id (:reporter_id);
:report := l_reporter.get_lines_cursor ();
END;
:reporter_id(VARCHAR[32],IN)='FF33DED2EB6442B88170BADFA5B47210'
:report(CURSOR,OUT)=

-- Session: MY_SCHEMA@(DESCRIPTION=(ADDRESS=(PORT=1234)(HOST=xx.xx.xx.xx)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=mycomputer)))
-- Timestamp: 15:26:32.933
DECLARE
l_reporter_0 ut_realtime_reporter := ut_realtime_reporter();
l_reporter_1 UTPLSQL.UT_DOCUMENTATION_REPORTER := UTPLSQL.UT_DOCUMENTATION_REPORTER();
l_reporters ut_reporters;
l_ids VARCHAR2 (4000);
l_run_res NUMBER;
e_run_failure EXCEPTION;
pragma exception_init (e_run_failure, -20213);
BEGIN

SYS.DBMS_OUTPUT.ENABLE(buffer_size => NULL);

l_reporter_0.set_reporter_id (:reporter_id);
l_reporter_0.output_buffer.init ();

l_reporters := ut_reporters(l_reporter_0, l_reporter_1);

l_ids := 'UTPLSQL.UT_DOCUMENTATION_REPORTER=' || l_reporter_1.id;

BEGIN
ut_runner.run (a_paths => ut_varchar2_list ('MY_SCHEMA.UT_DUR_MISC'),
a_reporters => l_reporters,
a_fail_on_errors => TRUE,
a_coverage_schemes => NULL,
a_include_objects => NULL,
a_exclude_objects => NULL);

l_run_res := 1;

EXCEPTION
WHEN e_run_failure THEN
l_run_res := 0;
END;

:out_res := l_run_res;
:out_ids := l_ids;

END;
:reporter_id(VARCHAR[32],IN)='FF33DED2EB6442B88170BADFA5B47210'
:out_res(INTEGER,OUT)=
:out_ids(VARCHAR[0],OUT)=

-- Session: MY_SCHEMA@(DESCRIPTION=(ADDRESS=(PORT=1234)(HOST=xx.xx.xx.xx)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=mycomputer)))
-- Timestamp: 15:26:33.051

-- Timestamp: 15:26:34.444
Disconnect: MY_SCHEMA@(DESCRIPTION=(ADDRESS=(PORT=1234)(HOST=xx.xx.xx.xx)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=mycomputer)))

-- Timestamp: 15:27:11.894
Disconnect: MY_SCHEMA@(DESCRIPTION=(ADDRESS=(PORT=1234)(HOST=xx.xx.xx.xx)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=mycomputer)))

4

Thanks for sending this in, Alan. Strange that your last disconnect took most of the time here. I'm sure Quest Dev folks will be looking at this, but just to make sure that we get this documented for other customers, can you please open a Support ticket here. Thanks.

5

Thanks Gary. I created a ticket.