Who's online

There are currently 0 users and 23 guests online.

Recent comments


Five Hints

This is the content of a “whitepaper” I wrote for my presentation “Five Hints for Optimising SQL” at the recent DOAG conference.


Adding hints to production code is a practice to be avoided if possible, though it’s easy to make the case for emergency patching, and hinting is also useful as the basis of a method of generating SQL Plan Baselines. However, notwithstanding (and sometimes because of) the continuing enhancements to the optimizer, there are cases where the only sensible option for dealing with a problem statement is to constrain the broad brush strategy that the optimizer can take in a way that allows it to find a reasonable execution plan in a reasonable time.

This note describes in some detail the use and effects of five of the “classic” hints that I believe are reasonable strategic options to redirect the optimizer when it doesn’t choose a path that you consider to be the most appropriate choice.

CLOBs are not just big varchars

We had a question on AskTom the other day, talking about comparing clobs.  (Paraphrasing) the question inquired as to why we had a DBMS_LOB.COMPARE function, when you can you just compare clobs directly, using the following example:

UK bound

I’m off to the UK tomorrow for the annual UKOUG conference in Birmingham.

The developer advocate team have a busy schedule, which you can read about here

The conference has always been a favourite of mine – lots of good content, and good networking opportunities.  The full agenda is here

Also, if you are in the finance sector, I’ll be doing some talks in London in the days following the event.  Contact your local Oracle rep for details.

See you there !

WordPress and Docker

One of the attractive things about Docker to me, is that I can spin up containers with software that doesn’t affect the rest of my system. I can spin down the container, remove the container, make multiple containers. It’s all nice neat and clean unlike traditional application installation on an OS where files are spewed all through the file system and it’s a  mess to try and clean it it up and/or remove it if need be. Sort of like the different between brain surgery and removing or putting on a piece of clothing.

In the last couple of posts I’ve talked about using WordPress with Delphix and Docker. One of my desired use cases was to be able to spin up multiple wordpress containers as in

Docker and Delphix architectures

In my last post I showed using Docker and Delphix to support WordPress.

I use wordpress for this blog. Works fine on it’s own. It is just me making a few posts here and there. Occasionally there are problems like an upgrade that goes bad or a hack that get’s some redirection code into the site. In those cases I have to go to a backup of my MySQL database that is used by wordpress on my site. The database is small so it’s pretty quick to backup, but I don’t back it up normally.  I know I should and occasionally it would be nice to have a backup in the event that data is corrupted somehow (like hacks into the contents of the database).

The twelve days of … AskTom

On the 1st day of AskTom, a poster sent to me

A question about 12c


On the 2nd day of AskTom, a poster sent to me

two SQL queries,

in a question about 12c


On the 3rd day of AskTom, a poster sent to me

three table joins,

two SQL queries,

in a question about 12c


On the 4th day of AskTom, a poster sent to me

four nested queries,

three table joins,

two SQL queries,

in a question about 12c


On the 5th day of AskTom, a poster sent to me

five pattern matches,

four nested queries,

three table joins,

two SQL queries,

in a question about 12c


On the 6th day of AskTom, a poster sent to me

six analytic functions,

five pattern matches,

four nested queries,

three table joins,

two SQL queries,

in a question about 12c



This is the text of the “whitepaper” I submitted to DOAG for my presentation on “Core Strategies for Troubleshooting”.


In an ideal world, everyone who had to handle performance problems would have access to ASH and the AWR through a graphic interface – but even with these tools you still have to pick the right approach, recognise the correct targets, and acquire information at the boundary that tells you why you have a performance problem and the ways in which you should be addressing it.

My agenda

It’s gettting to that time of year, so here are some of the sessions I’ve pencilled in for UKOUG Tech 15:

Sunday 6th:

Probably the whole of the Development stream.

Monday 7th:

9:00 – 9:50: Tom Dale – Fivium : An insight into Oracle Standard Edition, what’s included, what’s not, and how to plug some of those holes!

11:20 – 12:10: Me (et. al.) : Cost Based Optimizer – Panel Session

12:20 – 13:10: Franck Pachot – DBi Services : All About Table Locks: DML, DDL, Foreign Key, Online Operations,…

14:10 – 15:00: Maria Colgan – Oracle : Oracle Database In-Memory By Example

15:10 – 16:00: Philip Brown – Red Stack Tech : How to Evaulate the 12c In-Memory Option

16:30 – 17:20: Tony Hasler – Anvil Computer Services : Optimizer Roundtable

17:30 – 18:20: Joel Goodman – Oracle : Oracle Standard Edition Roundtable

Where did my triggers go ?

You need to be careful when coding and using triggers when it comes to Datapump (or anything that transposes triggers between schemas).  A lot of people make assumptions about what will happen with their triggers, and often get a nasty shock when they see the reality.  Lets explore with an example

I’m going to add three triggers to the standard EMP table in the SCOTT schema

Tech 15

Updated 27th Nov 2015:

Thanks for all the questions so far – there are plenty of useful topics coming out. At this rate I may have to pass some of these on to the round-table that Tony Hasler.

I’ve made a couple of comments in response, but generally I plan to avoid making any comments until after the panel.

Monday 7th Dec: 11:20 – 12:05

I’ve arranged a panel session on the Cost Based Optimizer for UKOUG Tech 2015, with Maria Colgan, Nigel Bayliss, and Chris Antognini joining me to answer (and maybe argue about) questions from the audience.

To keep things moving along, we aim to have a few questions available before the day and collect a few questions at the door as well as accepting questions from the floor. Martin Widlake and Neil Chandler will be acting as MCs, wielding the microphones, and collecting questions at the door.