CODEWITHSUNDEEP
oracle-databasevariablesoracle11gbindobiee
LordMax
LordMax

Reputation: 176

OBIEE and Bind Variables

I'm in the need to rewrite some query made by OBIEE

My need is to change all the literal value in bind variable but I didn't find how to use bind variables

Someone can help me?

Thanks

Upvotes: 0

Views: 1520

Answers (1)

Mark P.
Mark P.

Reputation: 1827

First, a statement:

If you are utilizing ADF (Oracle Application Development Framework) with OBIEE, there is a setting in the ADF layer which allows us to specify how the View Criteria and the WHERE clauses used in the queries fired on the VOs should be handled.

By default the setting useBindVarsForViewCriteriaLiterals is set to "False" in adf-config.xml. If this setting is False, then ADF will generate SQLs with literals for the view criteria and this can cause contention in the shared pool area of the database. If we change the setting in adf-config.xml to "True" ADF generates SQL with bind variables for all view criterias.

However this setting should not be changed for BI (OBIEE), as BI does not support Bind variables in the queries. If we see any queries (or its related logs) which are using Bind variables in OBIEE queries/reports, then its possible that it is due to the above setting.

For BI, this setting should be left to the default value, i.e. "False"

Quick answer: It would seem that OBIEE is not capable of using bind variables in place of literals.

Now, the reasoning behind the restriction on bind variables:

In a data warehouses, instead of running say 1,000 statements per second, they do something like take an average of 100 seconds to run a single query. In these systems, the queries are few but big (they ask large questions). Here, the overhead of the parse time is a tiny fraction of the overall execution time. Even if you have thousands of users, they are not waiting behind each other to parse queries, but rather are waiting for the queries to finish getting the answer.

In these systems, using bind variables may be counterproductive. Here, the runtimes for the queries are lengthy-in seconds, minutes, hours, or more. The goal is to get the best query optimization plan possible to reduce the runtime, not to execute as many of OLTP, one-tenth-second queries as possible. Since the optimizer's goal is different, the rules change.

Sometimes using a bind variable forces the optimizer to come up with the best generic plan, which actually might be the worst plan for the specific query. In a system where the queries take considerable time to execute, bind variables remove information the optimizer could have used to come up with a superior plan. In fact, some data warehouse-specific features are defeated by using bind variables. For example, Oracle supports a star transformation feature for data warehouses that can greatly reduce the time a query takes. However, one restriction that precludes star transformation is having queries that contain bind variables.

Upvotes: 1

Related Questions