The thoughts of Jason Arneil

Menu

Book Review: Mastering Oracle PL/SQL

I have never been a developer and I would never attempt to sell myself on the basis of my coding abilities. I come from an SA/storage background, which may put me in a minority amongst DBAs but is sure useful when you are talking about luns and ASM and I think I fulfill the criteria of DBA 2.0 as set out by Joel Goodman and Harald van Breederode.

However, I’ve often felt that I should brush up on my PL/SQL knowledge, but just never found a book that particularly appealed to me. Now I have. I’ve seen some of Connor McDonald’s fantastic presentations at the UKOUG Birmingham Conferences, but until now I had not read his book.

I have found Mastering Oracle PL/SQL to have been an absolutely cracking read. The book is subtitled Practical Solutions and that is what it delivers in spades, it is not just a walkthrough of the syntax.

One of the most enjoyable features of the way this book is written is how Connor shows different ways of tackling a particular problem and then provides concrete benchmarking of each of them so you can scientifically see which is the most performant solution and he then explains why.

Chapters I particularly liked were:

Chapter 1: Efficient PL/SQL

This is a big chapter and has a strong focus on performance. Connor starts by stating that it’s important to use the right tool for the right job and that by keeping it simple often by using PL/SQL it leads to less coding effort than fancy expensive fat mid tiers. I particularly like the statement:

“not using PL/SQL with Oracle is coding with one hand tied behind your back”.

The point was made that performance is all about what is acceptable to your users, not making something go as fast as possible. Any solution of course must also have acceptable concurrency levels as well.

Good discusion about parsing with some nice, clear examples of why you want to minimise this as much as possible.

Chapter 2: Package it All Up

As you may have guessed this is a chapter about packages, and Connor is a big fan of them. He goes on to show various advantages of packages, including a detailed look at how they may lead to less invalidations when you perform changes to your code. Liked the bit on package overloading here as well.

Chapter 3: Cursors

There is a bit of a comparison between implicit and explicit cursors – Connor thinks implicit cursors have had a bit of a bad press and goes on to show how they are actually often times the best thing to use and at least (if not more so) as performant as explicit cursors. He demonstrates his points about cursors with good scientific examples. Talks a little about pre-fetching and why this helps implicit cursors. Claims there are a lot of myths out on the internet about implicit cursors and they really are good thing.

Of course sometimes only an explicit cursor will do.

Chapter 4: Effective Data Handling

Really important to assign the correct datatype to a variable. Test harness to show the inefficiency of having to do implicit conversions – it’s definitely something to avoid.
Good discussion on %TYPE and how it’s used to tie the definition of a variable to how the column is defined within a table. Both %TYPE and %ROWTYPE make making changes to your schema much easier as the changes ripple throughout your PL/SQL rather than having to search everywhere to make changes.

Good amount of detail on bulk collections, showing with an example how more performant it is. Also very interesting discussion, diagram, and examples on context switching between PL/SQL and SQL. It’s something you want to minimise for sure.

Chapter 5: PL/SQL Optimisation

This chapter showed examples of minimising parsing and that you can’t just suck everything up into memory as you wont scale beyond a tiny amount of users, Connor showed examples of how much memory various techniques used. Basically large collections equals large amounts of memory used.

Bit about triggers and showing that coding the work inside a package is more efficient than doing the same work in a trigger: it does less parsing.

Code in SQL if you can (seen a caveat that sometimes PL/SQL is more maintainable sometimes than over complicated SQL) PL/SQL is an extension of SQL.

Chapter 6: Triggers

Big chapter on triggers. Begins with comparing before row triggers and after row triggers. The after row trigger being more efficient. Triggers run with the privileges of the triggers owner and run with roles disabled. Discussions on “instead of triggers”, mutating tables, and autonomous transactions.

I had not heard of table versioning available from DBMS_WM (workspace manager). Looks like a potential way of auditing changes in tables so you can see a history of them.

Chapter 8: Security Packages

Insightful details on definer rights and invoker rights. PL/SQL procedures only run with directly granted privileges NOT those granted through a role. Privs granted via Roles only enabled in execution under invoker rights, never under definer and never upon compilation.

All in all I thought this was a really excellent book, in particular the first 5 chapters really stood out for me, I surprised myself by how much I enjoyed reading them. It’s definitely not for the total beginner, but the first 5 chapters in particular I think can be picked up fairly straightforwards by someone with only slight exposure to PL/SQL.

The book actually came out in 2004 but I would not let that put you off, it covers up to and including 10gR2, so I think the techniques are still current.

I’d highly recommend this book if you have not availed yourself of reading it already.