Life in Oracle Oracle Database 12c PL

4 лют. 2014 р.

Oracle Database 12c: PL/SQL ins >

oracle

У наведеному вище запиті, для кожного рядка таблиці виконуюче середовище SQL (SQL-engine) має викликати функціюinc_amount, виконання якої здійснює віртуальна машина PL/SQL (PL/SQL Virtual Machine). Після того, як виконання функції буде завершено, отриманий результат повинен бути повернутий у SQL-engine і далі буде використаний для подальшого отримання результату вибірки. Цей процес, пов'язаний з перемиканням у середу PL/SQL VM та поверненням результатів у SQL-engine називається "перемикання контексту між SQL та PL/SQL" (SQL and PL/SQL context switch).

На перемикання контексту витрачається додаткові ресурси, насамперед процесорний час.

Загалом у всіх популярних книгах з оптимізації продуктивності в середовищі Oracle Database рекомендується уникати використання PL/SQL-дзвінків усередині SQL-запитів. На жаль, це не завжди можливо: для реалізації складних обчислень прямо в тексті запиту часто недостатньо засобів лише мови SQL.

1.1 PL/SQL-підпрограми у визначенні SQL-запиту

До Oracle Database версії 12c доводилося миритися з втратою продуктивності на перемикання контексту між середовищем SQL і PL/SQL VM.

У версії 12c з'явилася можливість у тексті SQL-запроса, у фразі WITH, включати визначення функцій які у ньому використовуються. Це дозволяє мінімізувати витрати на перемикання контексту.

Таким чином, у версії 12с, наведений вище приклад можна переписати наступним чином:

Спробуємо порівняти час виконання запиту, написаного у двох варіантах. Варіант з PL/SQL-підпрограмами визначеними поза запитом (всього втаблиці знаходяться 1 млн. 200 тис. записів): Варіант із функціями визначеними в самому тексті запиту:

У наведеному вище тесті швидкість виконання запиту збільшилася більш ніж у три рази. Звичайно, цей приклад є штучним: запит дуже простий, і основні витрати при його виконанні становлять саме витрати на перемикання контексту. У реальних додатках виграш, швидше за все, не буде таким фантастичним, але все одно буде значним! Також рекомендується, щоб всередині PL/SQL-підпрограм визначених всередині SQL-запиту, не було сторонніх PL/SQL-дзвінків (об'єкти, що викликаються в яких визначені поза запитом).

Повернемося до нашого прикладу, і спробуємо визначити процедуруincпоза запитом, тобто звичайним чином: Як Ви бачите, тривалість виконання запиту збільшилася, але все одно вона менша, ніж з варіантом використання PL/SQL-підпрограм цілком визначених усередині SQL-запиту.

Слід зазначити, що при визначенні PL/SQL-підпрограм всередині запиту не відбувається його безпосереднього виконання в середовищі SQL-engine, тобто середовище виконання SQL не має у своєму складі власної віртуальної машини PL/SQL. Відбувається генерування додаткової інформації для SQL-компілятора, за допомогою якої під час компіляції та виконання запиту зменшуються витрати на перемикання контексту. Таким чином, перемикання контексту як таке все одно відбувається, але значно знижуються накладні витрати на це перемикання!

1.2 Прагма компіляції UDF для вже існуючих PL/SQL-підпрограм

Як же бути в тому випадку, якщо вже є працюючий успадкований додаток (розроблений до версії Oracle Database 12c) в якому зазвичай визначені PL/SQL-функції, і ці функції використовуються в SQL-запити? Переписувати всі SQL-запити, в яких є PL/SQL-дзвінки, може бути трудомістким завданням. Більш того, може виявитися так, що частина PL/SQL підпрограм викликаються як в SQL-запитах, так і в інших PL/SQL-об'єктах. У цьому випадку переписування запитів призведе до дублювання коду, тобто один і той самий код потрібно буде супроводжувати у двох місцях!

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

Час виконання викликів PL/SQL-процедур оголошених з директивою компіляції UDF збільшився приблизно на сім відсотків. Уповільнення викликів невелике, але все-таки не варто встановлювати цю директиву компіляції у всіх PL/SQL-підпрограмах, - це потрібно робити тільки в тих процедурах, які будуть інтенсивно викликатися в SQL-запитах.

1.3 Використання PL/SQL-підпрограм у підзапитахЄ одна важлива особливість пов'язана з використанням PL/SQL викликів у підзапитах. Якщо ми спробуємо використовувати визначення PL/SQL у підзапиті, негайно отримаємо помилку: Додавання підказки оптимізатора (hint) WITH_PLSQL у запиті верхнього рівня (top level query) вирішує цю проблему: /*+ WITH_PLSQL */1.4 Проблема з використанням детермінованих функцій у запитахОсобливу увагу слід звернути на використання недермінованих функцій у запиті. Всередині запиту функція стає недетермінованою, навіть незважаючи на те, що опціяDETERMINISTICбула вказана в її визначенні: Тепер спробуємо функціюinc_amountоголосити детермінованою: На жаль, функція inc_amount почала викликатися для кожного рядка незважаючи на те, що була оголошена в тексті запиту як детермінована. При цьому не важливо: чи тіло функції було явно включено в текстабо ми скористалися директивою компіляціїUDF:

database

Мал. 1Функція перестає бути детермінованістю всередині SQL-запиту

Якщо функція "важка", тобто при виконанні дає велике обчислювальне навантаження, доводиться використовувати старий синтаксис, із значними витратами на перемикання контексту:

Сподіватимемося, що у майбутніх версіях СУБД, цей недолік буде усунено.

1.5 Використання всередині запитів функцій з кешем результатів

Хороша новина полягає в тому, що для існуючих функцій, що використовують кеш результатів (RESULT_CACHE), їх вбудовування в запит через директиву компіляціїUDFчудово працює:

Але для функцій визначення яких безпосередньо входить у запит, помилка виникає ще етапі компіляції:

Також сподіватимемося, що в наступних версіях (або патчсетах) СУБД цю проблему теж буде усунуто.

ВисновокВбудовування PL/SQL-збережених процедур безпосередньо в текст запитів, є дуже сильним інструментом підвищення продуктивності SQL-запитів, що використовують виклики PL/SQL. Директива компіляціїUDF (pragma UDF)дозволяє з мінімальною модифікацією коду програми використовувати цю нову технологію. При використанні вбудовування PL/SQL-підпрограм для визначення запиту, слід звернути увагу на детерміновані (deterministic) PL/SQL-функції. В даний момент детерміновані функції при перенесенні їх у тіло запиту (явно чи неявно, за допомогою директиви UDF) втрачають цю властивість.

На мій погляд, перше, що повинен зробити розробник після переходу на Oracle Database 12c - це вставити прагму UDF у всі недетерміновані PL/SQL-функції, які використовуються вSQL-запитах! :-)