You Asked

Tom,

Our system has many search packages which have dynamic queries on multiple tables with joins. The package has a context created on it. DBMS_SESSION.set_context and sys_context are used in building the dynamic search query.

Can you tell me what is the difference in building a dynamic search query with contexts and without contexts?

I meant what is the the advantage of writing a dynamic search query using contexts? Can you tell me in what scenarios it is better to use contexts for dynamic search queries?

Yes Tom, exactly, why should I create a query like:

where x = sys_context( 'my_ctx', 'x' );

instead of

where x = 42;

Thanks

and Tom said...

It is all about performance, scalability and security.

If you create a query using literals:

where x = 42;
where x = 55;
....
where x = 10325432;

you will create a unique SQL statement for each unique set of inputs. Each unique sql statement will necessitate a hard parse (which is very very very bad). A hard parse takes MUCH longer and uses MANY MORE resources than "no parse" or "soft parse" does. (see:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2588723819082
for details on hard/soft/no parse...)

If you create a query using sys_context (or bind variables), you will generate just ONE query:

where x = sys_context('my_ctx','x')

or at least a small number of queries (if you have more than one possible column to place the predicate against). You will generate a reasonable number of unique sql statements - each of which will be hard parsed ONCE and then soft parsed from then on in. You will have reduced dramatically the amount of hard parsing you do.

That is good for single user performance (you might be surprised to find that for small quick SQL statements - up to 90-95% of your runtime can be spent in PARSING sql, not actually EXECUTING sql!).

It is also imperative for multi-user performance. In order to hard parse you have to LATCH (which is another way to say "lock", to say "serialize") data structures in the SGA frequently. This is a MAJOR (I might say "the major") scalability inhibitor I see in systems today. Applications that hard parse frequently cannot execute hundreds of statements per second - simply because you cannot hard parse hundreds of statements per second - because of the serialization.

Lastly - it is relevant from a security perspective. If you are using the famous "single application user account in a middle tier application" (whereby everyone logs into the database as a single user), you will be subject to SQL injection if you place literals in your SQL. SQL injection is insidious and hard to detect/protect against. If you use bind variables or the sys_context trick - your SQL will not be subject to sql injection since the input to your procedure will not become PART of the sql statment - it will only be inputs to the SQL statement.

So, you do this for

o single user performance
o multi-user scalability
o security

reasons.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Context vs Bind Variables

stephan, September 30, 2010 - 8:50 am UTC

Hi Tom,

Your list of reasons to use contexts seem basically the same as your list of reasons to use bind variables: parsing, security, &c.

Could you clarify, or point out, if there are times when contexts are more appropriate than binds - or vice versa?

Thanks!

September 30, 2010 - 11:01 am UTC

... Your list of reasons to use contexts seem basically the same as your list of
reasons to use bind variables: parsing, security, &c. ...

exactly! Application contexts where invented so that fine grained access control could "bind without binding". They are useful in times when you want to bind (almost always :) ) but using bind variables would be impractical.

For example - when you want to return a ref cursor from a plsql routine to a client - and the plsql routine is going to dynamically build the query based on inputs - and you don't know how many bind variables you are going to have. Prior to 11g - you had to use native dynamic sql to open the query and since you didn't know at compile time how many inputs you would have - application contexts to the rescue.

In 11g - you can use dbms_sql to open the cursor now and then convert it into a ref cursor - so you could use either "real binds" or "context binds"

Also, when doing query modifications such as you do with fine grained access control - application contexts come into play.

So, you use them as you would bind variables in situations where bind variables just would be cumbersome or not possible to use.

Another place is in a view, to "parameterize it". You refer to the context in the view definition and the application would set "parameters" to the view by calling some stored procedure to set them and then just query the view.

Alexander, September 30, 2010 - 10:30 am UTC

I 2nd what Stephan wrote.

HI TOM

kothandaraman, October 21, 2010 - 5:58 am UTC

Hi Tom i have three fiels named product_id,product_type,product_price

product_id product_type product_price
100 soap 100
100 soap 100

this is nmy sample table.in this i need product_price to be sum based on the product_id and product_type should display twice(product_type is not going to be grouped)

October 25, 2010 - 3:19 pm UTC

select t.*, sum(product_price) over (partition by product_id) tot_sum
  from t;

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Context vs Bind Variables

HI TOM

More to Explore

PL/SQL demos

PL/SQL docs

dynamic search queries using contexts

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Context vs Bind Variables

HI TOM

More to Explore

PL/SQL demos

PL/SQL docs