How Does Automatic Caching Work with Row-Level Security in Oracle Analytics DV?

Dhaval Parikh Stantec

Oracle Analytics DV automatically caches the most frequently queried data to improve performance. However, if a dataset is sourced from a subject area with row-level security (RLS) applied, the query results can vary for each user.

Given that the dataset output differs based on user access, does the automatic caching mechanism still result in cache hits? Or does every user’s query create a unique cache entry, making caching ineffective?

Are there any best practices to optimize caching when RLS is involved?

Appreciate any insights from the community!

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/24674/how-does-automatic-caching-work-with-row-level-security-in-oracle-analytics-dv

Hi @Dhaval Parikh Stantec,

Please take a look into the following document especially 'About Cache Hits' which might be useful to understand the various scenarios.

https://docs.oracle.com/en/cloud/paas/analytics-cloud/acabi/manage-query-caching.html#GUID-91F896A4-79C1-47C5-84B9-C43DB9ED3C3E

Hope this help.

Cheers,

Bhaskar Konar

Hi @Dhaval Parikh Stantec,

Additionally it depends on how the way 'Row Level Security' has been configured using semantic model or database.

If row-level security has been implemented in the semantic model, it provide some benefits like:
a) All the users share cache for better performance.
b) All the users share the same database connection pool for better performance.
etc.

If it is done via, database using Virtual Private Database (VPD) feature:
Oracle Analytics query engine matches a list of security-sensitive variables to each prospective cache hit.
Cache hits will only occur for those cache entries which included and matched all security-sensitive variables.

Please take a look into the following Oracle Note for more details.

https://docs.oracle.com/en/cloud/paas/analytics-cloud/acmdg/work-row-level-security.html#GUID-108274FC-57F4-42BE-93D2-CBD52CBDF7DD

Hope this help.

Cheers,

Dhaval Parikh Stantec

@Bhaskar Konar Thanks for sharing this. I will take a look at the shared documents.