I rarely blog about PLSQL for a very simple reason. It quickly becomes like Halloween night because all the ghouls and goblins come out to play . No matter what I post, I immediately get inundated with a swag of comments and tweets about “stored procedures are evil”, “PLSQL is vendor lock-in”, “Server side code is an Oracle plot for world domination”, “PL/SQL is not OO so its impossible to use and learn”, “Database-resident code can’t scale” etc etc. To be honest it all just gets exhausting because it feels like you have wandered into a cult meeting with the wrong point of view.
Yet…here I am, returning to the well of PL/SQL once again ready for the flame bots to rise up and lose their minds again. Today I have a very simple proposition for you:
You might just need a little bit of PL/SQL
However, when it comes to interacting with the database then SQL and PL/SQL are great partners. Now before you accuse me of being an Oracle bigot (side note: I am ), I’m happy to broaden that discussion to SQL and any database-resident code execution environment. The core message here is not “PL/SQL is the golden child” – rather this post is language agnostic, namely:
Running code on a database server close to the data is a good thing!
In order to stay away from the emotional debates, this post is just going to focus on just one of the flame comments I mentioned earlier, namely, application performance will somehow suffer when using database-resident code, aka PLSQL. This is the most common of FUD avenues of attack when PL/SQL is discussed because running more code on a database server must “surely” work that database server harder? So I’m going to explore that with something that is an excellent approximation to a real world application that uses an Oracle database. I’m using Swingbench by Dom Giles, which is a Java application that can be used to model various typical application scenarios. I’ve downloaded the latest version and adjusted the configuration to do the following:
- Run an order entry style benchmark, with a mixture of customer queries and transactions
- It runs as a single user only with zero think time, because I want to get the upper limit of single thread performance.
The beauty of running Swingbench is that Order Entry benchmark can be run in 2 modes – one where all database interactions are with SQL coming from the Java application, and the other where the same workload is done but the database interaction is all performed with PL/SQL program units being called from the Java application. Thus we end up with a perfect comparison of the same database workload coming directly from a common middle tier application language versus PL/SQL doing some of the heavy lifting.
Swingbench has lots of cool GUI options but for the sake of simplicity, I’ll work with it in character mode. I’m going to fire off Swingbench and report every second what the current transaction rate (per second) is. (Remember we’re doing just a single user thread here). Also, everything in these benchmarks (database, swingbench) is running on the same machine (my laptop) so network latency is as small as it can be.
Here’s the first one with all SQL coming straight 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 : 22.214.171.1243 Results will be written to results.xml Time TPS 12:48:16 0 12:48:17 83 12:48:18 121 12:48:19 158 12:48:20 167 12:48:21 172 12:48:22 217 12:48:23 207 12:48:24 225 12:48:25 242 12:48:26 246 12:48:27 265 12:48:28 267 12:48:29 268 ... ... ... 12:49:12 293 12:49:13 310 12:49:14 298 12:49:15 308 12:49:16 304 Saved results to results.xml Completed Run. C:\oracle\swingbench\winbin>grep Transactions results.xml <TotalCompletedTransactions>16689</TotalCompletedTransactions> <TotalFailedTransactions>0</TotalFailedTransactions> <AverageTransactionsPerSecond>277.4</AverageTransactionsPerSecond>
After a few seconds to ramp up, we got to around 300 transactions per second. The final average is 277 because that takes the ramp up into account, but let me be generous and call it 300.
Now I can run the same workload, the only difference being the Java application now calls PL/SQL routines to interact with the database. Yes, that’s right – we’re introducing an additional layer here. Java calls PL/SQL which then runs the SQL that was originally coming straight out of the Java classes.
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 : 126.96.36.1993 Results will be written to results.xml Time TPS 12:50:14 0 12:50:15 114 12:50:16 169 12:50:17 216 12:50:18 328 12:50:19 421 12:50:20 411 12:50:21 397 ... ... ... 12:51:10 418 12:51:11 433 12:51:12 390 12:51:13 416 12:51:14 415 Saved results to results.xml Completed Run. C:\oracle\swingbench\winbin>grep Transactions results.xml <TotalCompletedTransactions>22540</TotalCompletedTransactions> <TotalFailedTransactions>0</TotalFailedTransactions> <AverageTransactionsPerSecond>375.64</AverageTransactionsPerSecond>
Our final average is 375, but we can see from the detail that there were regular peaks over 400 tps. On the same machine, same database, same workload and the same application, we obtained a hefty performance boost.
As we say in Australia, lets not beat around the bush here. If you can get more work done with the same server, this means more bang for your buck. Conversely, in an environment where you are charged for resource consumption, the same amount of work can be done by spending less.
So do you need to rewrite your apps? No! For your own existing applications, you do not need to refactor the entire code base to get the benefits of PL/SQL. We simply adopt the Pareto principle. Just touching maybe 10% of the code in your application, namely the code that uses the database most heavily, and selectively adding PL/SQL into the mix for that 10% is going to give you all benefits you need.
So next time you see the intertubes decrying stored procedures, or database-resident code, ask yourself if getting swept up in such emotional debates is really worth missing out on a 30% performance boost for your database applications