Ladies and gentlemen, boys and girls, cats, dogs, goats, cows, furry objects, Kevin Clossen, and whoever else may have accidentally stumbled here: the great blogosphere may have appeared to gradually slow. But fear no more, the tides are changing! You may now stop pinching yourself and trying to find your doctor’s phone number because this is not a dream and you are not having strange hallucinations. This is NOT a test of the emergency broadcast system; this IS the real thing.

YES…

Congratulation on becoming a genuine reader of the 55th edition of the OFFICIAL, INTERNATIONAL, EXTRA-TERRESTRIAL, INTERGALACTIC, ASTROLOGICAL, DECIDUOUS

First and foremost, a quick thanks to that great wonder of wonders, that marvelous man of men – Dave Edwards at Pythian. He’s invited me to host this week’s log buffer and to “have fun.” I would also like to point out that this is the first log buffer I’ve posted – and it actually wasn’t quite as easy as I thought it would be. There is in fact quite a bit of activity in the database blogging world and it’s impossible to capture it all in a short summary! Bravo to all the great editors who have put these together in the past. And make sure to visit the aggregators (such as orana, orablogs, and planetmysql) and read all the posts I’ve left out! And now for the carnival…
› Read more

I’ve been spending quite a bit of time over the past week studying subquery optimization. It started with an anomaly I noticed at a client site and led me through a refresher on Jonathan Lewis’ chapter on transformations and through a lot of time reading trace files. I’ll probably write a few posts about subqueries… but as a quick starter here’s the anomaly that got me started with the whole thing.

First things first: my demos in this article will be from Oracle 10.2.0.2. Subquery optimization is an area of active development and there are even changes between point releases of Oracle. I saw different plans generated for some of my test cases between 10g releases 1, 2 and 3. The differences are even more dramatic between major releases – not to mention between different databases. Oracle 8i unnested nothing, 9i unnested everything, and 10g tries to work out the cost. MySQL can’t unnest and can’t use indexes on FROM clause subqueries since it always materializes them. (Just yesterday I finally listened to Timour Katchaounov’s “Query Optimizer Internals” presentation from this year’s MySQL Conference where he spent a lot of time talking about MySQL’s capabilities and limitations with subqueries.)

Oracle 10g doesn’t always get it right either, although – not surprisingly – it is far more sophisticated than MySQL in how it handles subqueries. The “anomaly” that I observed was 10g unnesting a subquery when it was clearly cheaper not to unnest it. The original query was a bit complex and the subquery existed to find the current row in an SCD type 2 table. (If you have these tables then I’d wager that you use subqueries for this same purpose and could run into this exact problem!)
› Read more

A few more 11g new features – that you might not have heard about yet – which stand out in my opinion!

Direct NFS

First, Kevin just posted whitepaper/press release links and made some comments about Direct NFS. Oracle 10g introduced ASM – streamlining hardware access by effectively allowing the database engine to bypass all that kernel filesystem code. In 11g it looks like they’ve done the same thing with NFS.

Direct NFS is an NFS client built directly into the database kernel. Two big advantages: first, it streamlines data access by bypassing code paths into the kernel and providing code that’s optimized for only what the database needs. Secondly there’s no more fussing with mount settings for NFS mounts – since Oracle controls it internally the right window sizes and sync settings will always be there.

I’m not quite as sold on it as Kevin is since my biggest issue with NFS has always been lack of instrumentation – and Direct NFS is going to remove the tiny bit of instrumentation that we did have (from nfsstat). (At least with ASM you still can look at I/O latencies and transfer rates through the iostat utility…) But this idea definitely has potential and I do like it a lot despite my hangups. And actually it’s possible that with the NFS code in the Oracle kernel they could provide instrumentation that the OS has never provided.
› Read more

Well 11g is officially “launched”. I think that just means you can download the white papers from OTN now. Seems that the actual software itself still isn’t available although there was talk that it would be released by August or something – on Linux at least. I’m not exactly holding my breath although I do think they’ll have it out soon, as promised.

I missed the first hour of the Oracle 11g Launch because I was at the Illinois Secretary of State trying to get license plates for the car I just bought. I took some vacation time last week for a trip to visit my family in Michigan and bought a used car while I was there. (I’ve been looking for a few months now.) Trying to have all the right paperwork for the government is always a hassle and I had to leave twice and upon returning always ended up queuing in line again. While in line the third time, I actually remember standing there thinking about how this was similar to database processes that can’t obtain a latch by spinning and have to sleep. Perhaps my vacation wasn’t long enough.

Anyway… it was interesting to see which new features got top billing. Not many surprises (that I noticed anyway). › Read more