Print Thread
Lines written using dbms_output takes a very long time to fetch
#44710 10/05/12 07:55 PM
Joined: Dec 2005
Posts: 2,004
Roima Denmark
Member
OP Offline
Member
Joined: Dec 2005
Posts: 2,004
Roima Denmark
I have database method that uses dbms_output.put_line to output debug data.

The method itself executes in seconds, but the additional time it takes to fetch 1000+ lines of output is very long.

This is because PLD calls the following code 1000+ times:
Code
sys.dbms_output.get_line(line => :line, status => :status);
Please rewrite the fetch of dbms_output using the following code:
Code
sys.dbms_output.get_lines (lines => :lines, numlines => :numlines);
where :lines is an array of type dbms_output.chararr and :numlines is a variable that indicates how many lines are fetched. The maximum of lines fetched in one call is the max size of an pls_integer (more than 2 billion) so a couple of loops until there is no more lines to fetch should do. If the way PLD calls the Oracle database can not handle index-by arrays, the array can be turned into a clob before returning and then be splitted into lines after return.

In all cases returning thousands of lines can be done in a matter of seconds using the latter 'bulk' method instead of minutes fetching the lines one by one.

The exact format for the call may be dependant of the database version, but I imagine this is a bump that can also be solved.

Will such an improvement be implemented in version 10 of PLD?

Re: Lines written using dbms_output takes a very long time to fetch
Claus Pedersen #44715 10/08/12 09:43 AM
Joined: Aug 1999
Posts: 22,249
Member
Offline
Member
Joined: Aug 1999
Posts: 22,249
This is indeed on the list of enhancement requests.


Marco Kalter
Allround Automations
Re: Lines written using dbms_output takes a very long time to fetch
Marco Kalter #47163 06/01/13 06:49 AM
Joined: Jun 2011
Posts: 14
L
Member
Offline
Member
L
Joined: Jun 2011
Posts: 14
in last version 10.0.3.1701 that behavior is alive. when it can be solved?

Re: Lines written using dbms_output takes a very long time to fetch
longman #47165 06/01/13 10:12 AM
Joined: Aug 1999
Posts: 22,249
Member
Offline
Member
Joined: Aug 1999
Posts: 22,249
What do you mean by "alive"?


Marco Kalter
Allround Automations
Re: Lines written using dbms_output takes a very long time to fetch
Marco Kalter #47167 06/03/13 05:43 AM
Joined: Jun 2011
Posts: 14
L
Member
Offline
Member
L
Joined: Jun 2011
Posts: 14
smile i want to say that problem is exists in version 10+

Re: Lines written using dbms_output takes a very long time to fetch
longman #47171 06/03/13 10:31 AM
Joined: Mar 2011
Posts: 237
Russia
Member
Offline
Member
Joined: Mar 2011
Posts: 237
Russia
I observe dramatic performance boost upon retrieving DBMS_OUTPUT lines in PLSDev v.10.
It still takes time, but I would say it is ten times faster now.


Best regards,
Maxim
Re: Lines written using dbms_output takes a very long time to fetch
Maxmix #48735 03/09/14 02:00 PM
Joined: Dec 2005
Posts: 2,004
Roima Denmark
Member
OP Offline
Member
Joined: Dec 2005
Posts: 2,004
Roima Denmark
Marco, when will this issue be addressed by the development team?
The rewrite using the method sys.dbms_output.get_lines (the plural version, my emphasis) should not be that complicated?
Retrieving dbms_output on a slow VPN connection takes forever ...

Hope soon to hear some news about this issue.

Re: Lines written using dbms_output takes a very long time to fetch
Claus Pedersen #52848 01/05/16 03:34 PM
Joined: Dec 2005
Posts: 2,004
Roima Denmark
Member
OP Offline
Member
Joined: Dec 2005
Posts: 2,004
Roima Denmark
@Marco: do you have an planned code change for this issue?


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.093s Queries: 14 (0.023s) Memory: 2.5315 MB (Peak: 3.0396 MB) Data Comp: Off Server Time: 2024-07-17 13:54:44 UTC
Valid HTML 5 and Valid CSS