Today a box arrived from Oracle Press. In it were a few copies of “Real-World SQL and PL/SQL” which I co-authored with Arup Nanda, Brendan Tierney, Heli Helskyaho and Alex Nuitjen. I know I only blogged about the book a couple of weeks back, how I became involved and the impact it had on my life for several months. But as I can now physically handle and read the final article, I could not resist putting up a quick post on it. Honestly, I’ll stop being a book bore soon.
My contribution to the book was three chapters in the section “Essential Everyday Advanced PL/SQL”. The idea was to covers some core, standard ways of using PL/SQL which are often overlooked or implemented without considering the impact they can have. There are a few things I cover that are often talked about, generally regarded as a good thing to do – but so often are not done! So just to quickly summarise my chapters:
Chapter 6 is about running PL/SQL from SQL, ie calling both built-in and user defined functions from SQL. It’s a great way to compartmentalise your business logic and extend the capabilities of Oracle’s SQL implementation in an easy and seamless manner. Only people are often unaware of the potential performance and read consistency impact it can have, or how Oracle 11g and 12c help reduce these issues.
Chapter 7, “Instrumenting and Profiling PL/SQL”, covers something that I feel is a major oversight in many PL/SQL development projects. Instrumenting your code, any code (not just PL/SQL), is vital to producing an application that is professional and will continue to work correctly for many, many years. However, it’s a bit like washing your hands after going to the loo – we all know it is the correct thing to do but so many people just don’t! Without instrumentation it is almost impossible to see how your code is performing, where time is spent and where problems are when they occur. I’m sick of having to guess where the problem is when people report slow performance when some basic and light-weight instrumentation will tell you exactly where the problem is. And as for profiling PL/SQL, it’s one of the rarest things to be done but it is so helpful.
Chapter 9 is on using PL/SQL for Automation and Administration. Like many people, I have automated many tasks with a PL/SQL harness – backups, partitions maintenance, metric gathering, data life-cycle management, regular data loads. You end up writing the same sort of process over and over again and usually there are several versions of such controlling frameworks across a company, written by different people (and sometimes the same people!). A large part of this chapter takes the code for creating the examples from chapter 6 and the instrumentation from chapter 7 and builds up a simple but comprehensive framework which can be used to control almost any data load or administrative task you need to do with an Oracle database. The key thing is it can be used for many, many processes so you need only the one framework. So you don’t have to keep writing them as it’s boring to keep writing them. And because the framework demonstrated includes instrumentation, the framework you implement will be easy to monitor and debug in years to come. I have to confess, I kind of wrote that chapter “just for me”. It is my standard automation framework that I now always use, so I can concentrate on the actual task being done and not the nuts-and-bolts of controlling it, and I’ve wanted to properly document it for years.
Something all the authors agreed on is that whilst most technical books describe how elements of a language or feature work, they do not really talk about the “how and why” you do things. The stuff you learn by using the language for a long time and doing a good deal of things wrong. In this book we attempt to put in some of that “how and why”. In my chapters there are a few anecdotes about when things have gone wrong and, as a result, you learn some of the “how not”