Bad optimizer Oracle index choice on execution plan for SQL Statement.

[Former Member]

Hello,

The optimizer execution plan for SQL Statement not use the best primary Oracle index.

This problem give time-out on SAP user request with Oracle table SAPR3.BKPF.

The CPU cost is too hight because the request with column criterias MANDT, BELNR, BUKRS and GJAHR use index BKPF___3 instead index BKPF___0 based on this 4 fields.

Many thanks for your help.

Best regards. Jean-Pascal.

[former_member184473]

Hello Jean,

Do you have the parameters set as per note [830576|http://service.sap.com/sap/support/notes/830576] (Oracle 10g) or note [1431798|http://service.sap.com/sap/support/notes/1431798] (Oracle 11g)? You can use the script from note [1171650|http://service.sap.com/sap/support/notes/1171650] to check it.

Also, check the statistics.

Regards,

Eduardo Rezende

[ashish_vikas]

please provide sufficient information.

Also Run Update stats for this table with BRtools.

thanks

ashish

[fidel_vales]

>

> please provide sufficient information.
> 
> Also Run Update stats for this table with BRtools.
> 
> thanks
> ashish

this is a good example about "guessing"

There is not enough information as he implies with the first phrase, but nevertheless he is asking to update statistics 😛

[stefan_koehler]

Hello Jean-Pascal,

too less information .. if you want us to look at your problem.

Please collect the needed information with the procedure provided in SAPnote #1257075, upload the output to a hoster and post the link here.

Regards

Stefan

[fidel_vales]

Hi,

The information provided is not enough to answer your question.

We do not know the query

We do not know your Oracle version not what optimizer merge fix is installed (which affect the CBO decisions)

We do not know if your parameters (which affect the CBO decisions)

We do not know ALL indexes in your table (which affect the CBO decisions)

We do not know the statistics of those indexes (which affect the CBO decisions)

probably I'm forgetting something.

The best would be that you open a message at SAP and someone can connect to your system and review the previous information.

you can also look at the note 212986 "Required data for processing CBO/RBO problems" to get an idea of the previous.

Another option would be to "GUESS", but I do not like it

http://www.battleagainstanyguess.com/

http://www.pythian.com/news/9219/hotsos-symposium-2010-battle-against-any-guess-is-won/