Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

SQL using Literals instead of Binds. Are all Literals evil?

with 7 comments

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?

  1. If the SQL is executed sporadically, then it does not matter (Literals or Binds).
  2. 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).
  3. 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.
  4. 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.

Written by Carlos Sierra

February 4, 2014 at 5:02 pm

7 Responses

Subscribe to comments with RSS.

  1. 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

  2. A very nice article, thank you Carlos.

    Vishal

    February 5, 2014 at 10:54 am

  3. 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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,606 other followers

%d bloggers like this: