Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Program terminated: Time limit exceeded, ABAP performance, max_wprun_time

Jarmo_Tuominen
Participant
0 Kudos

Hi,

I am running an ABAP program, and I get the following short dump:

Time limit exceeded. The program has exceeded the maximum permitted runtime and has therefore been terminated. After a certain time, the program terminates to free the work processfor other users who are waiting. This is to stop work processes being blocked for too long by

- Endless loops (DO, WHILE, ...),

- Database acceses with large result sets,

- Database accesses without an apporpriate index (full table scan)

- database accesses producing an excessively large result set,

The maximum runtime of a program is set by the profile parameter "rdisp/max_wprun_time". The current setting is 10000 seconds. After this, the system gives the program a second chance. During the first half (>= 10000 seconds), a call that is blocking the work process (such as a long-running SQLstatement) can occur. While the statement is being processed, the database layer will not allow it to be interrupted. However, to stop the program terminating immediately after the statement has been successfully processed, the system gives it another 10000 seconds. Hence the maximum runtime of a program is at least twice the value of the system profile parameter "rdisp/max_wprun_time".

Last error logged in SAP kernel

Component............ "NI (network interface)"

Place................ "SAP-Dispatcher ok1a11cs_P06_00 on host ok1a11e0"

Version.............. 34

Error code........... "-6"

Error text........... "connection to partner broken"

Description.......... "NiPRead"

System call.......... "recv"

Module............... "niuxi.c"

Line................. 1186

Long-running programs should be started as background jobs. If this is not possible, you can increase the value of the system profile parameter "rdisp/max_wprun_time".

Program cannot be started as a background job. We have now identified two options to solve the problem:

- Increase the value of the system profile parameter "rdisp/max_wprun_time"

- Improve the performance of the following SELECT statement in the program:

SELECT ps_psp_pnr ebeln ebelp zekkn sakto FROM ekkn

INTO CORRESPONDING FIELDS OF TABLE i_ekkn

FOR ALL ENTRIES IN p_lt_proj

WHERE ps_psp_pnr = p_lt_proj-pspnr

AND ps_psp_pnr > 0.

In EKKN we have 200 000 entries.

Is there any other options we could try?

Regards,

Jarmo

1 ACCEPTED SOLUTION

christian_wohlfahrt
Active Contributor
0 Kudos

Hi,

a commit work will reset the time-out counter, too. Placed directly behind your select might help - if time-out doesn't come in between.

But your select statement can be improved, too. The part 'AND ps_psp_pnr > 0.' should be skipped. If you have 0 (or negative values) in your table p_lt_proj, then delete these entries out of this table.

If these entries are still needed, create a copy p_lt_proj_sel, which you can prepare for selection:


data p_lt_proj_sel like p_lt_proj.
p_lt_proj_sel[] = p_lt_proj[].
sort p_lt_proj_sel by pspnr.
delete adjacent duplicates from p_lt_proj_sel comparing pspnr.
delete itab p_lt_proj_sel where pspnr < 0.

(I havn't checked syntax (sorry), but you can make this run correctly.)

One additional remark: for table EKKN index P matches your selection, check if it's existing and statistics are up to date. Check usage with a SQL-trace.

Kind regards,

Christian

7 REPLIES 7

christian_wohlfahrt
Active Contributor
0 Kudos

Hi,

a commit work will reset the time-out counter, too. Placed directly behind your select might help - if time-out doesn't come in between.

But your select statement can be improved, too. The part 'AND ps_psp_pnr > 0.' should be skipped. If you have 0 (or negative values) in your table p_lt_proj, then delete these entries out of this table.

If these entries are still needed, create a copy p_lt_proj_sel, which you can prepare for selection:


data p_lt_proj_sel like p_lt_proj.
p_lt_proj_sel[] = p_lt_proj[].
sort p_lt_proj_sel by pspnr.
delete adjacent duplicates from p_lt_proj_sel comparing pspnr.
delete itab p_lt_proj_sel where pspnr < 0.

(I havn't checked syntax (sorry), but you can make this run correctly.)

One additional remark: for table EKKN index P matches your selection, check if it's existing and statistics are up to date. Check usage with a SQL-trace.

Kind regards,

Christian

Former Member
0 Kudos

Hi Jarmo,

I hope you are checking to see that p_lt_proj has some entries in them before you use it to select entries from EKKN. If the table p_lt_proj is empty all entries from EKKN will be selected and that could be your problem.

A simple <b>"check not p_lt_proj[] is initial."</b>, would suffice.

<b>Additionally indexing EKKN on ps_psp_pnr would help.</b>

Rishi

Former Member
0 Kudos

I had a problem with time out, the reason I found was

When using FOR ALL THE ENTRIES statements. This will not work in all the cases for performance.

The reason may be,

1. Before using FOR ALL THE ENTRIES statements, check the internal table is not initial p_lt_proj, else it will select for all the records.

2. You can removed FOR ALL THE ENTRIES statements and moved the value of p_lt_proj-pspnr to a range and try.

Please let no the results if it works

Prabhu Rajesh

0 Kudos

Also, in regard to the "FOR ALL ENTRIES" extensions, I have seen good response when the ITAB that you are referencing in the "FOR ALL ENTRIES IN ?" statement is sorting by the key in which you are accessing it.

Example.



SORT P_lt_proj ascending by pspnr.

SELECT ps_psp_pnr ebeln ebelp zekkn sakto FROM ekkn
    INTO CORRESPONDING FIELDS OF TABLE i_ekkn
        FOR ALL ENTRIES IN p_lt_proj
                WHERE ps_psp_pnr = p_lt_proj-pspnr
                  AND ps_psp_pnr > 0.

Regards,

Rich Heilman

Jarmo_Tuominen
Participant
0 Kudos

Thanks for your help, this problem seems to be quite challenging...

In EKKN we have 200 000 entries. 199 999 entries have value of 00000000 in column ps_psp_pnr, and only one has a value which identifies a WBS element.

I believe the problem is that there isn't any WBS element in PRPS which has the value of 00000000. I guess that is the reason why EKKN is read sequantially.

I also tried this one, but it doesn't help at all. Before the SELECT statement is executed, there are 594 entries in internal table p_lt_proj_sel:

  DATA p_lt_proj_sel LIKE p_lt_proj OCCURS 0 WITH HEADER LINE.
  p_lt_proj_sel[] = p_lt_proj[].
  DELETE p_lt_proj_sel WHERE pspnr = 0.
  SORT p_lt_proj_sel by pspnr.

  SELECT ps_psp_pnr ebeln ebelp zekkn sakto FROM ekkn
  INTO CORRESPONDING FIELDS OF TABLE i_ekkn
  FOR ALL ENTRIES IN p_lt_proj_sel
  WHERE ps_psp_pnr = p_lt_proj_sel-pspnr.

I also checked that the index P in EKKN is active.

Can I somehow force the optimizer to use the index?

Regards,

Jarmo

0 Kudos

Hi Jarmo,

with current distribution of values (only one prps filled > 0) you have just one entry in p_lt_proj_sel? Should go pretty fast, so let's help database optimizer to see it.

Don't try to force to take a specific index - that's just possible with native SQL (not open SQL). But you most probably have a cost based optimizer, which needs up-to-date statistics. You can refresh statistics with transaction DB20. (Looks little bit different depending on database and SAP version, but you should figure it out). Online refresh can't harm (simultaneous updates are possible), don't fear runtime for one table (~some minutes).

Afterwards database optimizer should like index P.

Regards,

Christian

Jarmo_Tuominen
Participant
0 Kudos

Thanks for your help!

I fixed it as follows:

data p_lt_proj_sel like p_lt_proj.
p_lt_proj_sel[] = p_lt_proj[].
sort p_lt_proj_sel by pspnr.
delete adjacent duplicates from p_lt_proj_sel comparing pspnr.

Now the SELECT statement is much more efficient.

Regards,

Jarmo