SQL using Literals instead of Binds. Are all Literals evil?
Every so often I see systems where there is a good amount of SQL that uses Literals instead of Binds, and executes enough times to create a large number of Cursors. Is this a red flag? As many questions regarding performance, I would say the right answer is: it all depends.
Of course we want to use Binds instead of Literals in order to reduce the frequency of Hard Parses, and in turn reduce CPU consumption and space utilization in the Shared Pool. Does it mean we want to replace all Literals with Binds? Do we declare war on Literals? In my opinion, the answer is simply: NO.
If a SQL has a Predicate on a date column, or a key column, then I would expect the Number of Distinct Values (NDV) for such column to be high, and in some cases as high as the number of rows in the Table (unique values for example). In the other hand, if the Predicate is in one of those columns that denotes a code, like Process Type or Status, and the NDV is small, then I’d rather keep the Literal in place. Specially if the data in such column is skewed and I have (or plan to have) Histograms on it.
What do I propose?
- If the SQL is executed sporadically, then it does not matter (Literals or Binds).
- If the SQL executes frequently, and the Predicate in question is on a Column where the Number of Distinct Values (NDV) is high, then use a Bind instead of a Literal (for this Predicate).
- If the SQL executes frequently, and the Predicate in question is on a Column where the NDV is low, then use a Literal (for this Predicate). This assumes the NDV for these Literals is also small.
- Regardless if using a Literal or a Bind for a particular Predicate: If the data in a Column referenced by a Predicate is heavily skewed, gather Statistics with Histograms on this Column.
Follow-up question: When the NDV is high or low? The answer is also: it all depends. I personally prefer to see Literals if the NDV for this Column (and this Literal) is less than 10 (or so).
Why having Binds and Literals on same SQL is better than having all Binds?
If we have good set of CBO Statistics, and we have Histograms on skewed data, and we are using bind peeking, and we are on 11g, and Adaptive Cursor Sharing (ACS) is enabled, and we plan using SQL Plan Management (SPM), then we are for a treat:
With all the “ands” above, by using Binds on predicates with high NDV and Literals in those with low NDV, then we will end up having a small number of different SQL_IDs for what we consider “the same SQL”. Each incarnation of this SQL could potentially have its own set of optimal Execution Plans created by ACS and the CBO (by making use of Histograms on the data and Selectivity Profiles on ACS). Then, with the aid of SPM we could provide stability to those multiple optimal Execution Plans for each version of the SQL. That means that SQL Q1 with Literal L1 could have a different set of optimal plans than Q1 with Literals L2.
Conclusion
Replacing some Literals with Binds but not all Literals sounds like a lot of work, but actually the extra work may be worth the effort. In my opinion, the end result is better if we replace most, but not all (as per proposal above). WHY? Even when ACS does a good job at finding multiple optimal plans for a SQL by using the selectivity of the predicates; by allowing a small number of cursors for the same SQL given the use of Literals in columns with low NDV, we are basically reducing the times we would have to execute a SQL with a sub-optimal plan due to current ACS ramp-up process. This extra granularity provided by a small number of incarnations of the “otherwise same SQL” could be crucial for tuning complex SQL or corner cases.
Carlos Sierra's Tools and Tips 
Carlos,
One of the best example that I have seen where literals should be used is in the predicate “where rownum < :maxval". If maxval is constant, it HAS TO be a literal and definitely not a bind variable. I guess the bottom line is with literals the CBO has the best chance to come up with the best execution plan but at the cost of other things like shared pool abuse, parsing overhead, limiting scalability, sql injection etc. whereas with bind variables, CBO will have to guess and may not always come up with the best possible execution plan for a query. The idea is to strike the right balance.
Narendra
February 5, 2014 at 12:48 am
Totally agree Narendra.
Carlos Sierra
February 5, 2014 at 5:59 am
A very nice article, thank you Carlos.
Vishal
February 5, 2014 at 10:54 am
I agree very much with this reasoning – it makes perfect sense.
Which is why it bothers me when I have created a PL/SQL package with constants defined for such “low-NDV literals” and use the constant in embedded SQL, then PL/SQL will turn the constant into a bind variable rather than a literal.
When I asked Bryn Llewellyn, he explained something about “constant” keyword not necessarily meaning “constant always” – it is possible for example to declare a “constant” variable and assign sysdate to it (constant seems more to mean “read-only variable.”)
Makes sense sometimes, but then I would like a keyword “literal” to define “really constant” constants and then let the PL/SQL compiler turn those into true literals rather than binds in embedded SQL.
Kim Berg Hansen (@kibeha)
February 6, 2014 at 8:19 am
Kim,
Not sure what you mean by “PL/SQL will turn the constant into a bind variable rather than a literal”. Be sure cursor_sharing is EXACT.
Carlos
Carlos Sierra
February 6, 2014 at 8:34 am
Something like this:
create or replace package my_constants
as
c_accounting constant dept.deptno%type := 10;
c_research constant dept.deptno%type := 20;
c_sales constant dept.deptno%type := 30;
c_operations constant dept.deptno%type := 40;
end;
/
create or replace package my_emp_managing
as
procedure display_accounting;
end my_emp_managing;
/
create or replace package body my_emp_managing
as
procedure display_accounting
is
begin
for accounting in (
select emp.ename
from emp
where emp.deptno = my_constants.c_accounting
) loop
dbms_output.put_line(accounting.ename);
end loop;
end display_accounting;
end my_emp_managing;
/
begin
my_emp_managing.display_accounting;
end;
/
CLARK
KING
MILLER
Search for the embedded SQL that the PL/SQL compiler has created:
select sql_text
from v$sql
where sql_text like ‘SELECT EMP.ENAME%’
and sql_text not like ‘%sql_text%’
/
SELECT EMP.ENAME FROM EMP WHERE EMP.DEPTNO = :B1
I use “my_constants.c_accounting” rather than type the literal 10, but the PL/SQL compiler turns it into bind variable :B1
And yes, I am using EXACT:
NAME TYPE VALUE
———————————— ———– —–
cursor_sharing string EXACT
Anyone using the “best practice” of making constants in PL/SQL rather than literals scattered all over the code needs bind variable peeking to perform well 😉
This is where I would like a new keyword – something like this:
create or replace package my_constants
as
c_accounting LITERAL dept.deptno%type := 10;
And then at compile time it should “paste in” the actual literal value instead of using :B1
/Kim
Kim Berg Hansen
February 6, 2014 at 9:50 am
I am totally with you Kim. I have been in your shoes here, and what I do is use literals instead of the constant. I think you have a case for an enhancement request for PL/SQL. You may want to give it a shot and log a Service Request with Oracle Support.
Carlos Sierra
February 7, 2014 at 5:08 pm