SHARED_CONTEXT_SENSITIVE VPD policy type

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

 

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.