SQL Hints в документації Oracle
- «Про синтаксис: / * + HINT HINT ... * / в PL / SQL пробіл між« + »і першою літерою підказки має значення, в разі відсутності пробілу підказка може бути проігноровано, тобто / * + ALL_ROWS * / правильне використання, а / * + ALL_ROWS * / - неправильне
- Підказки завжди «форсують» використання вартісної оптимізації (cost based optimizer) - крім підказки RULE
- Якщо в запиті використовуються псевдоніми (table alias), в підказках також повинні використовуватися псевдоніми замість назв таблиць:
- У посказках не повинно бути вказівки назви схеми:
- Некоректні підказки (invalid hints) ігноруються без попереджень ... некоректність підказки може бути неочевидна, наприклад:
- вказівка підказки FIRST_ROWS (для отримання перших рядків) для запиту з ORDER BY (оскільки дані повинні бути відсортовані перш, ніж будуть повернуті перші рядки запиту, використання first_rows може не дати бажаного результату)
- зазначені в підказці операції з даними (access path) повинні бути доступні Наприклад: підказка INDEX із зазначенням неіснуючого індексу буде проігнорована без повідомлень про помилки ... »
- спільні цілі оптимізатора
- порядок доступу
- методи з'єднання
- способи виконання [під] запиту
- статистика об'єктів
- трансформації [під] запитів
- використання курсорів
- паралельне виконання
Optimization Goals and Approaches hints
Підказки, що визначають загальні цілі і підходи для оптимізації плану виконання запиту, включаючи правила і методи доступу до даних. Відповідають (майже) описами значень параметра OPTIMIZER_MODE
/ * + RULE * /
Офіційно не підтримується, починаючи з Oracle 10, використовується оптимізація по точно оределённим правилами (Rule Based Optimization) без урахування статистики об'єктів бд. Опис використовуваних правил для інілізаціонного параметра optimizer_mode = rule
Зокрема, незважаючи на використання підказки RULE Oracle буде використовувати Cost-Based Optimization. якщо:
- крім RULE в запиті використовуються інші підказки
- запит використовує партіцірованние або таблиці, організовані у вигляді індексу (IOT), або матеріалізовані уявлення (mview)
- в запиті використовуються кляузи SAMPLE, SPREADSHEET, конструкції GROUPING SETS
- в запиті використовуються ANSI left | full outer join
- запит виконується паралельно
- використовується Flashback cursor (as of [scn | timestamp])
- ...
В 11.2 при використанні RBO може формуватися трейс оптимізатора і використовуватися деякі перетворення запитів за винятком Cost-Based Query Transformation (що логічно :) - див. Зауваження до Індексу в статусі unusable, підказка INDEX і оновлений RBO в Oracle 11.2
/ * + ALL_ROWS * /
«Підказка ALL_ROWS визначає метою якнайшвидше виконання всього запиту з мінімальною витратою ресурсів (best throughput при добуванні всього результуючого набору даних). При одночасному з ALL_ROWS або FIRST_ROWS вказівці підказок, що визначають методи доступу до даних (NO_INDEX_SS, INDEX_COMBINE.) Або вказують методи об'єднання об'єктів БД (LEADING, USE_NL_WITH_INDEX.), Оптимізатор віддає перевагу підказкам методів доступу і об'єднання »
/ * + FIRST_ROWS * /
З документації Oracle 8: «Підказка FIRST_ROWS визначає вартісної підхід (cost-based approach) для оптимізації блоків запиту (statement block) з метою кращого часу відгуку (response time. Мінімальної витрати ресурсів для повернення перших рядків запиту). Відповідно до цієї підказкою оптимізатор робить такі переваги [у виборі операцій доступу до даних і методів з'єднання]:
- При наявності оптимізатор використовує сканування за індексом (index scan) замість повного сканування таблиці (full table scan)
- Якщо є сканування за індексом (index scan), оптимізатор вибирає nested loops join замість sort-merge join в разі, коли сканується індексована таблиця може бути використана як відома таблиця (inner table) для операції nested loops
- Якщо використання індексу (index scan) може бути використано для отримання відсортованих даних (в порядку, визначеному фразою ORDER BY), оптимізатор вибирає індексний доступ, щоб уникнути додаткової сортування »
Починаючи з Oracle 9i: «Підказка FIRST_ROWS зазначена без аргументів, призначена для оптимізації плану виконання з метою якнайшвидшого повернення першого рядка запиту, зберігається тільки для зворотної сумісності (backward compatibility) і стабільності планів виконання (plan stability)»
Значення інілізаціонного параметра OPTIMIZER_MODE = FIRST_ROWS (що рівносильно застосуванню підказки FIRST_ROWS для всіх запитів) анонсують в документації аж до версії Oracle 11.2
/ * + FIRST_ROWS (n) * /
Оптимізація, заснована на вартості (Cost Based Optimization) + використання правил (переваг у виборі плану) з метою отримання кращого часу відгуку для отримання перших n рядків. План розраховується з урахуванням значення n, як цільового кількості обраних запитом рядків (query cardinality).
Не зовсім зрозуміла фраза в документації. «Оптимізатор ігнорує цю підказку в SQL пропозиціях DELETE і UPDATE і в запитах SELECT, що включають блокують операції, такі як сортування і угрупування. Такі SQL пропозиції не можуть бути оптимізовані з метою найменшого часу відгуку (best response time), оскільки Oracle повинен обробити всі рядки запиту до того, як повернути перший рядок результату. При вказівці цієї підказки запити зазначеного типу оптимізуються з метою кращого часу отримання всіх рядків запиту з мінімальною витратою ресурсів (best throughput. Як при використанні підказки ALL_ROWS) »
Oracle always uses ALL_ROWS mode internally for DML statement - в блозі Dion Cho є показовий приклад для версії 10.2
У Oracle 11.2 нічого не змінилося - незалежно від кількості оновлюваних рядків (Rows), що встановлюється функцією rownum ні плани виконання (крім додаткової операції COUNT STOPKEY), ні вартість, ні очікуваний час (Time) не змінюються:
- при спробі оновити тільки один рядок Oracle вибирає той же план з недешевої операцією HASH JOIN SEMI (в запиту використовується конструкція EXISTS) - більш придатною для отримання всіх рядків оновлюваної таблиці (ALL_ROWS mode)
Простий тест для показує, як по-різному режими ALL_ROWS. FIRST_ROWS і FIRST_ROWS (n) впливають на поведінку оптимізатора: власне, плани виконання і методи доступу до даних + Cost + Rows :) на прикладах сортування або угруповання при використанні безглуздого умови object_id> 1 - яким задовольняють всі рядки таблиці T1 - за умовою створення таблиці min (object_id) = 2) *:
*) Статистика для таблиці T1 актуальна і оптимізатор прекрасно «знає» про мінімальний і максимальному значенні T1.Object_id:
або так, простіше:
Access Path Hints
Підказки, що визначають конкретні способи доступу до даних, порядок і застосовувані методи об'єднання проміжних наборів даних (result sets) *
Том Кайт називає ці підказки поганими (bad hints):
«Погані підказки вказують оптимізаторові як слід діяти [при виконанні запиту], який індекс використовувати, в якому порядку обробляти таблиці, за допомогою якої операції (join technique) виробляти з'єднання [джерел даних]»
*) Перед застосування необхідно враховувати ймовірність зміни статистики та заходів, спрямованих (таблиць і індексів), які використовуються в запиті з часом і, як наслідок, можливу неоптимальність згаданих операцій для майбутніх наборів даних. План виконання, оптимізований за допомогою підказок цього типу в середовищі розробки, може виявитися далеко не оптимальним для бойової бд (production system) через відрізняються наборів даних і характеристик системи ( «залізо»)
/ * + LEADING ([@query_block] [tablespec], [tablespec].) * /
«Підказка LEADING вказує оптимізатора використовувати перерахований порядок доступу до таблиць при побудові плану виконання запиту ... гнучкіша, ніж ORDERED ... Повністю ігноруються при використанні двох або більше конфліктуючих підказок LEADING. Для оптимізатора підказка ORDERED має перевагу проти LEADING »
У версії 11.2 може ігноруватися оптимізатором при виконанні перетворень, заснованих на вартості (Cost-Based Query Transformation), наприклад, Table Expansion
/ * + ORDERED * /
«Підказка ORDERED вказує Oracle [при виконанні запиту] проводити з'єднання таблиць в тому ж порядку, в якому таблиці перераховані в конструкції FROM. Oracle рекомендує замість ORDERED використовувати підказку LEADING, що володіє більшою гнучкістю ... », тобто дає оптимізаторові більше можливостей у виборі плану виконання
/ * + USE_HASH ([@query_block] [tablespec] [tablespec] ...) * /
/ * + NO_USE_HASH ([@query_block] [tablespec] [tablespec] ...) * /
... вказує оптимізатора використовувати / не використовувати операцію hash join для з'єднання кожної зазначеної таблиці з іншими джерелами даних Операції CBO: Hash Join
/ * + USE_NL ([@query_block] [inner_table]) * /
/ * + NO_USE_NL ([@query_block] [inner_table]) * /
/ * + USE_NL_WITH_INDEX ([@query_block] inner_table [indexspec]) * /
Ігор, спасибі велике за матеріал, вами виконана величезна робота!
Є питання. Будь ласка, доповніть статтю, якщо вам відомо як діяти в таких ситуаціях.
Ви пишете:
«DRIVING_SITE передбачає управління виконанням (mapping) всього курсора (а не окремих підзапитів) ... [проте можна перенести виконання частини курсора / підзапиту в віддалений огляд]»
Це факт. Ніяк не виходить відправити підзапит цілком на віддалений сервер. NO_MERGE не допомагає, DISTINCT і GROUP BY в підзапиті відсутні. В результаті кожен крок підзапиту цілком витягується з віддаленого на локальний і тільки на локальному сервері відбувається відсікання по access predicates.
Добрий день, Дмитро
добре б, звичайно, бачити запит / тесткейс цілком)