Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

UTL_HTTP.get_response performance...

Daljit RJan 5 2017 — edited Jan 6 2017

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

Comments

Billy Verreynne

Why do you not set Content-Length in the header? Just how large is the envelope? And how do you post the SOAP envelope? It is better to create the enveloper upfront as a 32KB varchar2 string, or as a CLOB - and then pass that (by reference) to the procedure unit that does the HTTP interaction.

Daljit R

Hi Billy,

I thought I might get asked about Content-Length after I posted There isn't really a 'reason' why I don't set the Content-Length, other than that in this case (with chunked transfer-encoding) it appears to make no difference to the performance.

Here is the code that checks the request length/envelope and then sets whether to use chunked transfer-encoding, you'll notice I have now set the Content-Length:

IF l_request_length <= 32767

      THEN

        

         logger.LOG ('Short request.',

                     k_scope,

                     NULL,

                     l_params);

        

         UTL_HTTP.set_header (l_http_req, 'Content-Length', l_request_length);

         UTL_HTTP.write_text (l_http_req, p_soap_request);

         logger.LOG ('Request text written.',

                     k_scope,

                     NULL,

                     l_params);

      ELSE

     

         UTL_HTTP.set_header (l_http_req, 'Content-Length', l_request_length);

         UTL_HTTP.set_header (l_http_req, 'Transfer-Encoding', 'chunked');

        

         logger.LOG ('Long request - To be chunked.',

                     k_scope,

                     NULL,

                     l_params);

                    

         WHILE (l_offset < l_request_length)

         LOOP

            DBMS_LOB.read (p_soap_request,

                           l_amount,

                           l_offset,

                           l_buffer);

            UTL_HTTP.write_text (l_http_req, l_buffer);

            l_offset := l_offset + l_amount;

         END LOOP;

        

         logger.LOG ('Request text written.',

                     k_scope,

                     NULL,

         l_params);

      END IF

     l_http_resp := UTL_HTTP.get_response (l_http_req);

The processing 'only' takes a 1-3 seconds to reach line 43 (in above code listing), which includes generating the envelope, converting the blob to base64clob but then line 43 takes 240 seconds for UTL_HTTP.get_response (l_http_req) to return. However, when it does eventually return the response is valid and I can do what I need.

l_request_length for the test 10Mb file is 14524103, p_soap_request is the envelope that is prepared outside of this routine (based upon the SOAP action being performed) and passed in (IN OUT NOCOPY) but changing this to be just passed IN makes no difference on performance.

Below is the signature for my HTTP interaction subroutine:

FUNCTION communicate_with_api (p_soap_request IN OUT NOCOPY CLOB, p_soap_action IN VARCHAR)

      RETURN XMLTYPE

The question is around the discrepancy in performance between SOAP UI and UTL_HTTP.get_reponse.

Billy Verreynne

I suspect that up to the get_response() call, there is not yet any network transfer to the web server - and that the calls prior like set_header() and write_text(), only preps the internal struct of the db core C module that services the UTL_HTTP API.

If so, then get_response() is a pure TCP write to socket call (and responsible for writing the 10MB payload to the TCP socket) - which means you are looking a networking issue of sorts. Can you test SOAPUI on the actual db server, to ensure that the SOAPUI test uses the same network infrastructure than your UTL_HTTP code?

If not, perhaps wget or curl can be used to make the 10MB SOAP call?

In my view the network is the obvious culprit - and needs to be eliminated as the cause of the problem, before popping the hood on the Oracle server process running the UTL_HTTP code.

Daljit R

Appreciate your reasoning Billy, I agree this is a sensible way to try and isolate this. I don't have access to perform SOAPUI testing directly from the DB server myself but I can certainly request for it to be done before delving further into the Oracle processes. Up until this point SOAPUI testing has been conducted using my client PC so can almost guarantee it doesn't follow the same 'route' on the network, may also be able to Wireshark the requests to give a bit more information.

I will post back with some further additions once these tests have been conducted.

Thanks

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 3 2017
Added on Jan 5 2017
4 comments
1,065 views