Які плани у Oracle?

Зміст



Всі SQL-запити, що надходять в СУБД Oracle, як відомо, і як це влаштовано і в інших системах, "проживають" в загальному випадку однакові стадії свого життєвого циклу:


(1) синтаксичного аналізу;
(2) вироблення алгоритму для виконання;
(3) власне виконання і
(4) пред'явлення результату.


Не варто й говорити, як багато залежить від стадії 2 вироблення алгоритму виконання запиту: якщо він дає правильний результат, але складений нераціонально, час реакції СУБД на запит може зрости проти більш оптимального в десятки, а то й тисячі разів.


За вироблення алгоритму для виконання запиту в Oracle відповідає частина СУБД, звана оптимізатором. Сам алгоритм по термінології Oracle носить назву плану [обробки запиту]. Якщо точніше, то оптимізаторів в Oracle два: т.зв. "Rule based" (RBO) і "Cost based" (CBO). Кожен запит оброблятиметься одним з цих оптимізаторів, за винятком випадків з підзапитах, для яких вид оптимізатора можна вказувати індивідуально, безвідносно до оптимізатора для зовнішнього запиту.


Оптимізатор – програма, написана людьми із загальних припущень, а всі БД за визначенням конкретні, і тому зовсім не факт, що плани, пропоновані оптимізатором, завжди бездоганні. Таким чином, однією з найперших турбот розробника ІС та АБД стає контроль якості пропонованих оптимізатором планів з метою спробувати як-небудь змінити найменш прийнятні плани в кращу сторону. (У житті часто це контроль мимоволі, після виявлення неприйнятною повільності запитів).






Такий контроль – данина споживачів усіх сучасних СУБД, які називають себе "реляційними", багаторічному обману розробників цих СУБД. Адже в реляційному підході місця такому контролю немає, тому що передбачається, що плани, пропоновані СУБД, завжди "хороші" і не повинні доставляти головних болів користувачам, справа яких – проектувати БД і запити на логічному рівні, керуючись суто модельними міркуваннями. Проте великий бізнес одних вніс поправки в теорію інших, в результаті чого останні, стратегічно правильні і безперечні досягнення Oracle по оптимізації запитів в своїй СУБД можна сприймати лише як запізніле і неповне "зализування" своєї провини перед споживачами.

План, запропонований оптимізатором в Oracle, звичайно ж, залежить від тексту запиту, але аж ніяк не тільки від нього, особливо в CBO. Остання обставина нерідко ставить початківця користувача в глухий кут. Оскільки ціна проблеми може виявитися велика, розібратися в тому, які фактори, крім вихідного тексту, впливають на роботу оптимізатора в кожному конкретному випадку важливо. Нижче ці чинники перераховуються з короткими поясненнями.


Фактори впливу на вибір плану RBO


Історично RBO – попередник оптимізатора CBO, але починаючи з версії 8 він відійшов на другий план, хоча продовжує підтримуватися і навіть потроху розвиватися. Його головною особливістю є спроба виробляти план, більш кращий з точки зору "ефективнішого" доступу до даних. "Ефективність" визначається просто: за фіксованою таблиці різновидів доступу до даних, упорядкованих за критерієм переваги (з моменту виходу на рядок з фізичного адресою до повного сканування).


Логічно роботу RBO можна представити так: спочатку він складає повний перелік всіх можливих варіантів (планів) обробки запиту, потім обчислює для кожного варіанта вага і вибирає найбільш "легкий". (Зрозуміло, що фактично RBO так шалено не надходить через незліченної кількості варіантів у загальному випадку, але фактична техніка – це спроба наблизитися до результату, що дається саме такою логікою).


На вироблення плану конкретного запиту цим оптимізатором впливають такі фактори:



  1. версія СУБД, а значить і оптимізатора
  2. синтаксис запиту
  3. наявність або відсутність, а також деякі властивості, допоміжних збережених структур (індексів, кластерів) або основних (індексно-організована таблиця)

Невеликі зауваження з приводу цих факторів.


(1) У нових версіях СУБД оптимізатор може підправляти: розробники можуть намагатися усунути деякі помічені помилки або додати нові властивості поведінки. Внутрішня логіка RBO відносно проста, тому часто різниця може звестися лише до зміни таблиці перевагу доступу: у версії 9.0 ця таблиця складається з 20 позицій, а в деяких більш ранніх була коротшою.
(2) Різні за формою, але однакові за змістом запити, можуть мати різні плани. Взагалі-то, Oracle до цього не прагне, особливо в CBO (вважаючи, що визначальним для плану є саме сенс – і правильно робить), але фірмі не завжди це вдається. (Вина за різні плани порівну розподіляється між розробниками Oracle і SQL). Дуже простий приклад – порядок виконання з'єднання буде відповідати порядку формального перерахування таблиць у фразі FROM, якщо тільки таблиця перевагу доступу не змусить RBO цей порядок порушити (наприклад, через наявність індексу).
(3) Наявність або відсутність індексу або кластера – важливий фактор для доступу, істотно врахований RBO при виборі плану. Доданий або віддалений індекс здатний радикально поміняти план одного і того ж запиту.


Фактори впливу на вибір плану CBO


На відміну від RBO, CBO намагається оптимізувати витрати ресурсів комп'ютера на виконання кожного окремого запиту. Можна поспівчувати розробникам Oracle: завдання недосяжна! Дійсно, існує принаймні три таких ресурсу: процесорний час, витрата оперативної пам'яті і число звернень до диска. Багатокритеріальна оптимізація не має спільного рішення, і тому CBO майже завжди буде вам пропонувати якийсь компроміс. Зовсім не факт, що цей компроміс вас влаштує, і тоді доведеться використовувати "фактори впливу" у своїх корисливих цілях (є, правда, і більш жорсткі засоби).


CBO – програма, набагато більш складна, ніж RBO, і факторів впливу на вибір цією програмою плану істотно більше. Ось ці чинники:



  1. версія СУБД, а значить і оптимізатора
  2. синтаксис запиту
  3. наявність або відсутність, а також властивості допоміжних збережених структур (індексів, кластерів)
  4. значення деяких INIT-параметрів (SORT_AREA_SIZE, CURSOR_SHARING, DB_FILE_MULTIBLOCK_READ_COUNT та інших)
  5. наявність (або відсутність) і значення (при наявності) в момент виконання запиту попередньо зібраної статистики щодо зберіганні об'єктів

Невеликі зауваження з приводу цих факторів.


(1) Загалом, аналогічно RBO, але можна навести пару уточнень. Так, у версії СУБД 8.0.4 було усунуто дуже багато колишніх помилок. У версії 9.0 зроблено суттєве поліпшення роботи із змінними прив'язки в тексті запиту: якщо раніше вони до вироблення плану не оцінювалися, то тепер оптимізатор спочатку дізнається їх реальні значення, а потім запропонує план. Так плани у вашій програмі могли "самі помінятися".
(2) Загалом, аналогічно RBO.
(3) Загалом, аналогічно RBO.
(4) Цілий ряд INIT-параметрів використовується CBO при виробленні плану. Так, параметр COMPATIBLE перемкне оптимізатор на більш давню (або свіжу) схему роботи. Інші параметри підвищують або знижують вагу певних вузлів на дереві варіантів. Наприклад, більше значення SORT_AREA_SIZE призводить до більшої ймовірності отримати для з'єднання сортування зі злиттям. Є параметр спеціально для підвищення або зниження ваги "індексних" варіантів, підвищує або знижує ймовірність використання індексу в плані, запропонованому CBO.
(5) Фактор статистики для CBO настільки важливий, що нерідко користувачі про існуючі крім нього залишаються в невіданні. Не зайве нагадати, що аналізуватися можуть не тільки таблиці та індекси, але і стовпці. CBO може враховувати зібрану в результаті аналізу гістограму розподілів значень у стовпці, що істотно при нерівномірному розподілі цих значень в реальному таблиці. Це здатне дати різні плани на запитах, які, наприклад, різняться лише приводяться в тексті прізвищем або номером вироби.


Довіряй …


У цілому і експерти, і представники Oracle рекомендують працювати з CBO, а не з RBO. У цілому, знову-таки, CBO досить надійний і поводиться розумно. Проста ілюстрація тому – приклад відбору п'яти високооплачуваних співробітників (типова задача). Ось три ("з половиною") різних способу, які працюють у версії 8.1.7:






А 

SELECT ename, -sal sal
FROM (SELECT DISTINCT -sal sal, ename, empno FROM emp)
WHERE ROWNUM <= 5;






Б 

SELECT *
FROM (SELECT ename, sal FROM emp ORDER BY sal DESC)
WHERE ROWNUM <= 5;






У 

SELECT * FROM
       (
       SELECT ename, sal, RANK () OVER (ORDER BY sal DESC)
       FROM emp
       )
WHERE ROWNUM <= 5;






В " 

SELECT * FROM
   (
   SELECT ename, sal FROM
       (
SELECT ename, sal, RANK () OVER (ORDER BY sal DESC) salrank
       FROM emp
       )
   WHERE salrank <=5
   )
WHERE ROWNUM <= 5;


Читачам старих номерів журналу "Світ Oracle" нагадаю, що варіант А взятий звідти. Зверніть увагу, що порядок співробітників в видавали списках при прогоні запитів різний; це нормально. Варіант В " – Це вимушена виверт, тому що заміна SELECT * на SELECT ename, sal (як хотілося б) у зовнішньому SELECT варіанту У несподівано різко псує результат, що наводить на думку про відсутність знань про магістральний дорозі до бездоганного кодом у розробників Oracle.


А от відповідні плани:






А 

0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 3 Card = 16 Bytes = 320)
1        0      COUNT (STOPKEY)
2        1           VIEW (Cost=3 Card=16 Bytes=320)
2 Березня SORT (UNIQUE STOPKEY) (Cost = 3 Card = 16 Bytes = 192)
4 березня TABLE ACCESS (FULL) OF "EMP" (Cost = 1 Card = 16 Bytes = 192)






Б 

0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 3 Card = 16 Bytes = 320)
1         0      COUNT (STOPKEY)
2         1           VIEW (Cost=3 Card=16 Bytes=320)
2 Березня SORT (ORDER BY STOPKEY) (Cost = 3 Card = 16 Bytes = 144)
4 березня TABLE ACCESS (FULL) OF "EMP" (Cost = 1 Card = 16 Bytes = 144)






У 

0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 3 Card = 16 Bytes = 528)
      1         0       COUNT (STOPKEY)
      2         1            VIEW (Cost=3 Card=16 Bytes=528)
      3         2               WINDOW (SORT)
4 березня TABLE ACCESS (FULL) OF "EMP" (Cost = 1 Card = 16 Bytes = 144)






В "

0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 3 Card = 16 Bytes = 528)
      1         0       COUNT (STOPKEY)
      2         1            VIEW (Cost=3 Card=16 Bytes=528)
      3         2               WINDOW (SORT PUSHED RANK)
4 березня TABLE ACCESS (FULL) OF "EMP" (Cost = 1 Card = 16 Bytes = 144)


Видно, що всі плани різні ("фактор синтаксису"), але всі вони мають приємну для нас спільність:


– У всіх використовується STOPKEY, тобто уникає повний перебір


(З приводу вартостей запиту, зазначених параметром Cost не слід бути надто прискіпливим: по-перше, це лише оцінки оптимізатора, а не реальна статистика. За точної реальної статистикою витрат ресурсів доведеться звернутися до SQL Trace. По-друге, запити зроблені по невеликій табличці демонстраційного прикладу в схемі SCOTT).


… Але перевіряй


Тим не менш, повністю довірятися мудрості CBO сьогодні не можна. Так, ще один варіант запиту по "першій п'ятірці"






Г 

SELECT ename, sal FROM
       (
SELECT ename, sal, ROW_NUMBER () OVER (ORDER BY sal DESC) AS salnumber
       FROM emp
       )
WHERE salnumber <= 5;


дасть такий план:






Г 

0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 3 Card = 16 Bytes = 528)
1         0       VIEW (Cost=3 Card=16 Bytes=528)
2         1           WINDOW (SORT)
2 Березня TABLE ACCESS (FULL) OF "EMP" (Cost = 1 Card = 16 Bytes = 144)


STOPKEY пропав. Приклад Г дає ще один урок: схоже, що визначальну роль для цього плану відіграє синтаксис, і спроба "погратися" іншими факторами нічого нам не дасть. Крім, може бути, "фактора версії СУБД ": для порівняння, той самий запит у версії 9.2 отримає дещо інший план:






Г "9.2 

0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 4 Card = 14 Bytes = 462)
1         0       VIEW (Cost=4 Card=14 Bytes=462)
1 лютому WINDOW (SORT PUSHED RANK) (Cost = 4 Card = 14 Bytes = 112)
2 Березня TABLE ACCESS (FULL) OF "EMP" (Cost = 2 Card = 14 Bytes = 112)


Зверніть також увагу на зміни в оцінках вартості виконуваних кроків. У першому рядку Card = 14, мабуть, недоробка розробників, так як для А, Б і В / В " версія 9.2 дає більш точну оцінку: Card = 5 (Bytes в 9.2 теж оцінюється точніше).


Post Scriptum. Нові для Oracle аналітичні функції, самі по собі надійні, в деяких технічних деталях недостатньо чітко описані. А рішення А прив'язане до побічного ефекту. Тому для конкретного завдання першої (останньої) п'ятірки я б рекомендував наступні надійні рішення: Б і Г.


Посилання по темі

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*