Heavy MDX Query

Hi,

We're using SAP BPC on a .net server and it's calling SAP BW via an MDX query. We've run SQL trace on BW during the MDX query and identified a single SQL statement (attached) that takes on average 30 seconds to process.

The underlying table being queried holds 11 million records. Data storage is on XIV and our DB is on a P7 running AIX and Oracle 11g. We would like to reduce run times to sub 5 seconds. We do not believe hardware (CPU, memory, IO) is a constraint, but rather that the query is inefficient and that it takes Oracle time to process all the joins and aggregate the data.

We've run a SQL Explain (attached below) to identify the expensive parts of the SQL. Which parts of the SQL are expensive? If we pre-aggregate the table that has 11 million rows will this significantly improve response times? Or is there something else we are overlooking (Oracle tuning, indexes)?

Looking at the SQL Explain:

• "TABLE ACCESS BY INDEX ROWID" has a high cost (92) but has 0 %CPU
• "SORT UNIQUE" and "VIEW" are expensive (25 cost, 88 %CPU).
• "HASH JOIN" (128 cost, 10 %CPU)

SQL Statement

SQL Explain

We've been looking at this problem for many weeks and have not been able to make much progress. The slow response times are really hurting the business users who run and refresh reports many many times throughout the day, any advice or guidance is appreciated.

Thanks for your help

Brendan