My Oracle Support Banner

'Wrong' VPD Predicates Applied Using Sql Performance Analyzer via a DB LINK (Doc ID 1461308.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Server - Enterprise Edition - Version 11.2.0.3 to 11.2.0.3 [Release 11.2]
Information in this document applies to any platform.

Symptoms

 When running SQL Performance Analyzer (DBMS_SQLPA) via a Database Link and the Target Database has VPD set up, the VPD predicates applied to the queries run by SQLPA do not match those that should be applied given the Parsing Schema for a given captured query in a SQL Tuning Set.


For Example:  The Database Link below connects to the a Target Database as SYSTEM,  then use this database link to execute SQLPA Analysis Task.  The Parsing Schema Name for the given query in the Sql Tuning Set is TEST.

create public database link SYSTEM_DBLINK connect to SYSTEM identified by password using 'MY_11203_DB';


VARIABLE t_name VARCHAR2(100);
EXEC :t_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'NEW_TASK', -
       task_name => 'SYSTEM_SPAX');


exec DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER('SYSTEM_SPAX','DATABASE_LINK','SYSTEM_DBLINK');

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'SYSTEM_SPAX', -
       execution_type => 'TEST EXECUTE', -
       execution_name => 'SYSTEM_Execution');

 
The VPD Predicate applied use SYS_CONTEXT('USERENV','SESSION_USER') to determine the VPD context that should be applied. The user TEST should have the predicate "and test_number=1" and any other user should have "and test_number=-1" applied to any queries against TEST_TABLE.

create or replace function vpd_test(owner varchar2, name varchar2)
return varchar2
as
user varchar2(32):='';
pred varchar2(1000):='';
begin
  user := SYS_CONTEXT('USERENV','SESSION_USER');
  if user = 'TEST' then
    pred := 'test_number=1';
  else
    pred := 'test_number=-1';
  end if;
return pred;
end vpd_test;
/

exec dbms_rls.drop_policy('TEST','TEST_TABLE','TEST_POLICY');

begin
    dbms_rls.add_policy(
    object_schema=>'TEST',
    object_name=>'TEST_TABLE',
    policy_name=>'TEST_POLICY',
    function_schema=>'TEST',
    policy_function=>'VPD_TEST',
    statement_types=>'SELECT,INSERT,UPDATE,DELETE',
    enable=>TRUE);
    end;
    /

 
When running the SQL Performance Analyzer task against the database, predicate applied sets the VPD context as the Database Link user SYSTEM as opposed to the SQLPA Parsing Schema which is TEST. And we see the predicate "and test_number=-1".

-----------------------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |     3 |     0 |     1   (0)| 00:00:01 |        |
|   1 |  SORT AGGREGATE   |          |     1 |     3 |     0 |  4294M  (0)| 00:00:00 |        |
|*  2 |   INDEX RANGE SCAN| TEST_IDX |     1 |     3 |     0 |     1   (0)| 00:00:01 | B11203 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
 2 - access("TEST_NUMBER"=(-1))                                     <=== The VPD predicate applied is for the Database Link User and not the Parsing Schema User

 
If SQLPA used the Parsing Schema Name from the Sql Tuning Set to set the VPD context instead of the Database Link user, the query that we would expect to see should have the predicate "and test_number=1" applied.

---------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |     3 |     0 |    77   (3)| 00:00:01 |        |
|   1 |  SORT AGGREGATE       |          |     1 |     3 |     0 |  4294M  (0)| 00:00:00 |        |
|*  2 |   INDEX FAST FULL SCAN| TEST_IDX | 99991 |   292K|     0 |    77   (3)| 00:00:01 | B11203 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
2 - filter("TEST_NUMBER"=1)                                        <===  We expect that the Parsing Schema user applies this predicate and that this plan would be used.

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.