November 2016

Oracle Manuals available now!

Have a look what’s new and cool in the full Oracle manuals / documentation…

DBMS_LOCK: How to ensure only one instance of a PL/SQL procedure can execute concurrently.

I recently had a need to ensure that only one instance of a PL/SQL procedure was running concurrently. The solution was to create a named lock with Oracle supplied DBMS_LOCK package. There are lots of other blogs and posts on the internet on this subject, but this is how I used it.
Just as a row level lock will block another session from obtaining a lock on the same row, a session holding a named lock created with DBMS_LOCK can prevent another session obtaining a lock with the same name.
(updated 21.11.2016) An essential difference is that by default these locks will survive a commit or rollback.  Although you can chose to release them on commit.
In my package MY_PACKAGE, I have a procedure MY_PROCEDURE. I want to ensure that only a single instance of MY_PROCEDURE can run at any one time in my database.

OAK Table World 2016- Links and Videos, (Soon!)

Oak Table World 2016 was an incredible event.  I couldn’t get over how many attendees at Oracle Open World approached me to say it was the highlight of their week and how much they enjoyed the incredible content from all of us Oakies. I have no doubt their appreciation and attendance meant a lot from the over 450 people that came through the doors to sit through the different presentations over two days at the Children’s Creativity Museum.


A current question on the OTN database forum asks: “What’s the difference between object and tablespace reorganization?” Here’s an analogy to address the question.

I have three crates of Guiness in the boot (trunk) of my car, one crate has 4 bottles left, one has 7 bottles left and one has 2 bottles. I also have two cases of Louis Roederer Brut NV champagne, one case has 2 bottles left and one has only one. (I have two objects in my tablespace – one of type Beer, one of type Champagne – and my boot requires manual free space management .)

I move all the Guiness bottles into a single crate and all the champagne bottles into a single case. That’s a couple of “shrink space compact” calls – I’ve re-organised the objects to get all the bottles in each object close to each other, but the crates are still taking up space in the boot.

From Forms to DB v12.2 via Ask Tom, the Real World Performance Team, & The Optimizer Lady – UKOUG TECH16 next month

In just under 1 month the annual UKOUG conferences are happening – Tech16, Apps16 and JDE16

Filter Subquery

There’s a current thread on the OTN database forum showing an execution plan with a slightly unusual feature. It looks like this:

Oracle Database Cloud (DBaaS) Performance Consistency - Part 7

This part of the series is supposed to cover the results of I/O related tests performed on the Amazon RDS Oracle cloud instance.

As mentioned in the previous part of this series I've only used the "General Purpose SSD" storage type since the "Provisioned IOPS" storage was simply to expensive to me and it wasn't possible to get a trial license for that storage type.

Random fun

It has been some fun evenings lately. I have added a lot of new functionality and new data domains to the RANDOM_NINJA package. One of the bigger updates is that I have created a Markov Chain function for text generation, so the random text is a lot more real looking:

OTN tour 2016 APAC

The OTN tour came to APAC this year, so it’s been a pleasure and privilege to be able to participate in some of the legs.  Being Perth born and bred, I know all too well that any travel to Australia from … well… anywhere except Australia, is a long haul, so I’m very grateful to the array of overseas speakers who gave up their time and comfort to make the journey.

My first stop was Sydney and I was glad to get back there for a few reasons.  One of them was to catch up with family and offload, oops, I mean “share” my young boys with their grandmother.  The highlight of the trip for them of course was travelling back home to Perth unaccompanied :-) 

What I forgot and had to relearn about DESC indexes

The title for this blog post is probably more catchy than the post itself but the election is close so every sort of campaign is allowed, right?
This post is another one of those “I knew it, but I forgot and got bitten back” blog post so hopefully next time I see it I’ll be quicker in recognizing such behavior.

The goal of the SQL is to quickly return the Top-N rows  that match some filter condition(s), descendingly sorted by one of such columns. Pretty common requirement if you consider the filter/sort column to be a date one (“give me the last day worth of transactions, starting with the most recent ones”) and many people would solve using a DESC index on the date column.