Estimated Costs in ST05 transaction

[Former Member]

Hi,

I have asked to analyze whether to apply OSS Note 791479 or not. As per the note "...The ORACLE cost optimizer selects an unsuitable

database index." so by applying the note we forcefully select the suitable database index. I wrote a simple select statement and analyzed through ST05 transaction. Currently it selects MARC_{WRK index and after modifying the code as per OSS note it selects MARA}STD index. For me it looked like selecting MARA_{STD index is correct since it hit the first field in where clause. But I am getting confused by "Estimated Costs" field in ST05 transaction. When it hits MARA}WRK index ST05 is returning "Estimated Costs" = 2 & "Estimated Rows" = 331, whereas when it hits MARA~STD index it's returning "Estimated Costs"=6 & "Estimated Rows" = 1487. Could you explain Estimated Costs and should I implement the OSS note or not?

Current code is:

SELECT COUNT(*) UP TO 1 ROWS FROM MARC WHERE stdpd = lv_matnr

AND werks = iv_plant

AND cuobj NE space.

After applying the OSS note, it will be,

SELECT COUNT(*) UP TO 1 ROWS FROM MARC WHERE stdpd = lv_matnr

AND werks = iv_plant

AND cuobj NE space

%_HINTS ORACLE 'INDEX("MARC" "MARC~STD" "MARC^STD" "MARC______STD"

"MARC______STDX")'.

Thanks in advance.

Regards,

Balaji Viswanath.

[fidel_vales · Accepted solution]

Hi,

It is a bit difficult to explain how the CBO works in few words.

I'll try to make it simple.

CBO works with statistics. Using those statistics and the query conditions it "calculates" how much "cost" to retrieve the data using ALL possible access methods.

It gives a value to each of those possible access path ( having into consideration not only the table/index statistics, but also other things like the parameters and some internal rules )

Afterwards it chooses the access path with the lowest value.

In your case

index MARA~WRK -> cost 2

index MARA~STD -> cost 6

In normal conditions the CBO will choose the index WRK.

That does not means that the access is fastest, what it indicates is that ( with the current information ) the CBO thinks it will be better using that index.

Some times we know better.

Imagine a table with one million rows. And one field has two different values.

If you use that field Oracle will think that the data distribution is uniform and it will think that 1/2 million rows will be returned and perhaps does not use an index on the mentioned field.

But we know "better" and we know that the data distribution is unbalanced and the query we use returns few rows.

Your case is the same.

Oracle thinks index MARA_{WRK is better, but SAP thinks that, for this specific query, the index MARC}STD is better. It is necessary to provide more information to the CBO to help him to choose the correct index.

That is what the mentioned change does.

[Former Member · Accepted solution]

As Fidel already mentioned, when distribution of the data is skewed, the CBO can make bad choices as it assumes that distribution of the values is evenly spread.

There are a couple of options to consider - one approach is to force the use of a particular index via a hint, but there are other options as well. You could also look at setting some of the distinct value counts in the DB statistics and then lock the statistics so they do not get refreshed. This lets you change the information the CBO looks at to make it's decisions. And a third option would be to specify that histograms are created for the table which provides information to the CBO on the distribution of values, so it can make better informed decisions.

The frequent example cited as to demonstrate the use of histograms is a table containing patient data at a women's maternity hospital, with an index on the Gender column. One day, there is a man injured at the hospital, perhaps he fainted while wacthin his wife give birth and hit his head ( If you are a Robert Redford fan, something happens in the movie "The Natural" where he gets admitted to a maternity hospital.)

If the Hospital has 1000 patients, there would be 999 females - F, and one male - M. When statistics are collected on the table, they would indicate that there were 2 distinct values of gender in the Patient table. The CBO would not select the Gender index, as it is not selective enough, the CBO making the assumption that that 500 patients are Female and 500 are Male. It you built histograms, teh CBO would know that the distribution of Gender was extremely skewed, and it would decide NOT to use the index when a query predicate selects rows where the Gender = F, but it would choose to use the index when the query was selecting patients where Gender = M.

[fidel_vales · Accepted solution]

Hi,

Yes, there are a lot more things to consider that I had not mentioned.

As SAP already provides a note with the hint the two approach you mention are not needed.

For the first approach, the ntoe 724545 contains more information.

For the second you forget something VERY important in a SAP environment ( non BW like )

That is the usage of bind variables. If nothing changes ( like the query shown at the initial question ) then the histogram calculation will not be of much use, because Oracle will not know the content of the bind variable and it will not be able to use the information stored in the histograms.

As mentioned, things can become complicated