8 Replies Latest reply on Jan 8, 2015 7:39 PM by Kris Rice-Oracle

    resident connection pool for Apex ( Rest Services )

    vbuehringer

      Hello,

       

      is it possible to use the drcp ( resident connection pool ) for apex connections.

       

      I would like to use the connection pool for rest services.

       

      The performance of rest services is severly limited because every request is handled within a new connections.

       

      I tried to include "pooled" in  the jdbc connection string but then apex doesn't work .

       

      My Database is 12.1.0.2 and the apex version is the latest (2.0.9 for the rest listener ).

       

      Thanks.

       

      Volkmar

        • 1. Re: resident connection pool for Apex ( Rest Services )
          Erik Espinoza

          Hi there

           

          I'm having the same issue and after a lot of research I think this might an issue/bug in the ORDS since the I noticed that when the ORDS gets the metadata of the web service it will serve it DOES uses session pooling but when it will actually execute the content of the web service it will always open a new connection.

           

          Can anyone help and say for sure it is a missing functionality of the ORDS or it is missed configuration somewhere.

           

          BTW My ORDS version is 2.0.10.289.08.09

           

          Here is the log of a debug call to a rest web service in my apex instance and as you can see the listener is retrieving the metadata of the web service using pooling but when executing the content of the web service it uses my db schema user instead of pooling:

           

          Request Path passes syntax validation

          Mapping request to database pool: PoolMap [_failed=false, _lastUpdate=-1, _pattern=null, _poolName=apex, _regex=null, _type=REGEX, _workspaceIdentifier=null, _serviceName=null]

          Applied database connection info

          Attempting to process with PL/SQL Gateway

          Not processed as PL/SQL Gateway request

          Attempting to process as a RESTful Service

          Determining if request can be dispatched as a Tenanted RESTful Service

          Request path has one path segment, continuing processing

          No Tenant Principal established yet, continuing processing

          APEX_LISTENER pool exists, continuing processing

          Matching tenant exists, continuing processing

          Dispatching as tenanted request, with base: http://10.43.135.135:8080/ods/, target: api/sci/explore-items/133103/Glove/-1/-1/-1/-1, tenant: TenantPrincipal [name=ODS]

          api/sci/explore-items/{ITEM_MSTR_SET_ID}/{SEARCH_TEXT}/{UNSPSC_KEY}/{PSC_KEY}/{CNTR_KEY} matches: api/sci/explore-items/133103/Glove/-1/-1/-1/-1 score: 0

          Choosing: oracle.dbtools.rt.resource.templates.jdbc.JDBCResourceTemplateDispatcher as current candidate with score: Score [handle=JDBCURITemplate [scopeId=null, templateId=10608925485698212|34592402592751765, uriTemplate=api/sci/explore-items/{ITEM_MSTR_SET_ID}/{SEARCH_TEXT}/{UNSPSC_KEY}/{PSC_KEY}/{CNTR_KEY}], score=0, scope=SecurityConfig [constraint=none, realm=NONE, logonConfig=NONE], originsAllowed=[*], corsEnabled=true]

          Determining if request can be dispatched as a Tenanted RESTful Service

          Request path has one path segment, continuing processing

          Tenant Principal already established, cannot dispatch

          Chose oracle.dbtools.rt.resource.templates.jdbc.JDBCResourceTemplateDispatcher as the final candidate with score: Score [handle=JDBCURITemplate [scopeId=null, templateId=10608925485698212|34592402592751765, uriTemplate=api/sci/explore-items/{ITEM_MSTR_SET_ID}/{SEARCH_TEXT}/{UNSPSC_KEY}/{PSC_KEY}/{CNTR_KEY}], score=0, scope=SecurityConfig [constraint=none, realm=NONE, logonConfig=NONE], originsAllowed=[*], corsEnabled=true] for: GET api/sci/explore-items/133103/Glove/-1/-1/-1/-1

          Choosing: oracle.dbtools.rt.jdbc.entity.JDBCTenantDispatcher as current candidate with score: Score [handle=oracle.dbtools.rt.jdbc.entity.TenantDispatcherBase$TenantTarget@761e7117, score=1, scope=SecurityConfig [constraint=none, realm=NONE, logonConfig=NONE], originsAllowed=[], corsEnabled=true]

          Chose oracle.dbtools.rt.jdbc.entity.JDBCTenantDispatcher as the final candidate with score: Score [handle=oracle.dbtools.rt.jdbc.entity.TenantDispatcherBase$TenantTarget@761e7117, score=1, scope=SecurityConfig [constraint=none, realm=NONE, logonConfig=NONE], originsAllowed=[], corsEnabled=true] for: GET ods/api/sci/explore-items/133103/Glove/-1/-1/-1/-1

          ods/api/sci/explore-items/133103/Glove/-1/-1/-1/-1 is a public resource

          api/sci/explore-items/133103/Glove/-1/-1/-1/-1 is a public resource

          Using generator: oracle.dbtools.rt.json.query.JSONQueryGenerator

          Performing JDBC request as: DS

           

          Thanks!

          Erik

          • 2. Re: resident connection pool for Apex ( Rest Services )
            Kris Rice-Oracle

            ORDS definitely does do connection pooling we use UCP for all pools.  Why are you assuming it's opening a closing a connection on every request?  From this log or from looking at v$session? or something else?

            • 3. Re: resident connection pool for Apex ( Rest Services )
              vbuehringer

              my question refers to using ords for rest services.

              It always generates a new session for ever request ( v$session )

               

              so rest services or slow with ords.

               

              With the embedded listener of xml-db rest services are much faster thanks to the

              shared server architecture

               

              so ords is only plaything for rest, stick to the embedded listener or use java in the middleware

              • 4. Re: resident connection pool for Apex ( Rest Services )
                Kris Rice-Oracle

                No it doesn't, you have something is configured wrong.

                • 5. Re: resident connection pool for Apex ( Rest Services )
                  vbuehringer

                  Hello,

                   

                  pooling is used for the apex-schemas, but for the workspace schema for every rest call a new connection is created.

                   

                  So rest-calls are rather slow even with ords 3.0.

                   

                  With the embedded xmldb-listener rest-calls are much faster in two tier architecture.

                   

                  I would need something like rest-calls with a pl/sql procedure but in a 3 tier-architecture like ords.

                  • 6. Re: resident connection pool for Apex ( Rest Services )
                    Kris Rice-Oracle

                    As I asked before.  What is leading you to this conclusion because that is not how things are coded.

                    • 7. Re: resident connection pool for Apex ( Rest Services )
                      vbuehringer

                      Thank you Sir for your fast answer.

                       

                      In dba_proxies I find the new ords_public_user as proxy for ordstest.

                       

                      I test with:

                       

                      select httpuritype( 'http://192.168.104.71:8081/ords/ordstest/test/los/'||l_id).getclob() json from los where rownum < 1000;

                       

                      I achieve 10 answers per second. In v$session I see  sessions with username ordstest with rapidly increasing serial#.

                       

                      So the logical conclusion is that for every call a new proxy connection is created and destroyed.

                       

                      DB: 12.1.0.2, Ords 3.0 in standalone mode, oracle linux 6.5

                      • 8. Re: resident connection pool for Apex ( Rest Services )
                        Kris Rice-Oracle

                        You have an incorrect test for such things.  You basically have  test that has 1000 person concurrency with zero think time so of course it's going to be increasing to handle the load.

                         

                        The way it works is :

                           There is a base connection pool to ords_public_user

                           A connection is gotten from that pool on request

                           A proxy connection is opened off that base connection to the appropriate user ala : http://docs.oracle.com/cd/B28359_01/java.111/b31224/proxya.htm#BABEJEIA

                             <<WORK>>

                          Proxy session is close

                          Connection is returned to the generic pool.