1 2 3 Previous Next 35 Replies Latest reply on Jun 5, 2020 8:50 AM by 4235006

    ORDS TOMCAT PERFORMANCE IMPROVEMENT SETTINGS

    4235006

      Hi Community,

       

      I have installed ORDS 19.4 on Tomcat 9.0.34 on AWS RDS Oracle EE 12c.

       

      When fetching large number of rows, ORDS is very slow.

       

      I have already change entries in defaults.xml for JDBS maxlimitconnections:

       

      <entry key="jdbc.DriverType">thin</entry>

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

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

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

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

      <entry key="jdbc.InactivityTimeout">1800</entry>

      <entry key="jdbc.statementTimeout">900</entry>

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

       

      But there is no improvement.

       

      When changing the page_size from default 20 to 500 the time for fetching > 300.000 records comes from 11 hours to 1 hour.

       

      But still it is not acceptable and we need further improvements.

       

      The authentication is set on 'Basic Authentication', and ORDS service creates for every feched page a new connection/authentication.

       

      I wonder if this could be changed by AUTH2 authentication + storing the token for the secret client key? How should I implement this?

       

      Can we use connection pooling on Tomcat level?

       

      I would very much appreciate any feedback on above questions.

       

      Many thanks,

       

      Kind regards,

      Johan Hoste

        • 1. Re: ORDS TOMCAT PERFORMANCE IMPROVEMENT SETTINGS
          thatJeffSmith-Oracle

          you're asking for 300,000 rows, so that means

           

          1)ords has to fetch all those rows

          2)convert it to a json payload

          3)deliver that payload

           

          Now, compare that to what happens when you use the default pagesize of 25...it's MUCH faster.

           

          If you ask for more....you get more. But nothing's free.

           

          Better maybe to generate a file/blob and download that directly.

          • 2. Re: ORDS TOMCAT PERFORMANCE IMPROVEMENT SETTINGS
            4235006

            Hi Community,

             

            Is there someone with ORDS knowledge who can give some hints?

             

            Thanks a lot.

             

            Johan

            • 3. Re: ORDS TOMCAT PERFORMANCE IMPROVEMENT SETTINGS
              thatJeffSmith-Oracle

              ah, i thought were you pulling them all at once, you're paging them....

               

              in 19.4, connections are reused if possible, do you don't necessarily have a connection on very request now

               

              i'm not sure if the oauth2 workflow would speed things up

               

              what would be an acceptable amount of time to get your 300,000 records?

               

              I'm assuming you already tried bumping your pagesize to 1,000 from 500

              • 4. Re: ORDS TOMCAT PERFORMANCE IMPROVEMENT SETTINGS
                4235006

                yes, changing pagesize improve the performance but at a certain limit. When setting pagesize to 4000 and more, the time duration of the total fetch remains around 11 min. and I still needs to improve this. In comparison with the use of db links one fetch takes 2 min.

                 

                Thanks for your help

                 

                Johan

                • 5. Re: ORDS TOMCAT PERFORMANCE IMPROVEMENT SETTINGS
                  4235006

                  below is the logging when trying to simulate the fetch in PL/SQL package with just a select from tablepic_100.jpg

                  the first fetch of 50 records is after 106 seconds.

                   

                  This is the ORDS debug logging:

                   

                  10.69.86.43 - - [14/May/2020:13:10:49 +0000] "GET /ords/app_crf_p/crf4sp2/nam HTTP/1.1" 200 1246004

                  10.69.86.43 - - [14/May/2020:13:10:52 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=5000 HTTP/1.1" 200 1249074

                  10.69.86.43 - - [14/May/2020:13:10:54 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=10000 HTTP/1.1" 200 1248821

                  10.69.86.43 - - [14/May/2020:13:10:56 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=15000 HTTP/1.1" 200 1248180

                  10.69.86.43 - - [14/May/2020:13:10:58 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=20000 HTTP/1.1" 200 1249704

                  10.69.86.43 - - [14/May/2020:13:11:00 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=25000 HTTP/1.1" 200 1249496

                  10.69.86.43 - - [14/May/2020:13:11:02 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=30000 HTTP/1.1" 200 1247524

                  10.69.86.43 - - [14/May/2020:13:11:04 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=35000 HTTP/1.1" 200 1287175

                  10.69.86.43 - - [14/May/2020:13:11:06 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=40000 HTTP/1.1" 200 1304168

                  10.69.86.43 - - [14/May/2020:13:11:08 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=45000 HTTP/1.1" 200 1304005

                  10.69.86.43 - - [14/May/2020:13:11:10 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=50000 HTTP/1.1" 200 1303019

                  10.69.86.43 - - [14/May/2020:13:11:12 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=55000 HTTP/1.1" 200 1305186

                  10.69.86.43 - - [14/May/2020:13:11:14 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=60000 HTTP/1.1" 200 1304410

                  10.69.86.43 - - [14/May/2020:13:11:16 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=65000 HTTP/1.1" 200 1302759

                  10.69.86.43 - - [14/May/2020:13:11:18 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=70000 HTTP/1.1" 200 1297008

                  10.69.86.43 - - [14/May/2020:13:11:22 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=80000 HTTP/1.1" 200 1304017

                  10.69.86.43 - - [14/May/2020:13:11:24 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=85000 HTTP/1.1" 200 1303595

                  10.69.86.43 - - [14/May/2020:13:11:27 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=90000 HTTP/1.1" 200 1304208

                  10.69.86.43 - - [14/May/2020:13:11:29 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=95000 HTTP/1.1" 200 1304455

                  10.69.86.43 - - [14/May/2020:13:11:31 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=100000 HTTP/1.1" 200 1303063

                  10.69.86.43 - - [14/May/2020:13:11:33 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=105000 HTTP/1.1" 200 1298785

                  10.69.86.43 - - [14/May/2020:13:11:35 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=110000 HTTP/1.1" 200 1301414

                  10.69.86.43 - - [14/May/2020:13:11:37 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=115000 HTTP/1.1" 200 1304273

                  10.69.86.43 - - [14/May/2020:13:11:39 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=120000 HTTP/1.1" 200 1303400

                  10.69.86.43 - - [14/May/2020:13:11:41 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=125000 HTTP/1.1" 200 1303858

                  10.69.86.43 - - [14/May/2020:13:11:43 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=130000 HTTP/1.1" 200 1305141

                  10.69.86.43 - - [14/May/2020:13:11:45 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=135000 HTTP/1.1" 200 1302910

                  10.69.86.43 - - [14/May/2020:13:11:47 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=140000 HTTP/1.1" 200 1299958

                  10.69.86.43 - - [14/May/2020:13:11:49 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=145000 HTTP/1.1" 200 1299992

                  10.69.86.43 - - [14/May/2020:13:11:51 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=150000 HTTP/1.1" 200 1304451

                  10.69.86.43 - - [14/May/2020:13:11:53 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=155000 HTTP/1.1" 200 1303530

                  10.69.86.43 - - [14/May/2020:13:11:55 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=160000 HTTP/1.1" 200 1303456

                  10.69.86.43 - - [14/May/2020:13:11:57 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=165000 HTTP/1.1" 200 1305085

                  10.69.86.43 - - [14/May/2020:13:11:59 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=170000 HTTP/1.1" 200 1303880

                  10.69.86.43 - - [14/May/2020:13:12:01 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=175000 HTTP/1.1" 200 1300548

                  10.69.86.43 - - [14/May/2020:13:12:03 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=180000 HTTP/1.1" 200 1299189

                  10.69.86.43 - - [14/May/2020:13:12:05 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=185000 HTTP/1.1" 200 1303982

                  10.69.86.43 - - [14/May/2020:13:12:07 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=190000 HTTP/1.1" 200 1303720

                  10.69.86.43 - - [14/May/2020:13:12:09 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=195000 HTTP/1.1" 200 1303572

                  10.69.86.43 - - [14/May/2020:13:12:11 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=200000 HTTP/1.1" 200 1304969

                  10.69.86.43 - - [14/May/2020:13:12:13 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=205000 HTTP/1.1" 200 1303902

                  10.69.86.43 - - [14/May/2020:13:12:16 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=210000 HTTP/1.1" 200 1302826

                  10.69.86.43 - - [14/May/2020:13:12:18 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=215000 HTTP/1.1" 200 1296825

                  10.69.86.43 - - [14/May/2020:13:12:20 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=220000 HTTP/1.1" 200 1304014

                  10.69.86.43 - - [14/May/2020:13:12:22 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=225000 HTTP/1.1" 200 1303936

                  10.69.86.43 - - [14/May/2020:13:12:24 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=230000 HTTP/1.1" 200 1303386

                  10.69.86.43 - - [14/May/2020:13:12:27 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=235000 HTTP/1.1" 200 1304767

                  10.69.86.43 - - [14/May/2020:13:12:29 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=240000 HTTP/1.1" 200 1304437

                  10.69.86.43 - - [14/May/2020:13:12:31 +0000] "GET /ords/app_crf_p/crf4sp2/nam?offset=245000 HTTP/1.1" 200 1302480

                  • 6. Re: ORDS TOMCAT PERFORMANCE IMPROVEMENT SETTINGS
                    thatJeffSmith-Oracle

                    paging through 300,000 records is never going to be 'fast'

                     

                    which is why I suggested creating a payload you download all at once - but i don't know what your use case is either

                     

                    when moving from db_links to HTTP, your expectations need to shift a bit...what are you doing with these 300,000 rows?

                    • 7. Re: ORDS TOMCAT PERFORMANCE IMPROVEMENT SETTINGS
                      thatJeffSmith-Oracle

                      dumb question, how long does it take if you set the pagesize to 0, and also configure ords to allow for a pagesize of 300,000

                       

                      how long does it take to get in a single request (not that I recommend this per se, but...)

                      • 8. Re: ORDS TOMCAT PERFORMANCE IMPROVEMENT SETTINGS
                        4235006

                        Now it takes 4 min.

                         

                        This is only 2 times more then using db links.

                         

                        Do you have any other suggestions?

                         

                        Thanks a lot.

                        • 9. Re: ORDS TOMCAT PERFORMANCE IMPROVEMENT SETTINGS
                          4235006

                          Hi again,

                           

                          I am trying to setup a (Apache) load balancer pointing to 3 ORDS services running on 3 tomcat servers.

                           

                          I have adapt httpd.conf:

                           

                          <Proxy balancer://ordscluster>

                              # WebHead0 hostjoh

                              BalancerMember http://10.69.86.79:8080

                              # WebHead1 hostjoh3

                              BalancerMember http://10.69.86.55:8080

                           

                           

                              # WebHead2 hostjoh4

                              BalancerMember http://10.69.86.6:8080

                           

                           

                              ProxySet lbmethod=byrequests

                           

                           

                          </Proxy>

                           

                          Apache is running on port 80

                           

                          How should the ORDS URL now look like? instead of http://10.69.86.79:8080/ords/... ??

                           

                          Thanks for any feedback.

                           

                          Kind regards,

                          Johan

                          • 10. Re: ORDS TOMCAT PERFORMANCE IMPROVEMENT SETTINGS
                            Olafur T

                            Hi,

                             

                            When dealing with that number of rows I almost always move the service to PL/SQL and use htp.prn to pipe out the rows. The speed benefit is immense since ORDS no longer has to parse the data and produce a valid JSON.

                             

                            I simply mimic the results with {"items":[….]}

                             

                            PL/SQL + htp.prn does not follow the pagesize directive, so it can be any size. Also turn on gzip on your webserver to minimize the payload.

                             

                            A bit more work but as soon as you have it running you'll see the difference.

                             

                            Example code:

                            declare
                              l_comma boolean := false;
                            begin
                              owa_util.status_line(200, '', false);
                              owa_util.mime_header('application/json', true);
                              htp.prn('{"items":[');
                              for i in (select object_id, object_type, object_name from user_objects order by object_type, object_name) loop
                                if l_comma then
                                  htp.prn(',');
                                else
                                  l_comma := true;
                                end if;
                              
                                if i.object_id is null then
                                  htp.prn('{"object_id": null');
                                else
                                  htp.prn('{"object_id":' || i.object_id);
                                end if;
                                htp.prn(',"object_type":' || apex_json.stringify(i.object_type));
                                htp.prn(',"object_name":' || apex_json.stringify(i.object_name));
                              end loop;
                              htp.prn(']}');
                            end;
                            

                             

                            You can also easily add pagination with offset X rows fetch next Y rows only.

                             

                            regards

                            Olafur

                            • 11. Re: ORDS TOMCAT PERFORMANCE IMPROVEMENT SETTINGS
                              4235006

                              Hi Olafur,

                               

                              I can not follow your proposal, I have define an ORDS service with gives me following URL http://10.69.86.79:8080/ords/app_crf_p/crf4sp2/nam

                               

                              how should I use htp.prn based on that URL?

                               

                              Thanks,

                              Johan

                              • 12. Re: ORDS TOMCAT PERFORMANCE IMPROVEMENT SETTINGS
                                Olafur T

                                By using source type = PL/SQL instead of Collection Query in your GET handler. The URL doesn't matter.

                                 

                                Optional parameters area also easily implemented, this case :object_type

                                 

                                 

                                 

                                The url structure (schema alias, module, template) doesn't matter here.

                                 

                                 

                                Regards

                                Olafur

                                • 13. Re: ORDS TOMCAT PERFORMANCE IMPROVEMENT SETTINGS
                                  4235006

                                  Hi, I still can not follow your suggestion. Is this screenshot from APEX?

                                  My testcase is done manually without the use of APEX:

                                  now I have changed my ORDS service like you said:

                                   

                                  BEGIN

                                  ORDS.define_service(

                                      p_module_name    => 'crf13',

                                      p_base_path      => 'crf13/',

                                      p_pattern        => 'nam/',

                                      p_method         => 'GET',

                                      p_source_type    => 'PL/SQL',

                                      p_source         => 'SELECT per_id, family_name, given_name FROM "APP_CRF_P"."SP2_PER_NAMES_V"',

                                      p_items_per_page => 0);

                                    COMMIT;

                                  END;

                                   

                                  How can I use now this service to fetch all rows? I thought you need to use an URL to go via ORDS to the database where the table in above query is hosted?

                                   

                                  Can you explain how we can avoid parsing the json and use htp.prn?

                                   

                                  Thanks,

                                  Johan

                                  • 14. Re: ORDS TOMCAT PERFORMANCE IMPROVEMENT SETTINGS
                                    Olafur T

                                    Hi Johan,

                                     

                                    Yeah, I use the APEX environment or PL/SQL (ords_export + run on prod) ever since I found out that using SQL Developer ignored p_comment when using it to export from Dev to Prod. Didn't understand why my users found the Swagger documentation bad until I checked the created code SQLDev did.  PS. thatJeffSmith-Oracle Is that fixed?

                                     

                                    So, just run this in your test environment and it will create a new module called "123 Test" (just to get it on top).

                                     

                                    BEGIN
                                    
                                      ORDS.DEFINE_MODULE(
                                          p_module_name    => '123 Test',
                                          p_base_path      => '/testme/',
                                          p_items_per_page => 25,
                                          p_status         => 'PUBLISHED',
                                          p_comments       => NULL);
                                    
                                      ORDS.DEFINE_TEMPLATE(
                                          p_module_name    => '123 Test',
                                          p_pattern        => 'plsql-example',
                                          p_priority       => 0,
                                          p_etag_type      => 'HASH',
                                          p_etag_query     => NULL,
                                          p_comments       => NULL);
                                      ORDS.DEFINE_HANDLER(
                                          p_module_name    => '123 Test',
                                          p_pattern        => 'plsql-example',
                                          p_method         => 'GET',
                                          p_source_type    => 'plsql/block',
                                          p_mimes_allowed  => '',
                                          p_comments       => NULL,
                                          p_source         => 
                                    'declare  
                                      l_comma boolean := false;  
                                    begin  
                                      owa_util.status_line(200, '''', false);  
                                      owa_util.mime_header(''application/json'', true);  
                                      htp.prn(''{"items":['');  
                                      for i in (select object_id, object_type, object_name from user_objects order by object_type, object_name fetch next 500 rows only) loop  
                                        if l_comma then  
                                          htp.prn('','');  
                                        else  
                                          l_comma := true;  
                                        end if;  
                                        
                                        if i.object_id is null then  
                                          htp.prn(''{"object_id": null'');  
                                        else  
                                          htp.prn(''{"object_id":'' || i.object_id);  
                                        end if;  
                                        htp.prn('',"object_type":'' || apex_json.stringify(i.object_type));  
                                        htp.prn('',"object_name":'' || apex_json.stringify(i.object_name));  
                                      end loop;  
                                      htp.prn('']}'');  
                                    end; ');
                                            
                                    COMMIT;
                                    END;
                                    

                                     

                                    Then just do a GET  {your base path} + /testme/plsql-example

                                     

                                    When you define a GET handler as "PL/SQL" you need to create a PL/SQL block and not use standard SQL.  As soon as you get a hang on mixing both types depending on need and complexity, you find out that ORDS is a huge gamechanger in our industry.

                                     

                                    Regards

                                    Olafur

                                     

                                    ps. if you don't have APEX standard/runtime change the following lines

                                    htp.prn('',"object_type":'' || apex_json.stringify(i.object_type));   
                                    htp.prn('',"object_name":'' || apex_json.stringify(i.object_name));  

                                    to

                                    htp.prn('',"object_type":"'' || i.object_type || ''"'');   
                                    htp.prn('',"object_name":"'' || i.object_name || ''"'');  

                                    1 2 3 Previous Next