cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

7.40 changes the alias and the access of the column "CLIENT"

KayKa
Active Participant

on ‎11-07-2014 8:32 AM

0 Kudos

Hi all,

in the last days i faced the Problem that a join of two of our own tables ran longer in SAPM than in production.
All statistics and reorgs are made. Parameters are the same. But in SAPM i got a full-table-scan instead of index access via BNDTA_AGNR which is selectiv enough.

That is the join part of our program:



         INTO CORRESPONDING FIELDS OF TABLE e_tab_nko_npo


         FROM       /gkv/cd01_bn_npo AS a


         INNER JOIN /gkv/cd01_bn_nko AS b


         ON         a~bn_lfdnr     = b~bn_lfdnr


         FOR ALL ENTRIES IN i_tab_schaetz


         WHERE      b~obtyp        = i_tab_schaetz-obtyp AND


        b~bndta_agnr   = i_tab_schaetz-obgrf

and that is the part of the SQL-statement that ran slow:



FROM


       "/GKV/CD01_BN_NPO" "A" INNER JOIN


       "/GKV/CD01_BN_NKO" "B"


          ON "A"."MANDT"="B"."MANDT"  -- that's new


         AND "A"."BN_LFDNR"="B"."BN_LFDNR"


WHERE "A"."MANDT"=:A0


   AND "B"."OBTYP"=:A1


   AND "B"."BNDTA_AGNR"=:A2

And that the same part of the SQL-statement from production that ran fast



FROM


       "/GKV/CD01_BN_NPO" T_00 INNER JOIN


       "/GKV/CD01_BN_NKO" T_01


          ON T_01."MANDT"=:A0


         AND T_00."BN_LFDNR"=T_01."BN_LFDNR"


WHERE T_00."MANDT"=:A1


   AND T_01."OBTYP"=:A2


   AND T_01."BNDTA_AGNR"=:A3

The point is that production is on 7.31 but the newer SAP is on 7.40.

I search on "alias t_00" and found 2029981 - DBSL: Replacement of invalid aliases in Oracle
hints

Here we can read that the naming of the alias has changed.

And it seems that implementation of the access to CLIENT or MANDT has changed too.

Is there a chance to get back the old behaviour ?

best regards
Kay

Show replies
Show replies
Answer

Accepted Solutions (0)

Answers (1)

Answers (1)

stefan_koehler
Active Contributor
‎11-07-2014 9:32 AM
0 Kudos

Hi Kay,

SAPnote #2029981 does not apply in your case as you are not using any Oracle hint with aliasing in your code.

It seems like SAP introduced a (new) client awareness in its DBSL as the join condition reflects that. I would guess that both tables (/gkv/cd01_bn_npo and /gkv/cd01_bn_nko) are client specific. This makes sense from a logical/application point of view, but can lead to execution plan changes, especially if more or different join conditions are applied (instead of restrictions).

>> But in SAPM i got a full-table-scan instead of index access via BNDTA_AGNR which is selectiv enough.

Only a FTS, that changed? Can you please post both execution plans in DBMS_XPLAN output format.

Regards

Stefan

Show replies
Show replies
KayKa
Active Participant
‎11-07-2014 10:02 AM
0 Kudos

Hallo Stefan,

i put together some outputs from explain.


It's a little bit difficult to compare. In production i found only statements without only 1 OR in the where-clause.

In SAPM appeared a lot of statments with 4 OR-clauses. That depends on the for all entries.

Yesterday in SAPM ran a Batch that produces  these Statements. This Report is not present in production, but this report ran faster in past.

But if i put the Statement with 4 ORs into tx sdbe in production i get the same plan as in SAPM.


And i can change the statement in 2 ways to get a concatenate index access in SAPM.

1. change A.MANDT = :A0    to  B.MANDT = :A0 in the where clause

or

2. change ON "A"."MANDT"="B"."MANDT"  into  ON "B"."MANDT"=:A0  in the ON clause

regards
Kay

2014_11_06_MR2_sdbe_CD01_NKO_mit_A_MANDT_aber_B_MANDT_gleich_A0.txt.zip
2014_11_06_MR2_sdbe_CD01_NKO_mit_A_MANDT.original_4x_OR.txt.zip
2014_11_06_RPP_st04old_CD01_NKO_731.original_2x_OR.txt.zip
KayKa
Active Participant
‎11-07-2014 10:05 AM
0 Kudos


and some more files

2014_11_06_RPP_sdbe_CD01_NKO_mit_A_MANDT.txt.zip
2014_11_06_MR2_sdbe_CD01_NKO_mit_B_MANDT.txt.zip
2014_11_06_MR2_CD01_F05_SCH_NKO_SELECT.abap.zip
KayKa
Active Participant
‎11-07-2014 12:12 PM
0 Kudos

Hallo Stefan,

i opened a service request to BC-DB-ORA-SYS

regards
Kay

stefan_koehler
Active Contributor
‎11-07-2014 12:20 PM
0 Kudos

Hi Kay,

thanks for the output. I just used the following files for comparison as the amount of OR concatenations does not really matter in this case.

  • 2014_11_06_MR2_sdbe_CD01_NKO_mit_A_MANDT_aber_B_MANDT_gleich_A0.txt
  • 2014_11_06_MR2_sdbe_CD01_NKO_mit_A_MANDT.original_4x_OR.txt

As assumed this issue is introduced by the additional join condition A.MANDT = B.MANDT and the removed restriction on B.MANDT = :A0. You can spot this, if you look closely at the predicate section of the two execution plans. This is how Oracle works or better said how join conditions and filter predicates are handled and can not be considered as an Oracle bug. It is based on how the SQL is written (or better said re-written by SAP DBSL).

2014_11_06_MR2_sdbe_CD01_NKO_mit_A_MANDT_aber_B_MANDT_gleich_A0.txt



...


  "/GKV/CD01_BN_NPO" "A" INNER JOIN "/GKV/CD01_BN_NKO" "B"  


  ON "B"."MANDT" = :A0  


  AND "A"."BN_LFDNR"="B"."BN_LFDNR"


WHERE


  ("A"."MANDT"=:A0 AND "B"."OBTYP"=:A1 AND "B"."BNDTA_AGNR"=:A2  AND "B"."OSTWEST"=:A3 AND  "B"."FAELLTYP"=:A4 AND   


  "B"."BNDTA_ABRZU"<=:A5 AND "B"."BNDTA_ABRZO">=:A6) 


  OR 


  ("A"."MANDT"=:A7 AND "B"."OBTYP"=:A8 AND "B"."BNDTA_AGNR"=:A9 AND "B"."OSTWEST"=:A10  AND "B"."FAELLTYP"=:A11 AND


  "B"."BNDTA_ABRZU"<=:A12 AND "B"."BNDTA_ABRZO">=:A13)





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


| Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU)| Time     |


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


|   0 | SELECT STATEMENT               |                    |     2 |   308 |     4   (0)| 00:00:01 |


|   1 |  CONCATENATION                 |                    |       |       |            |          |


|   2 |   NESTED LOOPS                 |                    |       |       |            |          |


|   3 |    NESTED LOOPS                |                    |     1 |   154 |     2   (0)| 00:00:01 |


|*  4 |     TABLE ACCESS BY INDEX ROWID| /GKV/CD01_BN_NKO   |     1 |   127 |     1   (0)| 00:00:01 |


|*  5 |      INDEX RANGE SCAN          | /GKV/CD01_BN_NKOOB |     1 |       |     1   (0)| 00:00:01 |


|*  6 |     INDEX RANGE SCAN           | /GKV/CD01_BN_NPO~0 |     6 |       |     1   (0)| 00:00:01 |


|   7 |    TABLE ACCESS BY INDEX ROWID | /GKV/CD01_BN_NPO   |     6 |   162 |     1   (0)| 00:00:01 |


|   8 |   NESTED LOOPS                 |                    |       |       |            |          |


|   9 |    NESTED LOOPS                |                    |     1 |   154 |     2   (0)| 00:00:01 |


|* 10 |     TABLE ACCESS BY INDEX ROWID| /GKV/CD01_BN_NKO   |     1 |   127 |     1   (0)| 00:00:01 |


|* 11 |      INDEX RANGE SCAN          | /GKV/CD01_BN_NKOOB |     1 |       |     1   (0)| 00:00:01 |


|* 12 |     INDEX RANGE SCAN           | /GKV/CD01_BN_NPO~0 |     6 |       |     1   (0)| 00:00:01 |


|* 13 |    TABLE ACCESS BY INDEX ROWID | /GKV/CD01_BN_NPO   |     6 |   162 |     1   (0)| 00:00:01 |


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





...


   4 - filter("B"."FAELLTYP"=:A11 AND "B"."OBTYP"=:A8 AND "B"."OSTWEST"=:A10)


   5 - access("B"."MANDT"=:A0 AND "B"."BNDTA_AGNR"=:A9 AND "B"."BNDTA_ABRZO">=:A13 AND


              "B"."BNDTA_ABRZU"<=:A12)


       filter("B"."BNDTA_ABRZU"<=:A12)

2014_11_06_MR2_sdbe_CD01_NKO_mit_A_MANDT.original_4x_OR.txt



... 


   "/GKV/CD01_BN_NPO" "A" INNER JOIN "/GKV/CD01_BN_NKO" "B"  


   ON "A"."MANDT"="B"."MANDT"  


   AND "A"."BN_LFDNR"="B"."BN_LFDNR"


WHERE


  ("A"."MANDT"=:A0 AND "B"."OBTYP"=:A1 AND "B"."BNDTA_AGNR"=:A2  AND "B"."OSTWEST"=:A3 AND  "B"."FAELLTYP"=:A4 AND 


  "B"."BNDTA_ABRZU"<=:A5 AND "B"."BNDTA_ABRZO">=:A6) 





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


| Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |


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


|   0 | SELECT STATEMENT             |                    |     1 |   154 |   116K  (3)| 00:11:59 |


|   1 |  NESTED LOOPS                |                    |       |       |            |          |


|   2 |   NESTED LOOPS               |                    |     1 |   154 |   116K  (3)| 00:11:59 |


|*  3 |    TABLE ACCESS FULL         | /GKV/CD01_BN_NKO   |     1 |   127 |   116K  (3)| 00:11:59 |


|*  4 |    INDEX RANGE SCAN          | /GKV/CD01_BN_NPO~0 |     6 |       |     1   (0)| 00:00:01 |


|*  5 |   TABLE ACCESS BY INDEX ROWID| /GKV/CD01_BN_NPO   |     1 |    27 |     1   (0)| 00:00:01 |


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





...


  3 - filter( "B"."BNDTA_AGNR"=:A30 AND "B"."BNDTA_ABRZO">=:A34 AND


              "B"."BNDTA_ABRZU"<=:A33 AND "B"."FAELLTYP"=:A32 AND "B"."OBTYP"=:A29 AND


              "B"."OSTWEST"=:A31 OR "B"."BNDTA_AGNR"=:A23 AND "B"."BNDTA_ABRZO">=:A27 AND


              "B"."BNDTA_ABRZU"<=:A26 AND "B"."FAELLTYP"=:A25 AND "B"."OBTYP"=:A22 AND


              "B"."OSTWEST"=:A24 OR "B"."BNDTA_AGNR"=:A16 AND "B"."BNDTA_ABRZO">=:A20 AND


              "B"."BNDTA_ABRZU"<=:A19 AND "B"."FAELLTYP"=:A18 AND "B"."OBTYP"=:A15 AND


              "B"."OSTWEST"=:A17 OR "B"."BNDTA_AGNR"=:A9 AND "B"."BNDTA_ABRZO">=:A13 AND


              "B"."BNDTA_ABRZU"<=:A12 AND "B"."FAELLTYP"=:A11 AND "B"."OBTYP"=:A8 AND "B"."OSTWEST"=:A10


              OR "B"."BNDTA_AGNR"=:A2 AND "B"."BNDTA_ABRZO">=:A6 AND "B"."BNDTA_ABRZU"<=:A5 AND


              "B"."FAELLTYP"=:A4 AND "B"."OBTYP"=:A1 AND "B"."OSTWEST"=:A3)


   4 - access("A"."MANDT"="B"."MANDT" AND "A"."BN_LFDNR"="B"."BN_LFDNR")

So you can see that there is no filter predicate on MANDT in case of "2014_11_06_MR2_sdbe_CD01_NKO_mit_A_MANDT.original_4x_OR.txt" for table B, but a join condition on table A (index range scan /GKV/CD01_BN_NPO~0). So you might ask why Oracle does not choose index "/GKV/CD01_BN_NKOOB" anyway, even if MANDT is not provided as an access predicate?

Oracle can do a skip scan or a index (fast) full scan in this case (based on the previous outlined access predicate ""B"."MANDT"=:A0 AND "B"."BNDTA_AGNR"=:A9 AND "B"."BNDTA_ABRZO">=:A13 AND"B"."BNDTA_ABRZU"<=:A12", but the costs are higher than 116K in this case as the table needs to be accessed anyway. I guess you will also see that higher costs in case of hinting the second execution plan.

It depends on your data pattern (especially amount of distinct MANDT values in table B), if the real amount of work is higher, but the optimizer can not be aware of that at this point (especially based on the CBO limitations by the SAP Oracle database settings).

You can try to add some additional (and duplicate) predicate on B.MANDT in your (custom) ABAP code (OR list) and check if the SAP DBSL does not filter this out. The index "/GKV/CD01_BN_NKOOB" will be used as well in the second case, if there will be an additional filter predicate on B.MANDT.

Regards

Stefan

stefan_koehler
Active Contributor
‎11-07-2014 12:26 PM
0 Kudos

Hi Kay,

good luck

The issue is introduced by BC-DB-DBI (= DBSL - explanation see above) or maybe defined as "works-as-designed" with 7.40 DBSL, but then the code needs to be rewritten.

Regards

Stefan

KayKa
Active Participant
‎11-07-2014 1:09 PM
0 Kudos

Danke Stefan,

I'm waiting for the first DB2 systems running with 7.40. We see what happens then...

regards

Kay

KayKa
Active Participant
‎11-12-2014 7:11 AM
0 Kudos

Hi Stephan,

we don't need luck, we need a lot of time ;-((

Since friday nothing happened in my OSS call...

regards

Kay

Ask a Question