Who's online

There are currently 0 users and 37 guests online.

Recent comments



OTN Yathra 2014 : Jalandhar

Day one did not start well. I woke up and couldn’t stop throwing up. I got to the Lovely Professional University, thinking it would stop, but it didn’t. The people at the University were really helpful and took me to the University doctor, who gave me an injection to stop the nausea. A few minutes later, things calmed down, just in time for my first session…

As soon as I hit the stage I felt fine and the session went well. Adrenalin is a wonderful thing! After the session I started to flag a little, but I was taken to get some food, which picked me up. India is a great place for a vegetarian. I was a little nervous about eating after the events of the morning, but the food was great and I felt much better after it.

OTN Yathra 2014 : The Journey Begins

It’s been a really tough lead up to this tour. I’ve already blogged about the panic over my visa and flights. Since that post my flights were cancelled, switched to some different flights, then switched back again. I only got the final confirmation on the Thursday night before leaving on the Sunday.

What with that and me stressing out about some stuff going on at work at the moment, I felt like cancelling so I could lock myself in my house for 2 weeks and not speak to anyone. I’m in definite need of a holiday!

Today started off with a last-minute shop for a new suitcase. I had forgotten mine was broken, so I rushed out and bought the first thing I saw. I got home, packed, then got a banging headache. I had a couple of hours before my taxi, so I went to bed to try and sleep it off.

Recursive subquery factoring

This is possibly my longest title to date – I try to keep them short enough to fit the right hand column of the blog without wrapping – but I couldn’t think of a good way to shorten it (Personally I prefer to use the expression CTE – common table expression – over “factored subquery” or “subquery factoring” or “with subquery”, and that would have achieved my goal, but might not have meant anything to most people.)

If you haven’t come across them before, recursive CTEs appeared in 11.2, are in the ANSI standard, and are (probably) viewed by Oracle as the strategic replacement for “connect by” queries. Here’s a simple (and silly) example:

12c Subquery Factoring

From time to time I’ve posted a reminder that subquery factoring (“with subquery”) can give you changes in execution plans even if the subquery that you’ve taken out of line is written back inline by Oracle rather than being materialized. This can still happen in 12c – here’s a sample query in the two forms with the result sets and execution plans.  First, the “factored” version:

Caution – hints

Here’s a little example of why you should be very cautious about implementing undocumented discoveries. If you take a look at the view v$sql_hints in you’ll discover a hint (no_)cluster_by_rowid; and if you look in v$parameter you’ll discover two new parameters _optimizer_cluster_by_rowid and _optimizer_cluster_by_rowid_control.

It doesn’t take much imagination to guess that the parameters and hint have something to do with the costs of accessing compressed data by rowid on an Exadata system (see, for example, this posting) and it’s very easy to check what the hint does:

Upgrades to 11g are finally complete

Just a little slice of reality to cut through all the 12c stuff that is floating around at the moment. I’ve just moved the last of our databases to 11g. Yay! As well as upgrading, we’ve been culling or consolidating old and unused stuff, which has drastically reduced and simplified our Oracle database landscape.

We currently have four projects running databases on HP-UX on Itanium (spit), one project on Solaris and the rest on Oracle Linux under VMware. If I had my way we would kick out HP-UX and Solaris and do everything on Oracle Linux.

We’ve still got one project on 11gR1, but that is being held back intentionally because of some issues with the vendor of the application that runs against it. Hopefully that will soon be on 11gR2 also.

OTN Yathra 2014

I’ve been a little quiet on the blog lately, mostly because I’ve been spending my time freaking out about the arrangements for the OTN Yathra 2014 tour of India. I had to apply for a visa, which in itself was not too bad, but I spent quite a long time without a passport, thanks to the courier not bothering to attempt a delivery. All that time I didn’t know if my visa application had been successful, so I wasn’t sure if I would have to submit it again. By the time that arrived, it was so late that booking the flights became interesting… At one point I wrote to Debra Lilley and said words to the effect of, I think this is just not meant to be!

Well tonight (Tuesday evening) I got my flights confirmed for my trip that I start on Sunday. :)

Row Migration

At one of the presentations I attended at RMOUG this year the presenter claimed that if a row kept increasing in size and had to migrate from block to block as a consequence then each migration of that row would leave a pointer in the previous block so that an indexed access to the row would start at the original table block and have to follow an ever growing chain of pointers to reach the data.

This is not correct, and it’s worth making a little fuss about the error since it’s the sort of thing that can easily become an urban legend that results in people rebuilding tables “for performance” when they don’t need to.

RAC Plans

Recently appeared on Mos – “Bug 18219084 : DIFFERENT EXECUTION PLAN ACROSS RAC INSTANCES”

Now, I’m not going to claim that the following applies to this particular case – but it’s perfectly reasonable to expect to see different plans for the same query on RAC, and it’s perfectly possible for the two different plans to have amazingly different performance characteristics; and in this particular case I can see an obvious reason why the two nodes could have different plans.

Here’s the query reported in the bug:

IOT Catalogue

I’ve just added a catalogue of Richard Foote’s articles on IOTs to the list I made a couple of years ago of Martin Widlake’s articles, so this is just a temporary note to point people to the updated list.