Query runs fast when DB optimizer is disabled for them

[Former Member]

Hello,

Our customer is facing issue while execution of few quires in BW system.

RSRT is the transaction they run to run the following quires

When they run the query with default option (DB Optimizer Enabled) the query takes long time to complete.

While when they run the query in debug mode by removing the DB Optimizer option, the query runs fast for them.

From DB reports we don't see any issue but they are claiming issue with DB as the query runs fast without DB Optimizer option.

Can any one help here?

Regards

Manoj

[stefan_koehler]

Hi Manoj,

> From DB reports we don't see any issue but they are claiming issue with DB as the query runs fast without DB Optimizer option.

Not quite sure what you mean with "DB reports", but the query is run with the "NO_QUERY_TRANSFORMATION" hint, if they check "Deactivate DB Optimizer Functions" in RSRT.

Basically you "just" need to figure out which (logical) query transformation is causing the trouble, if this query is fast without query transformation, but slow with query transformation. There are a lot of query transformations in Oracle and you need to check both execution plans very carefully (and maybe need a CBO trace as well) to identify and fix it.

Regards

Stefan

[Former Member]

Thanks a lot Stefan

The information given is really great. Would you please guide us how to analyze the plans further and see what query transformation is creating issue?

Appreciate your great help

Regards

Manoj

[stefan_koehler]

Hi Manoj,

> Would you please guide us how to analyze the plans further and see what query transformation is creating issue?

I am sorry, but you should really open a SAP SR (and hope that someone who knows what he/she is doing gets the SR), if you don't know how to read and interpret execution plans. This topic is way too complex to explain it by a few lines via forum.

Regards

Stefan

[Former Member]

Thanks Stefan,

We had already raised SAP SR, but you know all the time they just ask us to check the DB parameters, DB health check etc.

And we are good in terms of DB parameters, health check etc.

Even no issue as per our ADDM AWR reports.

We tried explaining them many times but seems the message is not reaching to right person.

Regards

Manoj

[stefan_koehler]

Hi Manoj,

> We had already raised SAP SR, but you know all the time they just ask us to check the DB parameters, DB health check etc.

... well (unfortunately) this is how SAP support works most of the time nowadays in the "SAP on Oracle" area. Good old times with or are gone. Basically you only have two options: Wait and hope for SAP support or involve a third party consultancy / independent consultant, if this is a critical issue / query.

Regards

Stefan