Home » RDBMS Server » Performance Tuning » Poor performance of query with VPD policy (10gR1, 10.1.0.3 4 node RAC on AIX 5.2 64bit)
| Poor performance of query with VPD policy [message #307968] |
Thu, 20 March 2008 11:07  |
kwalz58
Messages: 13
Registered: February 2006
Location: SC
|
Junior Member |
|
|
I have a rather simple query that is performing in around second or less as long as the user is exempted from our column masking VPD policy. Any time the policy is in place the query response goes to 5+ min which is not acceptable for the users.
We are operating under a corporate and regulatory requirement that sensitive column data access be tightly controlled. Thus the VPD setup. As long as the user running the query is granted EXEMPT ACCESS POLICY the query runs fine for them. This is an issue with the security policy so I am trying to work this out so we can be compliant and allow the user to do what they need.
First the environment. As noted this is a 4 node RAC - Oracle 10.1.0.3 on AIX 5.2 64bit. There are three schemas of import. EADV which contains the online application objects and data, DBASEC which holds the context manager and VPD predicate functions, and EADV_RPT which contains the reporting objects.
The tables involved: ( I have simplified the creation statements to include only the relevant columns )
Customer table - 4,570,647 rows
CREATE TABLE eadv.customer
(
custnbr NUMBER(15) NOT NULL,
homelocnbr NUMBER NOT NULL,
businessunitcd VARCHAR2(5 BYTE) NOT NULL,
firstnm VARCHAR2(35 BYTE) NOT NULL,
lastnm VARCHAR2(35 BYTE) NOT NULL,
ssn CHAR(9 BYTE) NOT NULL,
search_lastnm VARCHAR2(35 BYTE),
search_firstnm VARCHAR2(35 BYTE)
)
TABLESPACE eadv_data_03;
ALTER TABLE eadv.customer ADD (
CONSTRAINT pk_customer
PRIMARY KEY
(custnbr)
USING INDEX
TABLESPACE eadv_index_03);
CREATE INDEX eadv.idx_c_searchname ON eadv.customer
(businessunitcd, homelocnbr, search_lastnm, search_firstnm)
LOGGING
TABLESPACE eadv_index_03
NOPARALLEL;
CREATE UNIQUE INDEX eadv.idx_uniqcustomer ON eadv.customer
(ssn, businessunitcd)
TABLESPACE eadv_index_03;
CREATE INDEX eadv.idx_c_ssn ON eadv.customer
(ssn)
TABLESPACE eadv_index_03;
CustomerAddress table - 4,599,930 rows
CREATE TABLE eadv.customeraddress
(
custnbr NUMBER(15) NOT NULL,
seqnbr INTEGER NOT NULL,
addresscd VARCHAR2(5 BYTE) NOT NULL,
addressln VARCHAR2(60 BYTE) NOT NULL,
apartmentnbr VARCHAR2(10 BYTE),
city VARCHAR2(35 BYTE) NOT NULL,
statecd CHAR(2 BYTE) NOT NULL,
postalcd VARCHAR2(9 BYTE) NOT NULL
)
TABLESPACE eadv_data_03;
ALTER TABLE eadv.customeraddress ADD (
CONSTRAINT pk_customeraddress
PRIMARY KEY
(custnbr, seqnbr)
USING INDEX
TABLESPACE eadv_index_03);
ALTER TABLE eadv.customeraddress ADD (
CONSTRAINT fk_custaddr_customer
FOREIGN KEY (custnbr)
REFERENCES eadv.customer (custnbr));
LocationPhone table - 7,968 rows
CREATE TABLE EADV.LOCATIONPHONE
(
LOCNBR INTEGER NOT NULL,
SEQNBR INTEGER NOT NULL,
PHONECD VARCHAR2(5 BYTE) NOT NULL,
PHONENBR VARCHAR2(10 BYTE) NOT NULL
)
TABLESPACE EADV_DATA_02;
ALTER TABLE EADV.LOCATIONPHONE ADD (
CONSTRAINT PK_LOCATIONPHONE
PRIMARY KEY
(LOCNBR, SEQNBR)
USING INDEX
TABLESPACE EADV_INDEX_02);
Context setup for use with the VPD:
CREATE OR REPLACE CONTEXT CTX_SEARCH
USING DBASEC.CTX_SEARCH_MGR;
CREATE OR REPLACE PACKAGE dbasec.ctx_search_mgr
AS
PROCEDURE set_ssn_search;
PROCEDURE clear_ssn_search;
END;
CREATE OR REPLACE PACKAGE BODY dbasec.ctx_search_mgr
AS
PROCEDURE set_ssn_search
AS
BEGIN
DBMS_SESSION.set_context ( namespace => 'ctx_search', ATTRIBUTE => 'ssn_search',
VALUE => 'TRUE' );
END set_ssn_search;
PROCEDURE clear_ssn_search
AS
BEGIN
DBMS_SESSION.clear_context ( namespace => 'ctx_search', ATTRIBUTE => 'ssn_search' );
END clear_ssn_search;
END ctx_search_mgr;
Predicate function and VPD policy
CREATE OR REPLACE FUNCTION dbasec.rls_eadv_ssn (
oowner IN VARCHAR2,
ojname IN VARCHAR2
)
RETURN VARCHAR2
IS
pred VARCHAR2 ( 200 );
BEGIN
pred := q'[ SYS_CONTEXT('ctx_search' , 'ssn_search') = 'TRUE' ]';
RETURN pred;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
RAISE;
END rls_eadv_ssn;
BEGIN
SYS.DBMS_RLS.add_policy ( object_schema => 'EADV',
object_name => 'CUSTOMER',
policy_name => 'VPD_CUSTOMER_SSN',
function_schema => 'DBASEC',
policy_function => 'RLS_EADV_SSN',
statement_types => 'SELECT',
policy_type => DBMS_RLS.shared_static,
long_predicate => FALSE,
sec_relevant_cols => 'SSN',
sec_relevant_cols_opt => DBMS_RLS.all_rows,
update_check => FALSE,
static_policy => FALSE,
ENABLE => TRUE
);
END;
Now to the meat of the manner. The idea is to have the user execute a procedure that sets the context for access - runs the query - then clears the context. Since the context manager execution is granted to only the schema owner accounts ( which no one is allowed to log in as ) the procedures have rights to set the context but no user does. Here is the procedure of issue
CREATE OR REPLACE PROCEDURE eadv_rpt.rpt_legalssn_search (
p_ssn IN customer.ssn%TYPE,
resultset IN OUT sys_refcursor
)
AS
BEGIN
dbasec.ctx_search_mgr.set_ssn_search;
OPEN resultset FOR
SELECT c.firstnm, c.lastnm, c.homelocnbr, ca.addressln, ca.city, ca.statecd, ca.postalcd, c.ssn, lp.phonenbr,
lp.phonecd
FROM ( eadv.customer c INNER JOIN eadv.customeraddress ca ON c.custnbr = ca.custnbr )
INNER JOIN
eadv.locationphone lp ON c.homelocnbr = lp.locnbr
WHERE c.ssn = p_ssn
AND lp.phonecd = 'F';
EXCEPTION
WHEN OTHERS
THEN
dbasec.ctx_search_mgr.clear_ssn_search;
RAISE;
END rpt_legalssn_search;
I have pulled out the query from the proc and have run explain plans both with the policy exempted and with it in place.
Query:
SELECT c.firstnm, c.lastnm, c.homelocnbr, ca.addressln, ca.city, ca.statecd, ca.postalcd, c.ssn, lp.phonenbr,
lp.phonecd
FROM ( eadv.customer c INNER JOIN eadv.customeraddress ca ON c.custnbr = ca.custnbr )
INNER JOIN
eadv.locationphone lp ON c.homelocnbr = lp.locnbr
WHERE c.ssn = :b1
AND lp.phonecd = 'F';
Plan without VPD :
Explain complete.
Plan hash value: 2985999499
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 120 | 10 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | CUSTOMERADDRESS | 1 | 68 | 3 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 120 | 10 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 52 | 7 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| CUSTOMER | 1 | 37 | 5 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_C_SSN | 1 | | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| LOCATIONPHONE | 1 | 15 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | PK_LOCATIONPHONE | 3 | | 1 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | PK_CUSTOMERADDRESS | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("C"."SSN"= :B1)
6 - filter("LP"."PHONECD"='F')
7 - access("C"."HOMELOCNBR"="LP"."LOCNBR")
8 - access("C"."CUSTNBR"="CA"."CUSTNBR")
Plan with VPD :
SELECT c.firstnm, c.lastnm, c.homelocnbr, ca.addressln, ca.city, ca.statecd, ca.postalcd, c.ssn, lp.phonenbr,
lp.phonecd
FROM ( eadv.customer c INNER JOIN eadv.customeraddress ca ON c.custnbr = ca.custnbr )
INNER JOIN
eadv.locationphone lp ON c.homelocnbr = lp.locnbr
WHERE c.ssn = :b1
AND lp.phonecd = 'F';
Explain complete.
Plan hash value: 832577640
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1246 | 146K| 32415 (3)| 00:06:29 |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERADDRESS | 1 | 68 | 3 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1246 | 146K| 32415 (3)| 00:06:29 |
|* 3 | HASH JOIN | | 1244 | 64688 | 28680 (3)| 00:05:45 |
|* 4 | TABLE ACCESS FULL | LOCATIONPHONE | 80 | 1200 | 16 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | CUSTOMER | 45706 | 1651K| 28663 (3)| 00:05:44 |
|* 6 | INDEX RANGE SCAN | PK_CUSTOMERADDRESS | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("HOMELOCNBR"="LP"."LOCNBR")
4 - filter("LP"."PHONECD"='F')
5 - filter(CASE SYS_CONTEXT('ctx_search','ssn_search') WHEN 'TRUE' THEN "SSN" ELSE
NULL END = :B1)
6 - access("CUSTNBR"="CA"."CUSTNBR")
I have verified that these are the actual plans being executed with 10046 trace / tkprof. I also took a 10730 trace to capture the active VPD information and that is as follows:
Logon user : KWALZ
Table/View : EADV.CUSTOMER
Policy name : VPD_CUSTOMER_SSN
Policy function: DBASEC.RLS_EADV_SSN
RLS view :
SELECT "CUSTNBR","HOMELOCNBR","BUSINESSUNITCD","FIRSTNM","LASTNM", CASE WHEN ( SYS_CONTEXT('ctx_search' , 'ssn_search') = 'TRUE' ) THEN "SSN" ELSE NULL END
"SSN","SEARCH_LASTNM","SEARCH_FIRSTNM" FROM "EADV"."CUSTOMER" "CUSTOMER"
From this information I am figuring that the actual query with the VPD policy in place is the following equivalent:
SELECT c.firstnm, c.lastnm, c.homelocnbr, ca.addressln, ca.city, ca.statecd, ca.postalcd, c.ssn, lp.phonenbr,
lp.phonecd
FROM ( SELECT custnbr, homelocnbr, businessunitcd, firstnm, lastnm,
CASE
WHEN ( SYS_CONTEXT ( 'ctx_search', 'ssn_search' ) = 'TRUE' )
THEN ssn
ELSE NULL
END ssn, search_lastnm, search_firstnm
FROM eadv.customer customer ) c
INNER JOIN
eadv.customeraddress ca ON c.custnbr = ca.custnbr
INNER JOIN eadv.locationphone lp ON c.homelocnbr = lp.locnbr
WHERE c.ssn = :b1
AND lp.phonecd = 'F'
AND SYS_CONTEXT ( 'ctx_search', 'ssn_search' ) = 'TRUE';
This appears to be verified as the explain plan for this query exactly matches that of the plan for the base query with the policy active.
Since it is the query rewrite that is having the performance issue there is little I can do to the base query that has made an improvement. We tried doing a customer key sub-select based on the SSN number - but that made the response even worse with two full scans of the customer table. I ran the active query through the SQL Advisor in Grid Control and accepted the new plan it came up with but there was no noticeable improvement in response time - still 5+ minutes.
I am hoping for some suggestions - hints, tricks - cheats even that might help. Also, if anyone is aware of specific patches that might have made a difference in the optimization of queries under VPD that would be of benefit as well. We are looking to go to 11g later this year - whether that will make a difference or not we have yet to test.
Thanks for sticking through the long post - and thanks for any suggestions or observations that might come up.
|
|
|
|
|
|
|
|
| Re: Poor performance of query with VPD policy [message #308192 is a reply to message #307968] |
Fri, 21 March 2008 17:25  |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
IMHO you have a problem due to VPD design.
Apparently , when using column-masking feature, VPD substitutes column name with (in your case)
CASE SYS_CONTEXT('ctx_search','ssn_search') WHEN 'TRUE' THEN "SSN" ELSE NULL END
When it's done for a column referenced in WHERE clause it prevents index usage.
I think the simpliest solution is to define rpt_legalssn_search
procedure under schema exempted from VPD and to grant execute on that procedure to all relevant users.
HTH.
|
|
|
|
| Re: Poor performance of query with VPD policy [message #309102 is a reply to message #308192] |
Wed, 26 March 2008 07:33 |
kwalz58
Messages: 13
Registered: February 2006
Location: SC
|
Junior Member |
|
|
|
I have a feeling you have hit the nail on the head with that one. So far the only suggestion from Oracle support is to move the customer table to faster hard drives. Hopefully there will be some optimization in the VPD logic as the versions progress.
|
|
|
|
|
|
|
|
| Re: Poor performance of query with VPD policy [message #310525 is a reply to message #307968] |
Tue, 01 April 2008 08:45 |
kwalz58
Messages: 13
Registered: February 2006
Location: SC
|
Junior Member |
|
|
Finally got a reasonable response from Oracle support. Adding an index hint
/*+ INDEX(c IDX_C_SSN) */
actually decreased the runtime down to 1 min 40 seconds. While this was of help it was not sufficient for the users since they had been experiencing sub-second response time before the VPD was put into place.
In the end the fix is to move the proc into the application user schema which is exempt from the VPD policy. This has brought us back to the sub-second response time the users require and will still conform to the security policy as the procedure will only be granted to the users that are allowed the access.
Thanks for the help.
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 01:16:40 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
