Skip to Content
0

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

Dec 03, 2016 at 05:24 AM

143

avatar image

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.

10 |10000 characters needed characters left 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?

1
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Raymond Giuseppi
Dec 05, 2016 at 08:51 AM
2

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

Share
10 |10000 characters needed characters left characters exceeded
Spring Long Dec 05, 2016 at 01:03 AM
1

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.

Share
10 |10000 characters needed characters left characters exceeded
Kiran K Dec 15, 2016 at 04:52 AM
1

Anand,

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

K.Kiran.

Share
10 |10000 characters needed characters left characters exceeded