topic Re: Buffer cache memory when execute multi thread in Application Development and Automation Discussions

Buffer cache memory when execute multi thread

Former Member — Thu, 31 Dec 2015 08:57:54 GMT

Dear all

I have program to execute at the same time are 9 processing. But it has some issues as below.

1. Time limit exceeded

- The code error extract at the SELECT command (FAGLFLEXA table) .

- The BA had set the time to execute the program (runtime) is 20 minutes. however, the time that I execute my program on background mode is over 20 minutes, but it was running on background mode.

--------------------------------------------------------------------------------------------------------------

The maximum runtime of a program is set by system profile parameter

"rdisp/scheduler/prio_[high|normal|low]/max_runtime". The current setting is

1200 seconds. Once this limit has been

exceeded, the system attempts to interrupt the SQL statement or to

instruct the ABAP processor to terminate the program. It waits for a

maximum of 60 seconds. If the program is then still active, the work

process is restarted.

Last error logged in SAP kernel

--------------------------------------------------------------------------------------------------------------

Why this issue is happen ? How could I fix it ?

2. SQL error "SQL code: 863" occurred while accessing table "FAGLFLEXA".

- The code error extract at the SELECT command (FAGLFLEXA table) .

---------------------------------------------------------------------------------------------------------

Database error text: "SQL message: [ASE Error SQL863]Buffer resources in cache

default data cache, id 0 are unavailable. Please re-run this query or ask the

system administrator to re-configure buffer cache memory.

---------------------------------------------------------------------------------------------------------

Why this issue is happen ? How could I fix it ?

Regards.

Re: Buffer cache memory when execute multi thread

Former Member — Mon, 04 Jan 2016 12:13:30 GMT

Dear Tran,

Can you provide the query which is causing the error. Please use package size addition in your select statement to get rows in packets so that resources of database doesn't get exhausted.

Re: Buffer cache memory when execute multi thread

RaymondGiuseppi — Mon, 04 Jan 2016 12:43:03 GMT

Does the program use some parallel processing, in this case the created tasks are executed in interactive processes, and not background ones.

Hint: Did you try to increase size of data cache, also can be some 'lock' problem when multiple tasks/jobs trigger stats update (Ask ASE/Sybase admin -> 'Named cache < cache_name, cache_id > is configured too small for current demands')

Regards,

Raymond

Re: Buffer cache memory when execute multi thread

Former Member — Tue, 12 Jan 2016 03:50:34 GMT

Thanks for your suggest. My coding as below:

Header 1
SELECT BKPF~BUKRS BKPF~GJAHR BKPF~MONAT FAGLFLEXA~PRCTR FAGLFLEXA~ZZSBVACC " custom field FAGLFLEXA~RACCT FAGLFLEXA~RTCUR AS WAERS_WRBTR FAGLFLEXA~DRCRK SUM( FAGLFLEXA~TSL ) AS WRBTR SUM( FAGLFLEXA~HSL ) AS DMBTR INTO CORRESPONDING FIELDS OF TABLE LT_RAISE FROM FAGLFLEXA INNER JOIN BKPF ON BKPF~BUKRS = FAGLFLEXA~RBUKRS AND BKPF~GJAHR = FAGLFLEXA~RYEAR AND BKPF~BELNR = FAGLFLEXA~DOCNR WHERE BKPF~MONAT IN IT_MONAT AND BKPF~BUDAT IN LT_R_BUDAT AND BKPF~BUKRS IN IT_BUKRS AND FAGLFLEXA~PRCTR IN IT_PRCTR AND FAGLFLEXA~RACCT IN IT_RACCT AND FAGLFLEXA~RLDNR EQ IM_RLDNR AND FAGLFLEXA~ZZSBVACC NE SPACE AND FAGLFLEXA~ZZSBVACC IN IT_ZZSBVACC GROUP BY BKPF~BUKRS BKPF~GJAHR BKPF~MONAT FAGLFLEXA~PRCTR FAGLFLEXA~ZZSBVACC FAGLFLEXA~RACCT FAGLFLEXA~RTCUR FAGLFLEXA~DRCRK ORDER BY BKPF~BUKRS BKPF~GJAHR BKPF~MONAT FAGLFLEXA~PRCTR FAGLFLEXA~ZZSBVACC FAGLFLEXA~RACCT FAGLFLEXA~RTCUR FAGLFLEXA~DRCRK .

Header 1

SELECT

BKPF~BUKRS

BKPF~GJAHR

BKPF~MONAT

FAGLFLEXA~PRCTR

FAGLFLEXA~ZZSBVACC " custom field

FAGLFLEXA~RACCT

FAGLFLEXA~RTCUR AS WAERS_WRBTR

FAGLFLEXA~DRCRK

SUM( FAGLFLEXA~TSL ) AS WRBTR

SUM( FAGLFLEXA~HSL ) AS DMBTR

INTO CORRESPONDING FIELDS OF TABLE LT_RAISE

FROM FAGLFLEXA INNER JOIN BKPF

ON BKPF~BUKRS = FAGLFLEXA~RBUKRS

AND BKPF~GJAHR = FAGLFLEXA~RYEAR

AND BKPF~BELNR = FAGLFLEXA~DOCNR

WHERE BKPF~MONAT IN IT_MONAT

AND BKPF~BUDAT IN LT_R_BUDAT

AND BKPF~BUKRS IN IT_BUKRS

AND FAGLFLEXA~PRCTR IN IT_PRCTR

AND FAGLFLEXA~RACCT IN IT_RACCT

AND FAGLFLEXA~RLDNR EQ IM_RLDNR

AND FAGLFLEXA~ZZSBVACC NE SPACE

AND FAGLFLEXA~ZZSBVACC IN IT_ZZSBVACC

GROUP BY

BKPF~BUKRS

BKPF~GJAHR

BKPF~MONAT

FAGLFLEXA~PRCTR

FAGLFLEXA~ZZSBVACC

FAGLFLEXA~RACCT

FAGLFLEXA~RTCUR

FAGLFLEXA~DRCRK

ORDER BY

BKPF~BUKRS

BKPF~GJAHR

BKPF~MONAT

FAGLFLEXA~PRCTR

FAGLFLEXA~ZZSBVACC

FAGLFLEXA~RACCT

FAGLFLEXA~RTCUR

FAGLFLEXA~DRCRK .

So, i tried to get all entries to assign IT, and then using AT NEW/ AT END to total the value. however, the time to execute the RFC is low than before.

I use the inner join between FAGLFLEXA and BKPF due to ensuring data is accurate.

for each month, the FAGLFLEXA table contain over 5 mil records.

Have you any advice ?

Regards.

Re: Buffer cache memory when execute multi thread

Former Member — Tue, 12 Jan 2016 03:59:35 GMT

Yep,

I figured out the reason why I get the issue about limit time to execute. Bc the parallel processing must call the RFC function. And my RFC calls with CALL FUNCTION are processed in work processes of type DIALOG. The DIALOG limit on processing of one dialog step (by default 300 seconds, system profile parameter rdisp/max_wprun_time) applies to these RFC calls.

As you suggest, I asked Sybase admin to re-config the memory cache.

Regards.