Reading MultiProvider with RSDRI_INFOPROV_READ in the batch
After the upgrade from 3.5 to BI 7.0 SP15 we got a performance problem with the program where RSDRI_INFOPROV_READ is used to read the data from the MultiProvider. The of this step increased from 300 secs to 2400secs on avarage. the Oracle DB was upgraded to 10g a week before, so it is not related to it.
There are 7 InfoCubes and 1 InfoObject in the MultiProvider. During execution of the RSDRI function all data is read from aggregates for respective InfoCubes. One aggregate - 100074 - is quite big, like 13mio records, and this is where system spends most of the time to read the data, although this aggregate does not return data due to selection conditions. All indexes and DB statistics are up-to-date.
ORDER AGGREGATE TIMEREAD DBTRANS WP 1 100037 200,554576 221.282 71 2 100039 80,198551 91.848 71 3 100040 141,451593 107.049 71 4 100059 0,386772 0 71 5 100060 7,732832 0 71 6 100074 1087,248599 0 71 7 100056 0,143263 0 71
It is difficult to narrow down the root-cause:
1) Is it because of the change in RSDRI_INFOPROV_READ (the function module code in 7.0 is completely different now, then it was in 3.5)?
2) Is it because of some changes in the OLAP/DM processing, like generating different SQL access plan, then before?
3) Is it because of some system resources utilization change?
<b>Did anyone experience similar problem?</b>
Can someone help with understanding this additional points:
a) In BI7.0 execution of the query in background is always sequential. Was it the same in BW3.5?
b) When data from particular part providers are merged during sequential execution? I.e. are data returned from previous part provider are somehow included into the SQL select for next part provider? (It might make no sense, but am just checking)
c) Can we change the order in which data is being read during sequential execution of the query on MultiProvider?
Will appreciate any ideas!! Thanks!