---

 

This article has been superseded by a newer version: Instrumenting OBIEE Database Connections For Improved Performance Diagnostics

---

 

(Previously on this blog: 1, 2, 3…)

Summary

Instrument your code. Stop guessing. Make your DBA happy. Make your life as a BI Admin easier.

The Problem

OBIEE will typically connect to the database using a generic application account. (Hopefully, you’ll have isolated it to an account used only for this purpose - if you haven’t, you should.)

Here’s a quotation that I’ve just read and wanted to share. It is all part of a BAAG approach to troubleshooting problems, performance in particular.

From Greg Rahn (web|twitter) on oracle-l:

There are always exceptions, but exceptions can be justified and supported with data. Just beware of the the silver bullet syndrome…

The unfortunate part […] is that rarely anyone goes back and does the root cause analysis. It tends to fall into the bucket of “problem…solved”.

Here’s a scenario that’ll be depressingly familiar to most reading this: after ages of running fine, and no changes to the code, a query suddenly starts running for magnitudes longer than it used to.

In this instance it was an ETL step which used to take c.1 hour, and was now at 5 hours and counting. Since it still hadn’t finished, and the gods had conspired to bring down Grid too (unrelated), I generated a SQL Monitor report to see what was happening: [sourcecode language=“sql”] select DBMS_SQLTUNE.REPORT_SQL_MONITOR( type=>‘HTML’, report_level=>‘ALL’,sql_id=>‘939abmqmvcc4d’) as report FROM dual; [/sourcecode] (h/t to Martin Berger for this)

Just because something produces the correct numbers on the report, it doesn’t mean you can stop there.

How you are producing those numbers matters, and matters a lot if you have an interest in the long-term health of your system and its ability to scale.

OBIEE is the case in point here, but the principle applies to any architecture with >1 tiers or components.

Let me start with a rhetorical question. The user has got a report which has ten rows of data. Which of the following methods is going to be a more efficient way to generate the report?

This post expands on one I made last year here about sampling frequency (of I/O throughput, but it’s a generic concept). The background to this is my analysis of the performance and capacity of our data warehouse on Oracle 11g.

Before I get too boring, here’s the fun bit:

Pork Pies per Hour (PP/h)

Jim wants to enter a championship pork-pie eating competition. He’s timed himself practising and over the course of an hour he eats four pork pies. So we might say that his Pork Pies per Hour (PP/h) rate is 4.

Cary Millsap recently published a paper “Mastering Performance with Extended SQL Trace” describing how to use Oracle trace to assist with troubleshooting the performance of database queries. As with all of Cary Millsap’s papers it is superbly written, presenting very detailed information in a clear and understandable way. (and yes I do have a DBA crush ;-)) It discusses how you can automate the tracing of specific sessions on the database, and requiring the application to be appropriately instrumented. This reminded me of a post that I made almost exactly 12 months ago here, where I explained how to pass through the username of the OBIEE user to the database. Initially I thought it would be useful simply for being able to pin a rogue query to an end-user, but reading Cary’s paper made me realise there is more potential to it.

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.

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.

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.

@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:

Here’s my presentation “Performance Testing and OBIEE” that I gave at the RittmanMead BI Forum 2010: Performance Testing and OBIEE.pptx

It’s a Powerpoint 2007 file (pptx) for which you may need the Microsoft PowerPoint Viewer 2007. I’ve included copious notes on each slide which hopefully cover the gist of what I talked about when I was delivering it. There are also a handful of funky animations which is why I’ve left it in pptx and not exported to PDF or other format (sorry Open Office users).

I’ve been playing around with SQL Tuning Sets, and was trying to clear up my mess.

To list all the tuning sets: [sourcecode language=“sql”] SET WRAP OFF SET LINE 140 COL NAME FOR A15 COL DESCRIPTION FOR A50 WRAPPED

select name,created,last_modified,statement_count,description from DBA_SQLSET [/sourcecode]

[sourcecode] NAME CREATED LAST_MODI STATEMENT_COUNT DESCRIPTION ————— ——— ——— ————— —————————————————————————————- sts_test_02 09-MAR-10 09-MAR-10 1 Test run 1 sts_test_01 12-FEB-10 12-FEB-10 1 an old STS test test test [/sourcecode]

A new document has been published by Oracle, discussing ways of improving performance for OBIA 7.9.6 and 7.9.6.1. Its primary focus is around improving ETL performance. There’s some very interesting content including hardware sizing recommendations, and I’d strongly recommend anyone working with OBIA reads it.

It’s called “Oracle Business Intelligence Applications Version 7.9.6 Performance Recommendations” and is available on My Oracle Support through Doc ID 870314.1

There is a LOT written about performance. And this post is now adding to it. Some of it’s excellent, some of it less so. But a lot of it starts from a point so far down the process that unless you know the first bit, you’re going to raring off and end up chasing your tail or p###ing in the wind…. (pardon my french). Without a well structured approach that you understand and always follow you’ll hit on solutions by luck only.

A very interesting new PDF from Sun on deploying OBIEE has been published, with discussions on architecture, performance and best practice.

This Sun BluePrints article describes an enterprise deployment architecture for Oracle Business Intelligence Enterprise Edition using Sun servers running the Solaris Operating System and Sun Storage 7000 Unified Storage systems. Designed to empower employees in organizations in any industry—from customer service, shipping, and finance to manufacturing, human resources, and more—to become potential decision makers, the architecture brings fault tolerance, security, resiliency, and performance to enterprise deployments. Taking advantage of key virtualization technologies, the architecture can be used to consolidate multiple tiers onto a single system to help reduce cost and complexity. A short discussion of the performance characteristics of the architecture using a realistic workload also is included.

A new OBIEE benchmark has been published by Oracle. It’s on the same hardware as August’s benchmark - Sun T5440s. Anyone would think that Oracle like Sun ;-)

Details here

Numbers added to my collation post here

This is a supplemental post to this one describing how to set up a VUser in LoadRunner to test OBIEE. It’s various notes that I made during the development but which aren’t directly part of the step-by-step tutorial. They’re not necessarily vital for recording scripts, but observations and explanations that should be helpful when working with LoadRunner and OBIEE.

Validation using sawserver logs

It’s no use running a load test if the load you think you’re applying isn’t actually being applied. To validate the test you compare what happens on the server when the scenario is manually performed with what happens with it’s from a VUser and hopefully the same behaviour is observed.

My two earlier posts (here and here) detail the difficulties I had with LoadRunner (now called HP Performance Center). After a bit of a break along with encouragement from knowing that it must be possible because it’s how Oracle generates their OBIEE benchmarks I’ve now got something working. I also got a useful doc from Oracle support which outlines pretty much what I’ve done here too.

In essence what you do - and what the Metalink document 496417.1 states - is you use the Web (HTTP/HTML) protocol with URL-mode.

A new blog from James Morle, who I don’t know but from other bloggers sounds well respected, and describes himself thus:

Since it’s been nearly ten years since I wrote my book, Scaling Oracle8i, I thought it was about time that I started writing again. I thought I would start with the new-fangled blogging thing, and see where it takes me. Here goes.

He’s got a really interesting post on “red flags” to look for in diagnosing performance problems in Oracle: Spotting the Red Flags (Part 1 of n).

(Updated 12th Oct 09)

Here’s a list of the OBIEE benchmark documents published by Oracle:

Benchmark	Date	Source document
1 - IBM System x3755	Sep-07	PDF
2 - HP DL380 G4	Sep-07	PDF
3 - Sun T2000	Sep-07	PDF
4 - Sun SPARC Enterprise T5440	Aug-09	PDF
5 - Sun SPARC Enterprise T5440	Oct-09	PDF

Collecting the numbers into one table gives this:

Based on the details in the documents I think these were all against OBIA’s Service Analytics schema & dashboards/reports.

Interesting to note the side-by-side comparison in benchmark 3 (Sun T2000) of two servers, in one case both running BI and Presentation Services and in the other having the two components separate. It appears to highlight the benefit that clustering provides in making the best use of resources.