CACHE query is not used when use Prompted Values

Camilo Flores

Hello

I have a query which is running very slow and I am trying to use the Cache feature

If i run the report more than once it hits teh cache correctly

So, i want to trun the report once in teh morning and then make good use of the cache ( i wouold like to do it with Agents but apaprently does not work with the dates filters, but will resolve that later)

The poblem now is , when i set prompt filters , if i do that it does not hit the cache and creates a new entry in the cache every time i set a prompt

I tried to fix this adding the prompted values into the select clause without filter,  in order to select all th combinations of thata with all the combination of possible filters

However it still does not work , creatres a new entry for  no selection , and for each prompt selection

Is that expected behavior? Is there a way to prevent this?  Am i missing some configuration?

The idea is not to have to run the report with all the combinations of values in the promptts to make it run faster

Thanks

Camilo

ArijitC

One option could be using Table Prompt instead of Dashboard Prompt.

Thanks

Arijit

Hendrik Schmidt

Hi,

for Analysis: OBIEE 10g: Reasons Why The Cache Is Not Used  (Doc ID 1067363.1)

regards

Robert Angel

Caching is no small question, I would recommend getting your head around; -

https://docs.oracle.com/middleware/12213/biee/BIESG/GUID-94B30F1B-69F3-49DC-91D0-59C2CAFEC6A6.htm#query_cache_architectu…

And then come back for specific areas of clarification, once you have ascertained the current aspects of your current cache set up.

Is your report too large to remove the filters and run using an agent to pre-seed your cache?

Also check, rpd what cache settings are there. Answers advanced tab ditto.

Note that even then cache is not an infinite resource, which is why I am recommending that you review your caching strategy, this report will be one of many...

Christian Berg-0racle

Additional to Robert's and Hendrik's comment you man want to look at https://de.slideshare.net/ChristianBerg8/back2-basics-a-day-in-the-life-of-an-oracle-analytics-query

Slides 42ff

Is it a SELECT column hit or subset? WHERE clause hit or subset?

Most likely there's some weirdness built into the objects which mes up the actual query. So check the acutal queries from the log.

And to re-iterate the most important point once more: rethink your inital problem again. Cache does never "solve performance issues". It covers them up. The real problem isn't even touched by it.

Joel Acha

OBIEE query caching is an interesting topic with many trade offs whichever side of the fence you're standing on. Without delving into that, a solution (although not ideal) is to seed the logical query of your report without any filters to ensure that any report run with filters will run from this cache. Just seen @Robert Angel's reply which suggests the same thing.

Camilo Flores

Hello Robert

One clarification, the cache is being populated, but ot used

Camilo

Camilo Flores

Joel

Thas is exactly what I am trying to do, the cache is populated correctly, without filtering by prompts, but the query cached is not used

Camilo

Joel Acha

Hi @Camilo Flores

Extract the Logical SQL from the advanced tab of your report and copy and just remove the filters (from the logical SQL) and then use this to seed your report to cache.

Camilo Flores

I just campared the cached query and the new query executed and the only difference is the  promped value (which is included in both cases in the select statement ), I still don't understand why it does not work. It does work when the select matches exactly the cached query

Cached Query

SELECT a , b , c FROM (

SELECT

   "xx"."xx"."Gls Nacionality" s_4,

.....

FROM "Proyecto X"

WHERE

((a  <> -1)

AND (year  IN (2015, 2016, 2017)))

) djm ORDER BY 1

;

New Query

SELECT a , b , c FROM (

SELECT

   "xx"."xx"."Gls Nacionalidad" s_4,

.....

FROM "Proyecto X"

WHERE

(a  <> -1)

AND "xx"."xx"."Gls Nacionality" s_4,

AND (year  IN (2015, 2016, 2017)))

) djm ORDER BY 1

Camilo Flores

This is what my last answer should say

just campared the cached query and the new query executed and the only difference is the  promped value (which is included in both cases in the select statement ), I still don't understand why it does not work. It does work when the select matches exactly the cached query

Cached Query

SELECT a , b , c FROM (

SELECT

   "xx"."xx"."Gls Nacionality" s_4,

.....

FROM "Proyecto X"

WHERE

((a  <> -1)

AND (year  IN (2015, 2016, 2017)))

) djm ORDER BY 1

;

New Query

SELECT a , b , c FROM (

SELECT

   "xx"."xx"."Gls Nacionality" s_4,

.....

FROM "Proyecto X"

WHERE

(a  <> -1)

AND "xx"."xx"."Gls Nacionality" = 'FOREIGN',

AND (year  IN (2015, 2016, 2017)))

) djm ORDER BY 1

Joel Acha

As mentioned before, remove all of the the WHERE clause from the cached query.

This is what you should be seeding:

SELECT a , b , c FROM (

SELECT

   "xx"."xx"."Gls Nacionality" s_4,

.....

FROM "Proyecto X"

Camilo Flores

Removed all the filters of the firest query and yet the second query (filtered) does not hit the cache

Query Cached:

SELECT s_0, s_1, s_2, s_3, s_4 FROM (

SELECT

   0 s_0,

   DM.T1.F1 s_1,

   DM.T2.F2 s_2,

   DM.T3.F3 s_3,

   REPORT_AGGREGATE(DM.T3.F3 BY ) s_4

FROM DM

) djm ORDER BY 1, 2 ASC NULLS LAST, 3 ASC NULLS LAST

FETCH FIRST 65001 ROWS ONLY

;

Filtered Query

SELECT s_0, s_1, s_2, s_3, s_4 FROM (

SELECT

   0 s_0,

   DM.T1.F1 s_1,

   DM.T2.F2 s_2,

   DM.T3.F3 s_3,

   REPORT_AGGREGATE(DM.T3.F3 BY ) s_4

FROM DM

WHERE

(T2.F2 = 'F')

) djm ORDER BY 1, 2 ASC NULLS LAST, 3 ASC NULLS LAST

FETCH FIRST 65001 ROWS ONLY

;

Joel Acha

Try caching the query without a filter and order by:

SELECT s_0, s_1, s_2, s_3, s_4 FROM (

SELECT

   0 s_0,

   DM.T1.F1 s_1,

   DM.T2.F2 s_2,

   DM.T3.F3 s_3,

   REPORT_AGGREGATE(DM.T3.F3 BY ) s_4

Camilo Flores

Hello Joel

I have tested this using another  fact column, which does not use REPORT_AGGREGATE, and it worked , apparently there is an issue with hit cache with  REPORT_AGGREGATTE and filters

If the query does not have Report_agregates it works even when the query is a subset of the cached query

if the report has Report_agreggate and the SQL is exactly the same it works

However i haven't found documentation abut that incompatibilty, i base my conclusions only on my tests

Camilo

Joel Acha

That was going to be my next suggestion - to remove the REPORT_AGGREGATE. If the Aggregation Rule of a measure is set to

Server Complex Aggregate then OBIEE will do a REPORT_AGGREGATE. Is this required on your report by the way?

Camilo Flores

Yes, It is required, that is the data that teh report needs to display

Camilo Flores

It is not a Server Copmplex Aggregate , only a Count (Distinct()) in the RPD, and Default(Sum) in the Presentation Server