Infrastructure at your Service

Clemens Bleile

Parameterized Views with SYS_CONTEXT in Oracle. How can the optimizer peek values?

There was a dicsussion a couple of years ago about a mssing feature in Oracle that the Optimizer is not peeking values when SYS_CONTEXT is being used in a predicate. I.e. specifically when using SYS_CONTEXT in Views to simulate passing parameters to views. See the following links concerning this topic:

https://connor-mcdonald.com/2016/10/20/taking-a-peek-at-sys_context
https://blog.jooq.org/2016/10/20/be-careful-when-emulating-parameterised-views-with-sys_context-in-oracle
https://community.oracle.com/ideas/15826

E.g. take the example of Connor McDonald here:

[email protected]@PDB1> create table t ( x varchar2(10), y char(100));

Table created.

[email protected]@PDB1> 
[email protected]@PDB1> insert into t
  2  select 'a', rownum
  3  from dual
  4  /

1 row created.

[email protected]@PDB1> 
[email protected]@PDB1> insert into t
  2  select 'b', rownum
  3  from dual
  4  connect by level <= 100000;

100000 rows created.

[email protected]@PDB1> commit;

Commit complete.

[email protected]@PDB1> 
[email protected]@PDB1> create index ix on t ( x ) ;

Index created.

[email protected]@PDB1> 
[email protected]@PDB1> exec dbms_stats.gather_table_stats('','T',method_Opt=>'for all columns size 5');

PL/SQL procedure successfully completed.

[email protected]@PDB1> 
[email protected]@PDB1> 
[email protected]@PDB1> select count(*) from t;

  COUNT(*)
----------
    100001

[email protected]@PDB1> 

I.e. we have 100’001 rows in the table. 1 Row with value ‘a’ and 100’000 rows with value ‘b’ in column X.

Let’s create the Context and the procedure to set it:

[email protected]@PDB1> create context blah using my_proc;

Context created.

[email protected]@PDB1> 
[email protected]@PDB1> create or replace
  2  procedure my_proc(p_val varchar2) is
  3  begin
  4   dbms_session.set_context('BLAH','ATTRIB',p_val);
  5  end;
  6  /

Procedure created.

[email protected]@PDB1> CREATE OR REPLACE VIEW v_t
  2  AS
  3  SELECT * FROM t
  4  WHERE x = sys_context('BLAH','ATTRIB');

View created.

[email protected]@PDB1> 

And run the query against the view:

[email protected]@PDB1> exec my_proc('a');

PL/SQL procedure successfully completed.

[email protected]@PDB1> select /*+ gather_plan_statistics */ count(y) from v_t;

  COUNT(Y)
----------
         1

[email protected]@PDB1> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4twdbb0d6ns5m, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(y) from v_t

Plan hash value: 2966233522

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |    1508 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |    1508 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |  50001 |      1 |00:00:00.01 |    1508 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"=SYS_CONTEXT('BLAH','ATTRIB'))


19 rows selected.

[email protected]@PDB1> exec my_proc('b');

PL/SQL procedure successfully completed.

[email protected]@PDB1> select /*+ gather_plan_statistics */ count(y) from v_t;

  COUNT(Y)
----------
    100000

[email protected]@PDB1> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4twdbb0d6ns5m, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(y) from v_t

Plan hash value: 2966233522

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |    1508 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |    1508 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |  50001 |    100K|00:00:00.01 |    1508 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"=SYS_CONTEXT('BLAH','ATTRIB'))


19 rows selected.

I.e. the E-Rows are always NUM_ROWS / NUM_DISTINCT = 100001 / 2 = 50000.5 = 50001. So the frequency histogram is no considered like with a literal (I’m accessing the table directly here):

[email protected]@PDB1> select /*+ gather_plan_statistics */ count(y)
  2  from t
  3  where x = 'a';

  COUNT(Y)
----------
         1

[email protected]@PDB1> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  125nr9j8ddga9, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(y) from t where x = 'a'

Plan hash value: 2143077847

-------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE                      |      |      1 |      1 |      1 |00:00:00.01 |       3 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN                  | IX   |      1 |      1 |      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("X"='a')


20 rows selected.

[email protected]@PDB1> select /*+ gather_plan_statistics */ count(y)
  2  from t
  3  where x = 'b';

  COUNT(Y)
----------
    100000

[email protected]@PDB1> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dd7aqu0sdv1rt, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(y) from t where x = 'b'

Plan hash value: 2966233522

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |    1508 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |    1508 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |    100K|    100K|00:00:00.01 |    1508 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"='b')


19 rows selected.

How can we get above result using the context I’m in?
If there are not too many different values returned by the different context-settings then Row Level Security (or Virtual Private Database = VPD earlier also called Fine Grained Access Control = FGAC) can be used.

I.e. for the previous example we just added the predicate

X = sys_context('BLAH','ATTRIB')

to the queries inside a view. To provide the value returned by sys_context(‘BLAH’,’ATTRIB’) to the optimizer I generate the predicate with VPD, but generate the predicate by concatenating the literal value:

[email protected]@PDB1> CREATE OR REPLACE FUNCTION set_mycontext(schemaname IN VARCHAR2, tabname IN VARCHAR2) RETURN VARCHAR2 IS
  2      predicate VARCHAR2(2000);
  3      my_context varchar2(10);
  4  BEGIN
  5    my_context:=SYS_CONTEXT('BLAH','ATTRIB');
  6    predicate := 'X='||''''||my_context||'''';
  7    RETURN predicate;
  8  END set_mycontext;
  9  /

Function created.

REMARK: Using

predicate := 'X=SYS_CONTEXT(''BLAH'',''ATTRIB'')';

would be better for cursor sharing, but the optimizer would produce the same results as with the example above.

Now we add the RLS policy:

[email protected]@PDB1> BEGIN
  2       DBMS_RLS.ADD_POLICY (object_schema   => user,
  3                         object_name     => 'T',
  4                         policy_function => 'set_mycontext',
  5                         policy_name     => 'my_context_policy',
  6                         statement_types => 'select,update,delete');
  7  END;
  8  /

PL/SQL procedure successfully completed.

I don’t need the view anymore then:

[email protected]@PDB1> exec my_proc('a');

PL/SQL procedure successfully completed.

[email protected]@PDB1> select /*+ gather_plan_statistics */ count(y) from t;

  COUNT(Y)
----------
         1

[email protected]@PDB1> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fwssbh14q2uhd, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(y) from t

Plan hash value: 2143077847

-------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE                      |      |      1 |      1 |      1 |00:00:00.01 |       3 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN                  | IX   |      1 |      1 |      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("X"='a')


20 rows selected.

[email protected]@PDB1> exec my_proc('b');

PL/SQL procedure successfully completed.

[email protected]@PDB1> select /*+ gather_plan_statistics */ count(y) from t;

  COUNT(Y)
----------
    100000

[email protected]@PDB1> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fwssbh14q2uhd, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ count(y) from t

Plan hash value: 2966233522

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |    1508 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |    1508 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |    100K|    100K|00:00:00.01 |    1508 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"='b')


19 rows selected.

Perfect, the optimizer calculates the cardinality correctly, because it gets the predicate as a literal. That allows cursor sharing only for the same literals, but usually we do not have many different values set with dbms_session.set_context.

Using that technique allows also to resolve more Optimizer cardinality mis-estimate issues when SYS_CONTEXT is used in more complex views.

E.g. take the example of

https://blog.jooq.org/2016/10/20/be-careful-when-emulating-parameterised-views-with-sys_context-in-oracle

with the UNION ALL view:

CREATE OR REPLACE VIEW v_categories_per_actor AS
SELECT DISTINCT actor_id, c.name
FROM category c
JOIN film_category fc USING (category_id)
JOIN film_actor fa USING (film_id)
WHERE fa.actor_id = TO_NUMBER(sys_context('MY_APP', 'ACTOR_ID'))
AND sys_context('MY_APP', 'USER_TYPE') = 'C'
UNION ALL
SELECT DISTINCT actor_id, c.name
FROM category c
JOIN film_category fc USING (category_id)
JOIN film_actor fa USING (film_id)
WHERE sys_context('MY_APP', 'USER_TYPE') = 'O';

The idea was to use a predicate
actor_id = TO_NUMBER(sys_context(‘MY_APP’, ‘ACTOR_ID’))
when sys_context(‘MY_APP’, ‘USER_TYPE’) = ‘C’
and no predicate at all
when
sys_context(‘MY_APP’, ‘USER_TYPE’) = ‘O’

The optimizer didn’t produce good plans with above settings.

Better for the optimizer would be to do it as follows:

CREATE OR REPLACE VIEW v_categories_per_actor AS
SELECT DISTINCT actor_id, c.name
FROM category c
JOIN film_category fc USING (category_id)
JOIN film_actor fa USING (film_id);

and add the following row level security:

CREATE OR REPLACE FUNCTION set_mycontext(schemaname IN VARCHAR2, tabname IN VARCHAR2) RETURN VARCHAR2 IS
    predicate VARCHAR2(2000);
    my_actor_context varchar2(10);
    my_user_type_context varchar2(10);
BEGIN
  my_actor_context:=SYS_CONTEXT('MY_APP','ACTOR_ID');
  my_user_type_context:=SYS_CONTEXT('MY_APP','USER_TYPE');
  IF my_user_type_context = 'C'
  THEN
     predicate := 'actor_id='||my_actor_context;
  ELSIF my_user_type_context = 'O'
  THEN
     predicate := '1=1';
  ELSE
     predicate := '1=2';
  END IF;
  RETURN predicate;
END set_mycontext;
/

and then add the RLS policy to the view:

BEGIN
   DBMS_RLS.ADD_POLICY (object_schema   => user,
                       object_name     => 'V_CATEGORIES_PER_ACTOR',
                       policy_function => 'set_mycontext',
                       policy_name     => 'my_cp_v_categories_per_actor',
                       statement_types => 'select');
END;
/

REMARK: I used the following procedure to set the context:

CREATE CONTEXT my_app USING set_ctx;
 
CREATE OR REPLACE PROCEDURE set_ctx(
  p_actor_id NUMBER := NULL,
  p_user_type VARCHAR2 := 'C'
) IS
BEGIN
  dbms_session.set_context('MY_APP', 'ACTOR_ID', p_actor_id);
  dbms_session.set_context('MY_APP', 'USER_TYPE', p_user_type);
END;
/

Running queries produces good results:

[email protected]@PDB2> alter session set statistics_level=all;

Session altered.

[email protected]@PDB2> EXEC set_ctx(1,'C');

PL/SQL procedure successfully completed.

[email protected]@PDB2> set feed only
[email protected]@PDB2> select * from v_categories_per_actor order by 1;

13 rows selected.

[email protected]@PDB2> set feed on
[email protected]@PDB2> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7ajqa6d5smd7b, child number 1
-------------------------------------
select * from v_categories_per_actor order by 1

Plan hash value: 210915017

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                        |      1 |        |     13 |00:00:00.01 |      43 |       |       |          |
|   1 |  VIEW                          | V_CATEGORIES_PER_ACTOR |      1 |      1 |     13 |00:00:00.01 |      43 |       |       |          |
|   2 |   HASH UNIQUE                  |                        |      1 |      1 |     13 |00:00:00.01 |      43 |  1452K|  1452K| 1289K (0)|
|   3 |    NESTED LOOPS                |                        |      1 |      1 |     19 |00:00:00.01 |      43 |       |       |          |
|   4 |     NESTED LOOPS               |                        |      1 |      1 |     19 |00:00:00.01 |      24 |       |       |          |
|*  5 |      HASH JOIN                 |                        |      1 |      1 |     19 |00:00:00.01 |      20 |  1922K|  1922K| 1432K (0)|
|   6 |       VIEW                     | VW_DTP_736F9BFB        |      1 |      1 |     19 |00:00:00.01 |       2 |       |       |          |
|   7 |        HASH UNIQUE             |                        |      1 |      1 |     19 |00:00:00.01 |       2 |  1818K|  1818K| 1347K (0)|
|*  8 |         INDEX RANGE SCAN       | PK_FILM_ACTOR          |      1 |     19 |     19 |00:00:00.01 |       2 |       |       |          |
|   9 |       INDEX FAST FULL SCAN     | PK_FILM_CATEGORY       |      1 |      1 |   1000 |00:00:00.01 |      18 |       |       |          |
|* 10 |      INDEX UNIQUE SCAN         | PK_CATEGORY            |     19 |      1 |     19 |00:00:00.01 |       4 |       |       |          |
|  11 |     TABLE ACCESS BY INDEX ROWID| CATEGORY               |     19 |      1 |     19 |00:00:00.01 |      19 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("FC"."FILM_ID"="ITEM_1")
   8 - access("FA"."ACTOR_ID"=1)
  10 - access("C"."CATEGORY_ID"="FC"."CATEGORY_ID")

Note
-----
   - this is an adaptive plan


34 rows selected.

[email protected]@PDB2> EXEC set_ctx(1,'O');

PL/SQL procedure successfully completed.

[email protected]@PDB2> set feed only
[email protected]@PDB2> select * from v_categories_per_actor order by 1;

2607 rows selected.

[email protected]@PDB2> set feed on
[email protected]@PDB2> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7ajqa6d5smd7b, child number 2
-------------------------------------
select * from v_categories_per_actor order by 1

Plan hash value: 3387127610

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                        |      1 |        |   2607 |00:00:00.01 |      45 |       |       |          |
|   1 |  SORT ORDER BY                   |                        |      1 |   2263 |   2607 |00:00:00.01 |      45 |   142K|   142K|  126K (0)|
|   2 |   VIEW                           | V_CATEGORIES_PER_ACTOR |      1 |   2263 |   2607 |00:00:00.01 |      45 |       |       |          |
|   3 |    HASH UNIQUE                   |                        |      1 |   2263 |   2607 |00:00:00.01 |      45 |  1422K|  1422K| 1424K (0)|
|*  4 |     HASH JOIN                    |                        |      1 |   5462 |   5462 |00:00:00.01 |      45 |  1744K|  1744K| 1607K (0)|
|   5 |      MERGE JOIN                  |                        |      1 |   1000 |   1000 |00:00:00.01 |      20 |       |       |          |
|   6 |       TABLE ACCESS BY INDEX ROWID| CATEGORY               |      1 |     16 |     16 |00:00:00.01 |       2 |       |       |          |
|   7 |        INDEX FULL SCAN           | PK_CATEGORY            |      1 |     16 |     16 |00:00:00.01 |       1 |       |       |          |
|*  8 |       SORT JOIN                  |                        |     16 |   1000 |   1000 |00:00:00.01 |      18 | 48128 | 48128 |43008  (0)|
|   9 |        INDEX FAST FULL SCAN      | PK_FILM_CATEGORY       |      1 |   1000 |   1000 |00:00:00.01 |      18 |       |       |          |
|  10 |      INDEX FAST FULL SCAN        | PK_FILM_ACTOR          |      1 |   5462 |   5462 |00:00:00.01 |      25 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("FC"."FILM_ID"="FA"."FILM_ID")
   8 - access("C"."CATEGORY_ID"="FC"."CATEGORY_ID")
       filter("C"."CATEGORY_ID"="FC"."CATEGORY_ID")


29 rows selected.

[email protected]@PDB2> 

I.e. regardless of the context I just needed around 45 logical reads for my query.

Summary:

If you are using SYS_CONTEXT to pass context-variables to views or restrict the result of queries depending on the your context and you have performance problems, because the optimizer does not peek the value returned by SYS_CONTEXT, then I do recommend to check if plans become better with RLS/FGAC/VPD and the predicate concatenated to produce a literal value returned by SYS_CONTEXT. So here we have to decide if it’s better to hard parse more often and provide the optimizer best information or reduce the number of hard parses and calls to generate VPD-predicates and use SYS_CONTEXT in views with potential sub-optimal plans. I.e. as usual both solutions have to be tested.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Clemens Bleile
Clemens Bleile

Technology Leader & Principal Consultant