Разница в производительности динамического SQL между bind переменныеми и конкатенированием

#sql #oracle #plsql

Есть ли весомая разница в производительности на примере БД ORACLE при использовании
bind переменных и конкатенировании для динамического SQL?

Например, если мы подставляем в EXECUTE IMMEDIATE переменную с типом дата, ORACLE
все равно приводит её к строке, или уже знает, что это дата и не выполняет неявного
преобразования?

Пример кода:

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;

    

Ответы

Ответ 1

Во первых эти два запроса в общем случае могут возвращать разные результаты, т.к.
во втором случае bind variable будет содержать компоненту времени, а в первом случае
только дату (т.е. время: 00:00:00).

Во вторых Oracle DB будет обрабатывать эти запросы по-разному:


запрос с to_date(''' || sdate || ',''dd.mm.yyyy'')' - будет парсится при каждом выполнении,
после этого будет расчитан хеш строки запроса и по этому хешу Oracle попытается найти
в Library Cache (принадлежит Shared Pool) не обрабытывал ли он уже точно такой же (с
точностью до знака/пробела) запрос раньше. Если такой хеш найден в Library Cache и
в запросе отсутствуют bind variables, то Oracle использует план запроса из Library
Cache, т.е. повторно план выполнения не строится. В вашем случае (если использовать
sysdate) каждый запрос с отличающейся датой будет иметь отличный хеш. Такие запросы
могут вытеснять другие полезные запросы из Library Cache, что может привести к более
длительной обработке запросов.
При использовании bind variables хеш запроса будет всегда одинаковым вне зависимости
от значения bind variable. Дальше все немного сложнее, т.к. Oracle будет анализировать
значение bind variable для того чтобы принять решение - подходит ли один из существующих
в Library Cache план выполнения запроса или же стоит построить новый план выполнения.
Подробнее о Adaptive Cursor Sharing можно прочитать здесь и здесь.


В общем случае лучше использовать bind variables, но в старых версиях Oracle (до
12.1) иногда Adaptive Cursor Sharing отрабатывал не очень хорошо и на практике в DWH-подобных
БД иногда специально делали так, чтобы Adaptive Cursor Sharing не отрабатывал.