Question Re: Data load from Write Optimized DSO taking long time in Technology Q&A

Data load from Write Optimized DSO taking long time

Former Member — Mon, 25 Nov 2013 12:22:44 GMT

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.

Re: Data load from Write Optimized DSO taking long time

former_member199945 — Mon, 25 Nov 2013 12:33:18 GMT

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

Re: Data load from Write Optimized DSO taking long time

former_member182470 — Mon, 25 Nov 2013 12:33:43 GMT

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.

Re: Data load from Write Optimized DSO taking long time

Former Member — Mon, 25 Nov 2013 12:46:02 GMT

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.

Re: Data load from Write Optimized DSO taking long time

former_member182470 — Mon, 25 Nov 2013 12:54:08 GMT

Do you have any semantic keys defined in W DSO?

Re: Data load from Write Optimized DSO taking long time

Former Member — Mon, 25 Nov 2013 12:56:10 GMT

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

Re: Data load from Write Optimized DSO taking long time

former_member182470 — Mon, 25 Nov 2013 13:11:55 GMT

Try reducing data package size to 50000 in DTP.

Re: Data load from Write Optimized DSO taking long time

Former Member — Mon, 25 Nov 2013 13:17:36 GMT

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

Re: Data load from Write Optimized DSO taking long time

former_member182470 — Mon, 25 Nov 2013 13:24:46 GMT

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

Re: Data load from Write Optimized DSO taking long time

marcel_scherbinek — Mon, 25 Nov 2013 13:53:38 GMT

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

Re: Data load from Write Optimized DSO taking long time

Former Member — Tue, 26 Nov 2013 09:36:18 GMT

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

Re: Data load from Write Optimized DSO taking long time

RamanKorrapati — Tue, 26 Nov 2013 09:40:39 GMT

Hi Madhu,

Try to do RSRV test on WDSO.

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

Thanks

Re: Data load from Write Optimized DSO taking long time

Former Member — Tue, 26 Nov 2013 14:30:51 GMT

No RSRV since the DSO is write optimized.

Re: Data load from Write Optimized DSO taking long time

Former Member — Tue, 26 Nov 2013 14:32:43 GMT

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

Re: Data load from Write Optimized DSO taking long time

former_member182470 — Tue, 26 Nov 2013 15:20:15 GMT

HI,

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

Re: Data load from Write Optimized DSO taking long time

marcel_scherbinek — Tue, 26 Nov 2013 15:22:44 GMT

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

Re: Data load from Write Optimized DSO taking long time

MGrob — Tue, 26 Nov 2013 16:38:35 GMT

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

Re: Data load from Write Optimized DSO taking long time

Former Member — Mon, 02 Dec 2013 06:13:37 GMT

Hi All,

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

Thnaks

Venkat

Re: Data load from Write Optimized DSO taking long time

Former Member — Mon, 02 Dec 2013 11:45:05 GMT

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

Re: Data load from Write Optimized DSO taking long time

former_member188080 — Mon, 02 Dec 2013 11:56:16 GMT

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