Learning is not a spectator sport

So after my post yesterday about PL/SQL being a no-brainer to get an easy boost to application performance, there was an interesting collection of responses on social media from both sides of the fence.

From the “the database is the universe” camp, there was criticism that the 30% benefit of PL/SQL was in fact understating the benefits because I ran the benchmark completely on a single machine. Thus I wasn’t taking into account the fact that database calls from the application tier (in this case Java) are more likely to be some “distance” from the database tier, and that the network latencies should also be taken into consideration (even though I explicitly stated I was deliberately keeping that out of the comparison ).

On the other side of the fence from the “the database is purely for data” camp, there was criticism that I was running the benchmark in a single thread, and thus was introducing a bias to the results, because in a true mid-tier application scenario there would be a pool of application processes all concurrently serving workload.

It seems it’s hard to please anybody nowadays

Anyway, such criticisms are trivial to address because Swingbench is easy to configure for any desired number of concurrent users, and given that my lounge room looks like a laptop graveyard, I’ve got plenty of machines to let me create a benchmark configuration where the database tier and the application tier are separated by a network. In my case, it will be a gigabit wired network. I’d love to say that this is nothing like a true data centre network, but I’m always surprised at just how poorly treated the network can be inside modern data centres. Anyway, I digress.

I’m keeping the think time at zero, so we’re throwing calls as fast as we can from the application server to the database, and I’m setting the number of concurrent users to 4 to keep my database laptop from throwing it’s toys out of the pram. Firstly, here are the results of Swingbench with all SQL calls coming directly from the Java client.

C:\oracle\swingbench\winbin>java -cp ../launcher LauncherBootstrap -executablename charbench charbench -c ..\configs\SOE_Client_Side.xml -a -v tps -nc
Swingbench
Author  :        Dominic Giles
Version :        2.6.0.1163

Results will be written to results.xml

Time     TPS
08:19:13 0
08:19:14 177
08:19:15 471
08:19:16 492
08:19:17 456
08:19:18 507
08:19:19 512
08:19:20 499
...
...
08:19:57 509
08:19:58 508
08:19:59 539
08:20:00 520
08:20:01 636
08:20:02 596
Saved results to results.xml

Completed Run.

C:\oracle\swingbench\winbin>grep Transactions results.xml
<TotalCompletedTransactions>24902</TotalCompletedTransactions>
<AverageTransactionsPerSecond>508.2</AverageTransactionsPerSecond>
   
   

Excluding the initial ramp up, I’ll be generous and call this ~550 transactions per second, and I want to be fair to the middle tier fans here. This result shows that a well written Java application that takes advantage of all of the Oracle scalability features (ie, uses binds, prepared statements, statement caching etc) does a great job at crunching through database transactions.

Now I’ll repeat the exercise with the PL/SQL based configuration of the order entry benchmark. Just to ensure no-one thinks I’m gaming the system, this is still a genuine Java application executing a call for each transaction. We still have to go across the network for each transaction, but we are bundling up as many calls as we can into a single trip. I wanted to mention this because I had a question (aka accusation of cheating ) that my PL/SQL benchmark was a single call from Swingbench at the start of the benchmark and that it never returned to the app until the benchmark was complete. This is definitely not the case.

C:\oracle\swingbench\winbin>java -cp ../launcher LauncherBootstrap -executablename charbench charbench -c ..\configs\SOE_Server_Side_V2.xml -a -v tps -nc
Swingbench
Author  :        Dominic Giles
Version :        2.6.0.1163

Results will be written to results.xml

Time     TPS
08:21:03 0
08:21:04 755
08:21:05 1113
08:21:06 1092
08:21:07 1138
08:21:08 1115
08:21:09 1051
08:21:10 1237
08:21:11 1256
08:21:12 1292
08:21:13 1079
...
...
08:21:54 1350
08:21:55 1280
08:21:56 1304
08:21:58 1293
08:21:59 1342
08:22:00 1298
Saved results to results.xml

Completed Run.

C:\oracle\swingbench\winbin>grep Transactions results.xml
<TotalCompletedTransactions>74046</TotalCompletedTransactions>
<AverageTransactionsPerSecond>1234.1</AverageTransactionsPerSecond>
   

So I hope everyone is happy now

You can get good performance with your Java application when its running across the network and you’re not using PL/SQL. The only problem is, you can get somewhere between double and triple that performance with the same application, the same network, the same application server and the same database server if you throw a little PL/SQL into the mix.

Or perhaps think of it in terms your IT senior management will understand:

You’re spending three times more money than you need to be

Here’s some additional much simple demos that show the benefit of having PL/SQL in your application stack