Taking a peek at SYS_CONTEXT

October 20, 2016

6 minutes

There was Twitter discussion about using context variables, accessed via SYS_CONTEXT within queries. It’s an easy means of passing parameters into a view. The question that got asked was – does a sys_context variable act exactly like a bind variable. Let’s take a look at a simple demo to explore that.

We’ll create a table with 1 row with a value of ‘a’, and 100,000 rows with a value of ‘b’. We’ll index that column, and histogram it, so we’ve got the standard example of skewed data to work with.



SQL>
SQL> create table t ( x varchar2(10), y char(100));

Table created.

SQL>
SQL> insert into t
  2  select 'a', rownum
  3  from dual
  4  /

1 row created.

SQL>
SQL> insert into t
  2  select 'b', rownum
  3  from dual
  4  connect by level <= 100000   5  / 100000 rows created. SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create index ix on t ( x ) ;

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T',method_Opt=>'for all columns size 5');

PL/SQL procedure successfully completed.

So in terms of accessing the table, it is relatively obvious to conclude that access to the rows with X=’a’, ie, single row lookup, should be done via the index, and access to the rows with X=’b’, ie, all rows except 1, should be done with a full scan of the table. Let’s look at some executions now of queries against our tables using a bind variable.




SQL>
SQL> variable b1 varchar2(10)
SQL>
SQL> exec :b1 := 'a';

PL/SQL procedure successfully completed.

SQL>
SQL> select /*+ gather_plan_statistics */ count(y)
  2  from t
  3  where x = :b1;

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

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7kv4f2uc7qjsy, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(y) from t where x = :b1

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"=:B1)


20 rows selected.

SQL>
SQL> exec :b1 := 'b';

PL/SQL procedure successfully completed.

SQL>
SQL> select /*+ gather_plan_statistics */ count(y)
  2  from t
  3  where x = :b1;

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

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  7kv4f2uc7qjsy, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(y) from t where x = :b1

Plan hash value: 2143077847

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

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

   3 - access("X"=:B1)


20 rows selected.

So our first peek at the bind yielded ‘a’, and we optimized the query accordingly. If we keep executing the query, we’ll adapt accordingly and pick a better path for the case where the bind value was ‘b’


SQL>
SQL> exec :b1 := 'a';

PL/SQL procedure successfully completed.

SQL>
SQL> select /*+ gather_plan_statistics */ count(y)
  2  from t
  3  where x = :b1;

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

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  7kv4f2uc7qjsy, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ count(y) from t where x = :b1

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"=:B1)


20 rows selected.

SQL>
SQL> exec :b1 := 'b';

PL/SQL procedure successfully completed.

SQL>
SQL> select /*+ gather_plan_statistics */ count(y)
  2  from t
  3  where x = :b1;

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

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7kv4f2uc7qjsy, child number 2
-------------------------------------
select /*+ gather_plan_statistics */ count(y) from t where x = :b1

Plan hash value: 2966233522

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

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

   2 - filter("X"=:B1)


19 rows selected.

And we can confirm that we’ve adapted to handle the two scenarios, by taking a look at V$SQL for each child. Child 0 (our original child) has been marked not shareable, it having been “replaced” with child 1 and 2 for our two skewed data queries.



SQL>
SQL> select child_number, is_bind_sensitive,is_bind_aware,is_shareable
  2  from v$sql
  3  where sql_id = '7kv4f2uc7qjsy';

CHILD_NUMBER I I I
------------ - - -
           0 Y N N
           1 Y Y Y
           2 Y Y Y

3 rows selected.

So that is all working as we would expect. Now we’ll repeat the same exercise using a context variable in place of the bind variable.



SQL> create context blah using my_proc;

Context created.

SQL>
SQL> 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.

SQL>
SQL>
SQL> exec my_proc('a');

PL/SQL procedure successfully completed.

SQL>
SQL> select /*+ gather_plan_statistics */ count(y)
  2  from t
  3  where x = sys_context('BLAH','ATTRIB');

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

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  8kj3bg89h2cbu, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(y) from t where x =
sys_context('BLAH','ATTRIB')

Plan hash value: 2966233522

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

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

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


20 rows selected.

SQL>
SQL> exec my_proc('b');

PL/SQL procedure successfully completed.

SQL>
SQL> select /*+ gather_plan_statistics */ count(y)
  2  from t
  3  where x = sys_context('BLAH','ATTRIB');

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

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  8kj3bg89h2cbu, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(y) from t where x =
sys_context('BLAH','ATTRIB')

Plan hash value: 2966233522

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

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

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


20 rows selected.

SQL> exec my_proc('a');

PL/SQL procedure successfully completed.

SQL>
SQL> select /*+ gather_plan_statistics */ count(y)
  2  from t
  3  where x = sys_context('BLAH','ATTRIB');

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

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  8kj3bg89h2cbu, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(y) from t where x =
sys_context('BLAH','ATTRIB')

Plan hash value: 2966233522

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

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

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


20 rows selected.

SQL>
SQL> exec my_proc('b');

PL/SQL procedure successfully completed.

SQL>
SQL> select /*+ gather_plan_statistics */ count(y)
  2  from t
  3  where x = sys_context('BLAH','ATTRIB');

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

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  8kj3bg89h2cbu, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(y) from t where x =
sys_context('BLAH','ATTRIB')

Plan hash value: 2966233522

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

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

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


20 rows selected.

No matter how many times we ran the query, the results were the same. There is also an obvious giveaway in the Estimated rows column, with “50001” being the commonly observed “fall back” position of rows / distinct values. When we take a look at V$SQL, we can also see that we are now just using the original and only child.



SQL> select child_number, is_bind_sensitive,is_bind_aware,is_shareable
  2  from v$sql
  3  where sql_id = '8kj3bg89h2cbu';

CHILD_NUMBER I I I
------------ - - -
           0 N N Y

1 row selected.

SQL>
SQL>

So SYS_CONTEXT values can be bound and hence yield sharable cursors, just like a bind variable, but we dont peek inside it like we do with a conventional bind variable. Whether this is a good thing or a bad thing, depends on whether you are a fan of bind peeking / adaptive cursor sharing or not Smile

9 responses to “Taking a peek at SYS_CONTEXT”

lukaseder

October 20, 2016 at 3:14 pm

Thank you very much for your detailed reply to my tweet. That indeed answers my original question, the way I worded it 🙂 Great to know, and I’m a fan of bind peeking / adaptive cursor sharing, so if the optimiser would peek at SYS_CONTEXT in the future, I wouldn’t be mad 🙂

I wish a tweet would have allowed me to phrase a more specific question though. My actual, related question was then answered by Chris Antognini: https://twitter.com/ChrisAntognini/status/788858556279820288

Thanks again!

Reply
1. lukaseder
  
  October 20, 2016 at 6:34 pm
  
  For the record, I followed up on this topic with another blog post:
  
  Be Careful When Emulating Parameterised Views with SYS_CONTEXT in Oracle
  
  Reply
Be Careful When Emulating Parameterised Views with SYS_CONTEXT in Oracle – Java, SQL and jOOQ.

October 20, 2016 at 6:13 pm

[…] In addition to that, since Oracle 11g, we have adaptive cursor sharing and bind variable peeking, which means that the same query can produce distinct execution plans depending on the actual bind variable values. This isn’t (currently) being done for SYS_CONTEXT. See also this very interesting article by Connor McDonald: https://connormcdonald.wordpress.com/2016/10/20/taking-a-peek-at-sys_context […]

Reply
rq

October 20, 2016 at 6:24 pm

An assumption from the article’s specific example is optimiser will not learn a better xplan when using sys_context, whereas it would using bind variable. Best to remove the potential downfall and use the bind variable for this specific example?

Reply
chatchai

October 25, 2016 at 12:37 am

Great !

Reply
Kevan Gelling

October 25, 2016 at 9:51 pm

A similar problem exists for DBMS_SQL. The CBO doesn’t do bind variable peeking for dynamic SQL run using DBMS_SQL.

This affects SQL embedded in APEX applications which is a big user of DBMS_SQL.

Reply
pnosko

January 23, 2017 at 2:46 am

On a side note, can’t wordpress do landscape? I hate line-wrapping– it’s as bad as an unintentional portrait YouTube video.

Reply
connormcdonald

January 23, 2017 at 7:14 am

I agree 100% – I’ve been trying to work out how to make things wider, but Oracle is my thing rather than WordPress 🙂 But I’ll keep labouring

Reply
Clemens Bleile

June 4, 2021 at 4:43 am

See https://blog.dbi-services.com/parameterized-views-with-sys_context-in-oracle-how-can-the-optimizer-peek-values/

Reply