Data load from Write Optimized DSO taking long time

[Former Member]

Hello Experts,

We are facing an issue with the DTP load from Write Optimized DSO.

Till some time back, a full load from Write Optimized DSO used to complete in 1-2 hours maximum. However it is taking more than 6 hours to load data currently. (This system is live from 2010, hence the increase in records will not be the reason. Also NO transformation routines - so no ABAP coding issues).

Maximum time takes in fetching data for first data package and once this is completed (after 6 hours), the remaining steps completes fast.

In SM50, sequential read from the active table of DSO is taking the time (program CL_SQL_RESULT_SET=============CP).

How can we improve the performance?

SAP version is SAP_BW, 701, SP 0012.

Oracle DB 11.2.0.3.0.

Regards,

Madhu.

[Former Member]

This issue was not observed after migrating system to HANA. Hence closing this question.

[Former Member]

Just use DB02 transaction to recreate indexes for the DSO and that should help improve the performance for the DTP..

[former_member188080]

this is more on basis side as you did not change anything.

just check below SAP notes

981875  - Optimizer merge fix for Oracle 10.2.0.2

1171650 - Automated Oracle DB parameter check

830576  - Parameter recommendations for Oracle 10g

192658  - Setting parameters for BW systems

Thanks and regards

Kiran

[Former Member]

Hi Madhu,

Are u loading to an Info cube from your write Optimized DSO?. If yes then can u try deleting the index for ur cube and then try to load the data. This will improve the performance.

Thanks,

Sathya

[Former Member]

Hi Experts,

We had below upgrades in the recent past:

1. SAP BW Support package upgrade to 701-012

2. Oracle DB schema upgrade from version 10 to 11.

Anything to do with these upgrades?

Regards,

Madhu

[former_member182470]

HI,

Increase the datapackage size to 200000 and BGDs as 8. This will improve the performance.

[marcel_scherbinek]

Hi Madhu,

as it takes too long by requesting the data with SQL and the WHERE conditions of the DTP filter. I guess it has something to do with the Oracle DB schema upgrade. Did you already try to reduce the filter values of the DTP to a minimum? If performance already increases by reducing the amount of filters to minimum it might indicate a problem with the Oracle database upgrade. But I have no actual overview of SAP notes refering to this issue.

Regards,

Marcel

[MGrob]

Hi

You can generate the side effects report on the market place to see if there is a know issue related to the newly expirienced DTP performance issues.

hope that helps

Martin

[Former Member]

Hi All,

Is the issue solved i am also facing same issue DTP load from W/DSO is struck at SM66.

Thnaks

Venkat

[marcel_scherbinek]

Has something changed on Oracle site?

As selecting the result set takes the most of the time (according due to your information) there might be problems by fetching the data from the database with the SQL statement.

As far as I know the SELECT statement is created individually with all the DTP filters for the database. So I would do a SQL trace or take a closer look on Oracle site about the main consumption of the time. Maybe playing a little bit with the DTP filters to find the "corrupt" characteristic which causes the long runtime.

Regards,

Marcel

[Former Member]

The only change performed at Oracle side was the schema upgrade from version 10 to 11. Will this have any impacts?

[RamanKorrapati]

Hi Madhu,

Try to do RSRV test on WDSO.

Tx - RSRV , All elementary test --> DSO. later try to load data to target.

Thanks

[Former Member]

No RSRV since the DSO is write optimized.

[Former Member]

No semantic keys. In order to improve the performance, we have created secondary indexes based on the DTP filters, still no improvement.

[former_member182470]

Try reducing data package size to 50000 in DTP.

[Former Member]

It was initially 50,000. It was not working then. Hence tried 100,000.

[former_member182470]

That's what, revert back to 50000 or 75000.

[Former Member]

No other jobs are running.

Number of parallel processing is 4

Data package size is 100.000.

This is something specific to Write Optimized DSO.

Thanks,

Madhu.

[former_member182470]

Do you have any semantic keys defined in W DSO?

[former_member182470]

Please ask your BASIS to check any other jobs are running in Parallel in over all BW system. This can slow down your DTP job. Hope you have not touched data Package size settings recently.

[former_member199945]

Hi Madhu,

Any DSO Related Performance Issues Go to TCODE rsodso_settings in BI. Hetre you can Increase BG Jobs, Sid generation, Maintenance of runtime parameters etc.

Also Check this oss notes : 1118205,25528,192658

Regards,
Seshu.P