Off to Qatar and some ranting

So, I just settled in to my first-ever trip to Qatar. I took an SN-Brussels flight earlier this evening out to Manchester and now I am sitting in the Qatar Airways A320-300 getting ready to take off. Qatar Airways has a fantastic reputation so I must say I am quite excited to experience their in-flight service. I am on my way to meet up with Jon Mead who is kicking off a project for a client over there. I can't really go in to any details of the work at this moment, but I am really looking forward to getting a chance to work in a part of the world I have never even been to. Jon's already been there for a week now so I expect him to give me the rundown of things once I get there early in the morning (by the time you read this, that moment will surely have passed). The next two weeks will surely prove to be interesting, I am certain.

Before I took off to day I had finish up on a few things with my client. One thing was to look into some performance problems that we were having on out OBIEE dashboard. The underlying data for the particular reports is a 60M+ records partitioned table containing monthly aggregates. What we have set up in the business layer is an aggregate aware fact table that should use this monthly aggregate at all times in the dashboard (as a sideline - my flights tonight will be showing up in the underlying facts and datamarts come Monday morning :)). What we had done also, a while back, was to set up additional materialized views, only intended for query rewrite purposes. When the client started complaining about the performance issues, my thoughts went immediately to the aforementioned mview. "Ahh, the view is just stale and all I need to do is refresh it and Bob's your uncle!". Alas, the view was not the culprit here so I then (and only then :|) took a look at the actual query in the nqQuery.log to take a further look at the execution plan. What I soon noticed then was that instead of using all my nice little bitmap indices, the CBO was doing full range partition scans! I then turned my attention to the bitmaps and found out that there weren't any! What? But! I am sure I had create a bunch of these before! The datamart has about 9 dimensions and only the date key and two dimension keys were actually bitmapped. So, I saw no point in trying to figure out why these weren't in place or who's fault it was they were missing (mainly because it was most likely myself who had dropped these during a back-loading process we had to go through about a month ago ...). I quickly wrote a PL/SQL block to generate the missing bitmap indices and after asking the ops DBA to put the tablespaces containing the historical partitions in READ WRITE, the bitmaps got created and then (and only then) Bob was everybody's uncle :)

Lesson learned? Make sure you have all the bits and pieces in place and keep an eye on your structures. Bad performance can really damage a DW project and don't count on the end-users to report bad performance.