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

Oracle External Table Pre-processing – Soccer Super-Powers and Trojan Horses

The end of the European Football season is coming into view.
In some leagues the battle for the title, or against relegation is reaching a peak of intensity.
Nails are being bitten throughout the continent…unless you are a fan of one of those teams who are running away with their League – Bayern Munich, Juventus, Celtic…Luton Town.
In their fifth season since relegation from the Football League to the Conference, Luton are sitting pretty in the sole automatic promotion place.
Simon is desparately attempting to balance his “lucky” Christmas-cracker moustache until promotion is mathematically certain. Personally, I think that this is taking the concept of keeping a stiff upper-lip to extremes.

"I'll shave it off when we're definitely up !"

“I’ll shave it off when we’re definitely up !”

With the aid of a recent Conference League Table, I’m going to explore the Preprocessor feature of External Tables.
We’ll start with a simple example of how data in an External Table can be processed via a shell script at runtime before the results are then presented to the database user.
We’ll then demonstrate that there are exceptions to the rule that “Simple is Best” by driving a coach and Trojan Horses through the security hole we’ve just opened up.
Finally, in desperation, we’ll have a read of the manual and implement a more secure version of our application.

So, without further ado… Continue reading

Installing SQLDeveloper 4 on Mint and Ubuntu – Minus the Alien

Deb recently bought a new kettle.
Now, a kettle used to simply boil water and turn itself off when it was done.
Not this thing.
It lights up as it boils and announces the fact that it’s finished with a melodious ping.
It’s got gauges and lights and switches.
I’ve decided that it’s probably a Dalek in disguise.
Like Daleks (or at least, the original Daleks), it can’t go up stairs – or if it can, it’s not advertising the fact.
Every morning, descending to the kitchen is filled with trepidation.
When will the Dalek tire of vaporizing innocent water molecules and move on to World Domination…

Doc-tor ! Doc-tor ! I feel like a ket-tle !

Doc-tor ! Doc-tor ! I feel like a ket-tle !

I wouldn’t be entirely surprised to find that, like most whizzy modern appliances, it runs on Java.
Which brings us, by a fairly circuitous route, to the topic at hand – SQLDeveloper.

Oracle’s latest incarnation of it’s IDE does indeed run on Java – the version 7 JDK to be precise.
In this post, I’ll go through the steps required on Mint to :

  • Install the Java 7 JDK
  • Install SQLDeveloper 4
  • Persuade SQLDeveloper 4 to play nicely with Java
  • Add SQLDeveloper to the Cinnamon Menu

The good news is that we can do all of this without the messy alien conversion of an rpm package to .deb format.

NOTE – I’ve followed these steps on Mint13, but they should be pretty much the same for any Debian Distro.
Anyway, without further ado…

Continue reading

PL/SQL Associative Arrays and why too much Rugby is bad for you

Once upon a time, working on an IT project in a large organisation meant reams of documentation, tangles of red-tape, and then burning the candle at both ends to get everything finished on time.
Then, someone discovered this Agile thing that dispensed with all of that.
The upshot ? Well, the documentation has been reduced ( although the red-tape is still problematic).
Many large organisations now adopt an approach that is almost – but not completely – unlike SCRUM.
It is at the business end of such a project I now find myself…burning the candle at both ends.
To pile on the excitement, Milton Keynes’ own Welsh enclave has become increasingly voiciferous in recent days.
The only SCRUM Deb is interested in is that of the Welsh Rugby Team grinding remorselessly over English bodies come Sunday.
She keeps giving me oh-so-subtle reminders of the result of last year’s game, such as when picking lottery numbers :
“Hmmm, three is supposed to be lucky…but not if your English. How about THIRTY !”
“But you’re married to an Englishman”, I pointed out during one of her more nationalistic moments.
“Which makes him half Welsh !”, came the retort.
At this point, I decided that discretion was the better part of logic and let the matter drop.
As a result of all this frenzied activity and feverish atmosphere, sometimes I’ve not been quite at the top of my game.
One particularly embarassing mishap occured late one evening and involved PL/SQL Tables – or Associative Arrays as they’re called these days – and the dreaded ORA-06531: Reference to uninitialized collection.

This particular post therefore, is mainly a reminder to myself of how to initialize and (just as importantly) clear down a Collection to prevent mysterious missing or, just as problematic, additional, records ( as well as less mysterious runtime errors). Continue reading

Speed Isn’t Everything – LOG ERRORS, SAVE EXCEPTIONS and Fun in the Snow

Before the fourth one-day international, Mitchell Johnson decided to shave off his moustache.
During the fourth one-day international, Mitchell returned the less than impressive figures of 0-72 off 10 overs.
At the end of the fourth one-day international, England had finally notched a win against Australia.
The logical conclusion to draw from all of this is that Mitchell Johnson is not a regular reader of this blog.

On the plus side, I did get a couple of interesting comments from my last post about the performance differences between Log Errors and Save Exceptions.
As well as Jim Dickson’s input, Steve Feuerstein made some observations on the Toadworld site (which you can see here if you’re interested).

These comments both had a similar theme to the effect that, whilst Log Errors and Save Exceptions are similar, there are some differences beyond their relative performance.

So, the aim of this post is to take a fresh look at these two mechanisms and how they compare.
For the code examples, I’m going to step away from the horror show that has been England’s cricket tour of Australia, and focus instead on the wacky world of Reality TV.

We’ve had celebrity high-diving, celebrity ballroom dancing, even celebrity dog-training.
With the Winter Olympics almost upon us, some particularly sadistic TV executive hit on the idea of assembling a collection of celebrities, strapping a plank of wood to each foot/handing them a tea-tray…and then pushing them off the side of a mountain.
All a bit of harmless fun. After all, what could possibly go wrong ?
Having said that, the producers of The Jump did hire a couple of extra cast members to account for the remote possibility that a broken rib/collar-bone/finger-nail might render one or more of the original contestants incapacitated. 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

The Anti-Pattern – EAV(il) Database Design ?

Early evening TV in our house is Soap time. Deb annexes the remote control, after which we are treated to an
assortment of angry women being angry with each other in a variety of accents originating from the North of England.
It could be worse, I suppose. We could be subjected to the offering on the other main channel ( angry London women being angry at each other in accents originating from the South East of England).
Then again, either is preferrable to an angry Welsh woman being angry at you in a Welsh accent.
Ok then, how do you make a database professional hot under the collar ? Mention the EAV design pattern.

This pattern goes by many names, most commonly :

  • EAV – Entity-Attribute-Value
  • OTLT – One True Lookup Table
  • Open Schema
  • Diabolically Enticing Method Of Data Storage (DEMONS)

OK. I made that last one up.
It is with some trepidation ( and having donned precautionary flame-proof underpants) that I am embarking on an exploration on the nature of EAV and whether it can ever be appropriate for use in a Database. Before we go any further though, I’d like to take a moment to clarify exactly what the term “database” means in the context of this discussion
Continue reading