Extraction job in R/3 is taking too long to load data in BI

[former_member189845]

Hi All,

We are facing issue in production, where one of extraction job from R/3 to BW is taking more than 7 hours of time.

Here Data source is the customized one, and extractor structure is based on view where consists of 9 tables. Problem here the same job where used to take only 15 minutes for records 458000 but after the migration happened ( Data center migration moved from one vendor to other vendor ) and after that only the issue is there where job is taking 7 hours for similar amount of records.

We checked the same scenario in quality, where there is no issue the job is taking only 15 - 20 minutes of time.

Through the first stage of analysis we had done the trace details for long running job in R/3 , As per the trace details the job is taking its maximum time at the DB end (Which is 82.1%). In ABAP its taking 17.9% of its total execution time and in DB its taking 82.1% of its total execution time.

Maximum time taking while calling the two exits

In Exit EXIT_SAPXXX_002 its taking the time at DB end while selecting the data from two tables. At this statement took 31.3% + 10.5% of total execution time. 

In function Z_BWEX_DS_MD_ZBW_XXX taking 40.3% of total execution time at the fetch statement.

I tired analysing with the subset of data i.e. by limit the data being processed by restricting it but unfortunately there is no selection available.

And also will check the system performance at that time and number of available back ground work process are free.

Don't know what exactly that cause the issued after the migration happened. We have raised an OSS message for that.I'm trying to understand why/what is use of the first exit, where two expensive select statement on two tables meant for ?

Note: Here our BW system DB is HANA , and our R/3 is oracle DB

Can you guys help me with the issue.

Thanks in advance for your help

Siva

[former_member450139 · Accepted solution]

Hi Siva,

Since the issue started and is happening after datacenter migration, I would assume that there is no application level change ( like code, volume , job variant and job schedule)  and no application level resource issue and most of time is on database side and in sequential read.. With those assumption, then the  issue  from application server point view, it is a database related performance issue. It could be an Oracle CBO issue, it could be a network traffic/latency issue, it could be an IO contention issue or Oracle Server capacity issue..You need to validate those area one by one.

A lot of my assumption can be validated via job runtime  statistics  which tell where the time is spent by the job.  Job runtime statistics can point you to right direction on what you should focus on investigation. For example, if job statistics show that the job spent a lot of time in "waiting for work process", then shortage of work process is a contributor to this issue. But this is unlikely case based on what has been shared so far. If table level statistics collection is enabled in your system, you can find out top most expensive table the job accessed during the execution.

You can use ST05 to do a trace to find the most sql statement and their execution plan.. Some times  you can use info from STAD to find out the expensive SQL from ST04 transaction... from there, you can analyze the execution plan to see whether plan switch is a concern..  ST04 can tell you where time is spent by a SQL... That information can normally tell you the next direction of tuning if plan is correct...

I knew you did a trace but duration is only a few minutes -- considering the job duration is 7 hours.. not sure of how typical your trace is....

Hope this help. If you need more information on SQL tuning, ST04, ST05 and STAD, following blog might help you. http://www.sap-perf.ca/

Eric

[Clemenss · Accepted solution]

Also check table size category - once exceeded access slows down!

Regards Clemens

[former_member189845 · Accepted solution]

Hi Clemens ,

Can you please be more specific about the table size , i see that table called MVKE is size category 3 and records stored is billions

Thanks,

siva

[Former Member]

Hi,

Check your back groung processes are available or not.

Kill the job and  restart.

BPL

[Former Member]

hi.

Why you not try to just compare access plan of SQL statements on QA and Prod.

If refresh of data on QA not so old and the plans is different then you must find a reason why it happen (broken indexes, statistics or data size increased so optimizer uses another access plan).

If your system migrated to another vendor then it can use different HW and disks. And I think it possible that data distribution on disks was changed (tablespaces and etc). And also some DB settings need to be adjusted. I agree with Sander that you need to involve DBA to make performance trace.

For example, few days ago we moved our saptempdb to another disk (it was already mounted to BW but was not used) and BW performance become very bad. After moving of the saptempdb back we got performance back...

[sander_vanwilligen]

Hi Siva,

Further to the reply of Raf (Feb 19, 2016 11:08 AM), I suggest to compare the Oracle release and patch level between the old system and the migrated system. Can you please check and share it with us?

The Oracle documentation says the following about the hint ORDERED:

The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause.

If you omit the ORDERED hint from a SQL statement performing a join, then the optimizer chooses the order in which to join the tables. You might want to use the ORDERED hint to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. Such information lets you choose an inner and outer table better than the optimizer could.

Let's assume that the Oracle hint was implemented with a valid reason, then I suspect that it might be an Oracle bug in the migrated system. It could be very helpful to involve an SAP Basis Administrator or an Oracle DBA.

Best regards,

Sander

[former_member189845]

Yes but that is not the case when we see in quality, where we have the same code and there is no issue in quality. I'm just checking on oracle HINT

Also there is no migration of system happened, just the data center got migrated from one vendor to another vendor

Thanks,

Siva

[Former Member]

Hi,

This sound like some rare issue on itab or SQL plan.

I wonder e_t_data is never clear in the program, if it is, try add a REFRESH statement at begin of the program, it may work like magic sometime.

Regards

Bill

[former_member189845]

Thanks for your reply Bill, Yes there is no refresh for the internal table .. But logically the issue is after migration, any clue that why this had happened ?

Perhaps any settings had been changed during migration of data center, This affects only this DS and rest all are fine.

Any clue ?

[Former Member]

Hi,

From what you describe there are 2 rare cases come to my mind.

1) APPENDING data into itab without first CLEAR it.

I saw this case for a few times, which happen on code which APPENDING data to itab during data load process.

This piece of code will work normally most of the time, but in rare case it will become really slow.

And what happening here is, somehow the itab is mapped to some memory space and it happen to think it really have many rows of data in it, so if you have further code to loop on this item to do something, it become very time consuming as there are too many records.

2) Data in a complex join *just happen* come to some magic number, and it change the execute plan

This one is at database level, sometimes for some complex join SQL, it work perfectly.

But one day table row count increased to some point and caused the optimizer suggest a new plan, but the new plan turn out working real bad.

****

As I saw you using APPENDING, so I wonder you hit point one, but it can only check via debug or adding one clear statement.

I am not sure are you hitting point 2, and have no comment right now, as this may need to go into debug and check SQL plan.

Regards

Bill

[RafkeMagic]

check whether that Oracle Hint is still ok

[Former Member]

Dear,

Your code can be more optimized, Why don't you try to run your your data source in both systems considering you have set of data in both systems (QA and production). Also did you check if you have same code in both systems and same data set.

statement 'Your %_HINTS ORACLE 'ordered'" looks to be not associated with open SQL compatible with ABAP. Can you try to remove this statement, Also check if you are using keys in each table when you are using ON condition in joins.

Thanks & Regards,

M

[former_member189845]

Yes Mahendra I tested in both systems, where there is no issue in quality and job gets completed in 15 minutes , issue is after the data center migration happened.

[former_member189845]

: @RamanKV @Sander van Willigen : Can you ps help in this issue

[Clemenss]

Hi Siva,

thank you for mentioning me.

As said, the cause is the code in EXIT_SAPXXX_002.

I can not comment on that code as it is not given.

Please ask your DB admin to run (actualize) table statistics on the DB tables involved. This helps in most cases.

Regards Clemens

[former_member189845]

Hi Clemens Li,

Thanks for you reply, I just debugged by with few extraction records as below

Data Records/Call: 100

No of Extraction call : 10

And i see that the time is getting consumed during the Fetch statement.

here is the code below,

OPEN CURSOR WITH HOLD s_cursor FOR

      SELECT t1~mandt t1~matnr t1~werks t1~stlan t1~stlnr

             t1~stlal t2~stlty t2~stlkn t2~stasz t4~stpoz

             t4~idnrk t4~postp t4~posnr t4~meins t4~menge

             t3~bwkey t3~bwtar t3~verpr t3~vprsv t3~stprs

             t3~peinh t6~waers

             FROM XXX AS t2 INNER JOIN XXX AS t4

                   ON t2~stlty = t4~stlty

                    AND t2~stlnr = t4~stlnr

                    AND t2~stlkn = t4~stlkn

                    INNER JOIN XXX AS t1

                    ON t1~stlnr = t2~stlnr

                    AND t1~stlal = t2~stlal

                    INNER JOIN XXX AS t3

                    ON t4~idnrk = t3~matnr

                    AND t1~werks = t3~bwkey

                    INNER JOIN XXX AS t5

                    ON t3~bwkey = t5~bwkey

                    INNER JOIN XXX AS t6

                    ON t5~bukrs = t6~bukrs

            WHERE t1~stlan = '5' OR t1~stlan = 'K'

       %_HINTS ORACLE 'ordered'.

    ENDIF.

* Fetch records into interface table.

    FETCH NEXT CURSOR s_cursor

               APPENDING CORRESPONDING FIELDS

               OF TABLE XX_data

               PACKAGE SIZE i_maxsize.

    IF sy-subrc <> 0.

      CLOSE CURSOR s_cursor.

      RAISE ssss_data.

    ENDIF.

[RafkeMagic]

What exactly got migrated? Your datacenter or database? In your open cursor you have an Oracle specific statement (%_HINTS ORACLE) which other database won't be able to cope with.

Also, it doesn't really help when you change the actual table names in XXX...

[former_member189845]

Data center got migrated , ps let me know your email id ill send you the code

Thanks,

Siva

[former_member189845]

Thanks Celmens , I think some tricky should happen . Asked for basis team to check if any statistics for the tables and what is the current statistics update

[former_member185132]

Thanks for mentioning me Siva, but I have to agree completely with what Raf has been telling you.

Without the code, there's no way somebody can understand what might be going wrong.

[former_member189845]

Here is the Complete code Suhas,

Where it is taking more time during the fetch

OPEN CURSOR WITH HOLD s_cursor FOR

      SELECT t1~mandt t1~matnr t1~werks t1~stlan t1~stlnr

             t1~stlal t2~stlty t2~stlkn t2~stasz t4~stpoz

             t4~idnrk t4~postp t4~posnr t4~meins t4~menge

             t3~bwkey t3~bwtar t3~verpr t3~vprsv t3~stprs

             t3~peinh t6~waers

             FROM stas AS t2 INNER JOIN stpo AS t4

                   ON t2~stlty = t4~stlty

                    AND t2~stlnr = t4~stlnr

                    AND t2~stlkn = t4~stlkn

                    INNER JOIN mast AS t1

                    ON t1~stlnr = t2~stlnr

                    AND t1~stlal = t2~stlal

                    INNER JOIN mbew AS t3

                    ON t4~idnrk = t3~matnr

                    AND t1~werks = t3~bwkey

                    INNER JOIN t001k AS t5

                    ON t3~bwkey = t5~bwkey

                    INNER JOIN t001 AS t6

                    ON t5~bukrs = t6~bukrs

            WHERE t1~stlan = '5' OR t1~stlan = 'K'

       %_HINTS ORACLE 'ordered'.

    ENDIF.

* Fetch records into interface table.

    FETCH NEXT CURSOR s_cursor

               APPENDING CORRESPONDING FIELDS

               OF TABLE e_t_data

               PACKAGE SIZE i_maxsize.

[Former Member]

Hi Siva,

May be you can give a try by reactivating the datasource through the program RSDS_DATASOURCE_ACTIVATE_ALL.

[former_member189845]

May I how does it help in our case ? Or just a guess ?

Thanks,

Siva

[Former Member]

Sometimes after migration, this issues can be seen.system fails to get metadata for datasources while loading.

[Former Member]

Dear,

Why don't you run generic data source using RSA3 in ECC itself. Put a break point in your data source source code. Check following:

1. Indexes are created OR not on tables which are used for extraction? If not then ask your basis guys to create secondary indexes.

2. Check if you have same set of data in quality and production, if you are using FOR ALL entries clause, have a second look at that. Might be possible that source table is blank and and if extraction is from big tables that it will take long time if source table is blank.

3. While your info package is running, try to check job overview in source system and see at what stage your processing is sucking.

4. Try to use t-codes like SM66/SM50 to see what object what your extraction is doing and where it is taking time.

These are few of the trouble shooting ideas that I can think of...

Thanks & Regards,

M

[former_member189845]

Thanks for checking and giving some suggestion.

Yes, i'm already checking through RSA3 and trying to understand the code

You said about the secondary index , on what basis the secondary indexes can be created. As you see in above pic there is second exit which is hitting at DB level about 40% it.e the Fetch statement you have any idea about it ?

And checking had already been done through trace where I shared the results also where the background job is taking long time,

I just want to know why the issue is happening after the migration, basically why after migration the performance issue is ?

[RafkeMagic]

The problem is obviously in your user exit... so, have a look there.

[former_member189845]

Yes the problem is in exit, but I hope that you've read my issue clearly.

Issue is after the migration in production the same exit before migration was working and taking only less time,

[Former Member]

Have you checked the indexes?

[RafkeMagic]

I did read your issue carefully, but my answer remains the same... I have no clue what is in your exit as you did not share the code, so the problem (and most likely the answer) is there.