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 
