Print Thread
dbms output is still super slow in Developer 15
#64458 02/24/23 07:20 AM
Joined: Feb 2023
Posts: 4
C
Member
OP Offline
Member
C
Joined: Feb 2023
Posts: 4
Run this in the test window

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

And it does this in the background
Code
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

Code
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

Code
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;

Last edited by Cavalera; 02/24/23 07:20 AM.
Re: dbms output is still super slow in Developer 15
Cavalera #64461 02/24/23 09:12 AM
Joined: Aug 1999
Posts: 22,232
Member
Offline
Member
Joined: Aug 1999
Posts: 22,232
We'll try to enhance this.


Marco Kalter
Allround Automations
Re: dbms output is still super slow in Developer 15
Cavalera #64462 02/24/23 09:35 AM
Joined: Feb 2023
Posts: 4
C
Member
OP Offline
Member
C
Joined: Feb 2023
Posts: 4

Re: dbms output is still super slow in Developer 15
Cavalera #64464 02/25/23 10:42 AM
Joined: Dec 2005
Posts: 2,004
Roima Denmark
Member
Offline
Member
Joined: Dec 2005
Posts: 2,004
Roima Denmark

Re: dbms output is still super slow in Developer 15
Cavalera #64576 05/02/23 02:31 PM
Joined: Sep 2016
Posts: 8
Sweden
J
jaw Offline
Member
Offline
Member
J
Joined: Sep 2016
Posts: 8
Sweden
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.

Re: dbms output is still super slow in Developer 15
Cavalera #64648 06/07/23 08:40 AM
Joined: May 2007
Posts: 102
Tiel, The Netherlands
M
Member
Offline
Member
M
Joined: May 2007
Posts: 102
Tiel, The Netherlands
+1

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

Re: dbms output is still super slow in Developer 15
jaw #64658 06/14/23 07:18 PM
Joined: Jul 2004
Posts: 592
W
Member
Offline
Member
W
Joined: Jul 2004
Posts: 592
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.

Re: dbms output is still super slow in Developer 15
Cavalera #64728 07/20/23 01:03 PM
Joined: Mar 2007
Posts: 110
Hazerswoude-Dorp, NL
Member
Offline
Member
Joined: Mar 2007
Posts: 110
Hazerswoude-Dorp, NL
+1

This looks like a copy and past thingy to solve?


Certainty of death, small chance of succes,
What are we waiting for... (Gimly)

Moderated by  support 

Link Copied to Clipboard
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Release build 20200307)
Responsive Width:

PHP: 7.1.33 Page Time: 0.043s Queries: 15 (0.012s) Memory: 2.5355 MB (Peak: 3.0399 MB) Data Comp: Off Server Time: 2024-06-16 12:51:43 UTC
Valid HTML 5 and Valid CSS