Складний запит, використання Baseline та Bind-Aware Cursor Sharing, Oracle mechanics
Продуктивність СУБД та суміжні питання
Складний запит, використання Baseline та Bind-Aware Cursor Sharing
Отже, "все пропало - запит повільно працює" => запит, що раніше виконувався за секунди, запущений годину тому і продовжується:
Оскільки запит все ще виконується, можна увімкнути звичайнийSQL trace :
, який показує нескінченну низку одноблочних читань, виражених очікуваннямиread by other session, із зазначенням об'єкта, що читається бд obj#=35235, з незначними вкрапленнями супутніх кластерних очікуваньgc buffer busy acquire плюс місце в буферному кеші -latch: cache buffers chains :
Що підтверджується подальшою обробкою всього файлу:
один об'єкт бд (таблиця E_LINES з DATA_OBJECT_ID=35235) читається багато і в одноблочному режимі
SQL trace дає хороші оцінки кількості очікувань, для аналізу запиту та плану виконання можна використовуватиActive Session History, дані якої після закінчення запиту зовсім не зайве зберегти:
— як справедливо зазначив Олег Коротков у доповіді на Пітерському семінарі RuOUG 2012 — дані в буфері ASH (V$ACTIVE_SESSION_HISTORY ) значно повніші, ніж в історичній таблиціDBA_HIST_ACTIVE_SESS_HISTORY :
- в історії збереглася лише 10-та частина даних ASH. Тим не менш, цього достатньо для точного визначення"проблемного" місця в плані виконання - SQL_PLAN_LINE_ID 42,41:
— як видно з порівняння SQL трейсу та плану виконання, кількість рядків, які отримуються при скануванні індексу E_LINES_N1* = 1741K розраховано досить точно, а ось передбачуване число рядків, що отримуються під час операціїTABLE ACCESS BY INDEX ROWID з таблиці E_LINES ( 1233) явно недооцінено.
Причиною поганої оцінки може бути, наприклад, неточність в оцінці оптимізатором кіл-ва рядків, одержуваних в результаті картезіанського твору -MERGE JOIN CARTESIAN - див. фіча/покращення, які непросто точно визначити без трейсу оптимізатора, проте в цьому випадку можна обійтися без цього
Як я вказав спочатку, проблема відноситься до комплексного запиту, що складається з безлічі вкладених один в одного (як ляльки в матрьошку). При цьому окремо ці огляди виконуються швидко — всі вони зі старого, шановного додатку Oracle Application / OEBS — «шліфувалися століттями» :) І оскільки наведений вище проблемний фрагмент плану є наслідком об'єднання оглядів, можна спробувати відключити механізм. view merging , що відповідає заMERGE оглядів - об'єктів бд у простих випадках (без GROUP BY, DISTINCT, etc.), або відключити повністю всі операціїquery transformation, що включають числаview merging, або взагалі повернути поведінку оптимізатора до попередньої версії за допомогою параметраoptimizer_features_enable = '10.2.0.4' на рівні запиту
Перевірка припущень з актуальними значеннями пов'язаних змінних із V$SQL_MONITOR (запит повертає 400к+ рядків):
— враховуючи, що «проблемний» запит ізпрограми виконувався більше 4-х годин, результат вийшов цілком гідний, найшвидший результат отримано при відключенні всіх простих трансформацій запиту підказкоюno_query_transformation — запит справді великий розмір тексту > 300 рядків, використовує кілька оглядів, план містить до 100 операцій.
Отриманий «швидкий» план у частині, що цікавить:
відрізняється від повільного:
- збереженням структури оглядів -view merging не проводиться
- при цьому для доступу до таблиці E_LINES використовується інший індекс
- значно меншими оцінками кількості рядків -E-Rows - судячи з поганого плану, досить точними
- песимістичнішими оцінками часу виконання та вартості — очевидно, неправильними
Залишається закріпити правильний план без зміни тексту запиту, для чого рекомендується використовувати передову та досить гнучку технологіюSQL Plan Management :
Варіант 1-й із завантаженням плану з AWR-репозиторію
1a) Створення «порожнього»SQLSET, наприклад, імені проблемного запиту «SQLSET_a7xju9dz0abdx»:
1b) Завантаження існуючого запиту з «поганим» планом з AWR у SQLSET
- Показує «поганий» план, з яким був створенийSQLSET
1d) Завантаження «поганого» плану зі створеногоSQLSET уBASELINE :
Метою кроків 1a–1d було створенняBASELINE, що містить існуючий запит з «повільним» планом з репозиторію AWR Те саме можна зробити швидше, якщо проблемний запит знаходиться вSHARED POOL, наприклад так
Для наступного кроку користувачу, що виконує запит, може знадобитися додаткові привілеї:
2) Виконуємо той самий запит із «правильним планом» (відкоригованим за допомогоюпідказок, наприклад), після чого асоціюємо «правильний план» з наявнимBASELINE Приклад для запиту зі зв'язаними змінними:
3) (опційно) можна видалитиBASELINE з поганим планом:
4) (Опціонально) можна навіки зафіксувати BASELINE з правильним планом:)
Після перерахованих вище маніпуляцій у V$SQL можна бачити, що активні курсори успішно використовують створений V$SQL.SQL_PLAN_BASELINE='SQL_PLAN_a9q7v9pfcbpt8c8976431′, запит виконується швидко, нові технології SPM успішно працюють!
До цього моменту все було красиво за книжками, проте за кілька днів можна виявити, що створенийsql plan baseline вже не використовується запитом:
- Запит знову використовує не найкращий план - при цьому неважливо, що запит поки виконується швидко - просто вибирається трохи рядків
У секції Notes плану з'являється згадка про використанняcardinality feedback :
Курсори позначені якBIND AWARE і, судячи з того, щоBASELINE вже не використовується - вплив технологіяBind-Aware Cursor Sharing виявилася сильнішою, ніж уsql plan baseline, незважаючи на те, що в блозі Oracle Optimizer Team стверджувалося протилежне:
SPM and adaptive cursor sharing …
Так, якщо hard parse occurs,Нормальний SPM план вибору algorithm є використаним незважаючи на те, що стан є основою sensitive.
Крім того, за наявною інформацією можна зробити висновок, що рішення про застосуванняBASC Oracle приймає не пізніше, ніж дізнається, що для запиту існує зафіксований готовийbaseline, відповідно до плану якого має за ідеєю виконуватися запит
Для відключення BASC можна спробувати змінити набір підказок у запиті з «хорошим» планом, якийвикористовувався для покращення плануbaseline – використану підказку/*+ no_query_transformation*/ замінити додати на/*+ no_query_transformation no_bind_aware*/ :
Як виявилося, Oracle оцінює вміст (набір підказок) baseline до того, як приймає рішення про застосуванняBIND-AWARE Cursor Sharing - що досить розумно, т.к. інакше надійно зафіксувати план за допомогою SPM було б проблематично. Відповідно, після описаного відключенняBASC уbaseline додаванням підказкиno_bind_aware :
– всі активні курсори (IS_SHAREABLE=Y) почали успішно використовувати модифікований SQL_PLAN_BASELINE, і, судячи з значення полів V$SQL.IS_BIND_AWARE=N та V$SQL.IS_BIND_SENSITIVE=N — вже не є не тількиBind Aware, але й залежними від значень пов'язаних змінних, що в даному випадку радує - Oracle сприйняв сигнал) + у причинах створення нових активних курсорів CHILD_NUMBER=2,3,4,5 вже немаєBind mismatch, що вказувала на використання BACS