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 : 18.104.22.1683 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 : 22.214.171.1243 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