Adventures with VPD I: Result Cache

Like partitioning and other major features, implementing VPD on a “mature”* database that has been “evolving”* over many years can be nowhere near as simple as designing such a solution from scratch. (* “Mature” databases have not necessarily “evolved” in a way that you might recommend. Best practice might not apply.)

Anyway, I’ve been intending to write a case study of my current VPD implementation, dealing with some detail of the buy versus build decision of VPD versus OLS and the subsequent challenges and incompatibilities with various features. As usual, that article awaits but I wanted to kick off a mini-series on the various difficulties experienced (and this series might then help the coming together of a single article on the whole experience).

Challenge I
The challenge illustrated here is VPD + Result Cache.

Spoiler: For those who don’t want to read the whole article (it’s a bit long and the formatting which doesn’t help), the two work together but beware the subtle differences in behaviour between the SQL and PL/SQL Result Cache – read the documentation for each.

Quick Background
First a bit of background information which may help put these snippets of info into some sort of context.

As mentioned, this is a database that has been around for quite a few years now.

There has been a simple requirement to restrict access such that:

Users in the US can only see US data.

Users in the Rest of the World (ROW) cannot see US data.

VPD, OLS and a separate database were the options considered with VPD considered the only practical solution for one reason or another.

Good stuff. Spotted the mistake? Last time I checked, Citroen was French – should be labelled ROW. I’ll leave it in and update it after we’ve done some result cache queries just to show that that all works.

Step 5: Let’s take a look at the SQL result cache using the result_cache hint.Step 5a: We’ll go with APP_USER_US first.

Parameterized cache results can be reused if the query is equivalent and the parameter values are the same.

So what of the PL/SQL Function Result Cache?

Step 8: Create a result cache function to demonstrate (this is an artificial and simplified function – it demos what I want to demo, it’s derived from a real-world implementation but I would advocate using the result cache for this purpose)

Wrong! And just what I wanted to demo. By implementing a result function like this, I have bypassed my VPD and rendered results from this function unpredictable depending on who ran the results which were cached.

So, with the PL/SQL function RESULT_CACHE directive you have to be careful, not necessarily anymore so than certain other features but just something to be aware of.

Before I knew the solution, but after I had these results, what happened when I combined VPD with such a Result Cache mis-implementation was not a massive surprise, but I had expected that either an error message would be raised, similiar to Materialized Views or the MERGE statement (forward references to future articles on the challenges of combining VPD with those two), or perhaps that the RESULT_CACHE directive would be ignored.

Making Result-Cached Functions Handle Session-Specific Settings
The PL/SQL function result-caching feature does not automatically handle dependence on session-specific application contexts. If you must cache the results of a function that depends on session-specific application contexts, you must pass the application context to the function as a parameter. You can give the parameter a default value, so that not every user must specify it.

The same advice applies to other session specific settings, e.g. NLS_DATE_FORMAT etc.

Step 10: Make safe the result_cache implementation of APP_DATA.CARS_PKG.

Moral
The moral of the story is simple in this case – RTFM.
This time everything I needed was there in the docs. I hadn’t picked up on it fully when I implemented the result cache procs when VPD wasn’t in the picture and I didn’t review the information up front when looking at the VPD solution.
Caveat Developer.