Impact of Oracle Param QUERY_REWRITE_ENABLED on BW

[sap_cohort]

Hi, can anyone tell me what the impact of having the ORACLE Parameter QUERY_REWRITE_ENABLED set to TRUE is? I'm looking for the answer from a BW perspective.

I know that SAP Recommends it be set to FALSE for BW but for now we have it set to TRUE in our Production System and we are not experiencing any issues.

Thanks!

[former_member204746 · Accepted solution]

answer available at http://lmgtfy.com/?q=SAP+QUERY_REWRITE_ENABLED

[former_member185954 · Accepted solution]

Ha ha.. Eric that was very funny, sorry couldnt help posting a reply.

Regards,

Siddhesh

[sap_cohort · Accepted solution]

You impress us with your google skills. You are a moderator, but it seems you didn't really add any value with your moderated response.

I did look into Google prior and did not find a reasonable answer to the question from a BW Context.

[lbreddemann · Accepted solution]

>

> You impress us with your google skills.   You are a moderator, but it seems you didn't really add any value with your moderated response.  
> I did look into Google prior and did not find a reasonable answer to the question from a BW Context.

I agree with Eric in so far that the required information is available and just need to be read.

Anyhow, this is not the kind of super-easy question, so I'm going to answer this for now but briefly.

What should QUERY_REWRITE_ENABLED do at all? What it is useful for?

According to the Oracle documentation [here|http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/qrbasic.htm#i1008297] it's used to allow Oracle to change your query in a way that it uses precomputed resultset, a.k.a. materialized views.

The idea behind that is simple:

In BI systems you typically load data once and query it often the same data.

Thus it will safe time to pre-calculate result sets for specific given query parameters.

To the SAP BI follks this should sound very familiar.

This because there is a similar feature build right into SAP BI - it's called Aggregates!

Whether or not these aggregates are used for your BI query is determined by the OLAP processor in SAP BI, before anything is send to the database.

Since the aggregates are fully integrated into the warehouse management infrastructure they can be automatically kept up-to-date when you load new data and make it available to reporting.

Therefore, there's no need for the Oracle materialized views with SAP BI.

In turn, we don't need any of the query rewrite functionality provided by the parameter setting.

Unfortunately the Oracle Optimizer is a beast even without the query rewrite features - so turning the unneeded functions off is a rather defensive measure to keep things simpler.

Hope this explanation is what you were looking for.

regards,

Lars

[sap_cohort · Accepted solution]

Thanks Lars for the information... This adds the BW Context I was looking for and not a pointer to a Google link.

Greatly appreciated.

[former_member204746 · Accepted solution]

but it seems you didn't really add any value with your moderated response.

you are right, I am very good at googling. Maybe you should too! I could have locked the topic but I was "kind" enough to ask you to search... in a funny way.

I am glad you appreciated it.

[sap_cohort · Accepted solution]

Hi Lars, I agree with you that the question I presented

 is not the kind of super-easy question

. This is why people come to SDN. Even with the information available in Google (as I checked for) I would not have been able to understand all of the information in the experienced context you have presented it.

So your efforts are greatly appreciated by all SDN. Keep up the good work.

Thanks again,