I’ve been barely able to keep up with my current OWB projects and getting ready for my UKOUG presentation that I haven’t had ANY time to work on the previously floated ideas for blogs. However, I didn’t want to pass up the chance to add to a thread started on rittman.net.

Jon Mead, a colleague a SolStonePlus, emailed Mark and I about his findings about the tuning method “in the real world.” Jon and Mark had sorted out many of the details for how to generate diagnostic data for OWB Mappings and Flows. I asked to include this exceptional work in my OWB Tuning Workshop (which is the final afternoon of my Administration/Operations course) as part of a method for tuning OWB DW solutions. The method I put forth in the workshop includes information on generating detailed information for tuning particular mappings, but I included it as part of an overal process for tuning OWB Data Warehouses.

Method 1: OWB Runtime Data Analysis

This involves examination in greater detail the runtime audit information provided by the OWB runtime engine. The web based interface provides only per-run reporting, and there is currently not any Oracle provided interface for analyzing the data in the Runtime Repository. We’ll examine some of the data available as Oracle views, and understand what information is available to us. It provides performance data on a macro level, and does not provide detailed diagnostic information (an explain plan for instance).

Method 2: Mapping/Process Flow Analysis

We’ll learn how to set our mappings and process flows up to generate very detailed information about how Oracle is processing our logic, and an entire wealth of information used for common Oracle tuning practices. This includes being able to explain plan, receiving information on wait events, etc. We’ll learn how to implement these diagnostics in our mappings and process flows along with how to collect and assemble them from our Oracle server

Jon echoes the same reasons why this tracing should be used as part of an overal process: too much data and diagnostic data to sort through. Make sure your dollars are well spent and tune mappings one at a time. Tune the ones that will provide the most direct benefit (decrease in load time), or will not scale well (hockey stick anyone?). I have in my “OWB Toolbox” a little OWB Performance Data Mart that transforms the OWB Runtime View data into a ROLAP mart (with Disco). It’s pretty cool, actually! I’ll see if I can’t take some screenshots from a non-client instance (when I get the time, right?).

It might not be good business sense to publish parts of a course that I charge money for, but hitherto I’ve been a better techie than businessman so what do I care!? 🙂

Without further adieu here are the first 13 workbook pages (there’s 90 pages total) from the afternoon workshop that enumerates a method for tuning OWB solutions. Feel free to contact me with any feedback or if you think you could use some help tuning your OWB solution.

UPDATE: A reader sent me an email noting that there is a rather amusing metaphor mixup in the paper… Blog visitors from “.ca” should recognize it straight away. 🙂 I’ll revise it eventually.