Ще один погляд на Oracle SQL Plan Management, DB blogs and notes

Про особливості роботи з різними СУБД на commodity hardware

Ще один погляд на Oracle SQL Plan Management

Профілі - нове (щодо) слово в БД. Профілі навантаження на файлову систему, профілі роботи сесії, профілі SQL-запитів, ... Нарешті, Baseline або Oracle SQL Plan Management - новий засіб роботи з планами виконання запитів у процесі життя програми, що дозволяє керувати вибором плану виконання та його еволюціями.

Технічні питання вже неодноразово обговорювалися й інформації на цю тему маса:

Нижче робиться ще одна спроба розповісти про принципи роботи SPM на одному простому прикладі.

Чим принципово SPM відрізняється від Outline? SPM не зберігає ні плану виконання, ні будь-яких хінтів оптимізатору, які потрібно буде вставити у запит. Насправді це не потрібно: середовище виконання може змінитися, і хінти або перестануть працювати, або працюватимуть негаразд, як очікувалося.

SPM зберігає код плану виконання, який ми хотіли б отримати. Тобто. насправді є цільовою функцією. Тобто. тепер окрім first_rows, all_rows є можливість вказати код плану виконання, який ми хотіли б отримати. Працює це так. Припустимо, у нас є запит (1):

І ми хотіли б отримати nested loop щодо виконання цього запиту (це просто приклад). Беремо вихідний запит і прописуємо туди потрібні хінти (2):

Отримуємо план виконання

Для одного і того ж запиту ми можемо легко знайти в документації поради щодо створення baseline. Але що, якщо ми хочемо отримати такий же план виконання іншого запиту (1)? А впливати на код програми та вставити хінт безпосередньо в текст запиту не можна (немає часу тощо).

За допомогою SPM це робиться досить легко.для тексту запиту (1) ми вказуємо як мету план виконання запиту (2):

Іноді цього достатньо, але можете переконатися самі: для запиту (1) SPM не працює.

Розібратися, в чому справа, допомагає трасування 10053. Але перш, ніж лізти у налагоджувальні файли, давайте подивимося на передбачуваний план виконання запиту:

Бачимо, що план відрізняється від того, на який ми розраховували замість операції Nested loop у ньому Filter. Це не помилка. Дивимося секцію SPM у трасуванні 10053:

Що сталося? Оптимізатор запитів на етапі вибору плану виконання визначив, що є SPM для sql_id. На основі підказок із профілю SPM після етапу перерахування спробував отримати план із заданим номером і не зміг цього зробити. Те саме ми бачили, коли намагалися вивести план виконання SPM за допомогою пакета DBMS_XPLAN. У цей момент вже неможливо сказати, яким мав бути план виконання (відомий лише його ідентифікатор). Вірогідно відомо, що для заданого sql_id у поточному оточенні його отримати неможливо. Отже, стандартно вибирається план виконання з мінімальною вартістю.

Причини такої поведінки із запитом (1) – евристики оптимізатора. Частина операцій у певних умовах ніколи не розглядаються. За допомогою хінтів нам вдалося у запиті (2) змусити оптимізатор вибрати одну з таких операцій, але при спробі сказати оптимізатору для запиту (1): «Використовуй план із номером n», ми зазнали невдачі — план із номером n у нормальній ситуації взагалі не розглядається.

Інші причини, чому може не використовуватися SPM:

  1. Розбіжність sql_id. За будь-якої зміни тексту запиту змінюється його sql_id. Таке буває, наприклад, якщо не використовувати bind змінні, змінити порядок сортування або додатистовпців у секцію select.
  2. Змінився перелік об'єктів. Наприклад, було змінено або видалено один із індексів.
  3. Змінився тип об'єктів. Наприклад, стовпець став not null або одна з bind змінних змінила свій тип.
  4. Змінились параметри оптимізатора. Наприклад, відключили перетворення group-by placement.

Від статистики та даних у таблицях SPM не залежить (хороші новини) 🙂

Постскриптум

Запитання 1. Що ж за хінти у такому випадку зберігаються у вигляді XML у профілі? Хінти оптимізатор, за допомогою яких він намагається отримати цільовий план виконання. Ці хінти не впливають на запит і необхідні лише для прискорення пошуку. Справді, не виконувати дорогу фазу оптимізації щоразу заново.

Запитання 2. А що ж у такому разі показує display_sql_plan_baseline? План виконання, який удалося отримати за допомогою цих хінтів. Буває, як у прикладі вище, що ці хінти діють зовсім не так, як замислювалося. Добре, що в цьому випадку SPM не впливає на план виконання.