We have a performance issue in our OBIEE environment.
We created an analysis that displays Sales per week. The physical query is something like:
from fact_sales, dim_time
where fact_sales.key = dim_time.key
and WEEK = 30;
(This is of course a simplified version of the query)
When we first run this query, it takes 30 minutes to run. After running the query advisor in our Oracle 11gr2 database, we are able to change the SQL plan and execute the same query in 10 seconds.
Now when the OBIEE user changes the filter in the analysis to WEEK 31, the same query is issued, but with a different where clause:
and WEEK = 31;
The DB, instead of using our previous SQL plan, will create a new SQL ID and a new SQL plan that takes again 30 minutes to run.
How can I make the DB understand that it is the same query, only with a different value in the where clause?