Full table scan when parsing Open SQL in oracle na...

former_member187007 · ‎03-04-2015

Hello Experts,

I would like to ask you why after upgrade to EHP7 SP 6, a select statemen that worked well in EHP7 SP 0, now is making a full table scan over the vbfa table when i see the ST05 sql trace, what triggers to very poor performance when reading this table.

Please welcome all your advices and comments to this simple SQL.

SELECT t1~vbeln t1~posnr t1~vgbel t1~vgpos t1~lfimg t1~ntgew INTO TABLE it_entregas

FROM lips AS t1

INNER JOIN vbfa AS t2

ON t1~vbeln = t2~vbeln

AND t1~posnr = t2~posnn

FOR ALL ENTRIES IN it_pedidos

WHERE t2~vbelv = it_pedidos-vbeln

AND t2~posnv = it_pedidos-posnr

AND t2~vbtyp_n = 'J'

AND t2~vbtyp_v = 'C'.

The internal table it_pedidos contains the sales order document number with the position number, to get the delivery numbers, and quantity and net weigth.

Regardless the way of get the information (i mean if there is another way to select the information), i would like to know why this sentence have to do a full table scan over vbfa table.

When i realized that this was happening i do a sql trace with the same scenario in a system before upgrade and compare it with this system (after upgrade) so what i found was that before the upgrade there was not full table scan.

Below is the execution plan in system after upgrade:

SQL Statement

SELECT

DISTINCT "T1"."VBELN","T1"."POSNR","T1"."VGBEL","T1"."VGPOS","T1"."LFIMG","T1"."NTGEW"

FROM

"LIPS" "T1" INNER JOIN "VBFA" "T2" ON "T1"."MANDT"="T2"."MANDT" AND "T1"."VBELN"="T2"."VBELN" AND

"T1"."POSNR"="T2"."POSNN"

WHERE

("T1"."MANDT"=:A0 AND "T2"."VBELV"=:A1 AND "T2"."POSNV"=:A2 AND "T2"."VBTYP_N"=:A3 AND

"T2"."VBTYP_V"=:A4) OR ("T1"."MANDT"=:A5 AND "T2"."VBELV"=:A6 AND "T2"."POSNV"=:A7 AND

"T2"."VBTYP_N"=:A8 AND "T2"."VBTYP_V"=:A9) OR ("T1"."MANDT"=:A10 AND "T2"."VBELV"=:A11 AND

"T2"."POSNV"=:A12 AND "T2"."VBTYP_N"=:A13 AND "T2"."VBTYP_V"=:A14)

Below is the execution plan in system before upgrade:

SQL Statement

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

SELECT

"T1"."VBELN","T1"."POSNR","T1"."VGBEL","T1"."VGPOS","T1"."LFIMG","T1"."NTGEW"

FROM

"LIPS" "T1" INNER JOIN "VBFA" "T2" ON "T2"."MANDT"=:A0 AND "T1"."VBELN"="T2"."VBELN" AND "T1"."POS

NR"="T2"."POSNN"

WHERE

("T1"."MANDT"=:A1 AND "T2"."VBELV"=:A2 AND "T2"."POSNV"=:A3 AND "T2"."VBTYP_N"=:A4 AND

"T2"."VBTYP_V"=:A5) OR ("T1"."MANDT"=:A6 AND "T2"."VBELV"=:A7 AND "T2"."POSNV"=:A8 AND

"T2"."VBTYP_N"=:A9 AND "T2"."VBTYP_V"=:A10) OR ("T1"."MANDT"=:A11 AND "T2"."VBELV"=:A12 AND

"T2"."POSNV"=:A13 AND "T2"."VBTYP_N"=:A14 AND "T2"."VBTYP_V"=:A15)

After this results i try spliting this SQL into 2 SQL, one for VBFA and another to LIPS, the problem was solved, now it is not using full table scan:

Below is the execution plan for this SQL:

SQL Statement

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

SELECT

DISTINCT "VBELN","POSNN"

FROM

"VBFA" "T1"

WHERE

("MANDT"=:A0 AND "VBELV"=:A1 AND "POSNV"=:A2 AND "VBTYP_N"=:A3 AND "VBTYP_V"=:A4) OR ("MANDT"=:A5

AND "VBELV"=:A6 AND "POSNV"=:A7 AND "VBTYP_N"=:A8 AND "VBTYP_V"=:A9) OR ("MANDT"=:A10 AND "VBELV"=

:A11 AND "POSNV"=:A12 AND "VBTYP_N"=:A13 AND "VBTYP_V"=:A14)

My question is why did i have to do this if technically is better use joins that using for all entries, besides why SAP changed the way it determines the sql if it was working well.

Here some components version after upgrade.

I apprecitate all your comments.

Best regards.

Jhon Jairo Teran

kiran_k8 · ‎03-05-2015

John,

Refer to SAP Note 185530 for suggestions on performance improvement while accessing VBFA table.

K.Kiran.

former_member187007 · ‎03-05-2015

Hello Kiran, thanks you for your answer.

I already checked that note, but i read carefully and my sql is according to the SAP note you mentioned, coincidentally is the same case that the note puts as an example (delivery for order).

4. Other accesses in SD:

Incorrect: SELECT vbelv FROM vbfa WHERE vbeln ...

In table VBFA only the preceeding document is used to search for the subsequent document (for example, delivery for order). Searching the other way makes no sense with this table since the preceding documents (for example, order for delivery) are stored directly in the document tables. Thus reading in table VBFA is a one-way street.
Correct: SELECT vgbel FROM lips WHERE vbeln = ...; or SELECT vgbel FROM vbrp WHERE vbeln = ...; or
SELECT aubel FROM vbrp WHERE vbeln = ...

Best regards.

Jhon Jairo Teran.

Former Member · ‎03-05-2015

You might have a look at what secondary indexes were available on VBFA before and after the upgrade.

I suspect that the issue might have something to do with the JOIN conditions not using the first 2 index fields on VBFA.

rob

former_member187007 · ‎03-10-2015

Hello Rob.

Thanks for your feedback. I checked the indexes and are the same before and after the upgrade.

Could you please explain me a little bit more what do you mean about JOIN condition is not using the 2 index fields.

I forgot to say that this table was fully reconstructed in indexes and statistics in an effort to try to repair this issue, but neither worked out.

Thanks in advanced.

Jhon Jairo.

Full table scan when parsing Open SQL in oracle native SQL