Strange performance using JPA, am I missing something?

Question

We have a JPA -> Hibernate -> Oracle setup, where we are only able to crank up to 22 transactions per seconds (two reads and one write per transaction). The CPU and disk and network are not bottlenecking.

Is there something I am missing? I wonder if there could be some sort of oracle imposed limit that the DBA's have applied?

• Network is not the problem, as when I do raw reads on the table, i can do 2000 reads per second. The problem is clearly writes.
• CPU is not the problem on the app server, the CPU is basically idling.
• Disk is not the problem on the app server, the data is completely loaded into memory before the processing starts

Answer 1

Might be worth comparing performance with a different client technology (or even just a simple test using SQL*Plus) to see if you can beat this performance anyway - it may simply be an under-resourced or misconfigured database.

I'd also compare the results for SQL*Plus running directly on the d/b server, to it running locally on whatever machine your Java code is running on (where it is communicating over SQL*Net). This would confirm if the problem is below your Java tier.

To be honest there are so many layers between your JPA code and the database itself, diagnosing the cause is going to be fun . . . I recall one mysterious d/b performance problem resolved itself as a misconfigured network card - the DBAs were rightly insistent that the database wasn't showing any bottlenecks.

Answer 2

It sounds like the application is doing a transaction in a bit less than 0.05 seconds. If the SELECT and UPDATE statements are extracted from the app and run them by themselves, using SQL*Plus or some other tool, how long do they take, and if you add up the times for the statements do they come pretty near to 0.05? Where does the data come from that is used in the queries, and which eventually gets used in the UPDATE? It's entirely possible that the slowdown is not the database but somewhere else in the app, such a the data acquisition phase. Perhaps something like a profiler could be used to find out where the app is spending its time.

Share and enjoy.