Query time

[Former Member]

Hi

When i execute the query with VBELN no it is taking 15 msec but when i add VBELN no it is taking 2 mins.

Please let me know how this can be sloved. I have also created index.

SELECT MG.MBLNR, MG.MJAHR AS MJAHR, ES.VBELN, MG.MENGE, MG.EBELN, MG.EBELP as EBELP,

MG.LFBNR as LFBNR, MG.BWART, MG.MATNR AS MATNR, MG.ZEILE as ZEILE, MG.LIFNR as LIFNR

FROM EKES ES, EKBE EB, MSEG MG

WHERE ES.EBELN = EB.EBELN AND ES.EBELP = EB.EBELP AND ES.ETENS = EB.ETENS AND EB.BELNR = MG.MBLNR AND

EB.EBELN = MG.EBELN AND EB.EBELP = MG.EBELP AND EB.ZEKKN = MG.ZEKKN AND ES.VBELN = <vl31n no>

Request your support to solve the issue.

regards

Suresh S

[Former Member]

Suresh

Here are a couple of problems with your query below:

1) You are pulling the VBELN off the EKES table. If you look at the delivered indexes on EKES, there is NO index on VBELN.

Just the unique index EKES~0 (MANDT, EBELN, EBELP,ETENS) which does not contain VBELN.

2) So Oracle is doing a FULL tablescan of all 3 tables in the JOIN most likley as it shows in my system.

It is the FULL TABLESCANS on EKBE and MSEG that take so long.

SELECT

MG.MBLNR, MG.MJAHR AS MJAHR, ES.VBELN, MG.MENGE, MG.EBELN, MG.EBELP as EBELP, MG.LFBNR as LFBNR,

MG.BWART, MG.MATNR AS MATNR, MG.ZEILE as ZEILE, MG.LIFNR as LIFNR

FROM

EKES ES, EKBE EB, MSEG MG

WHERE

ES.EBELN = EB.EBELN AND ES.EBELP = EB.EBELP AND ES.ETENS = EB.ETENS

AND EB.BELNR = MG.MBLNR AND

EB.EBELN = MG.EBELN AND EB.EBELP = MG.EBELP

AND EB.ZEKKN = MG.ZEKKN AND ES.VBELN = :A1

SELECT STATEMENT ( Estimated Costs = 4,439,030 , Estimated #Rows = 1 )

--- 5 HASH JOIN

( Estim. Costs = 4,439,029 , Estim. #Rows = 1 )

Estim. CPU-Costs = 547,401,351,205 Estim. IO-Costs = 4,405,929

Access Predicates

-- 3 HASH JOIN

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

Estim. CPU-Costs = 136,086,962,632 Estim. IO-Costs = 1,199,790

Access Predicates

| |-----1 TABLE ACCESS FULL EKES

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

| | Estim. CPU-Costs = 12,358,945 Estim. IO-Costs = 112

| | Filter Predicates

| -

---

2 TABLE ACCESS FULL EKBE

| ( Estim. Costs = 1,207,240 , Estim. #Rows = 110,022,500 )

| Estim. CPU-Costs = 125,064,084,709 Estim. IO-Costs = 1,199,678

-

---

4 TABLE ACCESS FULL MSEG

( Estim. Costs = 3,230,296 , Estim. #Rows = 118,051,333 )

Recommendation:

1) In SAP, ALMOST all of our DELIVERED SAP INDEXES begin with MANDT, so if you want the Oracle CBO to "think" about

using the index, then you need to include the MANDT column in the WHERE clause.

So re-write the SQL as:

SELECT MG.MBLNR, MG.MJAHR AS MJAHR, ES.VBELN, MG.MENGE, MG.EBELN, MG.EBELP as EBELP,

MG.LFBNR as LFBNR,

MG.BWART, MG.MATNR AS MATNR, MG.ZEILE as ZEILE, MG.LIFNR as LIFNR

FROM EKES ES, EKBE EB, MSEG MG

WHERE

ES.MANDT = :A0

and

ES.EBELN = EB.EBELN

AND ES.EBELP = EB.EBELP

AND ES.ETENS = EB.ETENS

and EB.MANDT = :A1

AND EB.BELNR = MG.MBLNR

AND EB.EBELN = MG.EBELN

AND EB.EBELP = MG.EBELP

AND EB.ZEKKN = MG.ZEKKN

and MG.MANDT = :A2

AND ES.VBELN = :A3

This will give you this type of explain plan which now only does the FULL tablescan on

EKES but using the unique indexes on the joins. If the EKES table is very small,

then this should not take a lot of time and you are done.

Otherwise if EKES is large then the FULL TABLESAN on EKES will take the longest time and you should test

recommendation #2 below and create a custom index.

#2) If the EKES table has a lot of records (i.e. 500,000+) or the runtime is not as you like,

then create a custom index on EKES using the columns (MANDT, VBELN). Since VBELN is usually unique,

this will give you the fastest access of finding the VBELN in EKES quickly and then joing down EKBE and

MSEG using the unique indexes. If you like this speed, then create the index via an SAP transport

(NOT AT THE ORACLE LEVEL!).

Good Luck,

Mike Kennedy

[fidel_vales]

Hi,

There is absolutely not enough information to help you.

Perhaps some people will be bold enough to send you some "silver" bullets.

You should start looking at the different access paths taken.

What is doing in one case and what is doing in another case?

How are the index definitions (of all tables)?

What are the statistics?

...

I suggest you to

1) take a look at the note 212986 Required data for processing CBO/RBO problems

2) open a message at SAP with the information from the previous note.

3) perhaps go to some training about Oracle Tuning if that is your task. If it is not your task perhaps you can also ask the responsable person in your company for that area