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.

Tuning Time Series Metrics

In this post I am going to explore some performance issues related to OBI’s time series functions.  Released back in OBI 10g, the ToDate() and Ago() functions brought a significant improvement to the process of easily creating a variety of time series metrics.  In older versions of Siebel Analytics, creating time series was a very manual effort involving a lot of aliases and special joins that could at time become a little confusing to the developer.  They did have a wizard called the Time Series Wizard to assist, but if you are like me you never use wizards J.  The Time series functions however solved that; using them is a piece of cake, requiring only a minor enhancement to the Date dimension.

All is rosy with the world then, correct?  Well not so fast.  The reality is that these functions do some very strange things behind the scenes in order for them to work properly.  So strange in fact that the database engine typically has some difficulty figuring out what to do.  One thing I’ve learned over the years when it comes to database engine performance – keep it simple if you want it to run fast.

As it turns out these strange things that OBI does for the Time Series functions in fact cause a decent performance hit when compared with the old technique.  This short post will discuss this in more depth.

Build a Performance Layer to Make the BI Apps Fly

The BI Apps from Oracle present customers with a nice head start to getting their BI environment up and running.  Financial Analytics, HR Analytics, Supply Chain Analytics, etc. all come pre built with much of the code and reports you’ll need to build to support your business.  But for many customers they just are too slow for their user community while running dashboards, reports and ad-hoc queries.  In an era where an internet search engine can give you what you want in one second, reports running for a minute or more are just not acceptable.

In this post I’ll discuss some of the inherent performance limitations in the BI Apps and what you should do about it.  Note the vast majority of customers really don’t have a performance problem with their system, but you can always deliver reporting content faster.  If you are running at 15 seconds per page, wouldn’t 5 seconds be that much better?  The performance problem really lies with some large customers with larger data volumes.  It is here where the BI Apps design can be enhanced with more performance in mind.

I’ve written about OBI performance a few times in the past, and I’m sure there will be more to come.  As a refresher, here are a few other posts to take a look at:

• Achieving Good Performance with OBIEE
• OBI Performance Preso
• Performance Tuning Financial Analytics
• Stitch Joins

Performance Tuning and Financial Analytics

In the past I’ve written and presented on OBI performance from the ‘before perspective’: before you begin development, what are the things you should plan on and how should you implement them.  This paper however is with the ‘after perspective’; what to do if you are stuck with a performance problem that you need to solve quickly.  It will use the Financial Analytics (All versions) application from Oracle’s BI Applications (aka the BI Apps) to walk through a plan of attack and demonstrate specific changes using the Oracle Database 10x.  Thus, this paper has two purposes:

1. Specifically to document actual changes for Financial Analytics
2. Generally to walk through a performance tuning effort that you yourself may undertake

Note: You do not need to be working on Financial Analytics or even the BI Apps for the concepts in this article to apply.  It merely uses Financial Analytics as its example, and where appropriate I will explain the model.

I’m going to do something a bit different with this article in that I will tell the story of a recent performance tuning project for a client.  A previous integrator had delivered Financial Analytics and it was up and running in a production environment, but the performance was terrible.  Many queries were over 8 minutes.  We were asked to tune the GL Transactions star, but the lessons learned here will work for all modules of Financial Analytics, regardless of version.  In fact, implementing them for only one star actually boosted the performance of the other Financial Analytics Subject Areas.

Webinar on Performance, June 7th, 1pm EST

I’ll be doing a webinar called Planning for High Performance OBI.  I’ve given this preso a few times before, but it’ll be good to do it on the web for a change.  I discuss some of the basics you should have when beginning your designs.  It is not a performance tuning workshop, but rather what to do first to get it done properly the first time.  Register through KPI Partners website.

SSDs – A Short Story

Many of you are aware that there is a relatively new kind of storage out there called a Solid State Disk.  Instead of spinning platters with moving heads, SSDs are flash memory chips.  Think of them as a super fast, large thumb drive.

There are 2 main kinds of SSDs, SLC and MLC.  They roughly define the difference between enterprise (SLC) and consumer (MLC), based on both performance and reliability (which of course means price!).  Then there are 2 main interfaces out there – a traditional SATA II/SATA III (just coming out these days) or one that plugs into the bus directly (PCI for desktop PCs and most Intel servers).  The ones that plug into the PIC bus are really only for businesses, as they are very expensive.  But by removing the SATA interface bottlenecks, they are unbelievably fast.

OOW BI Imp Panel Questions #2: Can OBI do SubSecond Responses?

For the first post in this short series, see here.

I don’t think I answered this question properly during the panel; I’m going to chalk it up to the fact that I was sick as heck and my head was swimming.    But anyway here is a better shot at it:

Sidenote: I was so out of it that I spilled a bottle of water on my PC earlier during another presentation.  Luckily the computer stayed alive until I finished!

Question #2: Is it reasonable to expect sub-second response time from OBI?

Short Answer: Basically no, but once in a while yes

OOW BI Imp Panel Questions #1: Is Essbase Mandatory?

At this year’s Oracle Open World in San Francisco, I sat on a BI Implementation Panel with three other experienced Data Warehouse and BI experts.  Stewart Bryson moderated the hour long session with Mark Rittman, Jon Mead and Kevin McGinley on a variety of topics.  I wanted to use this post to discuss a few of the questions in more detail.  Stewart threw some provocative, crowd pleasing questions out there, and phrased them in a very aggressive manner where a few of them quite frankly got me upset!  But that’s all good – I know Stewart and he wanted an exciting panel!

Question #1: With the tighter integration of Essbase into the OBI11g stack, should Essbase be considered mandatory on OBIEE implementations?

Short answer: Useful on some?  Yes.  Mandatory?  Not even close.

It’s Not ‘To Stitch or Not to Stitch’, But Rather Where to Stitch

It’s been a while since I’ve posted anything aside from an announcement – in fact for the entire year.  That’s was due to being swamped on my current project where I was involved in a number of things.  But now that we’ve deployed our first 2 releases, things are starting to settle in a bit, so I have some time to touch on some topics.  Sorry about the ugly formatting – I’m no expert with this editor.

The first topic came out of the end of the project when we were performing a few weeks of load testing.  Our performance numbers at load (100 concurrent users) were pretty terrible.  After a few realizations, I decided to try a tweak in OBI that resulted in enormous impacts on our results.

Achieving Good Performance With OBIEE

Originally posted 1/30/2007

How do you extract good performance from Oracle Business Intelligence (OBIEE)?  Are there any tool specific features that can be used to achieve better performance, or is it all about the backend?  This post will discuss some things to consider to make a page or report perform well; scalability is not included here.