Heavy MDX Query


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


sql statement.pdf (16.7 kB)
sql explain.pdf (26.2 kB)
more ▼

asked Aug 08, 2012 at 09:25 AM in Default

avatar image

0 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Hi, any ideas out there....? Thanks, Brendan

more ▼

answered Aug 10, 2012 at 11:30 AM

avatar image

0 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 08, 2012 at 09:25 AM

Seen: 1262 times

Last Updated: Aug 10, 2012 at 11:30 AM

Copyright 2018 Redgate Software. Privacy Policy