Database Software

Hi!

I'm using column masking to protect certain values from being viewed by users except for a small group of people.
I control the access by using a logon trigger to set a sys_context value based on their operating system user name.
The sys_context value is then used in the policy function to apply the privilege.
If the sys_context value allows full access to the policy applied table I get good execution plans for select and update.
If the sys_context value denies full access, then execution plans changes dramatically for select and update.

Oracle version: 10.2.0.3 64-bit RAC (I know, I should upgrade)
Platform: Linux RedHat 4 update 7, 64-bit

Here are some details:

-- Policy function:
CREATE or REPLACE FUNCTION EXLPOL_TABLE1
(obj_schema VARCHAR2, obj_name VARCHAR2)
RETURN VARCHAR2 AS d_predicate VARCHAR2(2000);
BEGIN
IF sys_context('EXLPOL_CTX', 'EXLFLAG') = 1 THEN
d_predicate := '';
ELSE
d_predicate := 'CLIENT_ID NOT IN (''A001'') ';
END IF;
RETURN d_predicate;
END EXLPOL_TABLE1;
/


-- Policy:
BEGIN
dbms_rls.add_policy(
object_schema => 'PRDAPP',
object_name => 'TABLE1',
policy_name => 'POL_TABLE1',
function_schema => 'SYS',
policy_function => 'EXLPOL_TABLE1',
sec_relevant_cols => 'CLIENT_ID,TRANSACTION_ID',
sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
END;
/


Execution plan details:

-- Without policy:
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |

| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 5 |
| 2 | TABLE ACCESS BY INDEX ROWID| TABLE1 | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 3 | INDEX UNIQUE SCAN | IX1_TABLE1 | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 4 | TABLE ACCESS BY INDEX ROWID| TABLE2 | 1 | 4 | 1 |00:00:00.01 | 2 |
|* 5 | INDEX UNIQUE SCAN | IX1_TABLE2 | 1 | 1 | 1 |00:00:00.01 | 1 |


-- With policy:
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |

|* 1 | HASH JOIN | | 1 | 7 | 1 |00:00:00.07 | 2200 |
|* 2 | TABLE ACCESS FULL| TABLE2 | 1 | 4 | 379 |00:00:00.01 | 23 |
|* 3 | TABLE ACCESS FULL| TABLE1 | 1 | 869 | 1 |00:00:00.07 | 2177 |


The select statement:

SELECT T.CURRENCY,
:B11 ,
:B10 ,
:B9 ,
:B8 ,
:B7 ,
:B6 ,
:B5 ,
:B4 ,
:B3 ,
T.ATT_1_ID,
T.ATT_2_ID,
T.ATT_3_ID,
T.ATT_4_ID,
T.ATT_5_ID,
T.ATT_6_ID,
T.ATT_7_ID,
T.DESCRIPTION,
T.APAR_ID,
T.APAR_TYPE,
T.DIM_1,
T.DIM_2,
T.DIM_3,
T.DIM_4,
T.DIM_5,
T.CLIENT_ID,
T.DIM_6,
T.DIM_7,
T.ACCOUNT,
:B2 ,
T.VERSION,
:B1 ,
V.TRANS_TYPE
FROM TABLE1 T, TABLE2 V
WHERE T.TRANSACTION_ID = :B12
AND T.CLIENT_ID = V.CLIENT_ID
AND T.VERSION = V.VERSION
AND BITAND(V.BFLAG,512) = 512
AND T.PROGRESS >= V.PROGRESS
AND ((BITAND(V.BFLAG,2048) = 2048
AND T.WF_STATE = 'X') OR (BITAND(V.BFLAG,2048) <> 2048));



Extract from 10046 trace file:

PARSING IN CURSOR #3 len=758 dep=2 uid=0 oct=47 lid=0 tim=1250392251128143 hv=4243463949 ad='ac393230'
declare p varchar2(32767); begin p := EXLPOL_TABLE1(:sn, :on); :v1 := substr(p,1,4000); :v2 := substr(p,4001,4000); :v3 := substr(p,8001,4000); :v4 := substr(p,12001,4000); :v5 := substr(p,16001,4000); :v6 := substr(p,20001,4000); :v7 := substr(p,24001,4000); :v8 := substr(p,28001,4000); :v9 := substr(p,32001,767); :v10 := substr(p, 4001, 1); :v11 := substr(p,8001,1); :v12 := substr(p, 12001, 1); :v13 := substr(p,16001,1); :v14 := substr(p, 20001, 1); :v15 := substr(p,24001,1); :v16 := substr(p, 28001, 1); :v17 := substr(p,32001,1); end;
END OF STMT
Bind#0
oacdty=01 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=31 siz=32 off=0
kxsbbbfp=7fbfff3f86 bln=32 avl=06 flg=09
value="PRDADMIN"
Bind#1
oacdty=01 mxl=32(11) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=31 siz=32 off=0
kxsbbbfp=7fbfff4046 bln=32 avl=11 flg=09
value="TABLE1"
...
... snip
...
EXEC #3:c=1000,e=1098,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=4,tim=1250392251129390
BINDS #5:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=202001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2a97b39390 bln=22 avl=04 flg=09
value=-21000
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=202001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2a97b393d8 bln=22 avl=01 flg=09
value=0
...
... snip (for readability)
... and the rest of the 12 bind vars as stated in the select.

So for every row it executes the function. I can understand this, but why is FTS used instead of index unique scan as it is when the policy is disabled?
Would the parameter "POLICY_TYPE => CONTEXT_SENSITIVE" solve the execute function for each row problem since it is caching the results?

The statement and execution stats tkprofed:

INSERT INTO TABLE3 (CURRENCY, AMOUNT_1,AMOUNT_2,AMOUNT_3,AMOUNT_4, VALUE_1,
VALUE_2,VALUE_3,VALUE_4, PERIOD, ATT_1_ID,ATT_2_ID,ATT_3_ID,ATT_4_ID,
ATT_5_ID,ATT_6_ID,ATT_7_ID, DESCRIPTION,APAR_ID,APAR_TYPE, DIM_1,DIM_2,
DIM_3,DIM_4,DIM_5,CLIENT_ID,DIM_6,DIM_7, ACCOUNT,PERIOD_2,COLUMN_1,COLUMN_2,
TRANS_TYPE) SELECT T.CURRENCY, :B11 ,:B10 ,:B9 ,:B8 , :B7 ,:B6 ,:B5 ,:B4 ,
:B3 , T.ATT_1_ID,T.ATT_2_ID,T.ATT_3_ID,T.ATT_4_ID,T.ATT_5_ID,T.ATT_6_ID,
T.ATT_7_ID, T.DESCRIPTION, T.APAR_ID, T.APAR_TYPE, T.DIM_1,T.DIM_2,T.DIM_3,
T.DIM_4,T.DIM_5,T.CLIENT_ID,T.DIM_6,T.DIM_7, T.ACCOUNT,:B2 ,T.VERSION,:B1 ,
V.TRANS_TYPE FROM TABLE1 T, TABLE2 V WHERE T.TRANSACTION_ID = :B12 AND
T.CLIENT_ID = V.CLIENT_ID AND T.VERSION = V.VERSION AND BITAND(V.BFLAG,512) = 512
AND T.PROGRESS >= V.PROGRESS AND ((BITAND(V.BFLAG,2048) = 2048 AND
T.WF_STATE = 'T') OR (BITAND(V.BFLAG,2048) <> 2048))



call count cpu elapsed disk query current rows

Parse 1 0.00 0.00 0 0 0 0
Execute 19877 996.63 979.51 548 42995089 86491 19877
Fetch 0 0.00 0.00 0 0 0 0

total 19878 996.63 979.51 548 42995089 86491 19877

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 63 (PRDADMIN) (recursive depth: 1)

Rows Row Source Operation

19877 HASH JOIN (cr=42993951 pr=0 pw=0 time=968114172 us)
7533383 TABLE ACCESS FULL TABLE2 (cr=457171 pr=0 pw=0 time=8737712 us)
19877 TABLE ACCESS FULL TABLE1 (cr=42536780 pr=0 pw=0 time=833489164 us)

-- so the row source operation shows what ACTUALLY happened

Rows Execution Plan

0 INSERT STATEMENT MODE: ALL_ROWS
19877 NESTED LOOPS
7533383 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'TABLE1' (TABLE)
19877 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'IX1_TABLE1'
(INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'TABLE2'
(TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'IX1_TABLE2'
(INDEX (UNIQUE))

-- and this plan is what the optimizer thought would work


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache lock 2 0.00 0.00
library cache pin 1 0.00 0.00
row cache lock 61 0.00 0.01
gc current block 2-way 15 0.00 0.00
gc cr multi block request 14 0.00 0.00
gc cr block 2-way 1 0.00 0.00
gc current grant 2-way 485 0.00 0.10
db file sequential read 547 0.11 2.21
gc current block 3-way 34 0.00 0.01
enq: TT - contention 2 0.00 0.00
gc current grant busy 5 0.00 0.00
gc current block busy 36 0.00 0.02
********************************************************************************



To the index problem:

If I hint index for the tables in the statement while policy is active, it performs an INDEX FULL SCAN, so this is not an option. Could a funciton based index
solve my problem, or are there known issues about my configuration that I'm not aware of?

Regards

Måns

Edited by: user7346950 on Jul 30, 2010 1:56 AM

Edited by: user7346950 on Jul 30, 2010 3:15 AM