Hi Guys,
i do a lot of work to consume a soap webservice in pl/sql, acually i ran in an kind of weired performance issue. the call of an very easy request is very, very,very... (and so on) slow when executed within a stored procedure. the same code executed as PL/sql block ist pretty quick. and we talk abaout significant differences. 0.03 secs when executed as PL/SQL block versus 7 secs when called as stored proc.
here is the code snippet i used for testing:
declare
l_soap_request varchar2(30000);
l_soap_response varchar2(32000);
http_req utl_http.req;
http_resp utl_http.resp;
gStarttime Timestamp;
gStoptime Timestamp;
g_target_url varchar2(200) := 'http://PC-ETI:1048/soap/IIntegraWebService';
vRetval XMLType;
begin
l_soap_request := '<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:IntegraWebServiceIntf-IIntegraWebService">
<soapenv:Header/>
<soapenv:Body>
<urn:Ping soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<Token xsi:type="urn:TAuthToken" xmlns:urn="urn:IntegraWebServiceIntf">
<SessionID xsi:type="xsd:string">{integratest}</SessionID>
</Token>
</urn:Ping>
</soapenv:Body>
</soapenv:Envelope>';
gStarttime := systimestamp;
dbms_output.put_line('URL:'||g_target_url);
http_req:= utl_http.begin_request ( g_target_url, 'POST', 'HTTP/1.1');
gStoptime := systimestamp;
dbms_output.put_line('Begin Request-S:'||to_char(gStarttime,'HH24:MI:SS,FF')||'Ende:'||to_char(gStoptime,'HH24:MI:SS,FF'));
utl_http.set_header(http_req, 'Content-Type', 'text/xml');
utl_http.set_header(http_req, 'Content-Length', length(l_soap_request));
-- utl_http.set_header(http_req, 'SOAPAction', p_soap_action);
utl_http.write_text(http_req, l_soap_request);
-- the actual call to the service is made here
UTL_HTTP.SET_PERSISTENT_CONN_SUPPORT(http_req, TRUE);
gStarttime := systimestamp;
http_resp:= utl_http.get_response(http_req);
gStoptime := systimestamp;
dbms_output.put_line('Get Response-S:'||to_char(gStarttime,'HH24:MI:SS,FF')||'Ende:'||to_char(gStoptime,'HH24:MI:SS,FF'));
gStarttime := systimestamp;
utl_http.read_text(http_resp, l_soap_response);
gStoptime := systimestamp;
-- dbms_output.put_line('Get Text-S:'||to_char(gStarttime,'HH24:MI:SS,FF')||'Ende:'||to_char(gStoptime,'HH24:MI:SS,FF'));
utl_http.end_response(http_resp);
dbms_output.put_line('Ferdisch:'||to_char(gStarttime,'HH24:MI:SS,FF')||'Ende:'||to_char(gStoptime,'HH24:MI:SS,FF'));
end;
This produces the following dbms output:
URL:http://PC-ETI:1048/soap/IIntegraWebService
Begin Request-S:10:13:20,751000000Ende:10:13:20,767000000
Get Response-S:10:13:20,767000000Ende:10:13:20,907000000
Ferdisch:10:13:20,907000000Ende:10:13:20,907000000
Now i create an sp from the code above and execute it
"execute soap_perftest"
which prduces the follwing dmbs output
URL:http://PC-ETI:1048/soap/IIntegraWebService
Begin Request-S:10:15:46,414000000Ende:10:15:53,195000000
Get Response-S:10:15:53,195000000Ende:10:15:53,211000000
Ferdisch:10:15:53,211000000Ende:10:15:53,211000000
aftter that i tested the same sp on different database installations in the same network. the problem ony ocurrs when running on a 12c instance. on 10gR2 and 11gr2 the performace is almost the same, regardless wether executed as sp or pl/sql block.
any ideas which misconfiguration of the 12c instance could lead to that perf. issue?
thanks in advance
peter