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 Go to next message
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 #308133 is a reply to message #307968] Fri, 21 March 2008 07:02 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try adding a hint to the SQL:
SELECT /*+ LEADING(c) USE_NL(ca,lp)*/


Ross Leishman
Re: Poor performance of query with VPD policy [message #308134 is a reply to message #308133] Fri, 21 March 2008 07:10 Go to previous messageGo to next message
kwalz58
Messages: 13
Registered: February 2006
Location: SC
Junior Member
Gave it a shot. Unfortunately there was no change in either the plan or the run-time.
Re: Poor performance of query with VPD policy [message #308192 is a reply to message #307968] Fri, 21 March 2008 17:25 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 Go to previous messageGo to next message
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 #309213 is a reply to message #307968] Wed, 26 March 2008 17:13 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
I wouldn't hold my breath for it.

Michael
Re: Poor performance of query with VPD policy [message #309248 is a reply to message #307968] Wed, 26 March 2008 21:08 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
<edit>Oops - sorry - mis-posted

[Updated on: Wed, 26 March 2008 21:09]

Report message to a moderator

Re: Poor performance of query with VPD policy [message #310525 is a reply to message #307968] Tue, 01 April 2008 08:45 Go to previous messageGo to next message
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.
Re: Poor performance of query with VPD policy [message #313752 is a reply to message #307968] Mon, 14 April 2008 07:34 Go to previous message
kwalz58
Messages: 13
Registered: February 2006
Location: SC
Junior Member
Just got a note from Oracle support. There is an open bug on this issue - Bug 5948975 - they are stating however that it would require a major rewrite of the VPD code so they have no real time estimates on it.
Previous Topic: Performance tuning with indexes
Next Topic: bulk collect help in production
Goto Forum:
  


Current Time: Wed Dec 07 06:34:47 CST 2016

Total time taken to generate the page: 0.05967 seconds