Running PL/SOL that produces large quantity of dbms_output, seems to slow/hang Toad process.
However, the 5000 generated lines do not appear in Toad for Oracle for ten+ minutes.
In example loop of 5K lines, it appears each line of output is retrieved by a single call to dbms_output.get_line, which appears to be costly when executed 5000 times.
Running the same block in SQLPLUS generates the 5000 lines immediately.
Can server output handling in Toad for Oracle be configured to improve performance?
In addition, Development found a couple of problems here.
First, output was added to the panel one line at a time because of auto-debugger support.
Cleaning this component made a big difference.
Second, Toad for Oracle were fetching lines one at a time from Oracle.
Oracle documentation suggest fetching multiple lines at once into a table of Varchar2 for optimal performance using dbms_output.get_lines.
This shaved off about 30% of the time requesting the lines from Oracle.
Together the example block below executes in ~2.5 seconds returning all output.
WORKAROUND:
First the obvious, select "Execute via SQL*Plus" from the Toad execute dropdown button if the user doesn't want to see the output statements and just wants to run the anonymous block Or take the dbms_output items from of the SQL.
Finally, one can add set serveroutput off at the start of their script. This improves performance tremendously.
STATUS:
Waiting for fix in a future version of Toad for Oracle.
