Is there a significant difference in performance on the example of the ORACLE database when using bind variables and concatenation for dynamic SQL?

For example, if we substitute a variable with date type in EXECUTE IMMEDIATE , does ORACLE still lead it to a string, or already know that it is a date and does not perform an implicit conversion?

Code example:

 declare sdate date; str varchar2(255); table_type;--Табличный пользовательский тип begin sdate := sysdate; str := 'select * from oper o where date > to_date(''' || sdate || ',''dd.mm.yyyy'')'; execute immediate str bulk collect into table_type; str := 'select * from oper o where date > :sdate'; execute immediate str bulk collect into table_type using sdate; end;
  • 3
    Add a sample code to the question. - 0xdb
  • 2
    With the substitution of implicit conversion will not be. This method is preferable. - 0xdb

1 answer 1

First, these two queries can generally return different results, since in the second case, the bind variable will contain the time component, and in the first case, only the date (ie, time: 00:00:00 ).

Secondly, Oracle DB will handle these requests differently:

  1. the query with to_date(''' || sdate || ',''dd.mm.yyyy'')' - will be parsed at each execution, after that the hash of the query string will be calculated and for this hash Oracle will try to find the Library Cache ( belongs to the Shared Pool ) if he had not already processed the exact same request (up to the sign / space) before. If such a hash is found in the Library Cache and there are no bind variables in the query, then Oracle uses the query plan from the Library Cache, i.e. re-implementation plan is not built. In your case (if you use sysdate ) each request with a different date will have a different hash. Such requests may displace other useful requests from the Library Cache, which may lead to longer processing times.
  2. When using bind variables request hash will always be the same regardless of the value of bind variable . Then everything is a little more difficult, because Oracle will analyze the value of the bind variable in order to decide whether one of the Library Cache 's existing execution plans is appropriate for the query or is it worth building a new execution plan. Learn more about Adaptive Cursor Sharing here and here .

In the general case, it is better to use bind variables , but in older versions of Oracle (up to 12.1), sometimes Adaptive Cursor Sharing did not work very well and, in practice, in DWH-like databases, it was sometimes specially done so that Adaptive Cursor Sharing did not work.