Systems Engineering and RDBMS

Scenario for using a constant instead of a bind variable in an OLTP system

Posted by decipherinfosys on April 2, 2007

In an OLTP application, one should be aiming at the usage of bind variables in majority of the scenarios and thus using constant values won’t be a good idea. In a transaction system, we execute hundreds/thousands of queries per second. Therefore we MUST use bind variables and reduce the number of parses as much as possible. OLAP/data-warehouse apps. are different but let’s not go there in this blog post.

However, using a constant value can be useful at some times when we are in a need to use literals in some cases — say you have a table called PURCHASE_ORDER which has a column called STAT_CODE which has only say 3 distinct values in a 10 million row table. Typically, this will be a very bad column for the creation of an index – however the application rarely queries on 90 and 99 and always queries on only 0 value which at any given point of time will have very less number of records (this is the classic density vs selectivity discussion for index creation) so an index would make sense in this case. But this index may not get used under certain scenario even with a value of 0…consider this:

Suppose that the first time that this SQL runs, a value of 90 is passed (say a purge program), then the optimizer would have hard parsed and opted for a different plan (i.e. not use that index and rightfully so since the data-value of 90 qualifies for say 80% of the records)…the second time (normal processing in the application) that this SQL comes in again (this time with a value of 0), it will be soft parsed and since the plan is still in the cache from the previous run of 90, because of the usage of the bind variable, it will use the same plan again that it generated for the value of 90…even though in this case, this is un-optimal to do so (this issue is also termed as “bind variable peeking” in Oracle or “parameter sniffing” in SQL Server)…you can read up more on bind value peeking from the Oracle Guru: Tom Kyte:

http://asktom.oracle.com/pls/ask/f?p=4950:8:948105846467748805::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:7832114438832

In such scenarios, when you know that the data distribution will be heavily skewed and you know that the OLTP application is always interested in only that value that qualifies for lesser number of records and that value is “never” going to change in the query, usage of a constant is better so that it can make use of the data-distribution stats that are available to it (bottom-line: you do not need to bind those values that do not change from execution to execution)…but such requirements are small in an OLTP application. Majority of them fall in the criteria mentioned above (the bind variable usage).

5 Responses to “Scenario for using a constant instead of a bind variable in an OLTP system”

  1. […] variable usage can lead to issues and under which you might consider using a constant – see this post for an example of such a […]

  2. […] by decipherinfosys on October 23rd, 2007 In one of our previous blog post, we had covered bind variable peeking (Oracle) and parameter sniffing (SQL Server) behavior that […]

  3. […] 2) Another important addition that was made to the low level options was in the 10gR2 release: PEEKED_BINDS. This option is available when you use display_cursor(). This option allows for the display of the bind variables that were used to generate a particular plan. We had blogged about bind variable peeking before and you can read more on it here. […]

  4. […] value that was used for STAT_CODE was 20 and the code used a constant value to not run into the bind variable peeking […]

  5. […] for the values in the adhoc SQL queries submitted by the applications. Only under certain scenarios like this one it is advisable not to use parameterization but otherwise in all the OLTP based applications, one […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: