November 2016

Reorg

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.

Oracle Database Block Visualizer

Recently I wanted to demonstrate to some people on my training, how Oracle database maintains blocks in a datafile – what happens after truncate, truncate with drop storage clause, delete, regular insert, direct path insert and so on…

I didn’t find any tool for that so I’ve written my own. It’s core code is based on my previous database block research – project RICO http://blog.ora-600.pl/2015/09/03/rico/

Tool is very small and simple and right now it supports only regular blocks with data and no compression.

And this is how it works – let’s create a tablespace and 3 new tables in HR schema:

Oracle Database Block Visualizer

Recently I wanted to demonstrate to some people on my training, how Oracle database maintains blocks in a datafile – what happens after truncate, truncate with drop storage clause, delete, regular insert, direct path insert and so on…

I didn’t find any tool for that so I’ve written my own. It’s core code is based on my previous database block research – project RICO http://blog.ora-600.pl/2015/09/03/rico/

Tool is very small and simple and right now it supports only regular blocks with data and no compression.

And this is how it works – let’s create a tablespace and 3 new tables in HR schema:

Scoop – First glance of the new Oracle Live SQL?

When I was at OOW this year I saw the new (?) Oracle Live SQL…