Практика використання хинтов в Oracle
Підстановка хинтов в SQL-запит без модифікації запиту
В процесі експлуатації баз даних іноді виникає необхідність скорегувати плани SQL-запитів, не вдаючись до зміни тексту SQL-запиту
У цьому можуть допомогти методи, що з'явилися в Oracle, починаючи з Oracle 9i і розвинені в Oracle 11g, що дозволяють підставляти хинти (підказки оптимізатора) в запит, не змінюючи текст працюючого запиту і не компілюючи заново збережену процедуру (функція, процедур, пакет), в якої він знаходиться.
Практика показала, що ці методи особливо ефективні в період переходу від однієї версії Oracle до іншої. Так, наприклад, при переході з версії з Oracle 11.2.0.3 на 11.2.0.4 або на Oracle 12c плани виконання запитів, які були оптимальні в попередній версії, часом стають далеко не оптимальними.
В цьому випадку повернутися до оптимального плану виконання дозволяють різні прийоми, наприклад модифікація структури запиту або додавання хинта (наприклад, хинта index) в запит з подальшою перекомпиляцией збереженої процедури, в якій він знаходиться. Може бути також застосований хинт в запиті, який повертає роботу оптимізатора до попередніх версій Oracle, в яких плани виконання були оптимальними. Як показала практика, в Oracle 11g ветом можуть допомогти хинти:
При наявності проблем з планами виконання в запитах з bind-змінними (пов'язаними змінними) оптимізувати план виконання запиту може допомогти (крім хинта, такого як index) ще хинт:
/ * + Opt_param ( '_optim_peek_user_binds' 'false') * /
Даний хинт змінює значення Недокументовані параметра ініціалізації _optim_peek_user_binds, що впливає на плани виконання з bind-змінними, з true (встановлюється за умовчанням) на значення false.
В цьому випадку можна скористатися методами оперативної підстановки хинта в запит без перекомпіляції збереженої процедури. Застосування даних методів, крім того, дозволяє здійснювати швидке скасування дії застосування хинтов і оперативно пробувати інші варіанти хинтов для поліпшення плану.
Важливою перевагою застосування методів підстановки хинта є те, що перед тим, як вставити хинт в запит (з подальшою перекомпиляцией збереженої процедури, в якій знаходиться цей запит), можна переконатися, що цей хинт дійсно покращує план виконання запиту.
У той же час підстановка хинта в запиті може бути обгрунтована, як правило, в разі, коли перш за все важлива стабільність планів виконання. Підставка хинта може привести до штучного обмеження свободи дій оптимізатора, потенційно веде до неможливості використання інших, можливо, більш оптимальних планів (наприклад, в залежності від значень bind-змінних).
Починаючи з Oracle 9i з'явився метод заміни або додавання хинтов в запит, до якого немає доступу на редагування, відомий як метод використання збережених шаблонів (Stored Outlines). Даний метод дозволяє не тільки підставляти, але і замінювати існуючий хинт на інший, причому як в основному запиті, так і в підзапитах у запиту.
Разом з тим даний метод не знайшов у нас широкого застосування, в силу того, що зіткнулися з проблемою використання цього методу в запитах, в яких є bind-змінні (тобто в запитах типу SELECT * FROM agreement WHERE isn =: b) , в той час як більшість запитів у нас базується на bind-змінних.
Існують інші методи підстановки хинта в запит, наприклад SQL profiles, разом з тим у нас більш затребуваними виявилися методи, що з'явилися в Oracle 11g, такі як SQL plan baseline і SQL patch. Це обумовлено зручністю роботи з ними і можливістю роботи із запитами, які містять bind-змінні.
PDF-версію даного номера можна придбати в нашому магазині.