Обробка запитів в Oracle і PostgreSQL: слідства одного рішення

Обробка запитів 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 ДЛЯ 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 ДЛЯ 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 на 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 на position):

--------------------------------------


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


Predicate Information (identified на 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 на 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 на position):

--------------------------------------


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


Predicate Information (identified на 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 на 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-ін'єкцій актуальні для обох систем в рівній мірі).

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

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

У кожного з рішень є як свої плюси, так і мінуси; в будь-якому разі ці особливості мають враховувати розробники і адміністратори, проектують, реалізують і супроводжуючі прикладні системи.

Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.