Skip to Content
avatar image
Former Member

Long time taken in queries with FOR ALL ENTRIES when driver table has many entries

Hi experts,

I am not a BASIS guy..I am a developer. I have been after my basis team to try and get my queries to run faster....But they have given up.

Problem :

SELECT ryear
docnr
rldnr
rbukrs
docln
segment
FROM faglflexa
INTO TABLE it_flexa
FOR ALL ENTRIES IN it_belnr
WHERE ryear EQ it_belnr-gjahr
AND docnr EQ it_belnr-belnr
AND rldnr = '0L'
AND rbukrs EQ '9000'
AND docln = '000001'.

The query is exactly as per the index. Yet it takes a huge time.

After going through numerous posts, came across one which stated that ratio of wait time to CPU time should be 2:1 ...in my case it is like this

CPU Time Wait time

69,390,000 1,510,996,471

I have seen this to be so in many other queries in ST04 - Shared Cursor Cache.

Is there any tuning problem in my system ?

We are on EHP 7 , Oracle 11.2.0.4.0 on HP UX.

Add comment
10|10000 characters needed characters exceeded

  • If they have "given up" what have they checked so far and what input did you get from them? Did you check the execution path and what did it say? SQL Trace is ST05.

    Have you tried checking if the program can be changed to avoid FAE altogether?

  • Get RSS Feed

3 Answers

  • Dec 05, 2016 at 08:51 AM

    Once you exclude the beginner's mistake of forgetting to check the internal table is not empty, then perform some search on OSS notes for performance of FOR ALL ENTRIES for ORACLE database, look for hints and options like max_blocking_factor (its default value is something like 5, some tools like BW switched it to 60 for performance with big volumn of data extraction)

    Regards,
    Raymond

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 05, 2016 at 01:03 AM

    Hello Anand: Maybe you should try it:
    Before 'for all entries', Please first check it_belnr whether or is empty. Like this:

    IF not it_belnr[] is INITIAL.

    SELECT ryear
    ......

    FOR ALL ENTRIES IN it_belnr
    .......

    ENDIF.

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 15, 2016 at 04:52 AM

    Anand,

    Also explore using "Open Cursor,fetch and Close Cursor " method while fetching data from the table FAGLFLEXA.

    K.Kiran.

    Add comment
    10|10000 characters needed characters exceeded