The SHARED_CONTEXT_SENSITIVE VPD policy type seems like a good option for VPD predicates that don’t need to change during a user’s session.
I’m working on a system with VPD policies in place to cause users to see only certain parts of the data based on their job role. But, we have configured these policies with the default policy type which is DYNAMIC. We are seeing performance issues with parsing hanging up on library cache lock waits and various web sites and Oracle support have pointed to our use of DYNAMIC type policies as a potential source. But, there are five different policy types and I wanted to understand the difference between the types and which would be best for our situation. Based on my evaluation SHARED_CONTEXT_SENSITIVE looks best because it will result in the fewest calls to the predicate function and the fewest parses. The only down side appears to be that if things change behind the scenes while you are logged in you will not see the new predicates until you log out and in again.
I put together a test case to try out each of the different policy types and summarized the results in a spreadsheet. I created a policy function that would create a new predicate each time it was run if you waited at least a second between each run. Then I associated the function with two identical tables which were copies of DBA_OBJECTS. I did this for each of the five policy types: DYNAMIC, STATIC, SHARED_STATIC, CONTEXT_SENSITIVE, SHARED_CONTEXT_SENSITIVE. Then I ran a count(*) query twice on each table. Also, I created a second user and ran the same tests under the second user to see if logging in as a second user changed whether a new predicate was used or if the query was reparsed. Here is a summary of the results:
| Policy type | user | parses | executions | predicates |
| DYNAMIC | TEST | 5 | 13 | 4 |
| DYNAMIC | TEST2 | 4 | 14 | 4 |
| STATIC | TEST | 2 | 7 | 2 |
| STATIC | TEST2 | 2 | 5 | 2 |
| SHARED_STATIC | TEST | 2 | 6 | 1 |
| SHARED_STATIC | TEST2 | 2 | 5 | 0 |
| CONTEXT_SENSITIVE | TEST | 4 | 9 | 4 |
| CONTEXT_SENSITIVE | TEST2 | 4 | 9 | 4 |
| SHARED_CONTEXT_SENSITIVE | TEST | 2 | 6 | 1 |
| SHARED_CONTEXT_SENSITIVE | TEST2 | 2 | 6 | 1 |
DYNAMIC had the most parses and executions of the policy function. SHARED_STATIC had the fewest, but the predicate didn’t change when we logged in as a new user. CONTEXT_SENSITIVE was better than DYNAMIC on policy function executions but not on parses. SHARED_CONTEXT_SENSITIVE came out best with fewer parses and policy function executions, but the predicate still changed when we logged in as a new user.
– Bobby
