Hi,
Happy new year to the OTN community!
A bit of background I've created some APEX pages that front PL/SQL packages that use UTL_HTTP communicate with a web service to upload and download documents to a document management system. It 'works' from a functionality perspective, I can upload and download documents successfully with a reasonable level of consistency. I've noticed as part of testing that when uploading documents using my screen it is a lot slower than when testing with SOAP UI, for my sample 10Mb file:
SOAP UI: 35 seconds approx.
My PLSQL: 4 minutes (240 seconds) approx.
The SOAP UI XML request takes the following form:
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:onb="http://hostmachine/webserviceWCF">
<soapenv:Header>
<onb:SessionID>9ec64711-dc8c-46a0-b269-8dddddce388f</onb:SessionID>
<onb:FileExtension>DOCX</onb:FileExtension>
<onb:DocumentTypeId>227</onb:DocumentTypeId>
</soapenv:Header>
<soapenv:Body>
<onb:MTOMStreamIncoming>
<onb:FileContents>+mxa/wCFNS1I6r/wj90lvBeHgtE0YZUbr8y8jLs5ZLmeRzPdXUn3p5SBls
</onb:FileContents>
</onb:MTOMStreamIncoming>
</soapenv:Body>
</soapenv:Envelope>
Points of note, <onb:filecontents> is the file as base64encoded text, so I've just put a small amount in for the example. Other attributes are specific to the document management system.
The SOAP UI Raw request is:
POST http://webserviceWCF/PFOB.svc/Text HTTP/1.1
Accept-Encoding: gzip,deflate
Content-Type: text/xml;charset=UTF-8
SOAPAction: "http://hostmachine/webserviceWCF/IPFOB/AddDocumentViaStream"
Content-Length: 14269305
Host: hostmachine
Connection: Keep-Alive
User-Agent: Apache-HttpClient/4.1.1 (java 1.5)
I convert file blobs to base64clobs and I've pretty-much replicated the header generation in my PL/SQL by making the following calls to UTL_HTTP.set_header:
UTL_HTTP.begin_request (g_url, 'POST', 'HTTP/1.1');
UTL_HTTP.set_header (l_http_req, 'Accept-Encoding', 'gzip,deflate');
UTL_HTTP.set_header (l_http_req,'Content-Type','text/xml; charset=UTF-8');
UTL_HTTP.set_header (l_http_req, 'SOAPAction', 'http://hostmachine/webserviceWCF/IPFOB/AddDocumentViaStream');
UTL_HTTP.set_header (l_http_req, 'Connection', 'Keep-Alive');
UTL_HTTP.set_header (l_http_req, 'Transfer-Encoding', 'chunked');
The base64 encoding and the other calls to UTL_HTTP (write_text) appear to be performing fine, but it is:
l_http_resp := UTL_HTTP.get_response (l_http_req)
Which takes about 4 minutes to execute. So basically all the time is taken up by the above command. Are there any suggestions/known issues or bugs and/or system parameters that can be investigated to try and improve the situation or identify where the slowdown is being encountered? I can understand some overhead due to the database but this doesn't feel right. Downloading the same 10Mb file only takes a few seconds and is comparable with SOAP UI (approx. 3 seconds using both methods). The web service is on the same network and no throttling is in place that I am aware of.
Many thanks,
Daljit
My environment details
- APEX 5.0.4.00.12
- Oracle 11g (11.2.0.1), CentOS 5
- EPG
- Internet Explorer 11 (Windows 7)
- Universal Theme