I have several different applications using multiple schemas in a single database which used VPD and an after logon trigger to establish initial VPD driving context variables. A second db call, secmgr.my_appl_env spec listed below, is required by all apps to establish more specific application and user determining context variables. This lets the VPD know that user A is connecting as app B and he is restricting this app to activity(client) C.
PROCEDURE secmgr.my_appl_env (
pvar_application_cd IN application_ref.application_cd%TYPE,
pnum_activity_sa_id IN activity_ref.activity_sa_id%TYPE DEFAULT NULL,
pnum_multi_site IN NUMBER DEFAULT 0,
pvar_protected_role_nm IN VARCHAR2,
pvar_role_nm IN VARCHAR2 DEFAULT NULL,
pvar_key IN VARCHAR2 DEFAULT NULL)
AUTHID CURRENT_USER
How does the SPM autotuning wizardry take all of this into account? Does it tune the final state VPD'ed queries? To execute these queries and return any data I have to be a named user in one of my tables and properly associated to the activity. SYS is policy exempt so it can't tune too much unless I manually wrap the tables with the intended VPD clause. I'm just not sure if I can or should trust anything produced by the autotuning.
It is not much different the concept of the same SQL being "different" depending on context. For example, if *you* run select empno, ename from emp, and *I* run select empno, ename from emp, then we *might* be running the same query, but we *might* be referring to two totally different EMP's.
We would hence observe two children in v$sql for the same SQL. We get the same concept for VPD. Here's an example, where we add a predicate if we are in the last half of the current minute:
SQL> create table t_vpdspm as
2 select d.* from dba_objects d,
3 ( select rownum q from dual connect by level <= 20 );
Table created.
SQL>
SQL> create index ix on t_vpdspm ( owner );
Index created.
SQL> create or replace
2 function my_security_function( p_schema in varchar2,
3 p_object in varchar2 )
4 return varchar2
5 as
6 begin
7 if to_char(sysdate,'SS') < '30' then
8 return 'owner = ''SCOTT''';
9 else
10 return '1=1';
11 end if;
12 end;
13 /
Function created.
SQL>
SQL> begin
2 begin
3 dbms_rls.drop_policy( user, 'T_VPDSPM', 'MY_POLICY' );
4 exception
5 when others then null;
6 end;
7 dbms_rls.add_policy
8 ( object_schema => user,
9 object_name => 'T_VPDSPM',
10 policy_name => 'MY_POLICY',
11 function_schema => user,
12 policy_function => 'MY_SECURITY_FUNCTION',
13 statement_types => 'select, insert, update, delete',
14 update_check => true );
15 end;
16 /
PL/SQL procedure successfully completed.
SQL>
SQL> select to_char(sysdate,'SS') from dual;
TO
--
31
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL> set autotrace traceonly
SQL> select * from T_VPDSPM;
1988800 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 20651001
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1988K| 218M| 5850 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T_VPDSPM | 1988K| 218M| 5850 (1)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
163702 consistent gets
33350 physical reads
0 redo size
264995120 bytes sent via SQL*Net to client
1458997 bytes received via SQL*Net from client
132588 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1988800 rows processed
SQL> set autotrace off
SQL>
SQL>
SQL> select sql_id, child_number, sql_text from v$sql
2 where lower(sql_text) like 'select%vpdspm%'
3 and lower(sql_text) not like '%sql%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ----------------------------------------------------------------
06yfys99g5v0u 0 select * from T_VPDSPM
SQL> select to_char(sysdate,'SS') from dual;
TO
--
21
SQL> set autotrace traceonly
SQL> select * from T_VPDSPM;
280 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3763594420
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50995 | 5726K| 1714 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_VPDSPM | 50995 | 5726K| 1714 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX | 50995 | | 124 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
136 consistent gets
0 physical reads
0 redo size
31828 bytes sent via SQL*Net to client
749 bytes received via SQL*Net from client
20 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
280 rows processed
SQL> set autotrace off
SQL>
SQL> select sql_id, child_number, sql_text from v$sql
2 where lower(sql_text) like 'select%vpdspm%'
3 and lower(sql_text) not like '%sql%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ----------------------------------------------------------------
06yfys99g5v0u 0 select * from T_VPDSPM
06yfys99g5v0u 1 select * from T_VPDSPM
SQL>
Now although we dont *see* it in v$sql, we "know" that one of those SQL's had the "owner = 'SCOTT'" predicate appended. Because the other did not, we ended up with two children. But if we use SPM to capture that SQL, then the children and their respective plans will be captured
Lots of good information on SPM at blogs.oracle.com/optimizer
Hope this helps.