0 Replies Latest reply on Dec 22, 2015 3:44 PM by 2981486

    Oracle DB is creating new SQL Plan for each OBIEE analysis execution

    2981486

      Hello,

      We have a performance issue in our OBIEE environment.

      We created an analysis that displays Sales per week. The physical query is something like:

       

      select sales

      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:

       

      select sales

      from fact_sales, dim_time

      where fact_sales.key = dim_time.key

      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?

      Thank you!

      Joao