Performance issues with Outer joins - BO 5.1.7/Oracle 9i

building semantic layer
1

Hi,

We recently migrated to BO 5.1.7 with Oracle 9.2.0.3, We are having seviour performance issues on the reports using OUTER JOINS and VIEWS.

If anyone had similar issues or any resolution…please respond.

Thanks in advance.

suntra (BOB member since 2003-02-06)

2

Outer joins are known performance killers. Views depend on the SQL used to create them. If you have an outer join in a view, it will also affect performance.

What versions did you migrate from? Did you have any problems before? Did you use outer joins and views before? Did you modify any of the settings in your previous set up and, if so, are you sure your transfered them correctly to the new versions?

Lee Drake :us: (BOB member since 2002-08-15)

3

Hi Lee,

We migrated from BO 5.1.3 /Oracle 8.1.7 to BO 5.1.7 / Oracle 9.2.0.3, All the existing reports were running fine wthout any issues earlier.

We haven’t done any modifications in the earlier version, which needs to be taken care with 5.1.7.

As per the documentation I found, BO had done changes to it’s calculation engine which affects the reports with the below.

·Multiple data providers or a single data provider with multiple cubes, and global filters
·Multiple data providers or a single data provider with multiple cubes, and complex filters
·Incompatible objects that appear in the same block

But apart from the above we were having issues with Outer Joins and Views.

Thanks in advance.

suntra (BOB member since 2003-02-06)

4

I have not worked with Oracle for a couple of years, so I do not know much about the different versions of Oracle. There is a few things I would find out about first.

Which optimizer is Oracle using, Cost based or Rules based?

Did it switch optimizers when the uprade was implemented?

Do the views contain outer joins?

How do outer joins perform in SQL+?

How does selecting from a view perform in SQL+?

Basically, SQL performance is determined by the database. How the SQL is put together is determined by Businessobjects. Either Oracle is performing badly, BusObj is putting the SQL together badly, or both.

Lee Drake :us: (BOB member since 2002-08-15)

5

I’d agree with Lee, it certainly seems an Oracle rather than B.O. issue.

When we migrated from 7.3.4 to 8, we went through about 3 weeks worth of performance testing and tuning - get your dbas on the case and don’t settle for a no!!!

6

You also have to apply patch for a BO bug #1075432, Oracle bug #2755842.

This bug applies to oralce 9.2.0.3 version only

JaiGupta (BOB member since 2002-09-12)

7

Please have your DBA re-run catproc.sql and calalog.sql as SYS :lol:

emiller (BOB member since 2002-11-06)

8

I tend to agree with the general consensus of the earlier replies. My hunch is that you are using a Cost Based Optimizer and your Statistics are out of date or not collected. You need to speak with your DBA.

Paul Shovlar :uk: (BOB member since 2002-09-05)