On Day 2 of AIOUG, I discussed on Library Cache related latches and some causes of the contention on these latches. One such contention is for Row Cache Object latches that protects the Data Dictionary loaded in the Shared Pool. Dictionary Cache is one of the component of a Shared Pool and all the definitions, required to parse a Cursor, is stored in this cache, in the form of a ROW, thus is protected by Row Cache Object Latches.
The Issue
Almost a year back, I was involved in a major performance issue caused due to “Latch: row cache objects” contention. This was a 2 Node RAC setup running 10.2.0.3 database. The issue was normally reported during the peak hour with CPU Utilization above 95%. During this slowness window, there was a major drip in Transaction per Second and this resulted in an increase in pending volume to be entered into the system.
As mentioned earlier, as “Row Cache Object” latches protect Dictionary Cache information, contention on these latches needs investigating the sub-component of the Dictionary Cache and this can be done thru AWR reports. “Dictionary Cache Stats” section in AWR gives the Get Requests, PCT Miss and other required information for each of the sub-component of Dictionary Cache. This section revealed that the GET REQUESTS on DC_USERS was very high. Another alarming statistics was huge version_counts for the application queries. Since, the number of get requests on DC_Users seemed to be abnormally high, the focus was to know for the potential causes of these high numbers and the one related to this application or database. Further investigation drilled down the issue to Virtual Private Database (VPD). The customer here had implemented VPD and this implementation caused the contention on these latches.
Does it mean VPD is an Issue ?
VPD is a very useful feature as it provides an easy and manageable means of implementing row level security, but there are certain implementation portions, that needs to be considered based on the requirement, and it is this piece, if improperly implemented, can cause a major performance issue. In a nutshell, it is not VPD but the way it was being implemented that caused a major issue at the customer site. We will cover this piece of problematic implementation in this blog, with a test case, that will help you to test this on your test system and validate the results.
A brief explanation of VPD
VPD, also called as Row Level Security or Fine Grained Access Control, is a feature, introduced in Oracle 8i, that allows you to define security policies on tables by way of a function that implies a restriction on the rows that a user can SELECT or UPDATE or INSERT or DELETE, based on the privileges defined for that user. For example, assuming a following hierarchy :
Employee S is from South India Region, N from North India Region, W from West India Region, E from East India Region, I from All India Region and finally, APAC for Asia Pacific. With this herierchy, a requirement like :
- User S can View and Manipulate all the rows from South India Region, and,
- User N can View and Manipulate all the rows from North India Region, and,
- User W can View and Manipulate all the rows from West India Region, and,
- User E can View and Manipulate all the rows from East India Region, and,
- User I can View and Manipulate all the rows from 1 to 4 i.e.South, North, West and East India Region, while,
- User A can View and Manipulate all the rows from 1 to 5, including other countries in Asia Pacific Region.
These restrictions can be implemented by using VPD without much of an effort. Application does not require any changes for these implementations, as these restrictions are applied in the background at the runtime. There are three basic components of VPD. These are :
Policy : A declarative command that determines when and how to apply the policy i.e. during Selects, Insertions, Deletion, Updation or combination of any or all of these.
Policy Function : A Pl/SQL function that is called whenever the condition specified in the policy are met.
Predicate : A string that is generated by the policy function, and the applied to the users’ sql statements. This is an additional Filter predicate generated by the policy function and appended to the sql statement executed by the user, at the runtime.
Once these three are defined as a policy, then this policy can be attached to the tables that requires Security to be implemented. While Policy, Policy Function and Predicate are three main building blocks of VPD, there is another component, that is mostly ignored, called as Policy Type. In most of the cases, this is not defined, which means, it is left as default. If not defined, it defaults to a value depending on the database version and therefore, should not be left undefined. Starting Oracle 10g, the policy type defaults to DYNAMIC. While there are other policy types : SHARED Static, CONTEXT-SENSITIVE and SHARED Context Sensitive. The implementation of each of these depend upon the requirement.
Back to the Issue
In this case, it was the implementation of VPD that caused huge Version Count and both of these had a major impact on the performance due to heavy latch contention. Again, it was not the VPD that caused multiple version counts, but the implementation. The Application team, in this case, while implementing VPD, left POLICY_TYPE to default and this means DYNAMIC. DYNAMIC policy_type implies, dynamic builtup of the predicates at runtime, based on the policy function. The issue with Dynamic policy_type is that with each row processed by the query, the policy function is applied, predicate is evaluated and validated whether the row is to be fetched to the user. This means, the query goes through parse-execute-fetch phase for each and every row processed by the query and this significantly increases a Parse Overhead.
Based on the customer requirement, a better option would have been either Context-Sensitive or Shared Context-Sensitive, that internally implements Dynamic predicate generation, at runtime, but goes through the Parse phase only when the Context of the User Session changes and not for each and every row processed. Further, in this customer case, just changing the Policy_type from Dynamic to Shared Context-Sensitive did not help, as there was a major design flaw in the policy function. Lets walk thru a test case and at the end, I will present some facts from the customer system. The test case simulates the implementation at the customer site and the change suggested, thereafter to improve the performance. The change, as depicted in the test case, is in the policy function and the change in policy_type is already changed to Shared Context-Sensitive.
TEST CASE
For this test case to work, I have created some set of statistics table that will store the latch activity. Schema VIVEK contains an application table TEST_VPD. I further create USER_X schema and already have SCOTT and OE, as an application user schemas, ITDBA schema, that contains Security Table. This table defines the security policies for each application users. All of the VPD implementations pre-requisites are created in ITDBA schema.
Once the pre-requisites are created, we will execute certain queries, against TEST_VPD, from VIVEK : access to all the rows, OE Schema : access to rows pertaining to rcc_num=10, SCOTT Schema : access to rows pertaining to rcc_num=20 and USER_X Schema : access to rows pertaining to rcc_num=30. All these access restrictions are defined in EMP_SEC table under ITDBA Schema and the predicates are applied, at run time, by way of Policy Function GF_GLOBAL_POLICY_OPT_RSTRCT_VW, again under ITDBA Schema. Once these are done, we will run certain queries against our Statistics table and some data dictionary views to check for the performance. The policy function, GF_GLOBAL_POLICY_OPT_RSTRCT_VW, will then be changed to improve the performance and the queries against TEST_VPD and measure the performance.
connect vivek/vivek
drop table latch_stats_start;
drop table latch_stats_end;
drop table test_vpd;
drop user user_x cascade;
drop user itdba cascade;
drop public synonym as_test_vpd;
drop context rcc_ctx;
exec dbms_random.seed(0);
create global temporary table latch_stats_start as
select name, gets from v$latch;
create global temporary table latch_stats_end as
select name, gets from v$latch;
create table test_vpd as
select dbms_random.string('U',5)||lpad(rownum,5,'0') pan,
dbms_random.string('U',20) name,
case when mod(rownum,10)=0 then 'F' else 'M' end sex,
round(dbms_random.value(10,100),0) rcc_num
from all_objects
where rownum between 1 and 10000;
create unique index pk_pan on test_vpd(pan);
exec dbms_stats.gather_table_stats(user,'test_vpd',cascade=>true,method_opt=>'for all columns size 1');
create public synonym as_test_vpd for test_vpd;
grant select on as_test_vpd to public;
create user user_x identified by vivek;
grant connect,resource to user_x;
connect sys/vivek as sysdba
create user itdba identified by vivek;
grant dba to itdba;
grant execute on dbms_rls to itdba;
connect itdba/vivek
create context rcc_ctx using ITDBA.set_rcc_ctx;
create procedure set_rcc_ctx(p_attr in varchar2, p_val1 in varchar2, p_val2 in varchar2, p_val3 in varchar2)
is
begin
dbms_session.set_context('RCC_CTX',p_attr,p_val1, p_val2, p_val3);
end;
/
create table itdba.emp_sec (user_id varchar2(20), rcc_num number);
insert into emp_sec values('SCOTT',20);
insert into emp_sec values('OE',10);
insert into emp_sec values('USER_X',30);
CREATE OR REPLACE function ITDBA.GF_GLOBAL_POLICY_OPT_RSTRCT_VW(OBJECT_SCHEMA VARCHAR2, OBJECT_NAME VARCHAR2) return varchar2 is
L_SEL_FLG VARCHAR2(3);
L_RCC_NUM NUMBER;
L_USER_ID VARCHAR2(20);
l_predicate varchar2(30);
v_Restricted_Policy varchar2(1);
begin
IF L_USER_ID IS NULL AND USER IN ('ITDBA','SYS','SYSTEM','VIVEK') THEN
RETURN NULL;
ELSE
IF L_USER_ID IS NULL THEN
L_USER_ID:=USER;
END IF;
if l_user_id in ('USER_X','SCOTT','OE') then v_Restricted_Policy:='Y'; end if;
IF v_Restricted_Policy = 'Y' THEN
select rcc_num into l_rcc_num from itdba.emp_sec
where user_id = l_user_id;
set_rcc_ctx('RCC_NUM',l_rcc_num, null, null);
l_predicate:='RCC_NUM in '||sys_context('RCC_CTX','RCC_NUM');
return l_predicate;
ELSE
select rcc_num into l_rcc_num from itdba.emp_sec
where user_id = l_user_id;
set_rcc_ctx('RCC_NUM',l_rcc_num,l_rcc_num+100,99);
l_predicate:='RCC_NUM in '||sys_context('RCC_CTX','RCC_NUM');
return l_predicate;
END IF;
END IF;
Exception
When others then
Raise_Application_Error (-20000,SQLERRM);
END;
/
begin
dbms_rls.add_policy(object_schema=>'VIVEK',
object_name=>'test_vpd',
policy_name=>'RCC_NUM_POLICY',
function_schema=>'ITDBA',
policy_function=>'GF_GLOBAL_POLICY_OPT_RSTRCT_VW',
statement_types=>'INSERT,SELECT,UPDATE,DELETE',
update_check=>true,
policy_type=>dbms_rls.SHARED_CONTEXT_SENSITIVE);
end;
/
Now, since the pre-requisite is done, we will execute a query against TEST_VPD from VIVEK, OE, SCOTT and USER_X Schemas to check the rows returned by each of these users. This will help us validate and confirm whether the policy function is applied as per our requirement.
connect vivek/vivek
column pan for a20
column name for a30
select /*+ VIVEK */ * from as_test_vpd where rcc_num=10 and rownum<=3;
select /*+ VIVEK */ * from as_test_vpd where rcc_num=20 and rownum<=3;
select /*+ VIVEK */ * from as_test_vpd where rcc_num=30 and rownum<=3;
## User OE only allowed to see 10
----------------------------------
connect oe/vivek
select /*+ VIVEK */ * from as_test_vpd where rcc_num=10 and rownum<=3;
select /*+ VIVEK */ * from as_test_vpd where rcc_num=20 and rownum<=3;
select /*+ VIVEK */ * from as_test_vpd where rcc_num=30 and rownum<=3;
## User SCOTT only allowed to see 20
-------------------------------------
connect scott/tiger
select /*+ VIVEK */ * from as_test_vpd where rcc_num=10 and rownum<=3;
select /*+ VIVEK */ * from as_test_vpd where rcc_num=20 and rownum<=3;
select /*+ VIVEK */ * from as_test_vpd where rcc_num=30 and rownum<=3;
## User USER_X only allowed to see 30
---------------------------------------
connect user_x/vivek
select /*+ VIVEK */ * from as_test_vpd where rcc_num=10 and rownum<=3;
select /*+ VIVEK */ * from as_test_vpd where rcc_num=20 and rownum<=3;
select /*+ VIVEK */ * from as_test_vpd where rcc_num=30 and rownum<=3;
Now for the performance. This is the Original Implementation.
connect vivek/vivek
alter system flush shared_pool;
connect scott/tiger
insert into vivek.latch_stats_start select name, gets from v$latch;
select /*+ VIVEK */ * from as_test_vpd where rownum<=3;
insert into vivek.latch_stats_end select name, gets from v$latch;
connect oe/vivek
select /*+ VIVEK */ * from as_test_vpd where rownum<=3;
connect user_x/vivek
select /*+ VIVEK */ * from as_test_vpd where rownum<=3;
SQL>set lines 132
SQL> set pages 1000
SQL> column sql_text for a40 wrap
SQL>
SQL> select sql_id, sql_text, executions, child_latch, version_count from v$sqlarea where sql_text like 'select /*+ VIVEK%';
SQL_ID SQL_TEXT EXECUTIONS CHILD_LATCH VERSION_COUNT
------------- ---------------------------------------- ---------- ----------- -------------
1n62sv9g6nw77 select /*+ VIVEK */ * from as_test_vpd w 3 2 3
here rownum<=3
SQL>select sql_id, sql_text, executions, child_latch, child_address from v$sql where sql_text like 'select /*+ VIVEK%';
SQL_ID SQL_TEXT EXECUTIONS CHILD_LATCH CHILD_AD
------------- ---------------------------------------- ---------- ----------- --------
1n62sv9g6nw77 select /*+ VIVEK */ * from as_test_vpd w 1 2 69EAE56C
here rownum<=3
1n62sv9g6nw77 select /*+ VIVEK */ * from as_test_vpd w 1 2 69065194
here rownum<=3
1n62sv9g6nw77 select /*+ VIVEK */ * from as_test_vpd w 1 2 68FEDAE8
here rownum<=3
SQL>column predicate for a30
SQL>select sql_id, ADDRESS, PREDICATE from v$vpd_policy where sql_id='1n62sv9g6nw77';
SQL_ID ADDRESS PREDICATE
------------- -------- ------------------------------
1n62sv9g6nw77 69EAE56C RCC_NUM in 20
1n62sv9g6nw77 69065194 RCC_NUM in 10
1n62sv9g6nw77 68FEDAE8 RCC_NUM in 30
When the Query was executed from different application users, three in our case, the version_count of the query increased as well. The dynamic performance view V$VPD_POLICY shows the runtime plan, which is Literal in this case and causes a query to be hard parsed. Below mentioned query can be executed to check for the latch statistics.
column name for a30
set lines 200
set pages 1000
select * from (select a.name, b.gets end_gets, a.gets Start_gets, b.gets-a.gets
from vivek.latch_stats_start a,
vivek.latch_stats_end b
where b.name = a.name
order by 4 desc)
where rownum<=10;
MODIFICATION
The code below shows the modification to the function that is applied to the policy. Look for the l_predicate value at the end that is finally returned as an Output to the function.
connect vivek/vivek
CREATE OR REPLACE function ITDBA.GF_GLOBAL_POLICY_OPT_RSTRCT_VW(OBJECT_SCHEMA VARCHAR2, OBJECT_NAME VARCHAR2) return varchar2 is
L_SEL_FLG VARCHAR2(3);
L_RCC_NUM NUMBER;
L_USER_ID VARCHAR2(20);
l_predicate varchar2(60);
v_Restricted_Policy varchar2(1);
begin
IF L_USER_ID IS NULL AND USER IN ('ITDBA','SYS','SYSTEM','VIVEK') THEN
RETURN NULL;
ELSE
IF L_USER_ID IS NULL THEN
L_USER_ID:=USER;
END IF;
if l_user_id in ('USER_X','SCOTT','OE') then v_Restricted_Policy:='Y'; end if;
IF v_Restricted_Policy = 'Y' THEN
select rcc_num into l_rcc_num from itdba.emp_sec
where user_id = l_user_id;
set_rcc_ctx('RCC_NUM',l_rcc_num, null, null);
l_predicate := 'RCC_NUM = sys_context (''RCC_CTX'', ''RCC_NUM'')';
return l_predicate;
ELSE
select rcc_num into l_rcc_num from itdba.emp_sec
where user_id = l_user_id;
set_rcc_ctx('RCC_NUM',l_rcc_num,l_rcc_num+100,99);
l_predicate := 'RCC_NUM = sys_context (''RCC_CTX'', ''RCC_NUM'')';
return l_predicate;
END IF;
END IF;
Exception
When others then
Raise_Application_Error (-20000,SQLERRM);
END;
/
Once the change is done, we will execute the query on TEST_VPD from the three application users (USER_X, OE and SCOTT), and compare the latch statistics and version_count.
connect vivek/vivek
alter system flush shared_pool;
connect scott/tiger
insert into vivek.latch_stats_start select name, gets from v$latch;
select /*+ VIVEK */ * from as_test_vpd where rownum<=3;
insert into vivek.latch_stats_end select name, gets from v$latch;
connect oe/vivek
select /*+ VIVEK */ * from as_test_vpd where rownum<=3;
connect user_x/vivek
select /*+ VIVEK */ * from as_test_vpd where rownum<=3;
connect vivek/vivek
Connected.
SQL> set lines 132
SQL> set pages 1000
SQL> column sql_text for a40 wrap
SQL>
SQL> select sql_id, sql_text, executions, child_latch, version_count from v$sqlarea where sql_text like 'select /*+ VIVEK%';
SQL_ID SQL_TEXT EXECUTIONS CHILD_LATCH VERSION_COUNT
------------- ---------------------------------------- ---------- ----------- -------------
1n62sv9g6nw77 select /*+ VIVEK */ * from as_test_vpd w 3 2 1
here rownum<=3
SQL>select sql_id, sql_text, executions, child_latch, child_address from v$sql where sql_text like 'select /*+ VIVEK%';
SQL_ID SQL_TEXT EXECUTIONS CHILD_LATCH CHILD_AD
------------- ---------------------------------------- ---------- ----------- --------
1n62sv9g6nw77 select /*+ VIVEK */ * from as_test_vpd w 3 2 68BEE27C
here rownum <=3
SQL>column predicate for a30
SQL>select sql_id, ADDRESS, PREDICATE from v$vpd_policy where sql_id='1n62sv9g6nw77';
SQL_ID ADDRESS PREDICATE
------------- -------- ------------------------------
1n62sv9g6nw77 68BEE27C RCC_NUM = sys_context ('RCC_CT
X', 'RCC_NUM')
Improved performance can be clearly seen from the version_count, output of V$VPD_POLICY view and from the latch statistics. Once this was implemented, the customer reported a significant performance improvement, for which the real facts are shown below.
CUSTOMER MATRIX – BEFORE
TOP 5 WAITS
CUSTOMER MATRIX – AFTER
To summarize, Oracle provided features enable us to implement these with great ease. Implementing these require careful study of the requirement and proper implementation, that does not compromise with the performance.