BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: No events within the next 45 days.

Data Services Job has become very slow


 
Search this topic... | Search DI: Performance and Tuning... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Performance and Tuning  Previous TopicPrint TopicNext Topic
Author Message
Anand.P
Forum Member
Forum Member



Joined: 05 Dec 2011

Posts: 4
Location: Hyderabad, India


flag
PostPosted: Tue Mar 12, 2013 8:06 am 
Post subject: Data Services Job has become very slow

Hi All,

We are facing this issue since last month.
Suddenly our DS Job has become very slow.
I have tried the following things:
1. used bulk load with commit after 10000 rows.
2. increased the array fetch size to 10000.
3. increased the monitor job log to 10000.

But, there is no improvement in the execution time.
The same jobs were running quickly just 1 month ago. I have checked the data and there doesn't seem to be any huge increase in the number of rows being inserted.

One issue could be huge size of al_statistics table.(right now it has more than 100 million rows).

Sometime I see that the Job takes a long time in printing the message for data completion , having finished the load in the table almost 5 -10 mins ago.

Kindly suggest on ways to improve the execution time of the Job.

Regards,
Anand banghead.gif
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10591
Location: Germany


flag
PostPosted: Tue Mar 12, 2013 8:20 am 
Post subject: Re: Data Services Job has become very slow

If you don't need the statistics I would immediately truncate the AL_STATISTICS table. And figure out why the automatic deletion does not work.

And I would go through the standard methods of figuring out where the bottleneck is, adding a Map_Operation set to discard to see if it is the reader, the transformation or the loader.

_________________
Werner Daehn
Product Manager Data Services
SAP AG
�The opinions expressed in this post are my own views and not those of SAP.�
Back to top
Anand.P
Forum Member
Forum Member



Joined: 05 Dec 2011

Posts: 4
Location: Hyderabad, India


flag
PostPosted: Tue Mar 12, 2013 10:42 pm 
Post subject: Re: Data Services Job has become very slow

Hi Werner,

Thanks for the advice. Can you please tell me if the size of the al_statistics table has any effect on the speed of the job and on BO reports.
Recently few of our webI Reports have also started taking longer time.

Our database is Oracle9i and is in the process of upgrade to 11g.

Regarding your suggestion to find the bottlenecks, could you please explain a bit more on how to use map_operation to find where it is taking more time.

More Details about our job:
The same dataflows complete very quickly whenever the data is incremental (around 1000 rows), but on running them as complete reload (approx 2 million rows) , it is taking around 3000 seconds or even more sometimes.
Note:
At the same time 8 separate complete reload dataflows will be runnning, each populating around 0.4 million to 2 million rows. The time taken by these dataflows is in the range of 2000 secs to 4000 secs separately.

Kindly suggest you views.

Regards,
Anand
Back to top
eganjp
Forum Addict
Forum Addict



Joined: 12 Sep 2007
ASUG Icon
Posts: 4280
Location: Denver Area


flag
PostPosted: Wed Mar 13, 2013 2:59 pm 
Post subject: Re: Data Services Job has become very slow

I sincerely doubt that the size of the repository statistics table is the problem.

What likely is the problem is your use of Bulk Loader against Oracle. Enabling this causes the inserts to become a Direct Path load. When this happens Oracle will disable the indexes on the target table, load the data and then rebuild the indexes. It is the rebuild that can be very, very slow on large tables with b-tree indexes.

In no particular order, here are things to try:
1. Stop using Bulk Loader
2. Reduce the number of indexes
3. Switch to bitmap indexes (where appropriate)
4. Partition the table and use partition exchange

_________________
Jim Egan
ProKarma-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaker at Business Objects User Conference 2013
-My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Kill ETL bugs before they kill you!
My ETL blog

Latest Blog Posts
• 2014-04-17 Can Your ETL Make Your Reporting Faster?
• 2014-04-04 ProKarma Acquires Mantis Technology Group
• 2014-04-02 How to Create Charts in Webi (Part 2 of 2)
Back to top
rm24
Forum Member
Forum Member



Joined: 21 Jul 2017
ASUG Icon
Posts: 3


flag
PostPosted: Thu Dec 07, 2017 3:30 pm 
Post subject: Re: Data Services Job has become very slow

Hello Werner,

Recently my bods job have been running slowly, from 1-2 hours took 12 hours. No change in table configuration, no errors. Data source is OLFM and target is HANA.

After running for 11 hours it failed and restarted.

(14.2) 12-06-17 13:41:55 (E) (11989:537925376) DBS-070401: Dataflow DF_Init_OLFM_XLA_XLA_AE_LINES|XLA_AE_LINES
ODBC data source <VHPBIPH1DB> error message for operation <SQLExecute>: <[SAP AG][LIBODBCHDB SO][HDBODBC] General error;129
transaction rolled back by an internal error: Allocation failed ; $size$=40000; $name$=libhdbrskernel.so; $type$=pool;
$inuse_count$=1726511; $allocated_size$=1226819927>.
(14.2) 12-06-17 13:59:40 (E) (11941:2113906464) RUN-050316: |Session Job_Init_XLA_XLA_AE_LINES
Job failed
(14.2) 12-06-17 13:59:40 (E) (11941:2113906464) RUN-050304: |Session Job_Init_XLA_XLA_AE_LINES
Function call <raise_exception ( Job failed ) > failed, due to error <50316>: <Job failed>.
(14.2) 12-06-17 13:59:40 (E) (11941:2113906464) RUN-050316: |Session Job_Init_XLA_XLA_AE_LINES
Job failed


Please let me know your thoughts!

Thanks
Ravi.
Back to top
eganjp
Forum Addict
Forum Addict



Joined: 12 Sep 2007
ASUG Icon
Posts: 4280
Location: Denver Area


flag
PostPosted: Thu Dec 07, 2017 4:01 pm 
Post subject: Re: Data Services Job has become very slow

Werner is no longer frequenting this forum.

It looks like you ran out of space or other resources.

_________________
Jim Egan
ProKarma-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaker at Business Objects User Conference 2013
-My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Kill ETL bugs before they kill you!
My ETL blog

Latest Blog Posts
• 2014-04-17 Can Your ETL Make Your Reporting Faster?
• 2014-04-04 ProKarma Acquires Mantis Technology Group
• 2014-04-02 How to Create Charts in Webi (Part 2 of 2)
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Performance and Tuning  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0310 seconds using 17 queries. (SQL 0.0021 Parse 0.0009 Other 0.0279)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo