PLSQL Tuning: Bind Variables and execute immediate

With this blog post I am trying to highlight the Performance benefits of Bind Variables and How to use bind variables with execute immediate. I mostly do not like to have theory in my blog posts as it is already there perfectly in Oracle Documentation but here I am trying to give a little background.

First of all what is bind variable:
A bind variable is basically a place-holder SQL statement which is replaced by actual value when the SQL statement is executed. Bind Variables helps application to send exactly the same SQL to Oracle every time. Bind variable can highly improve performance by avoiding hard parsing. It also helps Oracle to lower Shared Pool Memory consumption and also helpful to avoid SQL injection.

Now about Hard Parsing and Soft Parsing
For each SQL submitted, Oracle picks the execution plan from shared pool if same exact SQL already exists there, which is called soft parsing. If the SQL submitted is not found in Shared Pool, Oracle has to do the hard parsing which is SQL statement needs to be checked for syntax and semantics errors, and generating various execution plans to find and select optimal one. Hard parsing is very CPU intensive.

Now my favourite part, lets code and check performance benefits of bind variables.

Prepare data for tesing the performance.
SQL> create table test_bind as
  2  select level col from dual
  3  connect by level <= 99999;
Table created.

SQL> commit;
Commit complete.

SQL> create index test_indx on test_bind(col);
Index created.

SQL> select count(*) from test_bind;
  COUNT(*)
----------
     99999

All good, we have created a table with 99999 records and we have an index on it. Lets run plsql block executing 99999 queries without bind variable.

SQL> set timing on
SQL> declare
  2     a number;
  3     i number;
  4     mysql varchar2(100);
  5  begin
  6    i := 1;
  7    while i<=99999
  8    loop
  9      mysql := 'select * from test_bind where col=' || i;
 10      execute immediate mysql
 11         into a;
 12      i := i+1;
 13    end loop;
 14  end;
 15  /
PL/SQL procedure successfully completed.
Elapsed: 00:27:15.55

99999 unique queries executed in more than 27 minutes, almost 60 queries per second.

Lets now try same code with bind variables on the same data

SQL> set timing on
SQL> declare
  2     a number;
  3     i number;
  4     mysql varchar2(100);
  5  begin
  6    i := 1;
  7    while i<=99999
  8    loop
  9       mysql := 'select * from test_bind where col=:1';
 10       execute immediate mysql
 11           into a
 12          using i;
 13       i := i+1;
 14     end loop;
 15  end;
 16  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.03

WOW, It got executed in just 5 seconds, what a performance gain almost 20000 queries in a second. We just changed is just used bind variables and found 95% performance gain. Hard Parsing seems to be a really resource/CPU consuming task.


Related Post:
- Avoiding unnecessary function calls to optimize SQL statements
- Bulk Collect with Limit Clause and %NOTFOUND
- PLSQL Tuning: Bulk Collect with Dynamic SQL
- DBMS_PROFILER: How to analyze pl/sql performance
- Ensure quality and performance in plsql code using Oracle Compiler
- How to Get Execution Plan and Statistics of SQL Query
- Oracle : TKPROF simple steps
- Query optimization tips for Oracle

20 comments:

  1. Very nice, learnt a new think. Thanks for sharing

    ReplyDelete
  2. Not sure why your title of discussion had execute immediate as this had nothing to do with your bind variable performance point (which was a good discussion). thanks

    ReplyDelete
  3. Very well explained. Thanks for sharing in such simple steps.

    ReplyDelete
  4. Thanks a lot, I share this with my developers co workers, I hope they improve their delevelops.

    ReplyDelete
  5. Absolutely good post. One small suggestion from my end, we do not require to issue commit command after test_bind table creation since it is ddl statement. As we know DDL's are autocommit.

    ReplyDelete
  6. one more thing not sure about the reason, on my m/c first query took only 6 seconds and second query 2nd seconds with bind variables.

    What could be the reason??

    ReplyDelete
  7. Sorry, but the second PLSQL code was faster because the cache is in the memory. The bind variable is amazing, but its not faster you are thinking.

    ReplyDelete
    Replies
    1. thats why i posted the result of 2nd execution for each block

      Delete
  8. Nice explanation of use of bind variables! I believe it could be better if you add the actual explanation of how to use the bind variables with execute immediate instead of showing a piece of code without details. Regards

    ReplyDelete
  9. Excellent demonstration of the need to use bind-variables: performance and prevention of attacks. Thank you!

    ReplyDelete
  10. Thanks for sharing. Very well explained.

    ReplyDelete
  11. Nimish, excellent article. Thanks for sharing.

    ReplyDelete
  12. :=i , in the 9th line is the bind variable ?

    ReplyDelete
    Replies
    1. 9 mysql := 'select * from test_bind where col=' || i;
      It is not bind variable

      9 mysql := 'select * from test_bind where col=:1';
      It is bind variable

      Delete
  13. In etl load via informatica they use alot bind variable even that performance is not upto mark.machine is exa x2 and db version is 11.2 . Can you please share how to get maximum improv. Over informatica loads . No of rows are always in avg of 10mn

    ReplyDelete
  14. execute immediate in to a: using i , why or how does this reduces total time. how does oracle engine or why does oracle execute fast ?

    @Mitt

    ReplyDelete
    Replies
    1. Its hard parsing which cause performance issue, please read the article...

      Delete
  15. good article with test case.. binding definitely helps in optimizing the sql plan..

    ReplyDelete
  16. Good one but you should explain little more bit about a Shared Pool part.When there is no sql statement how the optimizer will pass a select statement like....but nice one.

    ReplyDelete