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

[Former Member]

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.

[former_member195908]

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.

[raymond_giuseppi]

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

[Jelena]

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?

[kiran_k8]

Anand,

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

K.Kiran.