1 2 Previous Next 20 Replies Latest reply on May 14, 2020 7:12 PM by Olafur T

    Performance issue after migrating to ORDS 19.4 from mod_plsql.

    user12019829

      We are migrating from mod_plsql to ORDS 19.4 for a PL/SQL application (not APEX) that uses HTTP Basic dynamic authentication by connecting to the database as the username specified in the Basic challenge box. At the browser we are seeing that the application displays pages much faster when using mod_plsql vs ORDS. For example we created a JMeter test with 50 users, the test using mod_plsql ran in 2 min 54 sec in comparison ORDS 19.4 it took 48 minutes 44 sec.

       

      Another observation when logging in with 1 user. When using mod_plsql I could see the user session in V$SESSION. With ORDS 19.4 it the user is logged immediately.

       

      Comparison of User logins in dba_audit_trail:

      DESCRIPTION

      MOD_PLSQL

      ORDS 19.2

      ORDS 19.4

      Basic Auth Pop-up Login

      1

      2

      2

      Selected a Menu Option

      2

      4

      4

       

      /* List Logins */

      select * from dba_audit_trail

      where username = 'JAK'

      and action_name = 'LOGON'

      order by timestamp desc

      ;

       

      -- From ORDS 19.4 readme.

      Performance of ORDS based REST APIs was significantly improved in ORDS 19.4.0 by changing how ORDS handles proxied database connections. In ORDS 19.2.0 and earlier, for each HTTP request ORDS proxies from ORDS_PUBLIC_USER to the relevant ORDS enabled schema, performs the relevant database calls and then closes the proxy session. We term this behavior 'disposing' a connection.

      In ORDS 19.4.0 and later ORDS changes its default behavior to keep proxied connections in the pool, ameliorating away the cost of opening and closing the proxy sessions. This gives a very substantial performance improvement. We term this behavior 'recycling' a connection.

      Reverting to Previous behavior

      ORDS Administrators may revert to the old behavior of 'disposing' connections by performing the following command:

      java -jar ords.war set-property jdbc.cleanup.mode dispose

      To switch to recycling connections use:

      java -jar ords.war set-property jdbc.cleanup.mode recycle

      Managing Connection Re-use

       

      I don't see any difference in performance between ORDS 19.2 and 19.4

       

      I have these set in defaults.xml

      <entry key="jdbc.auth.enabled">true</entry>

      <entry key="jdbc.cleanup.mode">recycle</entry>

      <entry key="jdbc.InitialLimit">10</entry>

      <entry key="jdbc.MaxConnectionReuseCount">1000</entry>

      <entry key="jdbc.MaxLimit">200</entry>

       

       

      Is there anything else that needs to be set to improve performance with ORDS?

       

      Thanks

        • 1. Re: Performance issue after migrating to ORDS 19.4 from mod_plsql.
          thatJeffSmith-Oracle

          are you sure having the initial pool settings for 10 are correct for your environment/scenario? not sure how long it takes for the code you are testing to run, but having more sessions avail at the outset to satisfy your requests would probably help

          • 2. Re: Performance issue after migrating to ORDS 19.4 from mod_plsql.
            user12019829

            Hi,

             

            With <entry key="jdbc.InitialLimit">10</entry> and <entry key="jdbc.MaxLimit">200</entry> I noticed that jdbc.MinLimit was set to the default of 1 and jdbc.InactivityTimeout was set to the default of 1800 (30 min). I also defined <entry key="db.username">ORDS_OSMIS_USER</entry> and <entry key="db.password">xxxxxxxxx</entry>.  When I restarted ORDS I had 20 connections for user ORDS_OSMIS_USER. The load test ran hours later so the 19 users timed out leaving 1. The load test ran with 1 ORDS_OSMIS_USER which created new logins to user AMB_OSMIS (basic auth login user).

            Based on your suggestion I ran another 50 user load test with these changes:

            <entry key="jdbc.InitialLimit">100</entry>

            <entry key="jdbc.MaxLimit">200</entry>

            <entry key="jdbc.MinLimit">20</entry>

            There was not difference in the elapsed time or the number of logins. The test was ran after 30 minutes of ORDS restarting. I observed that 100 ORDS_OSMIS_USER were initially logged in and hours later when the load test ran there were 20. This number never increased during the load test even when it was at 1.

            The problem appears to be in the number of logins being created from the basic auth login user AMB_OSMIS. With mod_plsql there are 121 total logins vs ORDS 19.4 generated 8,000 logins. ORDS appears to be creating a new connections for every request.

            NBR USERS

            SERVER

            START_TIME 4/21/2020

            END_TIME 04/21/2020

            ELAPSED MINUTES

            LOGINS

            50

            mod_plsql

            4:23:06 PM

            4:23:39 PM

                      2.48

            121

             

             

             

             

             

             

            50

            ORDS 19.4

            4:35:15 PM

            5:24:15 PM

            49

            8000

             

            Thanks,

            • 3. Re: Performance issue after migrating to ORDS 19.4 from mod_plsql.
              thatJeffSmith-Oracle

              ORDS creates a physical connection to the database for each authentication attempt when using PL/SQL Gateway and HTTP basic authentication.

               

              MOD_PLSQL caches the connection (and the database user/password in memory) and re-uses the connection if the username/password provided in the HTTP basic Authorization header match those cached. However, if the password is changed in the database then the connection is still used for some time window, until mod_plsql recycles the physical connection.

               

              Attempting a login for each request is more work, but it's also more correct and secure.

              • 4. Re: Performance issue after migrating to ORDS 19.4 from mod_plsql.
                user12019829

                Hi Jeff,

                 

                Thanks for your explanation!

                 

                In regards to security our application uses a STAFF table that mirrors all of the application database accounts. In this STAFF table is a status_cd column that when set to inactive will immediately prevent the user from further accessing the system.

                 

                In our load test ORDS 19.4 is approximately 19.7% slower then mod_plsql preventing us from using this in production.

                 

                1) Do you know if there is a parameter that will turn on re-use for basic authentication database logins? 

                 

                2) The new 19.4 <entry key="jdbc.cleanup.mode">recycle</entry> parameter mentions that it significantly improved performance. "In ORDS 19.4.0 and later ORDS changes its default behavior to keep proxied connections in the pool, ameliorating away the cost of opening and closing the proxy sessions. This gives a very substantial performance improvement. We term this behavior 'recycling' a connection."

                Can you explain this 19.4 enhancement?

                 

                3) Which version of ORDS supports re-use of basic authentication database connections?

                 

                Thanks,

                 

                Joe

                • 5. Re: Performance issue after migrating to ORDS 19.4 from mod_plsql.
                  thatJeffSmith-Oracle

                  I think the solution is to abandon basic auth.

                  • 6. Re: Performance issue after migrating to ORDS 19.4 from mod_plsql.
                    thatJeffSmith-Oracle

                    that's for ORDS REST requests using ORDS_PUBLIC_USER...those connections are always there, when you go to access a rest service, we proxy connect to the user where the service is defined

                     

                    a good bit different than how the plsql gateway works

                     

                    using a REST Services vs the gateway would also provide relief, but more work on your part

                    • 7. Re: Performance issue after migrating to ORDS 19.4 from mod_plsql.
                      user12019829

                      Hi Jeff,

                       

                      Oracle's stated direction for the now deprecated mod_plsql has been to use Weblogic with ORDs as a replacement. I don’t understand why Oracle would recommend ORDS as a mod_plsql replacement when it does not scale for a production environment?

                       

                      We do have future plans to remove the basic authentication from the application but that will take some time.

                       

                      If I understood you correctly ORDS at one time worked like mod_plsql until a customer filed a bug. Isn’t it feasible for me to install the ORDS pre-bug version? Do you know what version that was?

                       

                      Thanks again for all of your help!

                       

                      Joe

                      • 8. Re: Performance issue after migrating to ORDS 19.4 from mod_plsql.
                        thatJeffSmith-Oracle

                        we recommend ords, but we do NOT recommend basic auth

                         

                        And, it's also not recommended to use older versions of ords

                         

                        I have no idea what your app is...you say you''re NOT using APEX. APEX is a plsql gateway app, that offers many forms of scalable authentication, and you can quickly build applications with it. Might be worth taking a look at.

                        • 9. Re: Performance issue after migrating to ORDS 19.4 from mod_plsql.
                          user12019829

                          Hi Jeff,

                           

                          We used the PL/SQL Web Toolkit and created a business application that uses mod_plsq basic authentication. This is an existing application that is 20+ years old and supports 3,000+ users. Using APEX is not an option, because we would have to rewrite the entire application.

                           

                          We are happy with mod_plsql, it is Oracle that de-supported it 12/31/2019 and stated to use ORDS as a replacement. Why would Oracle want their customers to migrate to ORDS from mod_plsql if it won’t scale in a production environment?

                           

                          We are refreshing our software and servers with Weblogic 12.2.1.4 with OHS and ORDS 19.4. "mod_plsql" is no longer supplied which leaves us with using ORDS as a replacement. Load testing shows a huge performance degradation with ORDS basic authentication that will not sustain a production load.

                           

                          You suggested an older version of ORDS that may have the performance equivalent of mod_plsql? Version? Any help to migrate an existing PL/SQL web toolkit application to a supported a Weblogic/OHS is appreciated.

                           

                          Thanks,

                           

                          Joe

                          • 10. Re: Performance issue after migrating to ORDS 19.4 from mod_plsql.
                            thatJeffSmith-Oracle

                            I never meant to imply that we recommended the older ORDS.

                             

                            If you can't move off of basic auth, you might want to look at seeing what you can do to improve the performance of the logins themself from the database perspective.

                            • 11. Re: Performance issue after migrating to ORDS 19.4 from mod_plsql.
                              user12019829

                              Hi Jeff,

                               

                              Is there any reason that you are not supplying the ORDS version that reuses basic authentication connection?

                               

                              Would you expect that tuning the performance of database logins could even come close to mod_plsql that does connection pooling with basic authentication? We do have multi-threaded server configured, and were only doing a 50 user load test, a 5 user load test also had the performance issue.

                               

                              Please correct my understanding. ORDS 19.4 will create a database login for every request vs mod_plsql reusing the same database connection. Establishing database connections are expensive so when migrating from mod_plsql to ORDS 19.4 you will not expect to see the same performance when using basic authentication.

                               

                              Thanks,

                               

                              Joe

                              • 12. Re: Performance issue after migrating to ORDS 19.4 from mod_plsql.
                                thatJeffSmith-Oracle

                                1. B/c i don't know off the top of my head

                                2. No idea, but it's worth a shot if you're stuck with basic auth

                                3. Correct, and it's an extra step, so there will be more work if you're using basic auth

                                 

                                Do you understand why we require a connection on every request when using basic auth? It's a security issue, which trumps performance.

                                • 13. Re: Performance issue after migrating to ORDS 19.4 from mod_plsql.
                                  AndyH

                                  user12019829 wrote:

                                   

                                  We are happy with mod_plsql, it is Oracle that de-supported it 12/31/2019 and stated to use ORDS as a replacement.

                                  When was the last time that you needed Oracle to support a mod_plsql issue - do you need it to be supported?

                                  • 14. Re: Performance issue after migrating to ORDS 19.4 from mod_plsql.
                                    user12019829

                                    Hi,

                                     

                                    We have used mod_plsql for 20+ years without any issues. The reason we are migrating to ORDS is that mod_plsql has not been included with OHS 12.2.1.4.0. Reference: Future Direction for Application Express (APEX) and the Oracle HTTP Server (OHS) / Mod_plsql (Doc ID 1945619.1)

                                     

                                    Thanks,

                                    Joe

                                    1 2 Previous Next