Skip to Main Content

Oracle Database Discussions

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.

Composite index vs Single column index

HesipesiDec 11 2015 — edited Dec 16 2015

Hi,

I have a table with a,b,c,d,e,f,g,h,i,j,k. columns and i have an index on a,b columns!

There is a sql statement now with where a= ?  and we are wondering if it could also be good to add a single index on just 'a' column!

doesn't help AT ALL? it does help in SOME CASES? or??? 

what do you think??

Thanks!

/Hesi

This post has been answered by Jonathan Lewis on Dec 14 2015
Jump to Answer

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 Jan 13 2016
Added on Dec 11 2015
10 comments
50,680 views