dbms output is still super slow in Developer 15

[Cavalera]

Run this in the test window

BEGIN
  FOR nn IN 1 .. 10000 LOOP
    dbms_output.put_line('#=' || nn);
  END LOOP;
END;

And it does this in the background

DECLARE
  lines sys.dbms_output.chararr;
BEGIN
  :numlines := 10;
  sys.dbms_output.get_lines(lines => lines, numlines => :numlines);
  IF :numlines > 0 THEN
    :line0 := lines(1);
  END IF;
  IF :numlines > 1 THEN
    :line1 := lines(2);
  END IF;
  IF :numlines > 2 THEN
    :line2 := lines(3);
  END IF;
  IF :numlines > 3 THEN
    :line3 := lines(4);
  END IF;
  IF :numlines > 4 THEN
    :line4 := lines(5);
  END IF;
  IF :numlines > 5 THEN
    :line5 := lines(6);
  END IF;
  IF :numlines > 6 THEN
    :line6 := lines(7);
  END IF;
  IF :numlines > 7 THEN
    :line7 := lines(8);
  END IF;
  IF :numlines > 8 THEN
    :line8 := lines(9);
  END IF;
  IF :numlines > 9 THEN
    :line9 := lines(10);
  END IF;
END;

Run the same in Oracle SQL Developer

set SERVEROUTPUT ON

BEGIN
  FOR nn IN 1 .. 10000 LOOP
    dbms_output.put_line('#=' || nn);
  END LOOP;
END;

And you will get the output much faster

It does this in the background

DECLARE
   l_line           VARCHAR2 (32767);
   l_done           NUMBER;
   l_buffer         VARCHAR2 (32767) := '';
   l_lengthbuffer   NUMBER := 0;
   l_lengthline     NUMBER := 0;
BEGIN
   LOOP
      DBMS_OUTPUT.get_line (l_line, l_done);

      IF (l_buffer IS NULL)
      THEN
         l_lengthbuffer := 0;
      ELSE
         l_lengthbuffer := LENGTH (l_buffer);
      END IF;

      IF (l_line IS NULL)
      THEN
         l_lengthline := 0;
      ELSE
         l_lengthline := LENGTH (l_line);
      END IF;

      EXIT WHEN    l_lengthbuffer + l_lengthline > :maxbytes
                OR l_lengthbuffer + l_lengthline > 32767
                OR l_done = 1;
      l_buffer := l_buffer || l_line || CHR (10);
   END LOOP;

   :done := l_done;
   :buffer := l_buffer;
   :line := l_line;
END;

 

[Marco Kalter]

We'll try to enhance this.

 

[Cavalera]

I highly doubt it https://forums.allroundautomations.com/ubb/ubbthreads.php?ubb=showflat&Number=58075

 

[Claus Pedersen]

+1:https://forums.allroundautomations.com/ubb/ubbthreads.php?ubb=showflat&Number=44710

More than 10 years, PLD team - you can do better...

 

[jaw]

I have noticed the same thing, and while checking I found that the problem is actually worse.

It seems that PL/SQL doesn't actually clear the bind variables between the calls to fetch 10 new lines, so first it sends the code to fetch 10 lines and gets the 10 lines back, then it sends the code for fetching 10 lines again, INCLUDING THE 10 LINES we just got back. After which DB responds with the 10 next lines. And then we send them back with the same code again.

On non-local networks this is a huge amount of data to push back and forth, and my example is actually sending more data to the database than we're getting back.

Marco, it would be great if you could actually try to fix some of the issues we keep reporting to you.

 

[Marcel Hoefs]

+1

One of the most annoying problems in PL/SQL Dev since many versions. I do not understand why this is not fixed/improved?

 

[Worker]

I've brought up clearing the bind variable for fetching HTP output 14 years ago (although I haven't confirmed if it now actually does). It seems like a very easy way to speed up HTP and DBMS output, and very worthwhile to see if this problem exists in other parts of the application.

 

[koja]

+1

This looks like a copy and past thingy to solve?