Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle Hint on Table MKPF and MSEG : force index MSEG~M to be utilize

Former Member
0 Kudos

Hi Experts, I'm quite new to ABAP performance tuning, I try to put details as much as I can in my question below, just to have a quick overview of my problem below, I want to use/force in my SQL below to force the utilization of index MSEG~M.

  • added an Oracle Hint forcing index MSEG~M

SELECT MATNR BWART MENGE

FROM MKPF

INNER JOIN MSEG

ON MKPFMANDT EQ MSEGMANDT

AND MKPFMBLNR EQ MSEGMBLNR

AND MKPFMJAHR EQ MSEGMJAHR

INTO CORRESPONDING FIELDS OF TABLE T_MSEG2

WHERE MKPF~BUDAT IN S_GSTRP "(index bud should be used)

AND MSEG~MJAHR IN R_DOCYEAR

AND BWART IN ('931', '932',

'901', '902',

'701', '702',

'703', '704',

'707', '708',

'711', '712',

'713', '714',

'715', '716',

'717', '718',

'551', '552',

'553', '554',

'555', '556') "ZZCHK: Constants Not Applicable

AND MATNR IN R_MATNR3

AND MATNR IN R_COMPNTS

AND WERKS EQ P_WERKS

%_hints oracle u2018INDEX(u201CMKPFu201D u201CMSEG~Mu201D)u2019.

****************************************************************

Note: I'm assuming that you'll take not some comments on further enhancement:

- " INTO CORRESPONDING FIELDS "

- use of field symbols

- I didn't modify it yet since my focus if for MSEG~M

- cannot make material number as mandatory field

****************************************************************

Objective: Force to use index MSEG~M

Problem:

After adding the Oracle Hint, I did the analysis in ST05 and it seems like index M is still not being forcefully

utilize instead MSEG~0 is being used.

I replaced the Oracle Hint with:

%_hints oracle u2018INDEX(u201CT_01u201D u201CMSEG~Mu201D)u2019.

In ST05, I can get see that index MSEG~M is now being utilize BUT worst thing happened after applying this, the program timed out.

I already take a look on the following Notes: 129385, 130480, 772497, 162034, 818321 but nothing seems to be helpful in my problem.

Questions:

1. In syntax %_hints oracle u2018INDEX(u201CT_01u201D u201CMSEG~Mu201D)u2019. T_01 stands for MKPF right which is the first table, do you what's the reason for the program time out after adding this Oracle Hint? Is this the proper syntax?

2. How can I force in my code to use index MSEG~M without any program timeout?

For the whole source code of my question above, please see the link:

http://rapidshare.com/files/211740852/ZDXX0028.txt.html

1 ACCEPTED SOLUTION

former_member194613
Active Contributor
0 Kudos

hints can help, but they are difficult to use, if anything is wrong then they are just ignored:

%_hints oracle u2018INDEX(u201CMKPFu201D u201CMSEG~Mu201D)u2019.

This is obviously wrong, as Thomas wrote several times, table name and index name must be identical

We had a similar select already in another question, there I also assume that the data would be

the most selective entry point.

so the start should use MKPF~BUDAT on table MKPF with BUD

and the join uses always the primary key fields, so second index is primary key.

Use aliases a and b and use them consequently for all conditions, also write the

order of the INNER JOIN in the order a and b.

USE:

%_hints oracle u2018INDEX(u201CMKPFu201D u201CMKPF~BUDu201D)u2019.

OR

%_hints oracle u2018INDEX(u201CMSEGu201D u201CMSEG~Mu201D)u2019.

What is actually better can not be answered remotely, it depends on your actual data distribution!

Siegfried

11 REPLIES 11

Former Member
0 Kudos

I'll appreciate any inputs from you guys, thanks!!

Thomas8
Active Contributor
0 Kudos

Well, it's Saturday, and we have some other hobbies too

%_hints oracle u2018INDEX(u201CMKPFu201D u201CMSEG~Mu201D)u2019.

should be

%_hints oracle u2018INDEX(u201CMSEGu201D u201CMSEG~Mu201D)u2019.

if R_MATNR3 contains a large selection of material numbers, index M cannot help much. What about S_GSTRP? If the BUDAT selection is narrow, then MKPF~BUD is the best index.

I would remove the hint altogether and let the optimizer choose the correct index, based on the runtime circumstances. Use hints only when the optimizer cleary chooses the wrong index, which is very rare.

In the end, these are large tables, so if you are reading very many entries (six figures or more), then you might have to run this in background anyway.

The following recommendations you might read here are useless or even counter-productive:

- remove INTO CORRESPONDING

- split the JOIN and use FOR ALL ENTRIES

- give order of fields in WHERE-clause as they appear in the index

Thomas

Former Member
0 Kudos

Hi Thomas, thanks with the prompt reply (even if it's Saturday )

Yes, MKPF~BUD is being used.

The budat selection is narrowed to 28 days though users can input any date range, a warning message will just be displayed if budat selection exceeded.

Yes, optimizer usually chooses the right index; I'm not in as the same experience as you are though, but I find it interesting that after I added the Oracle hints (several combination) the performance of the program was optimize up to 40% to 50%.

I uploaded a detailed SQL trace and the ABAP change done in my program, if you'll notice in the summary of the changes, after adding some hints, the duration of the program was improved..

(SQL Trace Summary of my program) this worth taking a look

http://rapidshare.com/files/211870091/ZD28_SQL_Trace.zip.html

I made a custom index in MSEG, but unfortunately I doubt that it will be approve by our performance capacity team. But I somehow have a hunch that this will further optimize the program.

MSEG~Z03

MANDT Client

WERKS Plant

BWART Movement Type (Inventory Management)

LGORT Storage Location

What do you think Thomas and SAP experts?

**Sorry for pasting the whole ST05 result

SELECT STATEMENT ( Estimated Costs = 131.250 , Estimated #Rows = 450 )

 6 FILTER

Filter Predicates

 5 NESTED LOOPS

( Estim. Costs = 131.249 , Estim. #Rows = 450 )

Estim. CPU-Costs = 1.885.249.948 Estim. IO-Costs = 129.700

 2 TABLE ACCESS BY INDEX ROWID MKPF

( Estim. Costs = 14.442 , Estim. #Rows = 288.570 )

Estim. CPU-Costs = 206.401.140 Estim. IO-Costs = 14.272

1 INDEX RANGE SCAN MKPF~BUD

( Estim. Costs = 655 , Estim. #Rows = 519.426 )

Search Columns: 2

Estim. CPU-Costs = 30.640.783 Estim. IO-Costs = 630

Access Predicates Filter Predicates

 4 TABLE ACCESS BY INDEX ROWID MSEG

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

Filter Predicates

3 INDEX RANGE SCAN MSEG~0

Search Columns: 3

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

Access Predicates Filter Predicates

Former Member
0 Kudos
MSEG~Z03
MANDT Client
WERKS Plant
BWART Movement Type (Inventory Management)
LGORT Storage Location

Hm, I don't think is very helpfull. Do you have really different clients on your system? All this fields are not narrowing down the amount of data to be read, except for some special cases. If you create an own index, you should try to reduce the number of lines which must be read for a sql statement. For MSEG I don't see this effect for normal cases.

regards

Matthias

Former Member
0 Kudos

Since hints are generally a bad idea, the first thing I would do is run the anlaysis bot with and without the hints to see if the hints actually help. (Analyze the 'explain'.) If it doesn't use your new index, I'd get rid of it as well.

Rob

0 Kudos

Yes, that's what I did, I had some several "trial & error" on how the Oracle Hints behave on table MSEG<->MKPF query:

1. %_hints oracle u2018INDEX(u201CT_01u201D u201CMSEGMu201D)u2019. --> program time out but was able to force index MSEGM

2. %_hints oracle u2018INDEX(u201CMKPFu201D u201CMSEGMu201D)u2019 --> improved the performance but does force index MSEGM instead it uses MSEG~0

I did a couple of Full Index Oracle Hints but #2 is still the best optimization so far.

%_hints oracle 'FULL(MSEG)'.

%_hints oracle 'FULL("T_01" "MSEG")'.

Before adding the hints, DBase Optimizer uses MSEG~0 as my index on my SQL above.

Would anyone know why #1 is choosing index MSEG~M but somehow time out?

Why #2 is using MSEG~0, anything wrong with the syntax?

THANKS.

Jaime

0 Kudos

> Why #2 is using MSEG~0, anything wrong with the syntax?

See my first reply for the correct syntax, replace "MKPF" by "MSEG".

Also, if MKPFBUD is chosen as the best access path then the nested access to MSEG will be via the primary key MSEG0.

Thomas

Former Member
0 Kudos

Hi Gents and Lads, need some F1 on my query above, thanks! T_T

former_member194613
Active Contributor
0 Kudos

hints can help, but they are difficult to use, if anything is wrong then they are just ignored:

%_hints oracle u2018INDEX(u201CMKPFu201D u201CMSEG~Mu201D)u2019.

This is obviously wrong, as Thomas wrote several times, table name and index name must be identical

We had a similar select already in another question, there I also assume that the data would be

the most selective entry point.

so the start should use MKPF~BUDAT on table MKPF with BUD

and the join uses always the primary key fields, so second index is primary key.

Use aliases a and b and use them consequently for all conditions, also write the

order of the INNER JOIN in the order a and b.

USE:

%_hints oracle u2018INDEX(u201CMKPFu201D u201CMKPF~BUDu201D)u2019.

OR

%_hints oracle u2018INDEX(u201CMSEGu201D u201CMSEG~Mu201D)u2019.

What is actually better can not be answered remotely, it depends on your actual data distribution!

Siegfried

0 Kudos

"See my first reply for the correct syntax, replace "MKPF" by "MSEG".

Also, if MKPFBUD is chosen as the best access path then the nested access to MSEG will be via the primary key MSEG0."

--> THANKS Thomas, got it now.

Thanks too Siegfried.

Appreciate your very quick response here, and still my education continues.

0 Kudos

Hi,

I would use hints ONLY in 2 cases

  • for testing purposes (force the usage of an index to see the response time changes )

  • a known bug inside the database handling the optimization of index access to the table (i.e. as described in some SAP standard table notes)

The CBO relies on informations about data volume , data objects (tables, idexes) ,data distribution and the valid use of data types for your fields. The first 2 seems obvious - the last 2 may not:

  • Use proper data types to express your data :Use Date types if you mean dates (i.e. SAP uses Varchar2 for dates ).

  • Use meaningful default values (some applications use 12/31/9999 as a date default

  • Example:

If you have to consider a skewness in your data distribution this would give definitley a false representation of the data. CBO would compute a wrong number of rows a query would return leading in a wrong execution plan.

Know your data distribution and have histograms in place if your data is skewed in the respective columns.

However , if you hide that information from the CBO you can't expect good execution plans.

If you have a flawed data model you may have to use hints but normally you would change the model and not writing your access plans in "stone" .

So , if you use index hints be aware that you will write the access plan kind-of "hard-coded" to the database:

If you have data volume changes inside the table the optimizer has no chance to develop an appropriate

plan. It is fixed to your all-purpose index - what definitley is not true (data volumes change,

filter condition change in a real system).

bye

yk