Обработка запросов SQL и  в Оракле, и в Постгресе имеет много общего. Так или иначе, надо выполнить синтаксический разбор, проверить семантику (для чего потребуется метаинформация, и не важно, называется ли это «словарь данных» или «системный каталог»), выполнить какие-то преобразования, построить оптимальный план выполнения (в обеих системах основанный на стоимости, а следовательно требующий заранее собранной статистики).

Но есть одно-единственное существенное различие, которое коренным образом меняет весь подход к обработке. Речь, конечно, о том, что Оракл использует глобальный кэш разобранных запросов, а Постгрес сохраняет запросы локально.

В статье мы попытаемся проследить, как из-за разницы в одном архитектурном решении логически следует совершенно разная идеология работы в запросами в двух СУБД.

Приведенные примеры (которые выполнялись на версиях Oracle 11.2 XE и PostgreSQL 9.4) содержат время выполнения запросов. Нас интересуют только относительные величины: во сколько раз изменилось время выполнения после внесения в запрос тех или иных изменений. При этом абсолютные цифры могут отличаться на порядки в зависимости от аппаратуры, нагрузки и настроек. Чтобы не давать повод для бессмысленных выводов на их основании, все абсолютные значения в статье отмасштабированы так, чтобы один из запросов составлял в обеих системах 10 секунд.

Оракл

Оракл использует глобальный для всего экземпляра кэш разобранных запросов (library cache, библиотечный кэш). План любого выполняемого запроса гарантированно находится в кэше: либо запрос выполняется с уже готовым планом из кэша, либо строится и сохраняется в кэше новый план — и это происходит автоматически.

Упрощенно общая схема выполнения запроса может быть представлена так:

1. Синтаксический анализ запроса (правильно ли написана команда SQL).
2. Семантический анализ (существуют ли указанные объекты и есть ли к ним доступ).
3. Если готовый план есть в кэше, то использовать его; иначе — дальше.
4. Трансформация (переписывание запроса по эвристическим правилам).
5. Оптимизация (выбор плана выполнения с минимальной стоимостью).
6. Помещение выбранного плана в кэш.

Один и тот же запрос, повторенный два раза подряд, будет обработан по-разному. Первый раз произойдет так называемый полный разбор (hard parse) — от первого до последнего пункта. Второй раз будет выполнен только частичный разбор (soft parse) — синтаксический и семантический анализ — после чего в кэше будет найден и использован уже готовый план, что существенно эффективнее.

Наличие глобального кэша подталкивает к тому, чтобы минимизировать число записей в него. Одна причина состоит в том, что большой поток «одноразовых» запросов может вытеснить из кэша полезные планы, в то время как сами эти запросы никогда больше не повторятся. Но самое главное, к общему кэшу обращаются параллельно работающие процессы, следовательно, он должен быть защищен блокировками и запись в него может стать узким местом.

Действительно, процесс, выполняющий много полных разборов, становится проблемой всего экземпляра. Рассмотрим эту ситуацию на следующем примере:

create table t(
  id number primary key,
  n number not null
);
insert into t(id, n)
  select level, 1
  from dual
  connect by rownum <= 100000;
exec dbms_stats.gather_table_stats(user,'T');
alter session set statistics_level=all;

Здесь мы создаем таблицу, вставляем в нее сотню тысяч строк (конструкция «from dual connect by rowid <= N» является идиомой для генерации выборки из N строк) и собираем статистику.

Выполним приведенный ниже код PL/SQL, выполняющий обновление таблицы построчно в цикле, используя динамически сформированные запросы update (возможно, пример выглядит надуманно, однако на практике встречается и не такое):

begin
  for i in (select id from t) loop
    execute immediate 'update t set n = n + 1 where id = '||i.id;
  end loop;
  commit;
end;
/

Если выполнить трассировку, то вот что можно обнаружить:

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count      cpu    elapsed       disk      query    current       rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse   100003    92.63      95.40          0       2837          0          0
Execute 100003    13.57      14.29          0     200002     102225     100000
Fetch     1002     0.87       0.75          0      10173          0     100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total   201008   107.08     110.46          0     213012     102225     200000

Misses in library cache during parse: 100001

Здесь показана информация по всем SQL-запросам, инициированным из блока кода. Столбец elapsed показывает общее затраченное время (которое складывается из cpu и различных ожиданий), а строки parse, execute, fetch соответствуют этапам разбора, выполнения и получения результатов запроса. Как видно, основное время (95 секунд из 110, столбец elapsed) ушло на разбор ста тысяч (столбец count) однотипных запросов и помещение их одноразовых планов в кэш. Если запустить несколько аналогичных процессов одновременно, начнут появляться ожидания вроде «latch: shared pool» и «latch: row cache objects» (названия меняются от версии к версии), говорящие о конкуренции за доступ к библиотечному кэшу.

Чтобы такого не происходило, в Оракле принято использовать переменные связывания (bind variables). Например, так:

begin
  for i in (select id from t) loop
    execute immediate 'update t set n = n + 1 where id = :A' using i.id;
  end loop;
  commit;
end;
/

Или проще, без динамического SQL, поскольку PL/SQL автоматически преобразует свои переменные в переменные связывания БД:

begin
  for i in (select id from t) loop
    update t set n = n + 1 where id = i.id;
  end loop;
  commit;
end;
/

Вот что покажет трассировка в этом случае:

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count      cpu    elapsed       disk      query    current       rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        3     0.02       0.03          0        297          0          0
Execute 100002     9.08       9.28          0     201694     102315     100000
Fetch     1001     0.77       0.68          0      10173          0     100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total   101006     9.87      10.00          0     212164     102315     200000

Время разбора сократилось до минимального — все запросы update теперь выглядят для СУБД одинаковыми. «Одинаковость», то есть фактически ключ для кэша, определяется двумя значениями:

• sql_id — хэш-код текста запроса (то есть запросы, отличающиеся любым символом — уже разные запросы),
• child_number — некоторое дополнительное число, необходимость которого вызвана хотя бы тем, что синтаксически одинаковые запросы (с одинаковым sql_id) могут быть семантически разными и должны иметь разные планы.

Таким образом, запрос update разбирается только один раз (число 3 в столбце count соответствует разборам PL/SQL-блока, запроса select в предложении for и запроса update в теле цикла). Его план помещается в кэш и дальше все работает относительно быстро.

(Почему «относительно»? Потому что правильный способ — выполнить обновление одной командой «update t set n = n + 1», которая выполняется еще на порядок быстрее.)

Однако «общий» план запроса, построенный без учета значений переменных, будет адекватен лишь для равномерно распределенных данных.

Изменим таблицу: добавим и проиндексируем поле flag, равное «Y» для 0,1% строк и «N» для остальных 99,9%.

alter table t add (
  flag char(1) check (flag in ('Y','N'))
);
update t
  set flag = case when mod(id,1000)=0 then 'Y' else 'N' end;
create index t_flag on t(flag);

Чтобы оптимизатор принял к сведению неравномерность данных в поле flag, требуется собрать гистограмму по этому полю. Например, так:

exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for columns flag size 2');

Интересно, что команда explain plan (результат которой доступен с помощью функции dbms_xplan.display) все равно покажет план, построенный из предположения равномерности, как будто оптимизатор ожидает получения половины таблицы:

explain plan for select * from t where flag = :f;
select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 50000 |   488K|    76   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 50000 |   488K|    76   (2)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FLAG"=:F)

Это означает лишь то, что по большому счету командой explain plan в Оракле пользоваться нельзя. Она не учитывает ни значений переменных, ни их типы, а сгенерированный ею план не попадает в кэш и никак не используется.

На самом же деле при выполнении запроса Оракл «подглядывает» значения переменных связывания (это называется «bind peeking») и строит план исходя из этих значений. Реальный план нужно смотреть непосредственно в кэше, когда запрос уже отправлен на выполнение и разобран. Для этого используется функция dbms_xplan.display_cursor; с указанными в примере параметрами она выводит план последнего выполненного запроса и информацию о переменных связывания:

var f char(1)
exec :f := 'Y'
select * from t where flag = :f;
...
100 rows selected.

select * from table(dbms_xplan.display_cursor(format=>'typical +peeked_binds'));

SQL_ID 6pncxxhknwgqc, child number 0

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |   135 |  1350 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_FLAG |   135 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :F (CHAR(30), CSID=873): 'Y'

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"=:F)

Теперь видно, что оптимизатор учел значение переменной (секция peeked binds), адекватно оценил число строк (135; погрешность не влияет на результат) и выбрал доступ по индексу.

Проблема в том, что построенный «частный» план попадает в кэш и будет повторно использоваться для таких же запросов — уже без учета значений переменных. Это не всегда хорошо: в нашем примере доступ по индексу будет крайне неэффективен для значения 'N'. Традиционно решение состояло в использовании динамического SQL с вклеенными в текст запроса литералами — но решение это неудачное: в дополнение к обсужденным выше минусам такой подход опасен также возможностью SQL-инъекций. Поэтому (начиная с версии 11g) Оракл умеет находить и специально обрабатывать запросы, чувствительные к значениям переменных связывания (это называется «adaptive cursor sharing»). При выполнении запроса используется уже имеющийся в кэше план, но отслеживаются реально затраченные ресурсы и сравниваются со статистикой предыдущих выполнений.

Посмотрим на часть информации из библиотечного кэша по нашему запросу:

select child_number, is_bind_sensitive, is_bind_aware, executions, buffer_gets from v$sql where sql_id='6pncxxhknwgqc';

CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE EXECUTIONS BUFFER_GETS
------------ ----------------- ------------- ---------- -----------
           0                 Y             N          1         128

Запрос помечен как чувствительный к значениям переменных (bind sensitive). Buffer_gets — число прочитанных блоков данных.

Если обнаруживается, что с другими значениями запрос выполнялся хуже, то при следующем выполнении он будет помечен как нуждающийся в разных планах (bind aware).

Выполним тот же запрос с другим значением поля flag:

exec :f := 'N'
select * from t where flag = :f;
...
99900 rows selected.

Убедимся, что запрос был выполнен с планом из кэша, а заодно продемонстрируем возможность вывода в плане не только ожидаемых, но и актуальных значений (именно для этого вначале был установлен параметр statistics_level):

select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

SQL_ID 6pncxxhknwgqc, child number 0

-----------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |  99900 |   41368 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |      1 |    135 |  99900 |   41368 |
|*  2 |   INDEX RANGE SCAN          | T_FLAG |      1 |    135 |  99900 |    6842 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("FLAG"=:F)

Налицо несовпадение ожидаемого количества строк (135) и реального (99900). Кроме того, видно, что для выполнения пришлось прочитать существенно больше данных, чем в первый раз (столбец buffer_gets):

select child_number, is_bind_sensitive, is_bind_aware, executions, buffer_gets from v$sql where sql_id='6pncxxhknwgqc';

CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE EXECUTIONS BUFFER_GETS
------------ ----------------- ------------- ---------- -----------
           0                 Y             N          2       41496

Выполним запрос еще раз:

select * from t where flag = :f;
...
99900 rows selected.

Теперь используется уже новый план, построенный для нового значения переменной связывания (обратите внимание на изменившийся child number и секцию peeked binds):

select * from table(dbms_xplan.display_cursor(format=>'typical +peeked_binds'));

SQL_ID 6pncxxhknwgqc, child number 1

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    77 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    | 99856 |   975K|    77   (3)| 00:00:01 |
--------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :F (CHAR(30), CSID=873): 'N'

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FLAG"=:F)

На этот раз оптимизатор правильно оценил число строк (99856, с небольшой погрешностью) и выбрал полное сканирование таблицы. А в библиотечном кэше теперь две версии плана для одного и того же запроса:

select child_number, is_bind_sensitive, is_bind_aware, executions, buffer_gets from v$sql where sql_id='6pncxxhknwgqc';

CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE EXECUTIONS BUFFER_GETS
------------ ----------------- ------------- ---------- -----------
           0                 Y             N          2       41496
           1                 Y             Y          1        6922

Стремление к минимизации числа планов в кэше вынуждает оптимизатор «споткнуться», прежде чем принять решение о необходимости иметь разные планы для одного запроса. Заметим, что этого можно избежать, заранее вручную сделав подсказку оптимизатору.

Постгрес

В Постгресе нет глобального кэша разобранных запросов. Более того, если не предпринять специальных усилий, то запрос не будет сохраняться и локально в памяти процесса.

В частности, при повторении одного и того же запроса он будет полностью разбираться каждый раз заново. Конечно, написанный таким образом процесс будет работать не оптимально, но это, по крайней мере, не влияет напрямую на остальные процессы.

Рассмотрим пример:

create table t(
  id serial primary key,
  n numeric not null
);
insert into t(n)
  select 1 from generate_series(1,100000);
analyze t;

Выполним следующий код на PL/pgSQL:

\timing on
do $$
declare
  i record;
begin
  for i in (select id from t) loop
  execute 'update t set n = n + 1 where id = '||i.id;
  end loop;
end;
$$ language plpgsql;
DO
Time: 36164,377 ms

Для того, чтобы сохранить результаты разбора, запрос надо подготовить, а уже затем сохраненный запрос можно многократно использовать:

prepare u(integer) as update t set n = n + 1 where id = $1;
execute u(1);
execute u(2);
...
execute u(100000);

Именно это происходит, если в блоке PL/pgSQL вызвать команду SQL без использования execute, как в первом примере. В нашем случае это дает выигрыш по скорости в 3,5 раза:

do $$
declare
  i record;
begin
  for i in (select id from t) loop
  update t set n = n + 1 where id = i.id;
  end loop;
end;
$$ language plpgsql;
DO
Time: 10000,000 ms

(А правильный вариант — одна команда SQL — выполняется быстрее еще в три раза.)

Общая схема разбора запроса состоит из следующих этапов:

1. Синтаксический анализ;
2. Семантический анализ;
3. Переписывание запроса (по правилам, причем как системным, так и пользовательским);
4. Оптимизация.

При подготовке запроса он анализируется и переписывается. Оптимизация же выполняется каждый раз заново при выполнении — таким образом для каждого значения переменных связывания строится свой «частный» план.

Рассмотрим пример неравномерного распределения данных (вместо символьной переменной мы можем использовать логический тип):

alter table t add column
  flag boolean;
update t
  set flag = mod(id,1000)=0;
create index on t(flag);

Необходимая гистограмма будет автоматически построена при анализе таблицы:

analyze t;

Подготовим запрос:

prepare s1(boolean) as select * from t where flag = $1;

Чтобы узнать, какой план выполнения будет выбран для истинного значения флага, надо воспользоваться командой explain. В Постгресе она осведомлена о значении и типе переменных связывания и показывает именно тот план, с которым команда будет выполнена:

explain execute s1(true);
                               QUERY PLAN
------------------------------------------------------------------------
 Index Scan using t_flag_idx on t  (cost=0.29..14.31 rows=110 width=10)
   Index Cond: (flag = true)
   Filter: flag

Оптимизатор предполагает выбрать 110 строк (также с небольшой погрешностью) и использует доступ по индексу.

Команда explain удобна также тем, что позволяет не только построить план, но и выполнить команду и сразу получить как ожидаемые, так и актуальные значения кардинальности. Продемонстрируем это для другого значения flag:

explain analyze execute s1(false);
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Seq Scan on t (cost=0.00..2958.00 rows=99890 width=10) (actual time=0.043..265.272 rows=99900 loops=1)
   Filter: (NOT flag)
   Rows Removed by Filter: 100
 Execution time: 385.455 ms

В данном случае оптимизатор ожидает получить 99890 строк (на самом деле 99900) и адекватно выбирает полное чтение таблицы.

Здесь возникает проблема, противоположная той, с которой сталкивается Оракл: что, если план не зависит от значений переменных связывания? В этом случае было бы выгодно не оптимизировать запрос каждый раз.

И действительно, Постгрес умеет переходить от «частных» планов к «общему» (generic plan), но делает это не сразу. Первые пять раз запрос оптимизируется в любом случае, а дальше предпочтение отдается общему плану, если его стоимость (по оценке оптимизатора) не превышает средней стоимости частных планов. Число пять здесь — некий компромисс: небольшое значение не дает достаточной статистики стоимости при разных значениях переменных связывания, а большое значение сводит на нет саму оптимизацию.

Рассмотрим этот механизм на примере с равномерным распределением данных:

prepare s2(integer) as select * from t where id = $1;
explain execute s2(1);
                           QUERY PLAN
-----------------------------------------------------------------
 Index Scan using t_pkey on t (cost=0.42..8.44 rows=1 width=10)
   Index Cond: (id = 1)

Это частный план, что видно по условию «Index Cond: (id = 1)» — здесь указано конкретное число.

Однако если вызвать explain или просто выполнить запрос еще четыре раза с любыми значениями переменных, то произойдет переключение на общий план:

execute s2(2);
...
execute s2(3);
...
execute s2(4);
...
execute s2(5);
...
explain execute s2(6);
                           QUERY PLAN
-----------------------------------------------------------------
 Index Scan using t_pkey on t (cost=0.42..8.44 rows=1 width=10)
   Index Cond: (id = $1)

Здесь в условии «Index Cond: (id = $1)» вместо конкретного значения указан номер переменной связывания — это и есть признак общего плана. Его стоимость в данном случае совпадает со стоимостью частных планов.

Теперь для запроса будет использоваться уже готовый план, что повышает эффективность выполнения (хотя и может привести к проблеме в случае ошибки расчета стоимости или если первые пять раз окажутся «не показательными»).

Заключение

Решение использовать в Оракле глобальный кэш разобранных запросов приводит к стремлению не записывать в него больше, чем совершенно необходимо — как из-за ограниченного размера и опасности вытеснения полезных планов, так и в силу конкуренции параллельных процессов за доступ к кэшу. Поэтому Оракл начинает с одного общего плана для запроса и только при необходимости переходит к нескольким частным.

Наоборот, решение не использовать в Постгресе глобальный кэш позволяет относиться к лишнему разбору проще. Постгрес наоборот начинает с частных планов и затем при возможности переходит к общему.

Оракл автоматически кэширует планы запросов. От разработчика в этом плане требуется только не забывать использовать переменные связывания, что в первую очередь продиктовано ограничениями глобального кэша. Ввиду серьезности проблемы Оракл даже предусматривает параметр cursor_sharing, принудительно заменяющий все константы на переменные.

Постгрес полностью отдает решение о необходимости сохранения разобранного запроса в руки разработчика — или инструмента разработки. Использование переменных связывания не играет в Постгресе столь драматической роли в производительности (хотя вопросы безопасности от SQL-инъекций актуальны для обеих систем в равной степени).

Если несколько процессов используют одинаковый запрос, в Оракле он будет разобран только один — первый — раз. Остальные процессы воспользуются уже готовым планом в глобальном кэше.

В Постгресе каждый процесс должен будет сам разобрать запрос. Зато одноразовые запросы выполняются без накладных расходов по помещению плана в кэш.

У каждого из решений есть как свои плюсы, так и минусы; в любом случае эти особенности должны учитывать разработчики и администраторы, проектирующие, реализующие и сопровождающие прикладные системы.