Tracing for fun and (tk)profit

If you ever wanted proof that time is relative, just consider The Good Old Days.
Depending on your age, nationality, personal preferences etc, that time could be when rationing finally ended; or when Trevor Brooking won the Cup for West Ham with a “bullet” header; or possibly when Joe Carter hit a three-run homer to seal back-to-back World Series for the Blue Jays.
Alternatively, it could be when you were able to get on to the database server and use tkprof to analyse those tricky database performance issues.

In these days of siloed IT Departments, Oracle trace files, nevermind the tkprof utility are out of the reach of many developers.
The database server itself is the preserve of Unix Admins and DBAs, groups which, with good reason, are a bit reluctant to allow anyone else access to the Server at the OS level.

Which is a pity. Sometimes there is just no substitute for getting into the nitty gritty of exactly what is happening inside a given session.

For those of you who miss The Good Old Days of tkprof, what follows is an exploration of how to access both trace files and even the tkprof utility itself without leaving the comfort of your database.
I’ll go through a quick recap of :

  • how to generate a trace file for a session
  • using tkprof to make sense of it all

Then, coming bang up to date :

  • viewing a trace file using an external table – and why you might want to
  • Using a preprocessor to generate tkprof output
  • implementing a multi-user solution for tkprof

Continue reading

PL/SQL is faster than SQL – Just ask Mitch.

After their comprehensive defeat at Lord’s back in June, some experts were confidently predicting that Australia would be on the wrong-end of a clean sweep in both of the back-to-back Ashes series.
Mitchell Johnson, if he was mentioned at all, was written off by all and sundry. After all, not only did he not hand homework in on time, he couldn’t be relied upon to hit a barn door, let alone a set of stumps.
Fast-forward a few months and you can see that conventional wisdom has held…to the extent that no barn doors have been dented.
Unfortunately, the same cannot be said of English pride.
Mitch and his mates have a bit of time on their hands before Australia visit South Africa next month – that nice Mr Lehman has let the class off homework – so they’re free to assist in contradicting another of those things that “everyone knows” – SQL is always faster than PL/SQL.

What we’re going to cover here (among other things) is :

  • a quick overview of the LOG ERRORS mechanism (Mitch doesn’t do any other speed)
  • a recap of the older PL/SQL SAVE EXCEPTIONS
  • performance comparison between the two with errors present
  • Explore the limits of LIMIT
  • performance comparison when no errors are present

Mitch is standing at the top of his run. A random English batsmen is quaking at the crease, so let’s get started… Continue reading

Implicit Cursors are from Venus, Explicit Cursors are from Mars

Domestic bliss. There’s nothing like it. There’s certainly nothing like it in our house, particularly when I indulge in one of my endearing little foibles that is guaranteed to get Deb pouting like Angelina Jolie suffering a nasty reaction to a wasp-sting.
Whether it’s leaving the toilet seat up, or hanging my clothes up on the nearest floor, there are some days where I just can’t seem to do anything right.
Having said that, I must confess that I myself, am not a model of toleration. My own personal pout trigger is a query that looks something like this :

SELECT NVL(COUNT(*), 0)
FROM some_table;

I’ve seen this quite a bit recently, usually in the form of an explicit cursor.
Once I’ve got the rant about this out of my system, I’ll then look at how you might make single-row sub-queries a bit more efficient without ending up knee-deep in implicit cursors.
I’ll also ponder what it actually is that we really know about cursors. Continue reading