Learning is not a spectator sport

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

I stress – I’m not jumping on some soapbox and demanding that you write your entire application stack in PL/SQL. I’m not saying that your choice of Java, Rust, Javascript, Python, C#, Go etc etc was a mistake. I don’t care what language is your preference – you can use whatever you like. I chuckle when IT professionals claim that they are “full stack” developers, but only as long as “full stack” means they build things solely in their favourite language, with a little bit of SQL plus begin able to SSH onto the servers to round out the fullness of “full stack”. I’ve always been of the opinion that in IT, like many other things, the best approach is generally going to be: “Right tool for the right job”, so pick whatever language you want.

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 :        2.6.0.1163

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 :        2.6.0.1163

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