使用utl_tcp时PL/SQL的性能问题

Question

我写了一些PL/SQL，它连接到一个本地服务，并获得非常少量的字符串数据。这个例程可以工作，但是非常慢，大约需要9秒才能返回数据。我在c#中重新创建了这个过程，它在一秒钟内得到了结果，所以我认为这是我在PL/SQL中做错的事情。我需要解决PL/SQL速度问题，因为我必须从一个非常旧的Oracle表单应用程序进行调用。下面是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;

我明白，当你不能访问系统时，性能问题很难追踪，但任何指导都将是非常感谢的。

Answer 1

我猜是这一行:

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

你真的在你的回复中读了100个字符吗?如果没有，它将读取可用的缓冲区，等待剩下的100个字符到达(但它们不会)，然后超时。

您已将tx_timeout设置为4 s。事实上，你有2个呼叫get_text, 1个呼叫sleep，而你的过程花费了9秒，这对我来说就是发生了什么。