PL/SQL – A Programmer’s Introduction – or Welcome to the Dark Side

This week, the Open Source Karma has been cast-aside. We’re going proprietary in a big way. We’re going to the very heart of Oracle’s power, deep inside the RDBMS – yes – it’s PL/SQL.

This post is dedicated to ( and essentially co-written by) Simon. Yes, my long-time best mate, long-time Luton Town fan, long-time Teradata expert and long time everything really ( we’ll he’s not as young as he was).
After all these years, Simon has become a bit curious about this PL/SQL thing I’m always going on about and would like to know more.
It is this desire – and large amounts of beer – that has persuaded him to play the Igor to my mad scientist and have a wander through this very quick guide to the language at the heart of most Oracle applications. In fact we came up with several possible descriptions of Simon’s role in this post, but he had a “hunch” that this was the right one.
So for him, and any other programmers who want to get up and running with PL/SQL, but don’t need to be told what a variable is, what follows is – not so much a PL/SQL 101 – as a PL/SQL 23-and-a-bit. Continue reading

Caching in – adventures in Oracle Tuning

Given the job of tuning a problem statement, I’ll usually try to work on it on a lightly used database. More importantly, where practical, I’ll execute the statement twice, one after the other, and use the response time for the second run as the starting point – the timing that my tuning changes to the statement need to improve upon.

My reason for doing this is simple – I want to filter out the effects of database caching on performance of this individual statement.

Obviously, this approach tends to work best for relatively short running queries. If you’re lumbered with a 12-hour Batch behemoth, then this isn’t going to be an appealing plan of attack. Continue reading