Skip to Content

AFVC, AFKO,AUFK,AFVU & JSTO taking longer time in first run

Dear All,

I have created a Zreport in this i am fetching data from AFVC, AFKO,AUFK,AFVU & JSTO, JEST and some other tables. The issue is everyday morninng when i run first run it is taking arround 1 hr and some times time out the session. but one time it is executed all second runs will take maximum of 3 mins... I thought it is taking from user buffer, i have logged out system resarted and executed its very quick, i observed that it is only happening very first run after system idel for 3 hrs or more. if we run before this time it is taking ages to execute that... I am pasting the code where the issue is.

First Select

***************************************************************************************

SELECT a~arbid a~aufpl a~aplzl b~aufnr c~phas0 c~phas1INTO TABLE l_i_aufnrFROM afvc AS a INNER JOIN afko AS b ON b~aufpl = a~aufpl
INNER JOIN aufk AS c ON c~aufnr = b~aufnr

INNER JOIN afvu AS d on d~aufpl = a~aufplAND d~aplzl = a~aplzl

WHERE a~arbid IN r_objidAND a~steus IN s_steusAND a~werks IN s_vwerksAND d~usr10 = p_usr10AND d~usr08 IN s_erstr

AND ( c~phas0 = abap_true OR c~phas1 = abap_true )
%_HINTS ORACLE 'LEADING(T_00)'
ORACLE 'USE_NL(T_00 T_01)'
ORACLE 'INDEX(T_00 "AFKO~1")'
ORACLE 'INDEX(T_01 "AFVC~2")'.

second Select

****************************************************************************************

lt fields contain 30 fields..

SELECT (lt_fields) FROM ('VIAUFK_AFVC')

INTO CORRESPONDING FIELDS OF TABLE gt_viaufk_afvciflos WHERE aufnr IN s_aufnrAND auart IN s_auartAND tplnr IN s_tplnrAND equnr IN s_equnrAND qmnum IN s_qmnumAND iwerk IN s_iwerkAND priok IN s_priokAND ernam IN s_ernamAND erdat IN s_erdatAND aenam IN s_aenamAND aedat IN s_aedatAND plnnr IN s_plnnrAND vtweg IN s_vtwegAND bukrs IN s_bukrsAND stort IN s_stortAND eqfnr IN s_eqfnrAND abckz IN s_abckzAND aufpl IN s_aufplAND arbid IN r_objidAND v_werks IN s_vwerksAND steus IN s_steus.

3rd Select

****************************************************************************************

"getting user statusSELECT a~objnrc~txt04INTO TABLE ex_usrst_txtFROM jsto AS a
INNER JOIN jest AS b ON b~objnr = a~objnr AND b~inact = abap_false
INNER JOIN tj30t AS c ON c~stsma = a~stsma AND c~estat = b~stat AND spras = sy-languFOR ALL ENTRIES IN im_objnrWHERE a~objnr = im_objnr-objnr.
"getting system statusSELECT a~objnr
b~txt04INTO TABLE l_i_statFROM jest AS a
INNER JOIN tj02t AS b ON b~istat = a~stat AND b~spras = sy-languFOR ALL ENTRIES IN im_objnrWHERE a~objnr = im_objnr-objnrAND b~txt04 in im_vstatinAND a~inact = abap_false.

Can you please help me why this is behavinng like this...

Note: I have sat with BASIS team, they said, all table stats are uptodate. all table have enough good memory, all these tables are not storing any buffer in database level.

The issue is for very first time and for 1 user... if first executiuon completed it is very fast for every uesr in company...

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Mar 16, 2017 at 11:01 AM

    Have you thought about creating indexes and have you checked with your basis-team that it is not swapped between hot and cold store.

    To get that problem solved I would split the selects and have a look more exactly on the specific database (tables). Because a running report with about 3 minutes is still slow.. not that slow, but still slow. It sounds like swapping from cold to hot. Maybe the reason is something between running jobs at night and the select every morning.

    Hard to help you with more specific recommendations because of not knowing the exact hardware, entries and side-information.

    ~Florian

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 16, 2017 at 04:12 PM

    Thanks for your reply for work arround i am currently doing in the same way, amount data is 4million nearly.. 4 mins after above select statements i have 100's of validations there are other few select statements.

    I checked with basis they coudn't help, but they are saying it's system warmup ofr first time.

    If anyone face same issue pls update me. Thank you,

    Thank you,

    Add comment
    10|10000 characters needed characters exceeded