dbi Blog

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:

cbleile@orclcdb1@PDB1> create table t ( x varchar2(10), y char(100));

Table created.

cbleile@orclcdb1@PDB1> 
cbleile@orclcdb1@PDB1> insert into t
  2  select 'a', rownum
  3  from dual
  4  /

1 row created.

cbleile@orclcdb1@PDB1> 
cbleile@orclcdb1@PDB1> insert into t
  2  select 'b', rownum
  3  from dual
  4  connect by level <= 100000;

100000 rows created.

cbleile@orclcdb1@PDB1> commit;

Commit complete.

cbleile@orclcdb1@PDB1> 
cbleile@orclcdb1@PDB1> create index ix on t ( x ) ;

Index created.

cbleile@orclcdb1@PDB1> 
cbleile@orclcdb1@PDB1> exec dbms_stats.gather_table_stats('','T',method_Opt=>'for all columns size 5');

PL/SQL procedure successfully completed.

cbleile@orclcdb1@PDB1> 
cbleile@orclcdb1@PDB1> 
cbleile@orclcdb1@PDB1> select count(*) from t;

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

cbleile@orclcdb1@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:

cbleile@orclcdb1@PDB1> create context blah using my_proc;

Context created.

cbleile@orclcdb1@PDB1> 
cbleile@orclcdb1@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.

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

View created.

cbleile@orclcdb1@PDB1>

And run the query against the view:

cbleile@orclcdb1@PDB1> exec my_proc('a');

PL/SQL procedure successfully completed.

cbleile@orclcdb1@PDB1> select /*+ gather_plan_statistics */ count(y) from v_t;

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

cbleile@orclcdb1@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.

cbleile@orclcdb1@PDB1> exec my_proc('b');

PL/SQL procedure successfully completed.

cbleile@orclcdb1@PDB1> select /*+ gather_plan_statistics */ count(y) from v_t;

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

cbleile@orclcdb1@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):

cbleile@orclcdb1@PDB1> select /*+ gather_plan_statistics */ count(y)
  2  from t
  3  where x = 'a';

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

cbleile@orclcdb1@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.

cbleile@orclcdb1@PDB1> select /*+ gather_plan_statistics */ count(y)
  2  from t
  3  where x = 'b';

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

cbleile@orclcdb1@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:

cbleile@orclcdb1@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:

cbleile@orclcdb1@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:

cbleile@orclcdb1@PDB1> exec my_proc('a');

PL/SQL procedure successfully completed.

cbleile@orclcdb1@PDB1> select /*+ gather_plan_statistics */ count(y) from t;

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

cbleile@orclcdb1@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.

cbleile@orclcdb1@PDB1> exec my_proc('b');

PL/SQL procedure successfully completed.

cbleile@orclcdb1@PDB1> select /*+ gather_plan_statistics */ count(y) from t;

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

cbleile@orclcdb1@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:

sakila@orclcdb1@PDB2> alter session set statistics_level=all;

Session altered.

sakila@orclcdb1@PDB2> EXEC set_ctx(1,'C');

PL/SQL procedure successfully completed.

sakila@orclcdb1@PDB2> set feed only
sakila@orclcdb1@PDB2> select * from v_categories_per_actor order by 1;

13 rows selected.

sakila@orclcdb1@PDB2> set feed on
sakila@orclcdb1@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.

sakila@orclcdb1@PDB2> EXEC set_ctx(1,'O');

PL/SQL procedure successfully completed.

sakila@orclcdb1@PDB2> set feed only
sakila@orclcdb1@PDB2> select * from v_categories_per_actor order by 1;

2607 rows selected.

sakila@orclcdb1@PDB2> set feed on
sakila@orclcdb1@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.

sakila@orclcdb1@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.

Post Views: 1,840

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

Summary:

Clemens Bleile

Leave a Reply:

Related blog articles

Summary:

Clemens Bleile

Leave a Reply:

Related blog articles

ODA X10-L storage configuration is different from what you may expect

ODA patching: ASR manager stuck to old version

REKEY operation on Oracle Database configured with Oracle Key Vault

ODA X10 lineup now supports Standard Edition 2