Taking a peek at SYS_CONTEXT

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

6 thoughts on “Taking a peek at SYS_CONTEXT

  1. 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!

  2. […] 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 […]

  3. 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?

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s