Keep It Simple Stupid

I’ve been spending a lot of time recently working on performance tuning projects.  Sometimes the BI apps are slow, sometimes it’s custom, sometimes it’s a mix.  I’ve gotten the chance to see what works in both Oracle and SQL Server.

My conclusion about both of these databases is that they are like a cat or dog that gets fooled when you play hide the ball; they aren’t very smart sometimes.  The only way you can really truly ensure database engines, even modern advanced ones, do things the right way to is to make it as simple and easy for them to understand as possible.  I guess this is nothing new; the KISS principle comes from Kelly Johnson of The Lockheed Skunk Works, the guys who created the SR-71 Blackbird spy plane.  I think he knew a few things about complexity in systems and how they tend to break or become difficult to maintain.  (BTW more on that topic as it pertains to OBI and the BI Apps at a later date.)

As I’ve been looking at performance tuning many reports and queries over these days, I find that a lot of time is spent trying to get the database to do the smart thing.  Too much time in fact.  Usually this is due to some small piece of non-simple SQL that causes the problem.  In more unusual cases I’ve seen something on one table completely break down the query plan, even something that should be trivial and very innocuous.

<Vent>For example, not being able to use an index on a table with 2,000 records should not radically alter the query plan, but in fact it will do that on you.  After you spend hours upon hours with it, after you’ve called up the DBA for help to dig into the extreme nitty-gritty details of the query plan, you make a change it works for that one query but not any others.  Then you decide to write this article because you’ve spend 10 hours on something so minuscule that in the end doesn’t even work consistently.  If only the query had been clean to begin with…</Vent>

In this brief article I’m just going to lay out a few things to consider to help make you system simpler for the database engine to understand and therefore do a better, faster job in answering a query.

Oracle’s Data Warehouse Reference Architecture

Last year Oracle published a white paper called “Enabling Pervasive BI Through a practical Data Warehouse Reference Architecture”.  I took a read through this paper and have some comments on it  (so maybe this is a book report).  For your convenience, I’ve uploaded a copy of it here Oracle DW Reference Architecture Feb 2010.  I also believe the architecture is a little vague (on purpose I suppose).  Depending on how you look at it, you might be looking at how the BI Apps are Architected, or you might be looking at how a real-time Enterprise Data Warehouse is built.