Output generated via dbms_output.put_line slow on remote connection

[Claus Pedersen]

When I execute program code that genereates output by calling dbms_output.put_line it seems to be very slow when I connect to remote databases (for instance via VPN).

The program unit finishes as normal and PLD reports the elapsed time, but the hour glass is still active and the program first responds to user input again when all lines have been sent to output.

This could sometimes take several minutes when the amount of output exceeds a couple of hundred lines.

SQL*Plus does the job considerably faster. Is this problem related to PLD or does the source of the problem lie elsewhere?

 

[Marco Kalter]

Which PL/SQL Developer version are you using?

 

[Claus Pedersen]

I am using latest version 7

 

[Marco Kalter]

To obtain some more diagnostic information, can you modify the shortcut and add the debugsql parameter? For example:

"C:\Program Files\PLSQL Developer\plsqldev.exe" debugsql

Reproduce the problem and send me the debug.txt file that is generated in the PL/SQL Developer directory.

 

[Claus Pedersen]

Hello again.

I have mailed a dump of a debug.txt to your support address.

As you can see, in an SQL window, I have executed the code:

BEGIN
  FOR i IN 1..1000 LOOP
    dbms_output.put_line (i);
  END LOOP;
END;

It is reported to be executed in 0.11 seconds on the server, but the output is brought back to PLD one line at a time with a round-trip time of 0.01-0.09 seconds, so the total time elapsed in this case is 43 seconds before control is returned to the user.

My suggestion, looking at the SQL, would be that instead of calling sys.dbms_output.get_line in 1000 seperate program units, one single program unit with a loop should collect the result in a PL/SQL table of strings and returned to the application, if this is applicable.

 

[Claus Pedersen]

Any news on this?

 

[Marco Kalter]

Not yet.

 

[Claus Pedersen]

Do you have an E.T.A. on this?

This issue has become quite time consuming for me, as we are making suppport on several databases connected via VPN or similar. The time used for displaying output is considerable.

 

[Marco Kalter]

The problem is that the dbms_output.get_lines function is not stable on all client versions. We probably need to create a preference for this.

 

[Claus Pedersen]

I can see your problem.

If this issue can be put on the request list with a fairly high priority, I would appreciate it