On what basis does the optimiser select the index

[Former Member]

Hi,

The following select statement is causing a performance issue.

SELECT SINGLE belnr FROM bkpf INTO bkpf-belnr

WHERE bukrs = t_input_invoice-detenteur_compte_banc

AND gjahr = w_gjahr

AND blart = 'ZR'

AND bstat IN r_bstat

AND xblnr LIKE w_bktxt.

When I checked in ST04 transaction,the optimiser is selecting BKPF_{3 index having fields bukrs,bstat and blart which are present in the where condition of the select statement.But there is also one more index BKPF}1 having fields bukrs,bstat and xblnr which are also present in the where condition of the select statement.

On what basis did the optimiser select BKPF~3 index?

Can anyone explain.

[ThomasZloch]

In rather non-scientific words I would say that your select statement is being analysed by the CBO (cost based optimizer) factoring in the current database statistics for table BKPF. The CBO decides that the access path with the least "cost" is via index BKPF~3.

If you think this is wrong and BKPF~1 would provide a better selectivity, you might want to try updating the statistics for table BKPF via DB20. With another transaction DB05 you can check which index really would have a better selectivity.

However, you select on XBLNR with a LIKE-operator, this might actually make index BKPF~1 unsuitable, depending on the selection pattern.

You should also investigate if selecting for BSTAT = space is sufficient (actually posted FI documents), this would definitely help to speed things up.

Thomas

[Former Member]

Hi Thomas,

Thank you.

Could you please tell me how to check the selectivity of index with DB05 transaction.

[ThomasZloch]

In a nutshell, you enter table name and choose option "analysis for specified fields. Also enter the fields of the respective index, e.g. MANDT, BUKRS, BSTAT, XBLNR. Submit in background.

In the resulting list, you want to see a large number of disctinctive values, which means that providing the full key will result in a small selection set.

Thomas

[former_member194613]

I have actually nerver used the DB05, before I found it in posting here.

It is an interesting transaction on one hand, but it can also be misleading.

The cost-based optimizer knows only the selectivity of single fields. If more fields

of an index can used, i.e if second or evn third field are also available in the WHERE condition, then the optimizer combines the single selectivities.

For example

field1 has 10 different values

field2 has 5 different values

field3 has 20 different values

Optimizier thinks (field1, field2, field3) has 10520 = 1000 different values.

Knowing that one combination for (field1, field2, field3), i.,e. (field1, field2, field3) = (a,b,c)

selects one combination out of 1000, this would reduce the possible hit by a factor of 1000.

That is what the optimizer judges with standard statistics.

The DB5 can count statistics of combined values, i.e for

(field1, field2, field3) together, and finds the only 120 out of the possible 1000 combinations really exist on the database.

I think it is more useful if you use the SQL trace. Systems with Oracle or DB6 show you the statistics in the explain. Double click on table, then you can see the statistics for all index fields. Do the field combination by multiplication and compare with DB5. LIKE gets a big malus, don't know how much.

Siegfried

[ThomasZloch]

Hi Siegfried,

thanks for clarifying this. Would you agree that DB05 is useful before creating a new index to check whether it would provide a decent selectivity of the existing data?

Cheers

Thomas

P.S. based on the output it seems to actually be designed to help decide if table buffering is an option or not.

[former_member194613]

I think the DB05 is intended to check whether more complicated statistics including histograms could help for some selections.

Histograms can help if the distribution are not equal. They can be useful for some special

hard problems, but they are rarely used right from the beginning.

I do not understand your P.S., how does this relate to buffering?

Siegfried

[ThomasZloch]

Quoting from the spool list of DB05:

Date/time of analysis:                  15.10.2008  10:52:19       
Analyzed table:                         BKPF                       
Total number of rows:                   88.534.661 rows          
Requested bytes per row:                24 bytes per row 
Requested bytes to be buffered 100%:    2.124.831.864 bytes         
Current buffering mode:                 no buffering     

The last two lines made me think that. Could be meaningless, of course.

Thomas

[Former Member]

Hi Thomas,

I have checked with fields bukrs,bstat,xblnr from bkpf_{1 in DB05 transaction.I think it would be better to use this index rather than index bkpf}3.When I checked in ST05 transaction with the fields from BKPF~1 index,the number of estimated costs reduced to 69 from 449 .

Now can I use index BKPF~1 in the where condition of the select statement.

[ThomasZloch]

What is the content of w_bktxt, that you use with the LIKE-operator for selecting XBLNR?

What is your database, Oracle?

Thomas

[Former Member]

I am working on sap.database is oracle

[ThomasZloch]

You can force usage of index BKPF~1 by adding this to the select statement:

%_hints oracle 'INDEX("BKPF" "BKPF~1")'.

But:

- document this well, because it makes this code part database dependent

- it can only make a difference if the pattern in w_bktxt does not have leading wildcards

So once again, please tell me the value of w_bktxt during the select statement.

Thomas

[Former Member]

Hi Thomas,

w_bktxt is showing up the value ING033% during selection.

[ThomasZloch]

Looks good, so try my above recommendation with the hint, whether that makes a difference.

Thomas

[Former Member]

Hi Thomas,

Could you please tell me where to use and how to use this hint in st05 transaction

[ThomasZloch]

You have to add this in your program code:

SELECT SINGLE belnr FROM bkpf INTO bkpf-belnr
WHERE bukrs = t_input_invoice-detenteur_compte_banc
AND gjahr = w_gjahr
AND blart = 'ZR'
AND bstat IN r_bstat
AND xblnr LIKE w_bktxt
%_hints oracle 'INDEX("BKPF" "BKPF~1")'.

After this, run ST05 again and check whether this index is now being used, and whether the overall runtime has improved.

Thomas

[former_member194613]

buffering is sometimes an option to solve performance problems because the buffer is faster

even rather slow buffer scans are still fast compared to database accesses.

BUT

+ whether some table can be buffered, must be decided on the general rules for buffering.

+ the size of the table is important

+ buffering supports only primary key, anything else is scanned sequentially.

The DB05 is a transaction for the service, they check all options.

Siegfried

[former_member194613]

with Oracle you can use a hint to tell the database what index should be used.

With hints you must be very careful with writing, there is no check, any mistake will make the hint

useless.

Better check SAP notes, 'Oracle Hints' and then check index hints.

With Oracle and DB6 you can get the statistics of very easily, just trace with ST05 a selection example of SE16 on the table BKPF.

You get a very short trace and the explain tell you all statistics, much faster than DB05.

Siegfried

this time Thomas was faster ...

Edited by: Siegfried Boes on Oct 16, 2008 1:11 PM

[Former Member]

With all due respects, Oracle hints are a lot like suicide. It's a permanent solution to a temporary problem.

The optimizer has used the statistics available and determined that BKPF~3 is the best index to use. If you put in a hint and leave it there, it may force the database to use a less than optimum index in this case and if the conditions are changed in the WHERE so that a completely different index should be used, it will still use the index from the hint.

I think Thomas' first idea is the best - update the statistics and leave the rest to the optimizer.

Rob

[Former Member]

Hi Rob,

Could you please tell me how to update the statistics using DB20 transaction

[Former Member]

>

> Could you please tell me how to update the statistics using DB20 transaction

This is normnally a basis/DBA function. They may have a schedule on which to take care of this sort of thing and there may be other tables done at the same time.

Rob

[former_member194613]

you can easily update the statistics out of the explain in the ST05. There is an analyse button

in the pop-up.

It is recommended to do it that way, because you can see, what the database does before, and

you can call the explain after the update. It should change then.

the other transaction is db02.

Siegfried