Performance issue with PL/SQL when using utl

I have written some PL/SQL that connects to a service on prem and gets a very small amount of string data back. The routine works, but it is incredibly slow, taking roughly 9 seconds to return the data. I have re-created the process in C# and it gets the results back in under a second, so I assume it is something I am doing wrong in my PL/SQL. I need to resolve the PL/SQL speed issue as I have to make the call from a very old Oracle Forms application. Here is the PL/SQL:

declare
    c  utl_tcp.connection;
    ret_val varchar2(100);
    reading varchar2(100);
    cmd varchar2(100) := 'COMMAND(STUFF,SERVICE,EXPECTS)';
    cmd2 varchar2(100);
begin
    c := utl_tcp.open_connection(remote_host => 'SERVICE.I.P.ADDRESS'
                               ,remote_port =>  9995
                               ,charset     => 'US7ASCII'
                               ,tx_timeout  => 4
                               );  -- Open connection
                               
    --This is a two step process.  First, issue this command which brings back a sequence number
    ret_val := utl_tcp.write_line(c, cmd);  -- Send command to service
    ret_val := utl_tcp.write_line(c);  -- Don't know why this is necessary, it was in the example I followed
  
    dbms_output.put_line(utl_tcp.get_text(c, 100));  -- Read the response from the server
  
    sys.dbms_session.sleep(1); -- This is important as sometimes it doesn't work if it's not slowed down!
  
    --This is the second step which issues another command, using the sequence number retrieved above
    cmd2 := 'POLL(' || ret_val || ')';
        
    reading := utl_tcp.write_line(c, cmd2);  -- Send command to service
    reading := utl_tcp.write_line(c);  --Don't know why this is necessary, it was in the example I followed 
      
    dbms_output.put_line(utl_tcp.get_text(c, 100));  -- Read the response from the server    
    utl_tcp.close_connection(c);  --Close the connection
end;

I appreciate performance problems are hard to track down when you don't have access to the systems, but any guidance would be greatly appreciated.

CodePudding user response：

My guess is that it's this line:

dbms_output.put_line(utl_tcp.get_text(c, 100));

Are you actually reading 100 characters in your response? If not, it will read the available buffer, wait for the rest of the 100 characters to arrive (but they won't), then timeout.

You've set tx_timeout to 4 s. The fact that you have 2 calls to get_text, a 1 s sleep, and your procedure is taking 9 s suggests to me that's what's going on.