topic Re: Select Join Performance in Application Development and Automation Discussions

Select Join Performance

Former Member — Tue, 08 Feb 2011 16:41:36 GMT

Hi,

I have a performance problem with this select statement: Takes 30 to 50 secs.

    SELECT k~ebeln  k~lifnr  k~ekgrp  k~ekorg  k~bukrs  k~bsart
           k~bstyp  k~bedat  k~reswk  k~spras  k~adrnr
           k~waers p~ebelp  p~pstyp  p~werks  p~matnr  p~matkl  p~txz01
           p~idnlf  p~labnr
           p~elikz  p~erekz  p~knttp  p~vrtkz  p~meins  p~mfrpn
            p~effwr p~netpr p~peinh p~bprme
           t~etenr  t~eindt  t~menge  t~wemng  t~slfdt  t~mahnz
           INTO CORRESPONDING FIELDS OF TABLE g_i_tab1
           FROM  ekko    AS k
           JOIN  ekpo    AS p
           ON    p~ebeln =  k~ebeln
           JOIN  eket    AS t
           ON    t~ebeln =  k~ebeln  AND
                 t~ebelp =  p~ebelp
           WHERE k~lifnr IN s_lifnr  AND
                 k~ekorg IN s_ekorg  AND
                 k~ekgrp IN s_ekgrp  AND
                 k~bedat IN s_bedat  AND
                 k~ebeln IN s_ebeln  AND
                 k~bstyp IN s_bstyp  AND
                 k~bsart IN s_bsart  AND
                 k~loekz =  space    AND
                 p~matnr IN s_matnr  AND
                 p~werks IN s_werks  AND
                 p~bstyp IN s_bstyp  AND
                 p~loekz =  space    AND
                 p~elikz IN r_elikz  AND
                 p~matkl IN s_matkl  AND
                 p~pstyp IN s_pstyp  AND
                 p~knttp IN s_knttp  AND
                 p~kanba <> 'Y'      AND
                 t~eindt IN s_eindt  AND
                 t~menge >  0        AND
                 t~menge >  t~wemng.

The SQL-Trace (ST05) shows two nested loops, the selection starts always with EKKO

I wonder whether I can force the System to start the selection with Table EKPO, using a certain Index?

(When I count the number of entries with SE16N it takes only 1 sec and I should have the Unique Index to read the other two tables.)

What can I do?

Best regards

Jens

Re: Select Join Performance

Former Member — Tue, 08 Feb 2011 17:01:26 GMT

Can you indicate your mandatory selection parameters? What is the index that it is currently hitting?

If Vendor or BEDAT is filled, I would assume that your query always go after the respective indexes... If you can always ensure that Vendor, EKORG, EKGRP and BEDAT is populated, your query should be faster or BSTYP and BEDAT would give you good results?

Re: Select Join Performance

volker_borowski2 — Tue, 08 Feb 2011 18:36:30 GMT

Hi Jens,

Hints are DB specific. What is your DB type ?

For Oracle, I can give some suggestions:

You need to do an SQL trace to catch the exact statement.

Put the statement in ST05 and use the "explain with hint" function to check the functionality.

Try to use as less as possible hints in a statement

For a join, you might want to use "leading(table-alias)" first to check if this is sufficiant.

That might look like "leading(k)" in your case.

If that is not good enough, you can give more hints like


leading(k) index(k,"EKKO~Z01")

suggest a nested loop of table EKPO utilizing the PK:


leading(k) index(k,"EKKO~Z01") use_nl(k,p) index(p,"EKPO~0")

For more hints use google to find what is possible.

To bring it in Open SQL, you need to append

HINTS ORACLE "Hint-Text"

to your ABAP statement. Validate in SQL Trace, that it is executed correctly.

Hope this helps

Volker

Re: Select Join Performance

Former Member — Tue, 08 Feb 2011 20:07:25 GMT


 WHERE k~lifnr IN s_lifnr  AND
                 k~ekorg IN s_ekorg  AND
                 k~ekgrp IN s_ekgrp  AND
                 k~bedat IN s_bedat  AND
                 k~ebeln IN s_ebeln  AND
                 k~bstyp IN s_bstyp  AND
                 k~bsart IN s_bsart  AND
                 k~loekz =  space    AND
                 p~matnr IN s_matnr  AND
                 p~werks IN s_werks  AND
                 p~bstyp IN s_bstyp  AND
                 p~loekz =  space    AND
                 p~elikz IN r_elikz  AND
                 p~matkl IN s_matkl  AND
                 p~pstyp IN s_pstyp  AND
                 p~knttp IN s_knttp  AND
                 p~kanba  'Y'      AND
                 t~eindt IN s_eindt  AND
                 t~menge >  0        AND
                 t~menge >  t~wemng.

This is never understood in this forum, a SELECT like yours is dynamic ... the ranges are dynamic and usually only a few are filled.

All conditions for k = ekko can be empty or all conditions for p = ekpo can be empty. So the starting table is not fixed.

Of course any other combination of conditions is possible. And not all combinations can be supported.

Usually the combinations, which make sense, are supported by indexes.

So your question should give the DB-statement which shows only the non-empty ranges!

Hints can not help here!

Siegfried

Re: Select Join Performance

HermannGahm — Tue, 08 Feb 2011 21:03:06 GMT

Hi Jens,


> What can I do?

as the others said we need the data from ST05. How does your statement look like in the DB.

Additionally the execution plan and indexes might help as well.

On some databases (e.g. ORACLE and DB6) you can control very detailed how the statement

should be executed. Other databases do not offer such a good control but more generic hints.

On the DB6 plattform we have conditional hints (check out SAP note 1484907). Having said that

i disagree with Siegfried that hints will not help here in general. With the conditional hints on DB6

it would be ok. I hope the conditional hints will follow for other DB plattforms. Without them you

would have to do this "conditional thing" yourself in ABAP which is dynamically possible as well

since a hint is just a string.

However, hinting will help you only on some plattforms. And then only for specific cases and

you have to be very precise and make sure you know what you do.

Gernerally you should find the root cause why your query is slow? Is the table with the smallest

result set processed as the first tabel? If not, why not? If better execution plans are available but

not picked up, get in contact with a DBA. They usually know the possible workarounds. Hinting

is one of them but there are others... .

If you want more help from this forum please post more details... execution plan, indexes, ... .

Kind regards,

Hermann

Re: Select Join Performance

Former Member — Wed, 09 Feb 2011 09:21:51 GMT

Hi,

thanks for your ansers so far. Hope this aditional information will help to find a good solution:

Mandatory are only the following values:

EKPO-WERKS EKPO-ELIKZ EKET-EINDT

This values are fixed in the select statement:

EKPO-LOEKZ EKPO-KANBA EKET-MENGE EKET-WEMNG

Usually the selection is executed at lease with some of these valuels:

EKKO-BSTYP EKKO-LIFNR EKKO-EKGRP EKKO-EKORG EKPO-MATNR

Database: ORACLE 10.2.0.4.0

ST05 looks like this: (with EKKO-LIFNR -the mainly used selection)

SQL Statement

SELECT

T_00 . "EBELN" , T_00 . "LIFNR" , T_00 . "EKGRP" , T_00 . "EKORG" , T_00 . "BUKRS" ,

T_00 . "BSART" , T_00 . "BSTYP" , T_00 . "BEDAT" , T_00 . "RESWK" , T_00 . "SPRAS" ,

T_00 . "ADRNR" , T_00 . "WAERS" , T_01 . "EBELP" , T_01 . "PSTYP" , T_01 . "WERKS" ,

T_01 . "MATNR" , T_01 . "MATKL" , T_01 . "TXZ01" , T_01 . "IDNLF" , T_01 . "LABNR" ,

T_01 . "ELIKZ" , T_01 . "EREKZ" , T_01 . "KNTTP" , T_01 . "VRTKZ" , T_01 . "MEINS" ,

T_01 . "MFRPN" , T_01 . "EFFWR" , T_01 . "NETPR" , T_01 . "PEINH" , T_01 . "BPRME" ,

T_02 . "ETENR" , T_02 . "EINDT" , T_02 . "MENGE" , T_02 . "WEMNG" , T_02 . "SLFDT" ,

T_02 . "MAHNZ"

FROM

"EKKO" T_00 , "EKPO" T_01 , "EKET" T_02

WHERE

( T_01 . "MANDT" = :A0 AND T_01 . "EBELN" = T_00 . "EBELN" ) AND ( T_02 . "MANDT" = :A1 AND T_02

. "EBELN" = T_00 . "EBELN" AND T_02 . "EBELP" = T_01 . "EBELP" ) AND T_00 . "MANDT" = :A2 AND

T_00 . "LIFNR" = :A3 AND T_00 . "EKORG" = :A4 AND T_00 . "BSTYP" IN ( :A5 , :A6 ) AND T_00 .

"LOEKZ" = :A7 AND T_01 . "WERKS" = :A8 AND T_01 . "BSTYP" IN ( :A9 , :A10 ) AND T_01 . "LOEKZ" =

:A11 AND T_01 . "ELIKZ" = :A12 AND T_01 . "KANBA" <> :A13 AND T_02 . "EINDT" BETWEEN :A14 AND

:A15 AND T_02 . "MENGE" > :A16 AND T_02 . "MENGE" > T_02 . "WEMNG"

Execution Plan

SELECT STATEMENT ( Estimated Costs = 19 , Estimated #Rows = 1 )

9 FILTER

8 NESTED LOOPS

( Estim. Costs = 19 , Estim. #Rows = 1 )

Estim. CPU-Costs = 171.394 Estim. IO-Costs = 19

5 NESTED LOOPS

( Estim. Costs = 15 , Estim. #Rows = 9 )

Estim. CPU-Costs = 135.184 Estim. IO-Costs = 15

2 TABLE ACCESS BY INDEX ROWID EKKO

( Estim. Costs = 8 , Estim. #Rows = 9 )

Estim. CPU-Costs = 71.777 Estim. IO-Costs = 8

1 INDEX RANGE SCAN EKKO~1

( Estim. Costs = 1 , Estim. #Rows = 45 )

Search Columns: 3

Estim. CPU-Costs = 6.113 Estim. IO-Costs = 1

4 TABLE ACCESS BY INDEX ROWID EKET

( Estim. Costs = 1 , Estim. #Rows = 1 )

Estim. CPU-Costs = 7.045 Estim. IO-Costs = 1

3 INDEX RANGE SCAN EKET~0

Search Columns: 2

Estim. CPU-Costs = 3.339 Estim. IO-Costs = 0

7 TABLE ACCESS BY INDEX ROWID EKPO

Estim. CPU-Costs = 4.023 Estim. IO-Costs = 0

6 INDEX UNIQUE SCAN EKPO~0

Search Columns: 3

Estim. CPU-Costs = 1.804 Estim. IO-Costs = 0

Used index: EKKO~1

MANDT

LIFNR

EKORG

EKGRP

BEDAT

Best regards

Jens

Edited by: jeman69 on Feb 9, 2011 10:22 AM

Re: Select Join Performance

HermannGahm — Wed, 09 Feb 2011 13:35:35 GMT

Hi Jens,

and this selection

WHERE

( T_01 . "MANDT" = :A0 AND T_01 . "EBELN" = T_00 . "EBELN" )

AND ( T_02 . "MANDT" = :A1 AND T_02 . "EBELN" = T_00 . "EBELN" AND T_02 . "EBELP" = T_01 . "EBELP" )

AND T_00 . "MANDT" = :A2

AND T_00 . "LIFNR" = :A3

AND T_00 . "EKORG" = :A4

AND T_00 . "BSTYP" IN ( :A5 , :A6 )

AND T_00 ."LOEKZ" = :A7

AND T_01 . "WERKS" = :A8

AND T_01 . "BSTYP" IN ( :A9 , :A10 )

AND T_01 . "LOEKZ" = :A11

AND T_01 . "ELIKZ" = :A12

AND T_01 . "KANBA" :A13

AND T_02 . "EINDT" BETWEEN :A14 AND :A15

AND T_02 . "MENGE" > :A16

AND T_02 . "MENGE" > T_02 . "WEMNG"

runs 50 seconds?

at first glance the execution plan (starting with lifnr) looks nice for me.

Have you checked if other options? it does not look like ekpo (T_01) would

be a better choice as a starting point in this case.

You can check the result sets for all 3 tables in SE16 with the given selection

criteria. The optimizer should start on the smallest result set. This should be supported

by an index. If LIFNR provides the smalles result set and we have index suport here

the entry looks fine for me.

Kind regards,

Hermann

Re: Select Join Performance

Former Member — Wed, 09 Feb 2011 16:30:33 GMT

Hi Hermann,

Yes this runs depending on the Number of Purchase Orders and Sheduling Agreements too long.

LIFNR with 12.000 EKKO-Entries: 90s

LIFNR with 4.500 EKKO-Entries: 20s

If I count the number of Entries of EKKO und EKPO using the selection criteria, I get the following runtimes in SE16N:

EKKO with 4500 Entries: 8s (number differs from 0 to 12.000 depending on LIFNR)

EKPO with 6200 Entries: 4s (number is constant per plant)

Total number of entries in the System:

EKKO: 450.000

EKPO: 1.100.000

Best Regards

Jens

Re: Select Join Performance

volker_borowski2 — Wed, 09 Feb 2011 19:16:41 GMT

Hi,

how does the plan change when you hint it with "leading(T_01)" in ST05 as your first suggestion was?

Are the stats up to date? The initial guess for the "~1" index entry is 35 rows,

which appears quite diffrent to the 4500 you mention as the reality.

How many rows do you get for a typical

AND T_02 . "EINDT" BETWEEN :A14 AND :A15

on table EKET in SE16, and how long does that take?

Volker

Re: Select Join Performance

HermannGahm — Thu, 10 Feb 2011 06:25:26 GMT

Hi Jens,

> Yes this runs depending on the Number of Purchase Orders and Sheduling Agreements too long.

> LIFNR with 12.000 EKKO-Entries: 90s

> LIFNR with 4.500 EKKO-Entries: 20s

uneqal data distribution

> If I count the number of Entries of EKKO und EKPO using the selection criteria, I get the following runtimes in SE16N:

> EKKO with 4500 Entries: 8s (number differs from 0 to 12.000 depending on LIFNR)

> EKPO with 6200 Entries: 4s (number is constant per plant)

combined with a join.

Challenging. For some LIFNRs the EKKO is the best entry (for those that have less rows than the other table result sets) and

for some EKKO is a suboptimal entry (for those that have more rows than the other table result sets). Theoretically histograms should help here but in this scenario i don't think it is a good option (depends of course).

You can of course choose EKPO (or maybe EKET, see Volkers questions) as the default entry as a tradeoff. If they are rather constant it would be an good option for LIFNRs with a big result set. Only for LIFNRs with small result sets there would be better options (starting with EKKO available). You can make some tests with hints and LIFNRs with big and small volumes choosing EKKO or EKPO as the starting point and compare the run times.

Kind regards,

Hermann

Re: Select Join Performance

Former Member — Thu, 10 Feb 2011 09:58:03 GMT

Hi Volker,

Last statistiks date is 27.11.2010. (Should be OK)

Hint in ST05 seams to work:

SQL Statement

SELECT

/*+

leading(T_01)

T_00 . "EBELN" , T_00 . "LIFNR" , T_00 . "EKGRP" , T_00 . "EKORG" , T_00 . "BUKRS" ,

T_00 . "BSART" , T_00 . "BSTYP" , T_00 . "BEDAT" , T_00 . "RESWK" , T_00 . "SPRAS" ,

T_00 . "ADRNR" , T_00 . "WAERS" , T_01 . "EBELP" , T_01 . "PSTYP" , T_01 . "WERKS" ,

T_01 . "MATNR" , T_01 . "MATKL" , T_01 . "TXZ01" , T_01 . "IDNLF" , T_01 . "LABNR" ,

T_01 . "ELIKZ" , T_01 . "EREKZ" , T_01 . "KNTTP" , T_01 . "VRTKZ" , T_01 . "MEINS" ,

T_01 . "MFRPN" , T_01 . "EFFWR" , T_01 . "NETPR" , T_01 . "PEINH" , T_01 . "BPRME" ,

T_02 . "ETENR" , T_02 . "EINDT" , T_02 . "MENGE" , T_02 . "WEMNG" , T_02 . "SLFDT" ,

T_02 . "MAHNZ"

FROM

"EKKO" T_00 , "EKPO" T_01 , "EKET" T_02

WHERE

( T_01 . "MANDT" = :A0 AND T_01 . "EBELN" = T_00 . "EBELN" ) AND ( T_02 . "MANDT" = :A1 AND T_02

. "EBELN" = T_00 . "EBELN" AND T_02 . "EBELP" = T_01 . "EBELP" ) AND T_00 . "MANDT" = :A2 AND

T_00 . "LIFNR" = :A3 AND T_00 . "EKORG" = :A4 AND T_00 . "BSTYP" IN ( :A5 , :A6 ) AND T_00 .

"LOEKZ" = :A7 AND T_01 . "WERKS" = :A8 AND T_01 . "BSTYP" IN ( :A9 , :A10 ) AND T_01 . "LOEKZ" =

:A11 AND T_01 . "ELIKZ" = :A12 AND T_01 . "KANBA" <> :A13 AND T_02 . "EINDT" BETWEEN :A14 AND

:A15 AND T_02 . "MENGE" > :A16 AND T_02 . "MENGE" > T_02 . "WEMNG"

Execution Plan

SELECT STATEMENT ( Estimated Costs = 4.445 , Estimated #Rows = 1 )

9 FILTER

8 TABLE ACCESS BY INDEX ROWID EKET

( Estim. Costs = 1 , Estim. #Rows = 1 )

Estim. CPU-Costs = 4.591 Estim. IO-Costs = 1

7 NESTED LOOPS

( Estim. Costs = 4.444 , Estim. #Rows = 1 )

Estim. CPU-Costs = 88.575.088 Estim. IO-Costs = 4.432

5 HASH JOIN

( Estim. Costs = 4.439 , Estim. #Rows = 9 )

Estim. CPU-Costs = 88.533.765 Estim. IO-Costs = 4.427

2 TABLE ACCESS BY INDEX ROWID EKPO

( Estim. Costs = 4.430 , Estim. #Rows = 4.672 )

Estim. CPU-Costs = 83.959.017 Estim. IO-Costs = 4.419

1 INDEX SKIP SCAN EKPO~1

( Estim. Costs = 1.983 , Estim. #Rows = 14.015 )

Search Columns: 4

Estim. CPU-Costs = 55.287.075 Estim. IO-Costs = 1.975

4 TABLE ACCESS BY INDEX ROWID EKKO

( Estim. Costs = 8 , Estim. #Rows = 9 )

Estim. CPU-Costs = 71.777 Estim. IO-Costs = 8

3 INDEX RANGE SCAN EKKO~1

( Estim. Costs = 1 , Estim. #Rows = 45 )

Search Columns: 3

Estim. CPU-Costs = 6.113 Estim. IO-Costs = 1

6 INDEX RANGE SCAN EKET~0

Search Columns: 3

Estim. CPU-Costs = 3.059 Estim. IO-Costs = 0

Although the estimates cost are very high I would like to test it.

How is the ABAP-Syntax for the Hint of the Select-Statement?

How do I get comparable results, since a second execution is always a lot faster ?

(Do I have to invalidate the Buffer for the tables? If so, how do I do this?)

Sorry for having so many questions.

typical T_02 . "EINDT" BETWEEN :A14 AND :A15

About 50.000 Entries, takes 14secs

Best regards

Jens

Re: Select Join Performance

HermannGahm — Thu, 10 Feb 2011 10:45:07 GMT

Hi Jens,

> Although the estimates cost are very high I would like to test it.
> How is the ABAP-Syntax for the Hint of the Select-Statement?
> How do I get comparable results, since a second execution is always a lot faster ? 
> (Do I have to invalidate the Buffer for the tables? If so, how do I do this?)
> Sorry for having so many questions.

in ABAP you need:

SELECT k_{ebeln k}lifnr k_{ekgrp k}ekorg k_{bukrs k}bsart

... t_{etenr t}eindt t_{menge t}wemng t_{slfdt t}mahnz

INTO CORRESPONDING FIELDS OF TABLE g_i_tab1

FROM ekko AS k

JOIN ekpo AS p

ON p_{ebeln = k}ebeln

JOIN eket AS t

ON t_{ebeln = k}ebeln AND

t_{ebelp = p}ebelp

WHERE k~lifnr IN s_lifnr AND

k~ekorg IN s_ekorg AND

... t~menge > 0 AND

t_{menge > t}wemng

%_hints ORACLE 'LEADING "EKPO"'.

(or %_hints ORACLE 'LEADING "T_01"'.)

execute the queries multiple times and compare the buffergets per execution in ST04. In ORACLE you can reset the sql cache kpis after each test set (multiple executions) and analyze the delta since reset.

You can try with and without hint for big and small LIFNRs.

Kind regards,

Hemrann

Re: Select Join Performance

volker_borowski2 — Thu, 10 Feb 2011 17:48:25 GMT

Hi,

I do not think the skip scan entry for this plan will lead to anything.

So you have

EKKO with 4500 Entries: 8s (number differs from 0 to 12.000 depending on LIFNR)

EKPO with 6200 Entries: 4s (number is constant per plant)

EKET About 50.000 Entries, takes 14secs

this is 26 for counting in total

... and a join delivering real fields takes 30 to 50 Secs

This does not sound all too bad taking the above 3 values into account

You did not say yet, how many rows the result has.

So to see, how many rows are eliminated in the join

Depending on that, one could try to change the join strategy.

One could try to hint the join from "nested loop" to a "sort merge" or a "hash", just

to measure, if this gets any results, but I would do this in sqlplus with autotrace on

to get the additonal block statistic for the fetch.

But I do not think, this will give you better results as well.

Volker

Re: Select Join Performance

Former Member — Fri, 11 Feb 2011 11:20:54 GMT

Hi,

wrote a test-programm containing only the select-statement with and without hint.

 %_hints ORACLE 'leading(T_00) index(T_00,"EKPO~1")'.

Hint is working, as I can see in ST05.

Performance without hint is better than with hint.(2-3 times)

Executed the the Report a number of times (with hint):

First execution: 60sec

Following executions: 0.6sec

Executed the the Report a number of times (without hint):

First execution: 30sec

Following executions: 0.2sec

ST04 shows, that in the following executions data is read from database data buffer. (No physical reads)

If the data were alway be in the database buffer the runtime would be perfect.

How can I do this, or is this against all principals?

I do not have direct acess to the ORACLE database. (Only via SAP)

Can I delete the database cache so that I can retest the excution time for the first execution?

Side effects?

Thypically the select results in 10 to 100 rows.

One more Question:

In the development system ST05 - Execution plan contains a HASH JOIN.

In the integration system ST05 - Execution plan contains a NESTED LOOPS instead.

What does the ABAP-Syntax look like to hint the Database to perform NESTED LOOPS.

Best regards

Jens Grimmelmann

Re: Select Join Performance

HermannGahm — Fri, 11 Feb 2011 12:03:47 GMT

Hi Jens,

> Performance without hint is better than with hint.(2-3 times)

as Volker said the original plan starting with LIFNR is probably a quite good one

> If the data were alway be in the database buffer the runtime would be perfect.

> How can I do this, or is this against all principals?

i would not do it. We see that customers do this from time to time (on ORACLE you

have to create a so called KEEP POOL and put your table there, in case you are

interested just google with key words ORACLE KEEP POOL). It would mean

to place all table/index blocks in a dedicated pool with enough space so that

there is a high chance that no displacement will take place. It's a rather drastic

measure. You put away a siginificant part of the db cahe to support a certain

set of sql statements that could mean all other statements may suffer because

they have less cache available. Our recommendation typically is to stic to the

default pool and let ORACLE do the discplacement based on the touch count

in order to achieve a good performance for the whole system.

> I do not have direct acess to the ORACLE database. (Only via SAP)

> Can I delete the database cache so that I can retest the excution time for the first execution?

alter system flush db cache or something like that.... i would use it only

for test purposes...

> Side effects?

nothing is in the cache... like after restarting the database... performance will be

bad for the whole system till the cache is filled again...

> One more Question:

> In the development system ST05 - Execution plan contains a HASH JOIN.

> In the integration system ST05 - Execution plan contains a NESTED LOOPS instead.

> What does the ABAP-Syntax look like to hint the Database to perform NESTED LOOPS.

The appropriate hint would be e.g.


%_hints ORACLE 'leading(T_00) index(T_00,"EKPO~1") use_nl(T_01)'.

in this example you specified to start with t_00 on index ekpo~1 and then use a

nested loop join (use_nl) to approach table t_01 (oracle decides the access path for

this table). you could continue with use_nl(T_02) to specify how the third table

should be joined. use_hash forces oracle to use a hash join if technically possible.

How much I/O is your query doing (buffer gets per exec)? If nothing is in cache

you can expect that each buffer get will be a disk read lasting for 6 - 15 ms (depending

on the i/O system) so you can calculate the expected time. Minimizing the i/O is the

only solution... if the i/O is already at a minimum... well...

Kind regards,

Hermann

Re: Select Join Performance

volker_borowski2 — Fri, 11 Feb 2011 13:44:37 GMT


> The appropriate hint would be e.g.
> 
> 
> %_hints ORACLE 'leading(T_00) index(T_00,"EKPO~1") use_nl(T_01)'. 
> 
>

Well the problem is, that you have only "leading" and "ordered" as hints for join order.

so even with that hint, it only suggests to start with EKKO and to use a nested loop

to EKPO, but it will not say WHEN, so it might be that with this hint you'll get right the original plan,

joining EKET at second position.

If you want to make sure, that EKPO is joined on position 2, you need to specify the sequence

in the FROM clause and use the "ordered" hint.


%_hints ORACLE 'ordered'.

(With "ordered" meaning to access the tables in the sequence they follow in FROM)

I'd not specify more at first (crosscheck in ST05), right because of the difference you notice

in hash vs. nested_loop. It might be, that for one system hash is better. But if you want to do it anyway:


2. try: %_hints ORACLE 'ordered use_nl(T_00,T_01)'. 
3. try: %_hints ORACLE 'ordered index(T_00,"EKKO~1") use_nl(T_00,T_01)'. 
4. try: %_hints ORACLE 'ordered index(T_00,"EKKO~1") use_nl(T_00,T_01) index(T_01,"EKPO~0")'.

But I think you will not get away without indexing EKET on EINDAT in addition to the join fields.

Just out of curiosity: how many rows full fill this?

T_02 . "MENGE" > T_02 . "WEMNG"

vs.

T_02 . "MENGE" = T_02 . "WEMNG"

vs.

T_02 . "MENGE" < T_02 . "WEMNG"

If this is the condition, that cuts down the rows, one can possibly try to stick that into a functionbased index that

allows it to query the already executed comparison instead of retrieving the rows nd do the comparrison every time

it is called. (Allthough I can not say how this access would have to be translated to ABAP for access).

Is that your code in a customer program or is that a part of standard coding?

Volker

Re: Select Join Performance

Former Member — Fri, 11 Feb 2011 13:49:42 GMT

Hi,

Data buffer reads per execution (ST04):

First Example:(High number of Entries for EKKO-LIFNR)

26.000 (with hint)(BD is doing HASH JOIN)

35.000 (with hint incl. Nested Loops)

50.000 (without hint)

Second Example: (Medium number of Entries for EKKO-LIFNR)

21.000 (with hint) (BD is doing HASH JOIN)

34.000 (with hint incl. Nested Loops)

26.000 (without hint)

Third Example: (Very low number of Entries for EKKO-LIFNR)

13.000 (with hint) (BD is doing HASH JOIN)

31.000 (with hint incl. Nested Loops)

500 (without hint)

Conclusion:

To force the Database to do a Nested Loop insead of a Hash join is a bad Idea.

To force the database to start the selection with EKPO only in certaim cases sensible.

Seams that there is no solution.

Best regards

Jens

Re: Select Join Performance

HermannGahm — Fri, 11 Feb 2011 14:06:55 GMT

Hi Volker,

> Well the problem is, that you have only "leading" and "ordered" as hints for join order.

> so even with that hint, it only suggests to start with EKKO and to use a nested loop

> to EKPO, but it will not say WHEN, so it might be that with this hint you'll get right the original plan,

> joining EKET at second position.

are you sure about that?

My understanding so far was that it should work. But I'm not too sure i must admit.

Looking at things like this:

http://jonathanlewis.wordpress.com/2007/01/16/full-hinting/

i got the impression it should work this way.

> If you want to make sure, that EKPO is joined on position 2, you need to specify the sequence

> in the FROM clause and use the "ordered" hint.

agreed. "ordered" nails down the execution order for sure.

Kind regards,

Hermann

p.s.: i really like your engagement here, it's always interesting and enriching reading from you.

Re: Select Join Performance

HermannGahm — Fri, 11 Feb 2011 14:17:10 GMT

Hi Jens,

and the runtimes do colerate with the buffer gets?

Well the main problem is the unequal data distribution. Histograms were designed to help here.

But histograms in OLTP systems are... 'difficult' since you could produce many different sql

queries easily (one for each LIFNR)... . So i doubt it would be a good idea.

I think you have to find out a (one) plan that fits all more or less and hint the optimizer to execute it.

Looking at your figures: Hash join + hint is in all cases the 2nd best option regarding I/O.

Kind regards,

Hermann

Re: Select Join Performance

volker_borowski2 — Fri, 11 Feb 2011 14:19:14 GMT

Hi Hermann,

no, I am not sure. I never did it diffrent becasue of this

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm

16.1.2.4 Hints for Join Orders

16.1.2.5 Hints for Join Operations

JL is pretty good, so I guess he did a good research on that.

Volker