We see considerable drop in performance as we increase the number of concurrent processing. From DB side we dont see hug degradation but end to end from ORDS we see significant bad performance

ORD S version 19.2

DB version 18c

tomCat version 7.8 -- running on Linux

We have singled handler select from a table with some where conditions and restricted to return 10 rows.  The where clause is restricted to combination of 3 fields and all indexed. The select happens with single input json parameter "where" and it is just added to the select dynamically and executed

Following testing done

** Java program which hits the ords rest api with various where clauses. The number of threads (concurrent) can be changed and total test cases is 10,000. So given count of thread say x number of test cases executed one after other = 10,000/x

DB -- ExaCC. One container 18c and one DB.

1. Installed on tomcat + ords on 8cpu 8GB RAM

          25 threads -- Average milli seconds per txn is around 50 and hence we achieve 20 TPS per thread. So max is 20*25=500 tps

          40 threads -- Average milli seconds increases to 75 and hence we achieve total 533tps

          60 threads -- Average milli seconds increases to 130 and hence starts dropping

Any further increase the time take per transactions increases --

DB side we took snaps and saw the performance variation is 1 to 5 milli seconds and max is 20ms -- but overall performance is slower. Maximum CPU usage seen was 70%

2. Installed on tomcat + ords on 12Cpu, 8GB RAM

     25 threads      -- Average 40 miili seconds,  total tps ~ 621

     65 threads      -- Average 51 milli seconds, total tps ! 1227

     80 threads      -- Average 65 milli seconds, total tps 1200

    200 threads     -- Average 155 milli seconds, total tps 1226

    500 threads     -- Average 300 milli seconds, total tps 2000

    1000 threads   -- Average 591 milli seconds, total tps 2254

** We had adjusted the total ORDS SQL connections and tomcat threads and dont see any connection pool error

DB side we took snaps and saw the performance variation is 1 to 5 milli seconds -- but overall performance is slower. Maximum CPU usage seen was 70% (same but higher number of CPU @ server level) . From db side we see not more than 70 ms as max and that translating to average of 591mill second seems to be the problem. THE PROBLEM SQL "SELECT MODULE .. USER_ORDS" does take 35mill seconds and that is included in above 70ms. Believe this is fixed on 19.4 and if any patch available now we can add and test that as well

When we try in both servers-- Server 25 threads and server 2 80 threads we get same performance from each server. So this again shows not much of impact at DB end.

As mentioned @DB end for any volume (even at 2000 tps) we dont see any degradation in db transaction.

We would have around 2500 txn per second as soon as we go into production across 60 APIs (some are single table and some are more tables). From db side we are confident that we can manage < 250ms max but based on the above test we see ORDS takes lot more time --

To ensure no addition to the output size we have tested set of input json restricting to 10rows maximum output -- so we are believe the test condition is constant

Any help will be highly appreciated