{"id":16394,"date":"2021-06-03T20:41:12","date_gmt":"2021-06-03T18:41:12","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\/"},"modified":"2021-06-03T20:41:12","modified_gmt":"2021-06-03T18:41:12","slug":"parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\/","title":{"rendered":"Parameterized Views with SYS_CONTEXT in Oracle. How can the optimizer peek values?"},"content":{"rendered":"

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:<\/p>\n

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

E.g. take the example of Connor McDonald here<\/a>:<\/p>\n

\ncbleile@orclcdb1@PDB1> create table t ( x varchar2(10), y char(100));\n\nTable created.\n\ncbleile@orclcdb1@PDB1> \ncbleile@orclcdb1@PDB1> insert into t\n  2  select 'a', rownum\n  3  from dual\n  4  \/\n\n1 row created.\n\ncbleile@orclcdb1@PDB1> \ncbleile@orclcdb1@PDB1> insert into t\n  2  select 'b', rownum\n  3  from dual\n  4  connect by level <= 100000;\n\n100000 rows created.\n\ncbleile@orclcdb1@PDB1> commit;\n\nCommit complete.\n\ncbleile@orclcdb1@PDB1> \ncbleile@orclcdb1@PDB1> create index ix on t ( x ) ;\n\nIndex created.\n\ncbleile@orclcdb1@PDB1> \ncbleile@orclcdb1@PDB1> exec dbms_stats.gather_table_stats('','T',method_Opt=>'for all columns size 5');\n\nPL\/SQL procedure successfully completed.\n\ncbleile@orclcdb1@PDB1> \ncbleile@orclcdb1@PDB1> \ncbleile@orclcdb1@PDB1> select count(*) from t;\n\n  COUNT(*)\n----------\n    100001\n\ncbleile@orclcdb1@PDB1> \n<\/pre>\n
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.<\/p>\n
Let’s create the Context and the procedure to set it:<\/p>\n
\ncbleile@orclcdb1@PDB1> create context blah using my_proc;\n\nContext created.\n\ncbleile@orclcdb1@PDB1> \ncbleile@orclcdb1@PDB1> create or replace\n  2  procedure my_proc(p_val varchar2) is\n  3  begin\n  4   dbms_session.set_context('BLAH','ATTRIB',p_val);\n  5  end;\n  6  \/\n\nProcedure created.\n\ncbleile@orclcdb1@PDB1> CREATE OR REPLACE VIEW v_t\n  2  AS\n  3  SELECT * FROM t\n  4  WHERE x = sys_context('BLAH','ATTRIB');\n\nView created.\n\ncbleile@orclcdb1@PDB1> \n<\/pre>\n
And run the query against the view:<\/p>\n
\ncbleile@orclcdb1@PDB1> exec my_proc('a');\n\nPL\/SQL procedure successfully completed.\n\ncbleile@orclcdb1@PDB1> select \/*+ gather_plan_statistics *\/ count(y) from v_t;\n\n  COUNT(Y)\n----------\n         1\n\ncbleile@orclcdb1@PDB1> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));\n\nPLAN_TABLE_OUTPUT\n-----------------------------------------------------------------------------------------------------------------------------------\nSQL_ID  4twdbb0d6ns5m, child number 0\n-------------------------------------\nselect \/*+ gather_plan_statistics *\/ count(y) from v_t\n\nPlan hash value: 2966233522\n\n-------------------------------------------------------------------------------------\n| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |\n-------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |    1508 |\n|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |    1508 |\n|*  2 |   TABLE ACCESS FULL| T    |      1 |  50001 |      1 |00:00:00.01 |    1508 |\n-------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   2 - filter(\"X\"=SYS_CONTEXT('BLAH','ATTRIB'))\n\n\n19 rows selected.\n\ncbleile@orclcdb1@PDB1> exec my_proc('b');\n\nPL\/SQL procedure successfully completed.\n\ncbleile@orclcdb1@PDB1> select \/*+ gather_plan_statistics *\/ count(y) from v_t;\n\n  COUNT(Y)\n----------\n    100000\n\ncbleile@orclcdb1@PDB1> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));\n\nPLAN_TABLE_OUTPUT\n-----------------------------------------------------------------------------------------------------------------------------------\nSQL_ID  4twdbb0d6ns5m, child number 0\n-------------------------------------\nselect \/*+ gather_plan_statistics *\/ count(y) from v_t\n\nPlan hash value: 2966233522\n\n-------------------------------------------------------------------------------------\n| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |\n-------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |    1508 |\n|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |    1508 |\n|*  2 |   TABLE ACCESS FULL| T    |      1 |  50001 |    100K|00:00:00.01 |    1508 |\n-------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   2 - filter(\"X\"=SYS_CONTEXT('BLAH','ATTRIB'))\n\n\n19 rows selected.\n<\/pre>\n
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):<\/p>\n
\ncbleile@orclcdb1@PDB1> select \/*+ gather_plan_statistics *\/ count(y)\n  2  from t\n  3  where x = 'a';\n\n  COUNT(Y)\n----------\n         1\n\ncbleile@orclcdb1@PDB1> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));\n\nPLAN_TABLE_OUTPUT\n-----------------------------------------------------------------------------------------------------------------------------------\nSQL_ID  125nr9j8ddga9, child number 0\n-------------------------------------\nselect \/*+ gather_plan_statistics *\/ count(y) from t where x = 'a'\n\nPlan hash value: 2143077847\n\n-------------------------------------------------------------------------------------------------------\n| Id  | Operation                            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |\n-------------------------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT                     |      |      1 |        |      1 |00:00:00.01 |       3 |\n|   1 |  SORT AGGREGATE                      |      |      1 |      1 |      1 |00:00:00.01 |       3 |\n|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |      1 |      1 |      1 |00:00:00.01 |       3 |\n|*  3 |    INDEX RANGE SCAN                  | IX   |      1 |      1 |      1 |00:00:00.01 |       2 |\n-------------------------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   3 - access(\"X\"='a')\n\n\n20 rows selected.\n\ncbleile@orclcdb1@PDB1> select \/*+ gather_plan_statistics *\/ count(y)\n  2  from t\n  3  where x = 'b';\n\n  COUNT(Y)\n----------\n    100000\n\ncbleile@orclcdb1@PDB1> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));\n\nPLAN_TABLE_OUTPUT\n-----------------------------------------------------------------------------------------------------------------------------------\nSQL_ID  dd7aqu0sdv1rt, child number 0\n-------------------------------------\nselect \/*+ gather_plan_statistics *\/ count(y) from t where x = 'b'\n\nPlan hash value: 2966233522\n\n-------------------------------------------------------------------------------------\n| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |\n-------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |    1508 |\n|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |    1508 |\n|*  2 |   TABLE ACCESS FULL| T    |      1 |    100K|    100K|00:00:00.01 |    1508 |\n-------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   2 - filter(\"X\"='b')\n\n\n19 rows selected.\n<\/pre>\n
How can we get above result using the context I’m in?
\nIf 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. <\/p>\n
I.e. for the previous example we just added the predicate <\/p>\n
\nX = sys_context('BLAH','ATTRIB')\n<\/pre>\n
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:<\/p>\n
\ncbleile@orclcdb1@PDB1> CREATE OR REPLACE FUNCTION set_mycontext(schemaname IN VARCHAR2, tabname IN VARCHAR2) RETURN VARCHAR2 IS\n  2      predicate VARCHAR2(2000);\n  3      my_context varchar2(10);\n  4  BEGIN\n  5    my_context:=SYS_CONTEXT('BLAH','ATTRIB');\n  6    predicate := 'X='||''''||my_context||'''';\n  7    RETURN predicate;\n  8  END set_mycontext;\n  9  \/\n\nFunction created.\n<\/pre>\n
REMARK: Using <\/p>\n
\npredicate := 'X=SYS_CONTEXT(''BLAH'',''ATTRIB'')';\n<\/pre>\n
would be better for cursor sharing, but the optimizer would produce the same results as with the example above.<\/p>\n
Now we add the RLS policy:<\/p>\n
\ncbleile@orclcdb1@PDB1> BEGIN\n  2       DBMS_RLS.ADD_POLICY (object_schema   => user,\n  3                         object_name     => 'T',\n  4                         policy_function => 'set_mycontext',\n  5                         policy_name     => 'my_context_policy',\n  6                         statement_types => 'select,update,delete');\n  7  END;\n  8  \/\n\nPL\/SQL procedure successfully completed.\n<\/pre>\n
I don’t need the view anymore then:<\/p>\n
\ncbleile@orclcdb1@PDB1> exec my_proc('a');\n\nPL\/SQL procedure successfully completed.\n\ncbleile@orclcdb1@PDB1> select \/*+ gather_plan_statistics *\/ count(y) from t;\n\n  COUNT(Y)\n----------\n         1\n\ncbleile@orclcdb1@PDB1> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));\n\nPLAN_TABLE_OUTPUT\n-----------------------------------------------------------------------------------------------------------------------------------\nSQL_ID  fwssbh14q2uhd, child number 0\n-------------------------------------\nselect \/*+ gather_plan_statistics *\/ count(y) from t\n\nPlan hash value: 2143077847\n\n-------------------------------------------------------------------------------------------------------\n| Id  | Operation                            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |\n-------------------------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT                     |      |      1 |        |      1 |00:00:00.01 |       3 |\n|   1 |  SORT AGGREGATE                      |      |      1 |      1 |      1 |00:00:00.01 |       3 |\n|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |      1 |      1 |      1 |00:00:00.01 |       3 |\n|*  3 |    INDEX RANGE SCAN                  | IX   |      1 |      1 |      1 |00:00:00.01 |       2 |\n-------------------------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   3 - access(\"X\"='a')\n\n\n20 rows selected.\n\ncbleile@orclcdb1@PDB1> exec my_proc('b');\n\nPL\/SQL procedure successfully completed.\n\ncbleile@orclcdb1@PDB1> select \/*+ gather_plan_statistics *\/ count(y) from t;\n\n  COUNT(Y)\n----------\n    100000\n\ncbleile@orclcdb1@PDB1> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));\n\nPLAN_TABLE_OUTPUT\n-----------------------------------------------------------------------------------------------------------------------------------\nSQL_ID  fwssbh14q2uhd, child number 1\n-------------------------------------\nselect \/*+ gather_plan_statistics *\/ count(y) from t\n\nPlan hash value: 2966233522\n\n-------------------------------------------------------------------------------------\n| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |\n-------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |    1508 |\n|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |    1508 |\n|*  2 |   TABLE ACCESS FULL| T    |      1 |    100K|    100K|00:00:00.01 |    1508 |\n-------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   2 - filter(\"X\"='b')\n\n\n19 rows selected.\n<\/pre>\n
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. <\/p>\n
Using that technique allows also to resolve more Optimizer cardinality mis-estimate issues when SYS_CONTEXT is used in more complex views.<\/p>\n
E.g. take the example of<\/p>\n
https:\/\/blog.jooq.org\/2016\/10\/20\/be-careful-when-emulating-parameterised-views-with-sys_context-in-oracle<\/a><\/p>\n
with the UNION ALL view:<\/p>\n
\nCREATE OR REPLACE VIEW v_categories_per_actor AS\nSELECT DISTINCT actor_id, c.name\nFROM category c\nJOIN film_category fc USING (category_id)\nJOIN film_actor fa USING (film_id)\nWHERE fa.actor_id = TO_NUMBER(sys_context('MY_APP', 'ACTOR_ID'))\nAND sys_context('MY_APP', 'USER_TYPE') = 'C'\nUNION ALL\nSELECT DISTINCT actor_id, c.name\nFROM category c\nJOIN film_category fc USING (category_id)\nJOIN film_actor fa USING (film_id)\nWHERE sys_context('MY_APP', 'USER_TYPE') = 'O';\n<\/pre>\n
The idea was to use a predicate
\nactor_id = TO_NUMBER(sys_context(‘MY_APP’, ‘ACTOR_ID’))
\nwhen sys_context(‘MY_APP’, ‘USER_TYPE’) = ‘C’
\nand no predicate at all
\nwhen
\nsys_context(‘MY_APP’, ‘USER_TYPE’) = ‘O’<\/p>\n
The optimizer didn’t produce good plans with above settings.<\/p>\n
Better for the optimizer would be to do it as follows:<\/p>\n
\nCREATE OR REPLACE VIEW v_categories_per_actor AS\nSELECT DISTINCT actor_id, c.name\nFROM category c\nJOIN film_category fc USING (category_id)\nJOIN film_actor fa USING (film_id);\n<\/pre>\n
and add the following row level security:<\/p>\n
\nCREATE OR REPLACE FUNCTION set_mycontext(schemaname IN VARCHAR2, tabname IN VARCHAR2) RETURN VARCHAR2 IS\n    predicate VARCHAR2(2000);\n    my_actor_context varchar2(10);\n    my_user_type_context varchar2(10);\nBEGIN\n  my_actor_context:=SYS_CONTEXT('MY_APP','ACTOR_ID');\n  my_user_type_context:=SYS_CONTEXT('MY_APP','USER_TYPE');\n  IF my_user_type_context = 'C'\n  THEN\n     predicate := 'actor_id='||my_actor_context;\n  ELSIF my_user_type_context = 'O'\n  THEN\n     predicate := '1=1';\n  ELSE\n     predicate := '1=2';\n  END IF;\n  RETURN predicate;\nEND set_mycontext;\n\/\n<\/pre>\n
and then add the RLS policy to the view:<\/p>\n
\nBEGIN\n   DBMS_RLS.ADD_POLICY (object_schema   => user,\n                       object_name     => 'V_CATEGORIES_PER_ACTOR',\n                       policy_function => 'set_mycontext',\n                       policy_name     => 'my_cp_v_categories_per_actor',\n                       statement_types => 'select');\nEND;\n\/\n<\/pre>\n
REMARK: I used the following procedure to set the context:<\/p>\n
\nCREATE CONTEXT my_app USING set_ctx;\n \nCREATE OR REPLACE PROCEDURE set_ctx(\n  p_actor_id NUMBER := NULL,\n  p_user_type VARCHAR2 := 'C'\n) IS\nBEGIN\n  dbms_session.set_context('MY_APP', 'ACTOR_ID', p_actor_id);\n  dbms_session.set_context('MY_APP', 'USER_TYPE', p_user_type);\nEND;\n\/\n<\/pre>\n
Running queries produces good results:<\/p>\n
\nsakila@orclcdb1@PDB2> alter session set statistics_level=all;\n\nSession altered.\n\nsakila@orclcdb1@PDB2> EXEC set_ctx(1,'C');\n\nPL\/SQL procedure successfully completed.\n\nsakila@orclcdb1@PDB2> set feed only\nsakila@orclcdb1@PDB2> select * from v_categories_per_actor order by 1;\n\n13 rows selected.\n\nsakila@orclcdb1@PDB2> set feed on\nsakila@orclcdb1@PDB2> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));\n\nPLAN_TABLE_OUTPUT\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\nSQL_ID  7ajqa6d5smd7b, child number 1\n-------------------------------------\nselect * from v_categories_per_actor order by 1\n\nPlan hash value: 210915017\n\n----------------------------------------------------------------------------------------------------------------------------------------------\n| Id  | Operation                      | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |\n----------------------------------------------------------------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT               |                        |      1 |        |     13 |00:00:00.01 |      43 |       |       |          |\n|   1 |  VIEW                          | V_CATEGORIES_PER_ACTOR |      1 |      1 |     13 |00:00:00.01 |      43 |       |       |          |\n|   2 |   HASH UNIQUE                  |                        |      1 |      1 |     13 |00:00:00.01 |      43 |  1452K|  1452K| 1289K (0)|\n|   3 |    NESTED LOOPS                |                        |      1 |      1 |     19 |00:00:00.01 |      43 |       |       |          |\n|   4 |     NESTED LOOPS               |                        |      1 |      1 |     19 |00:00:00.01 |      24 |       |       |          |\n|*  5 |      HASH JOIN                 |                        |      1 |      1 |     19 |00:00:00.01 |      20 |  1922K|  1922K| 1432K (0)|\n|   6 |       VIEW                     | VW_DTP_736F9BFB        |      1 |      1 |     19 |00:00:00.01 |       2 |       |       |          |\n|   7 |        HASH UNIQUE             |                        |      1 |      1 |     19 |00:00:00.01 |       2 |  1818K|  1818K| 1347K (0)|\n|*  8 |         INDEX RANGE SCAN       | PK_FILM_ACTOR          |      1 |     19 |     19 |00:00:00.01 |       2 |       |       |          |\n|   9 |       INDEX FAST FULL SCAN     | PK_FILM_CATEGORY       |      1 |      1 |   1000 |00:00:00.01 |      18 |       |       |          |\n|* 10 |      INDEX UNIQUE SCAN         | PK_CATEGORY            |     19 |      1 |     19 |00:00:00.01 |       4 |       |       |          |\n|  11 |     TABLE ACCESS BY INDEX ROWID| CATEGORY               |     19 |      1 |     19 |00:00:00.01 |      19 |       |       |          |\n----------------------------------------------------------------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   5 - access(\"FC\".\"FILM_ID\"=\"ITEM_1\")\n   8 - access(\"FA\".\"ACTOR_ID\"=1)\n  10 - access(\"C\".\"CATEGORY_ID\"=\"FC\".\"CATEGORY_ID\")\n\nNote\n-----\n   - this is an adaptive plan\n\n\n34 rows selected.\n\nsakila@orclcdb1@PDB2> EXEC set_ctx(1,'O');\n\nPL\/SQL procedure successfully completed.\n\nsakila@orclcdb1@PDB2> set feed only\nsakila@orclcdb1@PDB2> select * from v_categories_per_actor order by 1;\n\n2607 rows selected.\n\nsakila@orclcdb1@PDB2> set feed on\nsakila@orclcdb1@PDB2> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));\n\nPLAN_TABLE_OUTPUT\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\nSQL_ID  7ajqa6d5smd7b, child number 2\n-------------------------------------\nselect * from v_categories_per_actor order by 1\n\nPlan hash value: 3387127610\n\n------------------------------------------------------------------------------------------------------------------------------------------------\n| Id  | Operation                        | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |\n------------------------------------------------------------------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT                 |                        |      1 |        |   2607 |00:00:00.01 |      45 |       |       |          |\n|   1 |  SORT ORDER BY                   |                        |      1 |   2263 |   2607 |00:00:00.01 |      45 |   142K|   142K|  126K (0)|\n|   2 |   VIEW                           | V_CATEGORIES_PER_ACTOR |      1 |   2263 |   2607 |00:00:00.01 |      45 |       |       |          |\n|   3 |    HASH UNIQUE                   |                        |      1 |   2263 |   2607 |00:00:00.01 |      45 |  1422K|  1422K| 1424K (0)|\n|*  4 |     HASH JOIN                    |                        |      1 |   5462 |   5462 |00:00:00.01 |      45 |  1744K|  1744K| 1607K (0)|\n|   5 |      MERGE JOIN                  |                        |      1 |   1000 |   1000 |00:00:00.01 |      20 |       |       |          |\n|   6 |       TABLE ACCESS BY INDEX ROWID| CATEGORY               |      1 |     16 |     16 |00:00:00.01 |       2 |       |       |          |\n|   7 |        INDEX FULL SCAN           | PK_CATEGORY            |      1 |     16 |     16 |00:00:00.01 |       1 |       |       |          |\n|*  8 |       SORT JOIN                  |                        |     16 |   1000 |   1000 |00:00:00.01 |      18 | 48128 | 48128 |43008  (0)|\n|   9 |        INDEX FAST FULL SCAN      | PK_FILM_CATEGORY       |      1 |   1000 |   1000 |00:00:00.01 |      18 |       |       |          |\n|  10 |      INDEX FAST FULL SCAN        | PK_FILM_ACTOR          |      1 |   5462 |   5462 |00:00:00.01 |      25 |       |       |          |\n------------------------------------------------------------------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   4 - access(\"FC\".\"FILM_ID\"=\"FA\".\"FILM_ID\")\n   8 - access(\"C\".\"CATEGORY_ID\"=\"FC\".\"CATEGORY_ID\")\n       filter(\"C\".\"CATEGORY_ID\"=\"FC\".\"CATEGORY_ID\")\n\n\n29 rows selected.\n\nsakila@orclcdb1@PDB2> \n<\/pre>\n
I.e. regardless of the context I just needed around 45 logical reads for my query.<\/p>\n
Summary:<\/h3>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":35,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[59],"tags":[2343,2344,349,2345,2125,2346,1412,2347],"type_dbi":[],"class_list":["post-16394","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-bind-peeking","tag-fgac","tag-optimizer","tag-rls","tag-slow","tag-sys_context","tag-view","tag-vpd"],"acf":[],"yoast_head":"\nParameterized Views with SYS_CONTEXT in Oracle. How can the optimizer peek values? - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Parameterized Views with SYS_CONTEXT in Oracle. How can the optimizer peek values?\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-06-03T18:41:12+00:00\" \/>\n<meta name=\"author\" content=\"Clemens Bleile\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@ifgtxD2SrQ8r!YuXj\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Clemens Bleile\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"11 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\\\/\"},\"author\":{\"name\":\"Clemens Bleile\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0ac04011f60f2e93c115358d0789c2da\"},\"headline\":\"Parameterized Views with SYS_CONTEXT in Oracle. How can the optimizer peek values?\",\"datePublished\":\"2021-06-03T18:41:12+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\\\/\"},\"wordCount\":584,\"commentCount\":0,\"keywords\":[\"bind peeking\",\"fgac\",\"Optimizer\",\"rls\",\"slow\",\"sys_context\",\"view\",\"vpd\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\\\/\",\"name\":\"Parameterized Views with SYS_CONTEXT in Oracle. How can the optimizer peek values? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2021-06-03T18:41:12+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0ac04011f60f2e93c115358d0789c2da\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Parameterized Views with SYS_CONTEXT in Oracle. How can the optimizer peek values?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0ac04011f60f2e93c115358d0789c2da\",\"name\":\"Clemens Bleile\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"caption\":\"Clemens Bleile\"},\"description\":\"Clemens Bleile has more than 30 years of IT experience, thirteen in Oracle Support and fifteen in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.\",\"sameAs\":[\"https:\\\/\\\/www.dbi-services.com\",\"https:\\\/\\\/x.com\\\/ifgtxD2SrQ8r!YuXj\"],\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/clemens-bleile\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Parameterized Views with SYS_CONTEXT in Oracle. How can the optimizer peek values? - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\/","og_locale":"en_US","og_type":"article","og_title":"Parameterized Views with SYS_CONTEXT in Oracle. How can the optimizer peek values?","og_description":"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 […]","og_url":"https:\/\/www.dbi-services.com\/blog\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\/","og_site_name":"dbi Blog","article_published_time":"2021-06-03T18:41:12+00:00","author":"Clemens Bleile","twitter_card":"summary_large_image","twitter_creator":"@ifgtxD2SrQ8r!YuXj","twitter_misc":{"Written by":"Clemens Bleile","Est. reading time":"11 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\/"},"author":{"name":"Clemens Bleile","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"headline":"Parameterized Views with SYS_CONTEXT in Oracle. How can the optimizer peek values?","datePublished":"2021-06-03T18:41:12+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\/"},"wordCount":584,"commentCount":0,"keywords":["bind peeking","fgac","Optimizer","rls","slow","sys_context","view","vpd"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\/","url":"https:\/\/www.dbi-services.com\/blog\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\/","name":"Parameterized Views with SYS_CONTEXT in Oracle. How can the optimizer peek values? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2021-06-03T18:41:12+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Parameterized Views with SYS_CONTEXT in Oracle. How can the optimizer peek values?"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da","name":"Clemens Bleile","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","caption":"Clemens Bleile"},"description":"Clemens Bleile has more than 30 years of IT experience, thirteen in Oracle Support and fifteen in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.","sameAs":["https:\/\/www.dbi-services.com","https:\/\/x.com\/ifgtxD2SrQ8r!YuXj"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/clemens-bleile\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16394","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/35"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=16394"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16394\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=16394"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=16394"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=16394"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=16394"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}