Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Be Careful When Emulating Parameterized Views With SYS_CONTEXT in Oracle

DZone's Guide to

Be Careful When Emulating Parameterized Views With SYS_CONTEXT in Oracle

Unlike SQL Server, Oracle doesn't support parameterized views yet. You can emulate them, but there are a few things to keep in mind, ranging from optimization to security.

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

Everyone who writes complex SQL (or dare I say, moves business logic into the database?) wishes for parameterized views from time to time. This hypothetical syntax would be terrific:

CREATE VIEW v_categories_per_actor(
    p_actor_id NUMBER
) AS
SELECT DISTINCT c.name
FROM category c
JOIN film_category fc USING (category_id)
JOIN film_actor fa USING (film_id)
WHERE fa.actor_id = p_actor_id

Or as user-defined functions, if you insist (as available in SQL Server):

CREATE FUNCTION v_categories_per_actor(
    p_actor_id NUMBER
) RETURNS TABLE AS
SELECT DISTINCT c.name
FROM category c
JOIN film_category fc USING (category_id)
JOIN film_actor fa USING (film_id)
WHERE fa.actor_id = p_actor_id

As always on this blog, I’m using the useful Sakila database for examples.

The above-parameterized view could now be used very nicely with awesome SQL features like APPLY/LATERAL:

-- Oracle 12c / SQL Server syntax:
SELECT a.first_name, a.last_name, c.name
FROM actor a
OUTER APPLY v_categories_per_actor(a.actor_id)

-- Oracle 12c / PostgreSQL / SQL standard syntax:
SELECT a.first_name, a.last_name, c.name
FROM actor a
LEFT JOIN LATERAL v_categories_per_actor(a.actor_id) ON 1 = 1

More info about LATERAL here. The good news about the above is: SQL Server supports this through inline table valued functions, where the database optimizer can actually go inside the function/view and optimize it away, potentially implementing it with ordinary JOIN algorithms!

Unfortunately, this isn’t true for Oracle (yet). Even in Oracle 12c (where APPLY/LATERAL is now supported), ttable-valued functions are opaque for the optimizer, which cannot “peek inside” to apply nice SQL transformation operations to optimise such statements.

People Emulate Parameterized Views With SYS_CONTEXT

A lot of people use Oracle’s SYS_CONTEXT feature to emulate parameterized views:

A SYS_CONTEXT is essentially a global variable (per session), which you can set e.g. when getting a connection out of your connection pool in your Java client application. From then on, all SQL statements that use SYS_CONTEXT are globally “parameterized.” For example, our previous view becomes this:

CREATE VIEW v_categories_per_actor AS
SELECT DISTINCT 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'))

Now, of course, this view cannot be used (yet), because it will always return an empty result:

SELECT * 
FROM v_categories_per_actor
ORDER BY name;

Which yields:

NAME 
----

Now, let’s do that SYS_CONTEXT initialization magic:

-- Just some boilerplate you have to do once
CREATE CONTEXT my_app USING set_ctx;

CREATE OR REPLACE PROCEDURE set_ctx(
  p_actor_id NUMBER := NULL
) IS
BEGIN
  dbms_session.set_context('MY_APP', 'ACTOR_ID', p_actor_id);
END;
/

-- This will now set the ACTOR_ID for our session
EXEC set_ctx(1);

If we re-run our previous statement that queries the view now:

SELECT * 
FROM v_categories_per_actor
ORDER BY name;

We’ll get an actual result!

NAME
-----------
Animation
Children
Classics
Comedy
Documentary
Family
Foreign
Games
Horror
Music
New
Sci-Fi
Sports

Now, of course this is nowhere near as powerful as actual parameterized views, because I cannot lateral join this thing to an actual actor table, setting that context on each row. But it already helps if you want your Java client application be able to set a parameter for such a view. Let’s call it:

Oracle’s poor man’s parameterised views

How Does SYS_CONTEXT Impact Performance?

A client of mine makes heavy use of SYS_CONTEXT for security purposes, in order to implement something similar to row-level security. I’ll show an example later on.

You’ll also find a simpler “security” use-case on an interesting Ask Tom article:

Note how in the WHERE clause, I always use the SYS_CONTEXT function. I never put the literal value into the query — that would be very bad for Performance Scalability Shared pool utilization and, perhaps most importantly, security (SQL injection)

I don’t really agree with this, because in my opinion, bind variables are a much better approach to this simplistic security concern.

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:

Connor was kind enough to write this article as a reply to a question about SYS_CONTEXT I asked on Twitter.

Not peeking at SYS_CONTEXT may be good or bad, depending on your taste and on your opinion about Oracle 11g’s adaptive cursor sharing feature. I’m personally a big fan of it, although I do appreciate that some experts don’t like it too much (see also Tony Hasler’s opinions in Expert Oracle SQL).

Row Level Security With SYS_CONTEXT

The important thing is just to remember that no peeking is done at SYS_CONTEXT values. What does this mean?

This client of mine has always used SYS_CONTEXT just like I mentioned before, for security reasons. More particularly, they implemented sophisticated row-level security with it (they did this before Oracle had out of the box support for row-level security). Again, if you look at the previous query:

CREATE VIEW v_categories_per_actor AS
SELECT DISTINCT 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'))

You could interpret this as being a security feature. Actors can only access their own data across the entire database. In this case, if clients don’t get GRANTs to tables, but only to these views, it’s possible to prevent access to all sorts of data that is not related to “my own actor_id”, even if client code forgets to add the appropriate predicate.

The execution plan for a query against this view shows that it’s quite decent:

-----------------------------------------------------------------------------
| Id  | Operation                 | Name         | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |              |      1 |        |     13 |
|   1 |  SORT ORDER BY            |              |      1 |     16 |     13 |
|   2 |   VIEW                    | V_CATEGORIE..|      1 |     16 |     13 |
|   3 |    HASH UNIQUE            |              |      1 |     16 |     13 |
|*  4 |     HASH JOIN             |              |      1 |     27 |     19 |
|   5 |      NESTED LOOPS         |              |      1 |     27 |     19 |
|   6 |       INDEX FAST FULL SCAN| PK_FILM_CAT..|      1 |   1000 |   1000 |
|*  7 |       INDEX UNIQUE SCAN   | PK_FILM_ACTOR|   1000 |      1 |     19 |
|   8 |      TABLE ACCESS FULL    | CATEGORY     |      1 |     16 |     16 |
-----------------------------------------------------------------------------

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

   4 - access("C"."CATEGORY_ID"="FC"."CATEGORY_ID")
   7 - access("FA"."ACTOR_ID"=TO_NUMBER(SYS_CONTEXT('MY_APP','ACTOR_ID')) 
              AND "FC"."FILM_ID"="FA"."FILM_ID")

As you can see, the cardinality estimates are all pretty OK, and this query does the right thing, as the database knows it can use the single SYS_CONTEXT value for a fast access predicate on the FILM_ACTOR primary key.

Let’s ignore for a moment the slightly wrong cardinality estimate on PK_FILM_ACTOR, whose effects can be fixed by using a /*+LEADING(fa fc)*/ hint inside of the view. But this is not related to using SYS_CONTEXT.

Using SYS_CONTEXT to Overload View Behaviour

Now, that particular client evaluated whether SYS_CONTEXT can be used for something entirely else: To put two UNION ALL subqueries in a view and depending on the "session mode," they wanted to execute one or the other query. Let’s assume you have different types of users in your client application:

  • Normal customers: “C”
  • Operators: “O”

The latter have more privileges and can see entirely different data. In the terms of our view, this might mean the following:

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'

What this means now is that the same view is reused for both types of users: customers/actors (C) and operators (O). Customers only get to see their own data whereas operators get to see all data. Imagine that the real query is much more complex.

So, this is really nice, because then you can start reusing complex views and put these views in other complex views and the behaviour of your entire application starts changing depending on who is logging in. So, this shouldn’t just be called row level security, it should be called access control list, because that’s what they’re really doing.

Excellent!

So where’s the problem? The problem lies in the execution plans. Let’s update our procedure again:

CREATE OR REPLACE PROCEDURE set_ctx(
    p_actor_id NUMBER := NULL
) IS
BEGIN
  dbms_session.set_context('MY_APP', 'ACTOR_ID', p_actor_id);
  dbms_session.set_context('MY_APP', 'USER_TYPE', 
    CASE WHEN p_actor_id IS NULL THEN 'O' ELSE 'C' END);
END;
/

EXEC set_ctx(1);

Not high security. If we initialize the context with an P_ACTOR_ID, then we’re a customer (C), otherwise, we’re an operator (O). What’s our plan now?

SELECT * FROM TABLE (dbms_xplan.display_cursor(
  sql_id => 'cmdfbydppfqbu',
  format => 'ALLSTATS LAST'
));

Which yields…

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name                   | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                        |      1 |        |     13 |
|   1 |  SORT ORDER BY              |                        |      1 |   2275 |     13 |
|   2 |   VIEW                      | V_CATEGORIES_PER_ACTOR |      1 |   2275 |     13 |
|   3 |    UNION-ALL                |                        |      1 |        |     13 |
|   4 |     HASH UNIQUE             |                        |      1 |     12 |     13 |
|*  5 |      FILTER                 |                        |      1 |        |     19 |
|*  6 |       HASH JOIN             |                        |      1 |     27 |     19 |
|   7 |        NESTED LOOPS         |                        |      1 |     27 |     19 |
|   8 |         INDEX FAST FULL SCAN| PK_FILM_CATEGORY       |      1 |   1000 |   1000 |
|*  9 |         INDEX UNIQUE SCAN   | PK_FILM_ACTOR          |   1000 |      1 |     19 |
|  10 |        TABLE ACCESS FULL    | CATEGORY               |      1 |     16 |     16 |
|  11 |     HASH UNIQUE             |                        |      1 |   2263 |      0 |
|* 12 |      FILTER                 |                        |      1 |        |      0 |
|* 13 |       HASH JOIN             |                        |      0 |   5462 |      0 |
|* 14 |        HASH JOIN            |                        |      0 |   1000 |      0 |
|  15 |         TABLE ACCESS FULL   | CATEGORY               |      0 |     16 |      0 |
|  16 |         INDEX FAST FULL SCAN| PK_FILM_CATEGORY       |      0 |   1000 |      0 |
|  17 |        INDEX FAST FULL SCAN | PK_FILM_ACTOR          |      0 |   5462 |      0 |
-----------------------------------------------------------------------------------------

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

   5 - filter(SYS_CONTEXT('MY_APP','USER_TYPE')='C')
   6 - access("C"."CATEGORY_ID"="FC"."CATEGORY_ID")
   9 - access("FA"."ACTOR_ID"=TO_NUMBER(SYS_CONTEXT('MY_APP','ACTOR_ID')) 
              AND "FC"."FILM_ID"="FA"."FILM_ID")
  12 - filter(SYS_CONTEXT('MY_APP','USER_TYPE')='O')
  13 - access("FC"."FILM_ID"="FA"."FILM_ID")
  14 - access("C"."CATEGORY_ID"="FC"."CATEGORY_ID")

As you can see, the first part of the UNION-ALL concatenation is still roughly the same, except we now got this FILTER operation on operation #5. The second part of the UNION-ALL operation, however, got its cardinalities completely wrong. Operation 11 estimates 2263 rows, even if there are none.

The E-Rows column (estimated rows) estimate that all data is selected from our Sakila database, i.e. 1000 FILM_CATEGORY relationships and 5462 FILM_ACTOR relationships, which is all of our data. But the A-Rows column (actual rows) is zero, as expected, because we set our USER_TYPE context value to 'C' for customer, not 'O' for operator.

Also interesting is the Starts column, which shows that the operations below the FILTER operation on line #12 aren’t started.

Good News, No?

Yes and no.

  • YES: Because even if the plan looks quite bad (for customer usage), it performed optimally. At least, Oracle knew when to stop even if estimates were wrong.
  • NO: Because all these cardinality (and cost) estimates will propagate leading to bigger and bigger errors, depending on how you use this view.

Check out the following query:

SELECT actor_id, name, COUNT(*)
FROM v_categories_per_actor ca
JOIN category c USING (name)
JOIN film_category fc USING (category_id)
JOIN film_actor fa USING (film_id, actor_id)
GROUP BY actor_id, name
ORDER BY actor_id, name;

The above query could of course be stored in a view again to fit our security concept of giving grants only to views, not tables… In any case, what we’re doing here is the following:

We take all categories per actor from our previous view, then we want to count the number of total films in that category for that actor. If we’re still running this in a customer (C) context with ACTOR_ID = 1, we’ll get:

  ACTOR_ID NAME                        COUNT(*)
---------- ------------------------- ----------
         1 Animation                          1
         1 Children                           1
         1 Classics                           2
         1 Comedy                             1
         1 Documentary                        1
         1 Family                             2
         1 Foreign                            1
         1 Games                              2
         1 Horror                             3
         1 Music                              1
         1 New                                2
         1 Sci-Fi                             1
         1 Sports                             1

Which translates to: I (my user = ACTOR_ID = 1) have played in these categories, and these categories have so many total films that I played in. Again, the actual query might be much more complex, where we can’t easily factor out things (e.g. avoid doubling access to various tables). I’m just trying to make a point here.

What’s the plan of this query? It’s very bad

---------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                  |      1 |        |     13 |
|   1 |  SORT ORDER BY                  |                  |      1 |   2263 |     13 |
|   2 |   HASH GROUP BY                 |                  |      1 |   2263 |     13 |
|   3 |    NESTED LOOPS                 |                  |      1 |   3883 |     19 |
|*  4 |     HASH JOIN                   |                  |      1 |  62130 |    247 |
|*  5 |      HASH JOIN                  |                  |      1 |   2275 |     13 |
|   6 |       TABLE ACCESS FULL         | CATEGORY         |      1 |     16 |     16 |
|   7 |       VIEW                      | V_CATEGORIES_... |      1 |   2275 |     13 |
|   8 |        UNION-ALL                |                  |      1 |        |     13 |
|   9 |         HASH UNIQUE             |                  |      1 |     12 |     13 |
|* 10 |          FILTER                 |                  |      1 |        |     19 |
|* 11 |           HASH JOIN             |                  |      1 |     27 |     19 |
|  12 |            NESTED LOOPS         |                  |      1 |     27 |     19 |
|  13 |             INDEX FAST FULL SCAN| PK_FILM_CATEGORY |      1 |   1000 |   1000 |
|* 14 |             INDEX UNIQUE SCAN   | PK_FILM_ACTOR    |   1000 |      1 |     19 |
|  15 |            TABLE ACCESS FULL    | CATEGORY         |      1 |     16 |     16 |
|  16 |         HASH UNIQUE             |                  |      1 |   2263 |      0 |
|* 17 |          FILTER                 |                  |      1 |        |      0 |
|* 18 |           HASH JOIN             |                  |      0 |   5462 |      0 |
|* 19 |            HASH JOIN            |                  |      0 |   1000 |      0 |
|  20 |             TABLE ACCESS FULL   | CATEGORY         |      0 |     16 |      0 |
|  21 |             INDEX FAST FULL SCAN| PK_FILM_CATEGORY |      0 |   1000 |      0 |
|  22 |            INDEX FAST FULL SCAN | PK_FILM_ACTOR    |      0 |   5462 |      0 |
|  23 |      INDEX FAST FULL SCAN       | PK_FILM_ACTOR    |      1 |   5462 |   5462 |
|* 24 |     INDEX UNIQUE SCAN           | PK_FILM_CATEGORY |    247 |      1 |     19 |
---------------------------------------------------------------------------------------

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

   4 - access("CA"."ACTOR_ID"="FA"."ACTOR_ID")
   5 - access("CA"."NAME"="C"."NAME")
  10 - filter(SYS_CONTEXT('MY_APP','USER_TYPE')='C')
  11 - access("C"."CATEGORY_ID"="FC"."CATEGORY_ID")
  14 - access("FA"."ACTOR_ID"=TO_NUMBER(SYS_CONTEXT('MY_APP','ACTOR_ID'))
       AND "FC"."FILM_ID"="FA"."FILM_ID")
  17 - filter(SYS_CONTEXT('MY_APP','USER_TYPE')='O')
  18 - access("FC"."FILM_ID"="FA"."FILM_ID")
  19 - access("C"."CATEGORY_ID"="FC"."CATEGORY_ID")
  24 - access("FC"."FILM_ID"="FA"."FILM_ID" AND "C"."CATEGORY_ID"="FC"."CATEGORY_ID")

Why is it so bad?

Observe how at some point, I had a cardinality estimate of 62130 (operation #4), and the whole query was still expected to return 2263 rows (operation #1), when in fact, I got only 13.

Even if the optimizer got the number of rows almost right for the first UNION ALL subquery (estimated 12, got 13 on operation #9), the estimate for the second UNION ALL subquery made it think that with so many rows coming out of the view (2263 on operation #16), a hash join will be optimal to count the number of films (operation #4 and #5). While the actual numbers aren’t as bad as it was estimated, the hash join operation is much more costly for small data sets, than an equivalent nested loop join operation.

If we remove again that UNION ALL operation from the view, restricting the view back to the original customer (C) only use case:

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'

… then, we get a much better plan for that aggregation, where we now get those nested loops:

-------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Starts | E-Rows | A-Rows |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |      1 |        |     13 |
|   1 |  SORT ORDER BY                |                  |      1 |     12 |     13 |
|   2 |   HASH GROUP BY               |                  |      1 |     12 |     13 |
|   3 |    VIEW                       | VM_NWVW_1        |      1 |     47 |     19 |
|   4 |     HASH UNIQUE               |                  |      1 |     47 |     19 |
|*  5 |      FILTER                   |                  |      1 |        |     33 |
|   6 |       NESTED LOOPS            |                  |      1 |     47 |     33 |
|*  7 |        HASH JOIN              |                  |      1 |    746 |    361 |
|   8 |         NESTED LOOPS          |                  |      1 |     27 |     19 |
|*  9 |          HASH JOIN            |                  |      1 |   1000 |   1000 |
|* 10 |           HASH JOIN           |                  |      1 |     16 |     16 |
|  11 |            TABLE ACCESS FULL  | CATEGORY         |      1 |     16 |     16 |
|  12 |            TABLE ACCESS FULL  | CATEGORY         |      1 |     16 |     16 |
|  13 |           INDEX FAST FULL SCAN| PK_FILM_CATEGORY |      1 |   1000 |   1000 |
|* 14 |          INDEX UNIQUE SCAN    | PK_FILM_ACTOR    |   1000 |      1 |     19 |
|* 15 |         INDEX RANGE SCAN      | PK_FILM_ACTOR    |      1 |     27 |     19 |
|* 16 |        INDEX UNIQUE SCAN      | PK_FILM_CATEGORY |    361 |      1 |     33 |
-------------------------------------------------------------------------------------

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

   5 - filter(SYS_CONTEXT('MY_APP','USER_TYPE')='C')
   7 - access("FA"."ACTOR_ID"="FA"."ACTOR_ID")
   9 - access("C"."CATEGORY_ID"="FC"."CATEGORY_ID")
  10 - access("C"."NAME"="C"."NAME")
  14 - access("FA"."ACTOR_ID"=TO_NUMBER(SYS_CONTEXT('MY_APP','ACTOR_ID')) 
              AND "FC"."FILM_ID"="FA"."FILM_ID")
  15 - access("FA"."ACTOR_ID"=TO_NUMBER(SYS_CONTEXT('MY_APP','ACTOR_ID')))
  16 - access("FC"."FILM_ID"="FA"."FILM_ID" AND "C"."CATEGORY_ID"="FC"."CATEGORY_ID")

All the cardinality estimates are now much better, unsurprisingly. Let’s benchmark and measure, too:

SET SERVEROUTPUT ON
DECLARE
  v_ts TIMESTAMP;
  v_repeat CONSTANT NUMBER := 1000;
BEGIN
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      SELECT actor_id, name, COUNT(*)
      FROM v_categories_per_actor ca -- No UNION ALL
      JOIN category c USING (name)
      JOIN film_category fc USING (category_id)
      JOIN film_actor fa USING (film_id, actor_id)
      GROUP BY actor_id, name
      ORDER BY actor_id, name
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Statement 1 : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      SELECT actor_id, name, COUNT(*)
      FROM v_categories_per_actor2 ca -- With UNION ALL
      JOIN category c USING (name)
      JOIN film_category fc USING (category_id)
      JOIN film_actor fa USING (film_id, actor_id)
      GROUP BY actor_id, name
      ORDER BY actor_id, name
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Statement 2 : ' || (SYSTIMESTAMP - v_ts));
END;
/

Which yields:

Statement 1 : +000000000 00:00:01.940000000
Statement 2 : +000000000 00:00:02.923000000

Clearly, that UNION ALL and its resulting hash joins are hurting us drastically!

Conclusion: Use SYS_CONTEXT With Care

SYS_CONTEXT can be useful to emulate parameterised views as we’ve seen. There’s nothing wrong about doing “extended row-level security” by adding SYS_CONTEXT predicates in views and granting access only to views, not tables, e.g.

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'

However, due to the lack of peeking at those SYS_CONTEXT values, we cannot profit from the adaptive cursor sharing feature. This might have been possible with real parameterised views (such as they are supported in SQL Server), but in this case, we don’t get multiple alternative execution plans for the same SQL query, depending on SYS_CONTEXT values. This has been shown by Connor McDonald in his blog post.

I do hope that a future version of Oracle will treat SYS_CONTEXT more like bind variables, because ultimately, that’s what they are: Constant external values for the scope of a single query execution, or “parameters” like in “parameterized views”. Until we have that (or real parameterised views), I strongly advise against using SYS_CONTEXT for the use-case that my client was testing (cutting off individual UNION ALL subtrees from execution plans).

But the good news is: There’s nothing wrong with the ordinary use-case (forming predicates in WHERE clauses).

PS: If you like the idea of peeking at SYS_CONTEXT, be sure to also leave a comment here.

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:
database ,oracle database ,peformance ,parameters

Published at DZone with permission of Lukas Eder, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}