Introduction

This is the first part of three detailed articles making up a mini-series about OBIEE monitoring. It demonstrates how to capture OBIEE performance information, and optionally graph it out and serve it through an auto-updating webpage.

For some background on OBIEE’s Systems Management component, along with JMX and MBeans, see here and here. The following assumes you know your mbeans from coffee beans and jmx from a bmx.

The metric collection is built around the jmxsh tool. This is similar to jmxterm and both provide command-line access to jmx. Once it’s commandline, it’s scriptable :)

Those of you who read my blog regularly may have noticed I have a slight obsession with the OBIEE systems management capability which is exposed through JMX. Venkat has blogged this week about JMX in OBI11g, and it’s clearly a technology worth understanding properly. I’ve recently been tinkering with how to make use of it for monitoring purposes, most recently using JConsole and discussed here. What follows is an extension of this idea, cobbled together with a bit of shell scripting, awk, gnuplot, and sticky backed plastic. It’s built on OBIEE 10g - for OBI11g it may differ (although I understand that Performance MBeans still exist).

Hot on the heels of one problem, another has just reared its head.

Users started reporting an error with reports that included charts: [sourcecode] Chart server does not appear to be responding in a timely fashion. It may be under heavy load or unavailable. [/sourcecode]

Set up is a OBIEE 10.1.3.4.1 two-server deployment with BI/PS/Javahost clustered and loadbalanced throughout.

Diagnostics

Javahost was running, and listening, on both servers: [sourcecode] $ps -ef|grep javahost obieeadm 14076 1 0 Nov 25 ? 9:23 /app/oracle/product/OracleAS_1/jdk/bin/IA64N/java -server -classpath /app/oracle/product/obiee/web/javahost/lib/core/sautils.ja $netstat -a|grep 9810|grep LISTEN tcp 0 0 *.9810 *.* LISTEN [/sourcecode]

They say about travelling that it’s the journey and not the destination, and the same is true with this problem we hit during a deployment to Production.

We were deploying a new OBIEE 10g implementation, with authentication provided by Microsoft Active Directory (AD) through the LDAP functionality in OBIEE. As a side note, it’s a rather nice way to do authentication, although maybe I’m biased coming from our previous implementation which used EBS integrated authentication and was a bugger to set up and work with.

Folk from Yorkshire are tight, so the stereotype goes. So here’s a cheap-ass way to monitor OBIEE 10g using nothing but the OBIEE built-in systemsmanagement component, the jmx agent, and jconsole (which is part of the standard Java distribution):

From here you can also export to CSV the various counters, and then store history, plot it out with gnuplot or Excel, etc.

If anyone’s interested let me know and I’ll document a bit more about how I did this, but it’s basically building on previous work I’ve documented around jmx and OBIEE.

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.45.52.750 34.649 14-SEP-10 07.30.03.394 10.953 14-SEP-10 08.15.15.243 57.833 14-SEP-10 09.00.27.180 30.177 [/sourcecode] So, it looks like early in the morning we’re using about 200 MB/s throughput, and by about 9am somewhere around 30-50 MB/s ?

I’m a geek. I like understanding things in their absolute entirety. It frustrates me to have to make presumptions or assumptions about something. I like to get down ’n dirty and find out what makes things tick.

So that necessitates reading manuals. And unfortunately, Oracle don’t make that easy all the time.

Some of the manuals are not very good, and others are difficult to find. Given the complexity of the OBIEE stack and proliferation of terminology and product names, sorting the wheat from the chaff can be a headache. Fair enough, for a corporation as big as Oracle with a product portfolio in the [hundreds? thousands?] creating a unified, comprehensive, easy-to-navigate point of reference for documentation must be near on impossible - but that’s their problem to figure out and mine to bitch about until it’s done ;-)

Introduction

We’re in the process of implemention Resource Manager (RM) on our Oracle 11gR1 Data Warehouse. We’ve currently got one DW application live, but have several more imminent. We identified RM as a suitable way of - as the name would suggest - managing the resources on the server.

In the first instance we’re looking at simply protecting CPU for, and from, future applications. At some point it would be interesting to use some of the more granular and precise functions to demote long-running queries, have nighttime/daytime plans, etc. I’d also like to explore the management of IO but for us the pain is in bandwidth that a query consumes, and it looks like RM can only work with total session MB, or IOPS. Reading about Exadata it sounds like the Exadata I/O Resource Management might do this ([…]It allows intra and inter-database I/O bandwidth to be defined and managed[..]). But for that I’ll have to write to Santa and promise to be a good boy this year.

This problem, which in essence is bad behaviour from Informatica bringing down Oracle, is a good illustration of unintended consequences of an apparently innocuous security setting. Per our company’s security standards, database passwords expire every 90 days. When this happens users are prompted to change their password before they can continue logging into Oracle. This applies to application user IDs too. It appears that Informatica 8.6.1 HF6 (part of OBIA 7.9.6.1) doesn’t handle an expired password well, spawning multiple connections to the database, eventually bringing Oracle down through memory SWAP space exhaustion.

A very short blog post to break the drought, but I didn’t hit any google results for this error so thought it might be useful to record it.

In BI Publisher 10.1.3.4, trying to install the Scheduler (Quartz) schema, I got this error:

[sourcecode]Schema installation failed while creating tables. Schema may already exist. Please remove the existing schema or choose another database and try again.[/sourcecode]

To me, the error text is a bit unhelpful. Whilst the first statement is correct - “Schema installation failed while creating tables”, it doesn’t tell you the error it encountered, and then it goes on to suggest only one reason for the failure.

@alexgorbachev tweeted me recently after picking up my presentation on Performance Testing and OBIEE.

His question got me thinking, and as ever the answer “It Depends” is appropriate here :-)

Why is the measurement being done?

Without knowing the context of the work Alex is doing, how to measure depends on whether the measurement needs to be of: -

1. The actual response times that the users are getting, or
2. The response times that the system is currently capable of delivering

This may sound like splitting hairs or beard-scratching irrelevance, but it’s not. If the aim of the exercise is to be able to make a statement along the lines of: