How to clear OBIEE cursor cache (presentation server)

Question

I'm having an issue with a prompt in OBIEE 10g, such that it displays old database value due to the prompt query being serviced from cursor cache (presentation service). For example, if the prompt drop-down shows 1 value initially since there is 1 database row and when i delete this row from database, the prompt still shows the same database value unless i manually delete the cursor cache through analytics Setting > Administration > Manage sessions > clear cache/cursors

Tried checking OBIEE presentation service config file instanceconfig.xml, however there is no such parameter to permanently disable this cache. I referenced the following link, OBIEE 10G/11G - Presentation Service (Query|Result|Cursor) Cache

Resetting these parameters didn't seem to have any impact on the cursor cache, these are still getting generated and are not cleared after the timeouts set. (I have restarted the OBIEE services after changing these parameters). Am I missing something here.

Would appreciate any pointers to get this done i.e. getting cursor cache cleared/disabled without manual intervention as mentioned above (through Settings > Administration).

Answer 1

At some point I also faced that issue. The presentation cache in OBIEE is a bit shady sometimes.

What I did is to add some dummy comparison on the query of the prompt, involving sysdate with enough precision so it makes each query different to the cache.

It's a bit shabby, but at least you don't need any manual intervention... Maybe it can help you.

Good luck!

Answer 2

You may see this issue if using a Presentation variable as well, rather than a Prompt built using a SQL query.

The problem may be due to shared Presentation Services Query Cache, which means that even when the user logs out, the query cursor cache is still being shared by other users, so it does not refresh the new data after the user logs in again.

The cache file is in ORACLE_INSTANCE/tmp/OracleBIPresentationServices/coreapplication_obipsn/obis_temp

See this document for more detail.

You can configure the Virtual Private Database option in the repository physical database object and mark session variables as Security Sensitive in the repository to make the query cache not shared among users. See this documentation for more detail.