I’ve been involved with some performance work around an ODI DWH load batch. The batch comprises well over 1000 tasks in ODI, and whilst the Operator console is not a bad interface, it’s not very easy to spot the areas consuming the most runtime. Here’s a set of SQL statements to run against the ODI work repository tables to help you methodically find the steps of most interest for tuning efforts.

I heard this on Thinking Allowed and thought how applicable it was to the attitudes that you can sometimes encounter in both systems development, and the support of production systems: “Each uneventful day that passes reinforces a steadily growing false sense of confidence that everything is all right – that I, we, my group must be OK because the way we did things today resulted in no adverse consequences.” by Scott Snook (Senior Lecturer in the Organizational Behavior unit at Harvard Business School )

Jeff McQuigg has posted two presentations that he gave at Openworld 2010 on his website here: http://greatobi.wordpress.com/2010/10/26/oow-presos/ They’re full of real content and well worth a read. There’s excellent levels of detail and plenty to think about if you’re involved in OBI or DW development projects.

Continuing in the beard-scratching theme of Unix related posts (previously - awk), here’s a way to graph out the I/O profile of your Oracle database via the Oracle metrics in gv$sysstat, and gnuplot. This is only the system I/O as observed by Oracle, so for belts & braces (or to placate a cynical sysadmin ;-)) you may want to cross-reference it with something like sar. First, a pretty picture of what you can get:

More of a unix thing than DW/BI this post, but I have a beard so am semi-qualified…. The requirement was to improve the performance of some ODI processing that as part of its work was taking one huge input file, and splitting it into chunks based on content in the file. To add some (minor) spice the file was fixed width with no deliminators, so the easy awk answers that I found on google weren’t applicable.

Last month I wrote about a problem that Informatica as part of OBIA was causing us, wherein an expired database account would bring Oracle down by virtue of multiple connections from Informatica. I raised an SR with Oracle (under OBIA support), who after some back-and-forth with Informatica, were told: This is not a bug. That the two error messages coming back from Oracle are handled differently is the result of a design decision and as such not a product fault.

Jeff Atwood of Coding Horror fame observed: This is sage advice (if a little crude :-)) to bear in mind when building reports for users.

As Twitter learnt yesterday, you should always sanitise user input. I was amused to see My Oracle Support doing so….recursively :) The apostrophe in “doesn’t” got escaped once, and then again, and then again, and then again, and then again ……

Here’s one in the series of stupid things I’ve done but which Google has thrown no answers, so I post it here to help out fellow idiots. Today’s episode involves our SCM tool, TortoiseSVN. I’d been happily using it for over a year, when suddenly I couldn’t commit any more. I could browse and checkout to my heart’s content, but when I tried to commit, boom: Commit failed (details follow): Authorization failed

This query, based on AWR snapshots on sys.wrh$_sysstat, includes in its metrics the I/O read throughput for a given snapshot duration. However it’s important to realise the huge limitation to this figure - it’s an average. It completely shoots you in the foot if you’re looking at capacity requirements. Consider this real-life example extracted from the above query: [sourcecode] Timestamp Total Read MBPS =========================================== 14-SEP-10 05.15.12.660 113.748 14-SEP-10 06.00.40.953 202.250 14-SEP-10 06.