Відразу уточню, що описуватиму на прикладі використання фрі утиліти OraDeveloper Studio. Чому? Тому що звичайними запитами цього зробити не вдалося, а часу та бажання розбиратися не було, якщо вже є спосіб простіше. 😉

Отже, навіщо це взагалі потрібно? Опишу вам конкретний приклад, Через який я і був змушений проводити оптимізацію

Завдання - вантажити в базу десятки тисяч рядків даних. Для кожного рядка необхідно заздалегідь по базі знайти додаткові дані одним досить громіздким запитом (4 таблиці через джойни).
Проблема – завантаження 15 тисяч рядків займає 8-9 годин. Тому що за умовами завдання завантажувати треба часто, а не один раз на п'ятирічку… Загалом, треба довести час до прийнятного.

Що я зробив?
1. З'ясував, що гальмує саме селект (дані вставляються та оновлюються в таблицях, де купа рядків та частина з таблиць не має ні індексів, ні ключів – звідси й сумніви у вині селекту).
2. Перевірив наявність індексів на полях, що використовуються запитом. Додав відсутніх.
3. Запитав допомоги у знаючих. 🙂

Обізнані порадили проаналізувати план виконання запиту і пояснили, як це зробити в OraDev.
Створюємо нове вікно запиту (Ctrl+N). Копіюємо у нього наш запит. Тиснемо Alt+G. Вибираємо вже існуючу або створюємо нову таблицюплану.
Після виконання з'явиться дерево плану виконання. Самостійно і без півлітри в ньому розібратися не так просто. 😉

Що ж нас цікавить у цьому дереві? Нас цікавлять вузли (кроки), для яких вказано великий Cost кроку. Ціну кроку ви можете побачити у властивостях кроку (у мене віконце властивостей постійно відкрите і тому мені треба лише вибрати потрібний крок; вам може знадобитися вибирати властивості по правому кліку на кроці). Шукаємо повільний крок (найвищий вузол, корінь дерева плану, в розрахунок особливо не беремо - там буде вказана загальна ціна запиту, а ми вже знаємо, що проблема саме в цьому запиті). Знайшли? Тепер дивимося, з якою таблицею, якими її полями та з якою кількістю рядків працює крок – це є у властивостях та імені кроку. Дивимось і думаємо, чому у нас так повільно?
У мене, наприклад, один із кроків працював із 4000 записів замість одного-трьох записів (не тисяч). Такого бути не повинно було в принципі — я ж обмежую вибірку саме для того, щоб вибирати з потрібного діапазону, а не купи зайвого барахла. Уважно придивившись до умови джойна, я помітив, що втратив одне з полів. Додав поле у ​​запит і все стало на свої місця. Ціна запиту (повна) зменшилась з 531 до 6. 🙂

Спасибі камрадам nest та detect за допомогу.

P.S. Вибачте, що не скріншоти. З ними було б набагато наочніше, але… Через конфіденційність деякої інформації довелося б замазувати 80%, і тоді знову вийшло б малозрозуміло.
P.P.S. Загальний час завантаження значно скоротився. На завантаження до бази 17.5 тисяч рядків даних пішло 12 хвилин. Порівняно з 8-9 годинами… Ну, ви й самі вже зрозуміли. 😉

5 відповідей

Висновок EXPLAIN PLAN - це налагоджувальний висновок оптимізатора запитів Oracle. COST - це кінцевий результат оптимізатора витрат (CBO), метою якого є вибір того, який з багатьох можливих планів має використовуватися для запуску запиту. CBO розраховує відносну вартість для кожного плану, потім вибирає план із найнижчою вартістю.

(Примітка: у деяких випадках у CBO не вистачає часу для оцінки всіх можливих планів, у цих випадках він просто вибирає план із найнижчою вартістю, знайденою досі)

Загалом, одним із найбільших вкладів у повільний запит є кількість рядків, прочитаних для обслуговування запиту (точніше, блоків), тому вартість частково базуватиметься на кількості рядків, які оцінюють оптимізатор, необхідно буде прочитати.

Наприклад, скажімо, у вас є наступний запит:

SELECT emp_id FROM employees WHERE months_of_service = 6;

(Стовпець months_of_service має обмеження NOT NULL на ньому та звичайний індекс на ньому.)

Є два основних плани, які може вибрати оптимізатор тут:

• План 1: Прочитайте всі рядки з таблиці "співробітники", для кожного перевірте, чи є предикат дійсним (months_of_service=6).
• План 2. Прочитайте індекс, де months_of_service=6 (це призводить до набору ROWID), а потім отримайте доступ до таблиці на основі повернутих ROWID.

Припустимо, що таблиця "співробітники" містить 1000000 (1 мільйон) рядків. Нехай далі уявіть, що значення months_of_service варіюються від 1 до 12 і з якоїсь причини досить рівномірно розподілені.

Вартість Plan 1, яка включає FULL SCAN, буде коштувати читання всіх рядків в таблиці співробітників, що приблизно дорівнює 1 000 000; але оскільки Oracle часто може зчитувати блоки з використанням багатоблочних читань, фактична вартість буде нижчою (залежно від того, як налаштована ваша база даних) – наприклад, припустимо, що кількість відліків з кількома блоками дорівнює 10 – розрахункова вартість повного сканування становитиме 1,000,000/10 ; Загальна вартість = 100000.

Вартість Плану 2, яка включає сканування INDEX RANGE SCAN і пошук таблиці по ROWID, буде коштувати сканування індексу, а також витрати на доступ до таблиці за допомогою ROWID. Я не вдаватимуся до того, як сканування індексів діапазону буде коштувати, але нехай уявіть, що вартість сканування індексу індексу становить 1 на рядок; ми очікуємо знайти збіг в 1 із 12 випадків, тому вартість сканування індексу становить 1,000,000/12 = 83,333; плюс вартість доступу до таблиці (припустимо, що один блок зчитується за доступ, ми не можемо використовувати багатоблочні читання тут) = 83,333; Загальна вартість = 166666.

Як ви можете бачити, вартість плану 1 (повне сканування) менша, ніж вартість плану 2 (індексна перевірка + доступ по rowid) - це означає, що CBO обиратиме ПОВНЕ сканування.

Якщо припущення, зроблені тут оптимізатором, вірні, то насправді план 1 буде кращим і набагато ефективнішим, ніж План 2 - який спростовує міф про те, що ПОВНІ сканування "завжди погані".

Результати були б зовсім різними, якщо метою оптимізатора було FIRST_ROWS (n) замість ALL_ROWS - в цьому випадку оптимізатор віддасть перевагу плану 2, тому що він часто повертатиме перші кілька рядків швидше, ціною менш ефективною для весь запит.

CBO будує дерево рішень, оцінюючи витрати на кожен можливий шлях виконання, доступний для кожного запиту. Витрати встановлюються параметром CPU_cost або I/O_cost, встановленим в екземплярі. І CBO оцінює витрати наскільки це можливо з існуючою статистикою таблиць та індексів, які будуть використовуватися в запиті. Ви не повинні налаштовувати свій запит, ґрунтуючись лише на вартості. Вартість дозволяє зрозуміти, ЧОМУ оптимізатор робить те, що він робить. Без витрат ви могли б зрозуміти, чому оптимізатор вибрав план, який він зробив. Більше низька вартістьне означає швидший запит. Є випадки коли це вірно, і будуть випадки коли це неправильно. Вартість заснована на таблиці статистики, і якщо вони помиляються, вартість буде неправильною.

При налаштуванні запиту ви повинні подивитися на потужність та кількість рядків кожного кроку. Чи мають вони сенс? Чи оптимізатор вважається коректним? Чи правильно повертаються рядки. Якщо інформація є неправильною, то, швидше за все, оптимізатор не має належної інформації, необхідної для прийняття правильного рішення. Це може бути пов'язано зі застарілими або відсутніми статистичними даними у таблиці та індексі, а також cpu-stats. Найкраще оновлювати статистику при налаштуванні запиту, щоб максимально використовувати оптимізатор. Знання вашої схеми також дуже допомагає при налаштуванні. Знаючи, коли оптимізатор вибрав справді погане рішенняі вказав його на вірний шляхза допомогою невеликої підказки, можна заощадити час.

Ваша згадка "FULL" вказує мені, що запит виконує повноекранне сканування, щоб знайти ваші дані. У деяких випадках це нормально, інакше це показник поганого запису індексування/запиту.

Як правило, з поясненнями, ви хочете, щоб ваш запит використовував ключі, таким чином Oracle може знайти потрібні вам дані, звернувшись до найменшої кількості рядків. Зрештою, ви коли-небудь зможете отримати доступ до архітектури таблиць. Якщо витрати залишаються надто високими, вам, можливо, доведеться подумати про те, як налаштувати схему вашої схеми на більш високу продуктивність.

В останніх версіях Oracle COST є час, який оптимізатор очікує від запиту, виражений в одиницях часу, необхідного для читання одного блоку.

Отже, якщо читання одного блоку займає 2 мс, а вартість виражається як "250", запит може бути потрібним для завершення 500 мс.

Оптимізатор розраховує вартість, засновану на оцінному числі одноблочних та багатоблочних зчитувань, а також на споживанні процесора у плані. останній може бути дуже корисним для мінімізації витрат, виконуючи певні операції перед іншими, щоб спробувати уникнути великих витрат на CPU.

Виникає питання, як оптимізатор знає, як довго виконуються операції. останні версії Oracle дозволяють створювати колекції "системної статистики", які, безумовно, не слід плутати зі статистикою таблиць чи індексів. Системна статистика – це вимірювання продуктивності апаратного забезпечення, в основному важливо:

• Як довго виконується читання одного блоку.
• Як довго багатозадачне читання бере
• Наскільки велике багатоблочне читання (часто відмінне від максимально можливого через те, що розмір таблиці менше максимального значення та інші причини).
• Продуктивність процесора

Ці цифри можуть відрізнятися залежно від операційного середовища системи, і різні статистичні дані можуть зберігатися для операцій "денного OLTP" і операцій "нічної періодичної звітності", а для "звітів про кінець місяця", якщо ви хочете.

Враховуючи ці набори статистичних даних, даний планвиконання запиту можна оцінити для вартості в різних операційних середовищах, що може сприяти використанню повного сканування таблиць у кілька разів або сканування індексів інших.

Вартість не ідеальна, але оптимізатор стає кращим за самоконтролю з кожним випуском і може відгукнутися на фактичну вартість порівняно з розрахунковою вартістю, щоб приймати найкращі рішенняв майбутньому. це також ускладнює прогнозування.

Зверніть увагу, що вартість не обов'язково настінний час, Оскільки операції паралельного запиту споживають загальну кількість часу для декількох потоків.

У старих версіях Oracle вартість операцій з ЦП була проігнорована, а відносна вартість одно- та багатоблочних читань була ефективно виправлена ​​відповідно до параметрів init.

Ви також можете запросити v $sql та v $session для отримання статистики про операторів SQL, і це буде мати докладні показники для всіх видів ресурсів, таймінгів та виконань.

Це як цвях у підошві улюбленого черевика. Ходити можна, але все частіше ловиш себе на бажанні залишитися на місці або перепоручити справу іншим. Дрібні незручності не тільки уповільнюють нашу роботу, а й знижують мотивацію, вносять перешкоди у процес, знижують якість результату. І якщо знайшовся друг, який навчив вас взяти молоток і забити цей цвях, ви не тільки будете вдячні йому за допомогу, але й самі допоможете іншим, позбавивши їх дрібної, але дуже дратівливої ​​перешкоди. Для цього і потрібно спілкуватися, ділитися не лише глибокими та потаємними знаннями у форумах та на сайтах на кшталт Хабра, а й своїми простими трюками та «маленькими хитрощами»

Як і будь-який текст, запити та програми на SQL можна створювати в будь-якому текстовому редакторі. Але якщо ви професіонал, ви дуже багато і часто працюєте з SQL, то вам вже не буде достатньо наявності підсвічування синтаксису та автоматичного переформатування коду, особливо якщо вам доводиться перемикатися між різними версіями однієї СУБД або різними платформамиСУБД.

Нещодавно мені довелося спілкуватися з одним із провідних професіоналів СУБД Oracle. Він розповів багато цікавого про роботу з планами виконання запитів у різних версіяхцієї СУБД і не посоромився розповісти всім про використовувані ним інструменти, прийоми і дати трохи корисних дрібних порад. Я зробив переклад однієї зі статей у його блозі та хотів би запропонувати його увазі Хабравчан. Незважаючи на те, що описаний прийом застосовувався для роботи з Oracle, я тепер успішно застосовую той же підхід для MS SQL і Sybase.

Запустіть запит на виконання та з'явиться закладка Query Plan, заповнена планом виконання.


Помістіть курсор миші на будь-який з вузлів на діаграмі та з'явиться додаткова корисна інформація, що стосується цього кроку виконання з плану запиту!
За замовчуванням Rapid SQL показує план виконання у графічному вигляді. Я вийшов зі старого світу оптимізації…. Вважаю за краще текстову версію, тому натискаю праву кнопкумиші у вікні з планом та вибираю “View as Text”.
Вважаю за краще бачити текст запиту і план одночасно.


Це легко зробити. Чи бачите закладки вікон ISQL внизу головного вікна? Спочатку ми повинні налаштувати Rapid SQL, щоб він видавав план в окремому вікні.


Натисніть кнопку Options (лівий червоний кружок) і встановіть опцію 'Unattached' для Result window. Це призведе до створення двох окремих закладок внизу Rapid SQL після запуску запиту на виконання. Просто протягніть це вікно за закладку і з'явиться прямокутник, куди можна перемістити це вікно.
Або можна скористатися пунктом Tile windows із головного меню програми

І ще: все це так само працює і в DBArtisan – рішенні для адміністраторів баз даних.

Це як цвях у підошві улюбленого черевика. Ходити можна, але все частіше ловиш себе на бажанні залишитися на місці або перепоручити справу іншим. Дрібні незручності не тільки уповільнюють нашу роботу, а й знижують мотивацію, вносять перешкоди у процес, знижують якість результату. І якщо знайшовся друг, який навчив вас взяти молоток і забити цей цвях, ви не тільки будете вдячні йому за допомогу, але й самі допоможете іншим, позбавивши їх дрібної, але дуже дратівливої ​​перешкоди. Для цього і потрібно спілкуватися, ділитися не лише глибокими та потаємними знаннями у форумах та на сайтах на кшталт Хабра, а й своїми простими трюками та «маленькими хитрощами»

Як і будь-який текст, запити та програми на SQL можна створювати у будь-якому текстовому редакторі. Але якщо ви професіонал, ви дуже багато і часто працюєте з SQL, то вам вже не буде достатньо підсвічування синтаксису і автоматичного переформатування коду, особливо, якщо вам доводиться перемикатися між різними версіями однієї СУБД або різними платформами СУБД.

Нещодавно мені довелося спілкуватися з одним із провідних професіоналів СУБД Oracle. Він розповів багато цікавого про роботу з планами виконання запитів у різних версіях цієї СУБД і не посоромився розповісти всім про інструменти, прийоми і дати трохи корисних дрібних порад. Я зробив переклад однієї зі статей у його блозі та хотів би запропонувати його увазі Хабравчан. Незважаючи на те, що описаний прийом застосовувався для роботи з Oracle, я тепер успішно застосовую той же підхід для MS SQL і Sybase.

Запустіть запит на виконання та з'явиться закладка Query Plan, заповнена планом виконання.


Помістіть курсор миші на будь-який з вузлів на діаграмі і з'явиться додаткова корисна інформація щодо цього кроку виконання з плану запиту!
За замовчуванням Rapid SQL показує план виконання у графічному вигляді. Я вийшов зі старого світу оптимізації…. Віддаю перевагу текстовій версії, тому натискаю праву кнопку миші у вікні з планом і вибираю “View as Text”.
Вважаю за краще бачити текст запиту і план одночасно.


Це легко зробити. Чи бачите закладки вікон ISQL внизу головного вікна? Спочатку ми повинні налаштувати Rapid SQL, щоб він видавав план в окремому вікні.


Натисніть кнопку Options (лівий червоний кружок) і встановіть опцію 'Unattached' для Result window. Це призведе до створення двох окремих закладок внизу Rapid SQL після запуску запиту на виконання. Просто протягніть це вікно за закладку і з'явиться прямокутник, куди можна перемістити це вікно.
Або можна скористатися пунктом Tile windows із головного меню програми

І ще: все це так само працює і в DBArtisan – рішенні для адміністраторів баз даних.

Oracle Database 11g Налаштування продуктивності.

Як читати плани виконання запитів

Що таке план виконання?

План виконання - це висновок оптимізатора інтерпретований мовою движка виконання. Він інструктує двигун виконання про операції, які необхідно виконати для отримання даних, що вимагаються виразом максимально швидко і ефективно.

Вираз EXPLAIN PLAN захоплює план виконання обраний оптимізатором для виконання виразів типу SELECT, UPDATE, DELETE та INSERT. Кроки плану виконання не виконуються у порядку, у якому зазначені у плані. Між кроками існують стосунки типу батько-нащадок. дерево вихідних рядків – основа плану виконання. Воно містить таку інформацію:

• Сортування таблиць, на які посилається оператор
• Метод доступу для кожної таблиці, вказаної в операторі
• Метод з'єднання для таблиць, застосовуваний операторами з'єднання у виразі
• Операції з даними, такі як фільтр, сортування або агрегування

Додатково до дерева вихідних рядків (або дерева потоку даних у паралельних операціях) таблиця плану містить такі дані:

• Дані оптимізації, такі як вартість та кардинальність кожної операції
• Дані секціонування, такі як набір партій до яких виконувався доступ
• Дані паралельного виконання, такі як метод розподілу операцій з'єднання

Результати виконання EXPLAIN PLAN дозволяють визначити, чи оптимізатор вибирає конкретний план виконання, наприклад, використання вкладених циклів.

Де знайти плани виконання?

Існує багато способів отримати плани виконання для виразів у БД, найбільш популярні перераховані нижче:

• Команда EXPLAIN PLAN дозволяє переглядати план виконання, який оптимізатор може використовувати для виконання виразу. Ця команда дуже корисна, оскільки вона будує план виконання та записує його в таблицю, звану PLAN_TABLE, не зберігаючи при цьому SQL вираз.
• V$SQL_PLAN надає можливість переглянути плани виконання курсорів, які були нещодавно виконані. Інформація, що зберігається в V$SQL_LAN, дуже схожа на інформацію, яку видає команда EXPLAIN PLAN. Однак Explain Plan показує потенційний план виконання, а V $ SQL_PLAN зберігає плани вже виконуваних запитів.
• V$SQL_PLAN_MONITOR містить статистику моніторингу на рівні планів для кожного виразу SQL, знайденого в V$SQL_MONITOR. Кожен рядок, що міститься в V$SQL_PLAN_MONITOR, відповідає певній операції плану виконання.
• Інфраструктура AWR і Statspack зберігають плани виконання SQL, що найбільш часто викликаються. Плани поміщаються до представлення dBA_HIST_SQL_PLAN або STATS$SQL_PLAN.
• Плани виконання та джерела рядків записуються також у фали трасування, що генеруються DBMS_MONITOR.
• SQL Management Base - це частина словника даних, що зберігається у табличному просторі SYSAUX. Тут зберігається журнальна інформація про операції, історія планів виконання та опорні лінії так само як і профілі для SQL виразів.
• Подія діагностики 10053, що використовується для запису обчислень оптимізатора вартості, також може генерувати плани виконання запитів.
• Починаючи з версії 10.2, коли ви отримуєте dump стану процесу, план виконання також включається до згенерованого файлу трасування.

Перегляд планів виконання

Якщо ви виконуєте команду EXPLAIN PLAN в SQL * Plus, ви можете вибрати дані з таблиці PLAN_TABLE і переглянути згенерований плани виконання. Найбільш простим способомПереглянути план виконання є використання пакета DBMS_XPLAIN. Пакет DBMS_XPLAIN містить п'ять доступних функцій:

• DISPLAY: Використовується для форматування плану виконання.
• DISPLAY_AWR: Використовується для форматованого виведення плану виконання SQLвиразів, які у репозиторії AWR.
• DISPLAY_CURSOR: Використовується для форматованого виведення плану виконання з будь-якого завантаженого курсору
• DISPLAY_SQL_PLAN_BASELINE: Використовується для форматованого виведення одного і більше планів виконання SQL виразів, що ідентифікуються за заголовками.
• DISPLAY_SQLSET: Використовується для форматованого виведення плану виконання збереженого в SQL Tuning set.

Перевага використання пакета DBMS_XPLAIN полягає в наданні можливості перегляду форматованого плану виконання SQL виразу незалежно від джерела.

Команда EXPLAIN PLAN

• Команда EXPLAIN PLAN використовується для створення плану виконання запиту.
• Після того, як план згенерований, його можна переглянути, запитавши інформацію з таблиці PLAN_TABLE

PLAN TABLE створюється автоматично як глобальна тимчасова таблиця, яка використовується в подальшому всіма користувачами для зберігання планів виконання. Ви можете створити власну PLAN TABLE за допомогою скрипту $ORACLE_HOME/rdbms/admin/utlxplan.sql у разі потреби тривалого зберігання планів виконання.

Структура команди EXPLAIN PLAN

Команда EXPLAIN PLAN вставляє рядок у PLAN TABLE кожного кроку плану виконання.

Приклад виконання команди EXPLAIN PLAN

Ця командавставляє план виконання для вираження PLAN TABLE і додає тег demo01 для подальшого звернення.

Існує безліч способів отримання плану виконання. вище розглядається метод із використанням команди EXPLAIN PLAN. Ця команда генерує план виконання SQL виразу, не виконуючи його при цьому, і поміщає результат у PLAN TABLE. PLAN TABLE представляє деревоподібну структуру, за допомогою якої можна повернути план виконання для вираження, використовуючи колонки ID і PARENT_ID і конструкцію CONNECT BY ввиразі SELECT.

Виведення вмісту PLAN TABLE

У наведеному вище прикладі використовується ключ ALL для функції DBMS_XPLAIN.DISPLAY, який дозволяє переглянути всю доступну інформаціюплан виконання, що зберігається в PLAN TABLE. Цей висновоккрім стандартної інформації, містить додаткову інформаціютаку як PROJECTION, ALIAS та інформацію про REMOTE SQL, якщо операція розподілена.

Для більш точного контролю інформації, що виводиться, можуть бути використані наведені нижче ключові параметри. кожне ключове словододає окремий блок виведення інформації з PLAN TABLE. Ключові слова повинні відокремлюватися один від одного комою або пропуском:

• ROWS якщо це доречно, показує кількість рядків, імовірно порахована оптимізатором.
• ROWS якщо це доречно, показує кількість байт, імовірно порахована оптимізатором.
• COST якщо це доречно, показує вартість,імовірно пораховану оптимізатором
• PARTITION якщо це доречно, показує відкидання патрицій оптимізатором
• PARALLEL чи це доречно, показує інформацію PX (методрозподілу інформації та інформацію про черги доступу до таблиці)
• PREDICATEабо це доречно, показує інформацію про предикат
• PROJECTION чи це доречно, показує секціюпроекції

Використання Explain Plan у SQL Developer

AUTOTRACE

Коли виразвиконується у SQL*Plus або SQL Developer ви можете автоматично отримувати план виконання та статистику виконання виразу. Звіт генерується автоматично після виконання будь-яких видів операцій, таких як SELECT, INSERT, UPDATE та DELETE. Цю інформацію можна використовувати для діагностики та налаштування продуктивності SQL виразів.

Для використання AUTOTRACE в БД повинна бути створена PLAN TABLE та користувачу, який виконує AUTOTRACE, повинна бути видана роль PLUSTRACE. РольPLUSTRACE створюється та видається ролі DBA за допомогою скрипту$ORACLE_HOME/sqlplus/admin/plustrce.sql

Синтаксис AUTOTRACE

Ви можете виконувати Autotrace, використовуючи синтаксис вказаний на малюнку вище. Доступні для використання також такі опції:

• OFF Вимикає використання трасування
• ON Вмикає автоматичне трасування
• TRACE Включає автоматичне трасування та пригнічує виведення SQL
• EXPLAIN Показує план виконання, але не показує сатистику
• STATISTICS Показує статистику без плану виконання

Приклади використання AUTOTRACE

AUTOTRACE: STATISTICS

Статистика, що записується сервером при виконанні виразу, відображає обсяг системних ресурсів, Витрачених на виконання виразу і включає в себе такі основні показники:

• recursive calls- кількість рекурсивних викликів, згенерованих на клієнтській та серверній стороні. Oracle Databaseпідтримує таблиці, які використовуються для внутрішньої обробки. Коли Oracle Database необхідно внести зміни до цих таблиць, вона генерує внутрішній оператор SQL, який, своєю чергою, породжує рекурсивний виклик.
• db block gets- кількість разів, яку запросили блок CURRENT
• consistent gets- кількість разів, яку запросили операцію цілісного читання блоків даних.
• physical reads- Число блоків даних, прочитаних з диска. Це число становить суму значень physical reads direct та всіх читань з буферногокеша.
• redo size- загальна кількість генерованого redo у блоках
• bytes sent via SQL*Net to client- загальна кількість байт, переданих клієнту від фонового процесу.
• bytes received via SQL*Net from client- загальна кількість байт, отриманих від клієнта Oracle*Net
• SQL*Net roundtrips to/from client- Загальна кількість повідомлень Oracle NET надісланих та отриманих від клієнта.
• sort (memory)- кількість операцій сортування, успішно виконаних у пам'яті та не зажадали запису на диск.
• sort (disk)- кількість операцій сортування, яка вимагала виконання як мінімум однієї дискової операції.
• row processed- Кількість рядків, оброблених у процесі виконання операції.

db_block_getsпоказує операції читання поточного блоку бази даних. consistent gets- це операції читання блоків, які повинні задовольняти конкретний номер SCN. physical readsпоказує читання блоків із диска. db_block_getsі consistent gets- Показники статистики, які постійно моніторяться. Вони повинні бути низькими в порівнянні з кількістю рядків, що витягуються. Сортування виконуватиметься в пам'яті, а не на диску.

AUTO TRACE з використанням SQL*Developer

Подання V$SQL_PLAN

Це представлення показує плани виконання для курсорів, які все ще знаходяться у бібліотечному кеші. Інформація, що зберігається в даному поданні, багато в чому схожа на інформацію з PLAN TABLE. Однак V$SQL_PLAN містить плани виконання для виразів, які вже були виконані. План виконання, отриманий у процесі виконання EXPLAIN PLAN, може відрізнятися від фактичного плану виконання, що зберігається в курсорі. Так відбувається тому, що параметри сесії та значення BIND змінних можуть відрізнятись від поточних.

Інше корисне уявлення: V$SQL_PLAN_STATISTICSу якому представлена ​​статистика виконання кожної операції у плані виконання кожного кешованого курсора. Ще одна корисна вистава V$SQL_PLAN_STATISTIC_ALLоб'єднує в собі інформацію виконанню з V$SQL_PLAN_STATISTICSі V$SQL_WORKAREA c планом виконання, що зберігається V$SQL_PLAN.

Опис основних стовпців подання V$SQL_PLAN

Уявлення містить самі стовпці, як і PLAN TABLE і кілька додаткових стовпців. Стовпці, представлені в PLAN TABLE і мають однакові значення:

• ADDRESS
• HASH_VALUE

PLAN_HASH_VALUE - це стовпець, що містить числове уявлення плану SQL виразу для курсору. Порівнюючи значення стовпця PLAN_HASH_VALUE можна швидко визначити чи план виконання для одного і того ж виразу, не порівнюючи їх рядково.

Подання V$SQL_PLAN_STATISTICS

Подання V$SQL_PLAN_STATISTICS надає актуальну статистикувиконання для кожної операції в плані виконання, такі як кількість оброблених рядків або час виконання. Уся статистика, за винятком кількості рядків, накопичена. Наприклад статистика по об'єднанням таблиць може включати 3 операції об'єднання таблиць. Статистика, що зберігається в V$SQL_PLAN_STATISTICS, доступна для курсорів, які були скомпільовані з параметром ініціалізації STATISTICS_LEVEL = ALL або з використанням підказки оптимізатору GATHER_PLAN_STATISTICS.

Подання V$SQL_STATISTICS_ALL містить статистику використання пам'яті для всіх вихідних рядків, які використовували пам'ять SQL (сортування або HASH join) Дане уявлення об'єднує інформацію, що зберігається у поданні V$SQL_PLAN зі статистикою виконання з уявлень V$SQL_PLAN_STATISTICS і V$SQL_WORKAREA.

Зв'язки між важливими динамічними уявленнями продуктивності

V$SQLAREAпоказує статистику для розділених SQL областей і містить один рядок для кожної рядки SQLвирази. Це представлення надає статистику за SQL виразами, які вже розібрані, знаходяться у пам'яті та готові до виконання:

• SQL_ID- ідентифікатор SQL батьківського курсору у бібліотечному кеші
• VERSION_COUNTкількість дочірніх курсорів представлених у кеші для заданого батьківського курсору

V$SQLзберігає статистику по розділах, що розділяються SQL і містить один рядок для кожного SQL виразу нащадка походить від батьківського SQL виразу:

• ADDRESSпредставляє адресу заголовка батьківського курсору для даного курсору
• HASH_VALUE-значення батьківського вираження у бібліотечному кеші
• SQL_ID- SQL ідентифікатор батьківського курсору у бібліотечному кеші
• PLAN_HASH_VALUE- числове подання SQLплану для даного курсору
• CHILD_NUMBER- Номер дочірнього курсора

Статистика, що зберігається в V$SQL, оновлюється в міру виконання SQL виразів. Однак для висловів, що довго виконуються, інформація в поданні оновлюється кожні п'ять секунд. Це робить можливим оцінити вплив на продуктивність запитів, що довго виконуються, в процесі їх виконання.

V$SQL_PLANмістить інформацію про план виконання кожного дочірнього курсора, завантаженого в бібліотечний кеш. Стовпці ADDRESS, HASH_VALUEі CHILD_NUMBERможуть бути використані для з'єднання з V$SQLдля подальшого визначення дочірніх курсорів

V$SQL_PLAN_STATISTICSнадає статистику виконання на рівні вихідних рядків для кожного дочірнього курсору.Стовпці ADDRESS, HASH_VALUE можуть бути використані для об'єднання з поданням V$SQLAREAдля визначення батьківськогокурсор.Стовпці ADDRESS, HASH_VALUEі CHILD_NUMBERможуть бути використані для з'єднання зV$SQLвизначення дочірніх курсорів.

V$SQL_PLAN_STATISTICS_ALL містить статистику використання пам'яті для всіх вихідних рядків, які використовували пам'ять SQL (сортування або HASH join) Дане уявлення об'єднує інформацію, що зберігається у поданні V$SQL_PLANзі статистикою виконання з подань V$SQL_PLAN_STATISTICSі V$SQL_WORKAREA.

V$SQL_WORKAREAнадає статистику щодо робочих областей задіяних у процесі роботи SQLвирази. Кожний вираз SQL зберігається в пулі, що розділяється, один або більше дочірніх курсорів, інформація про які зберігається в V$SQL. V$SQL_WORKAREAмістить інформацію про всі робочі області, необхідні цим дочірнім курсорам.

V$SQL_WORKAREAможе з'єднуватися з V$SQLAREA (ADDRESS, HASH_VALUE)і з V$SQL ( ADDRESS, HASH_VALUE,CHILD_NUMBER).

Використовуючи це подання, можна отримати відповіді на наступні питання:

• Топ-10 робочих областей, які потребують найбільшогокількістьпам'яті для кешу
• Для робочих областей, що працюють у режимі AUTO,який відсоток робочих областей виконується з використанням максимальної кількостіпам'яті?

V$SQLSTATSвідображає основну статистику продуктивності для курсорів SQL, з кожним рядком, що представляє дані, що поєднують текст SQL вирази і план виконання SQL (поєднання SQL_IDі PLAN_HASH_VALUE). Стовпці в V$SQLSTATSідентичні, V$SQLі V$SQLAREA. Тим не менш, уявлення V$SQLSTATSвідрізняється від V$SQLі V$SQLAREAшвидкістю обробки, масштабованістю, великим терміном зберігання даних (статистичні дані можуть зберігатися в поданні, навіть після того, як курсор був витіснений з пула, що розділяється).

Приклад запиту даних із представлення V$SQL_PLAN

Ви можете запросити дані з подання V$SQL_PLANвикористовуючи функцію DBMS_XPLAIN.DISPLAY_CURSOR()для відображення поточного або останнього виконаного виразу (як показано на прикладі). Ви можете передати значення SQL_IDяк параметр для отримання плану виконання для даного виразу. SQL_ID - SQL_IDвирази, що зберігається в кеші курсорів. Ви можете отримати відповідне значення запитавши інформацію зі стовпця SQL_IDв V$SQLі V$SQLAREA. Альтернативно, ви можете вибрати PREV_SQL_IDдля певної сесії з V$SESSION. За замовчуванням цей параметр не заданий, у цьому випадку відображається план, що зберігається в останньому курсорі.

• IOSTATS: Припускаючи, що в процесі виконання SQL збирається базова статистика для планів виконання, параметр STATISTICS_LEVEL встановлений у ALL або використовується HINT GATHER_PLAN_STATISTICS) цей формат відображає статистику вводу/виводу для всіх при вказівці ALL (або тільки для останнього при вказівці LAST) виконання курсору.
• MEMSTATS: Припускаючи, що використовується автоматичне керування PGA (параметр pga_aggregate_target встановлено не нульовим значенням) цей формат дозволяє показати статистику використання пам'яті даний видстатистики застосовується тільки до операцій, що інтенсивно використовують пам'ять, таким як наприклад HASH Join, сортування або деякі з bitmap операторів.
• ALLSTATS: Синонім для "IOSTATS MEMSTATS"
• LAST: За замовчуванням, статистика планів виконання відображається для всіх виконань курсору. Використовуючи ключове слово LAST, можна переглянути статистику плану, генеровану після його останнього виконання.

Важливі уявлення AWR

Ви можете переглянути дані AWR в Enterprise Manager або сформувавши звіт AWR, додатково ви можете звернутися до наступних динамічних уявлень продуктивності, що зберігають дані AWR:

• V$ACTIVE_SESSION_HISTORY- дане подання показує інформацію про останню активність сесій, що поповнюється кожну секунду.
• Подання V$ metric представляють дані метрик для відстеження продуктивності системи. Список подань метрик можна переглянути звернувшись до представлення V$METRICGROUP.
• Подання DBA_HIST містять історичні дані, що зберігаються у базі даних. Ця група уявлень включає:

1. DBA_HIST_ACTIVE_SESS_HISTORYмістить вміст відібраної з пам'яті історії активного сеансу щодо недавньої активності системи
2. DBA_HIST_BASELINEмістить інформацію про опорні лінії, що зберігається в базі даних.
3. DBA_HIST_DATABASE_INSTANCEмістить інформацію про оточення БД
4. DBA_HIST_SNAPSHOTмістить інформацію про снепшоти, що зберігаються в системі
5. DBA_HIST_SQL_PLANмістить інформацію про плани виконання
6. DBA_HIST_WR_CONTROLмістить інформацію про налаштування AWR

Запит даних AWR

Генерація звітів щодо певногоSQLз репозиторію AWR

SQL моніторинг

Інструмент SQL Monitoringза замовчуванням, коли параметр STATISTICS_LEVELвстановлений у значення TYPICALабо ALL. Для використання даного інструментунеобхідно також встановити значення параметра CONTROL_MANAGEMENT_PACK_ACCESSна значення DIAGNOSTIC+TUNINGдля включення пакета діагностики та налаштування.

За замовчуванням моніторинг SQL включається автоматично, коли SQL вираз виконується в паралельному режимі або коли він споживає більше п'яти секунд процесорного часу або часу I/O в процесі одного виконання.

Існує дві підказки оптимізатору для явного включення або вимкнення SQL моніторингу для вираження. MONITORі NO_MONITOR.

Ви можете відстежувати статистику виконання SQL виразу, використовуючи уявлення V$SQL_MONITORі V$SQL_PLAN_MONITOR.

Після активації моніторингу SQL вираження динамічне представлення продуктивності V$SQL_MONITORдодається інформація, необхідна для відстеження ключових показників продуктивності таких як час виконання, CPU time, кількість операцій читання та запису, час очікування I/O та інші метрики очікування. Ця статистикаоновлюється в режимі реального часу в процесі виконання SQL за замовчуванням - кожну секунду. Після закінчення виконання інформація про виконання зберігається у поданні V$SQL_MONITORще хвилину, після чого вона видаляється.

Приклад звіту SQL Monitoring

У даному прикладіпередбачається, що ви вибираєте дані з таблиці SALES і в іншій сесії запускаєте SQL Monitoring.

Функція DBMS_SQLTUNE.REPORT_SQL_MONITOR може приймати кілька параметрів, що обмежують або розширюють рівень деталізації звіту, використовуючи додаткові параметри можна також вказати вихідний формат звіту (TEXT, HTML або XML), за промовчанням звіт формується у текстовому форматі.

Для унікальної ідентифікації двох виконань одного виразу SQL, генерується складовий ключ, званий ключ виконання. Цей ключскладається з трьох атрибутів, кожен з яких відноситься до колонки V$SQL_MONITOR:

• SQL_ID
• Внутрішньо генерований ідентифікатор для того, щоб переконатися, що цей первинний ключнасправді унікальний (SQL_EXEC_ID)
• Тимчасова мітка початку виконання виразу (SQL_EXEC_START)

Інтерпретація плану виконання

Висновок EXPLAIN PLANє табличним поданням деревоподібної структури плану виконання. Кожен крок (рядок у плані виконання або вузол у дереві) є джерелом рядка.
Порядок вузлів під parrent показує порядок виконання вузлів на цьому рівні. Якщо два кроки розташовані на одному рівні, перший по порядку виконуватиметься першим.
У форматі дерева листя ліворуч на кожному рівні дерева ідентифікують точку початку виконання.
Кроки плану виконання не виконуються у порядку, в якому вони пронумеровані. Є стосунки батько-дитина між кроками.
У PLAN_TABLE та V$SQL_PLAN важливими елементамидля отримання деревоподібної структури є стовпці ID, PARRENT_ID та POSITION. У файлі трасування ці стовпці відповідають полям id, pid і pos відповідно.
Одним із шляхів читання плану виконання є перетворення його на графік, що має деревоподібну структуру. Ви можете почати зверху, запис ID=1 є верхньою точкою дерева. Це справедливо для операцій, які мають parrent_id або pid значення 1.
Для представлення плану у вигляді дерева зробіть таке:

1. Візьміть ID з найнижчим значенням та помістіть його вгорі дерева.
2. Визначте рядки, що мають PID (parrent id), що дорівнює цьому значенню.
3. Помістіть їх у дерево нижче батьківського записувідповідно до їх POS значення від меншого до більшого зліва направо.
4. Після того, як всі ID батьків будуть знайдені, перемістіться на рівень вниз до наступного ID і повторіть процес, знаходячи нові рядки з одним PID.

Перше, що потрібно визначити в плані виконання – це якийсь із вузлів виконується першим. Метод показаний на малюнку пояснює як це зробити, але іноді в складних планах виконання важко зробити це і важко пройти по всіх кроках до кінця. Складні плани щодо складу не відрізняються від простих нічим крім кількості кроків. Для них застосовні ті ж прості правила. Ви можете завжди приховати кроки у плані, які не споживають значної кількості ресурсів.
Стандартний метод інтерпретації плану виконання:

1. Почніть зверху
2. Перейдіть вниз по операціях, поки не дійдете до тієї, яка робить дані, але при цьому не нічого споживає. Це початок операції.
3. Подивіться на дочірні операції, які є у цього батька. Дочірні операції виконуватимуться такими
4. Переміщайтеся вгору по дереву, доки всі операції не будуть переглянуті.

Стандартний метод інтерпретації дерева плану виконання:

1. Почніть зверху
2. Перемістіться вниз і вліво по дереву доки не досягнете лівого вузла він виконується першим
3. Подивіться на нащадків цього вузла. ці нащадки виконуватимуться далі.
4. Після того як виконаються нащадки, виконання батьківської операції продовжиться
5. Тепер, після того як ця операція і всі її нащадки виконані перемістіться нагору по дереву, і дивіться на нащадків вихідного ряду операцій та його батьків. Виконується, за тим самим принципом.
6. Переміщайтеся вгору по дереву доти, доки всі операції не будуть переглянуті

Інтерпретація плану виконання: Приклад 1

На малюнку вище представлена ​​інтерпретація плану виконання висловлювання. Запит, вказаний на малюнку, намагається знайти співробітників, чия зарплата вибивається і сітки зарплат. Запит вибирає дані з двох таблиць і включає підзапит заснований на вибірці з іншої таблиці для перевірки розмірів зарплат.
Подивимося порядок виконання цього запиту. Виходячи з цього та попереднього малюнка порядок виконання буде наступний: 3-5-4-2-6-1:

• 3: виконання плану розпочнеться з повного сканування таблиці EMP (ID=3)
• 5: рядки передаються кроку, який контролює об'єднання nested loop (ID=2), який використовує їх, щоб виконати пошук рядків в індексі PK_DEPT (ID=5)
• 4: ROWID рядків, отримані після сканування PK_DEPT, використовуються для отримання іншої інформації з таблиці DEPT (ID=4)
• 2: ID=2, процес об'єднання nested loop буде продовжено до його виконання
• 6: Після того, як ID=2 обробить усі вихідні рядки для об'єднання, виконається повне сканування таблиці SALGRADE (ID=6)
• 1: Дані, отримані після виконання ID=6, будуть використані для фільтра рядків з ID=2 та ID=6.

Процеси нащадки виконуються перед батьківськими процесами, хоча структури сполук мають бути сформовані до виконання процесів-нащадків. Можливо, найпростіший спосіб пояснити порядок виконання - для виконання операції з'єднання NESTED LOOPS з ID=2, два нащадки (ID=3 і ID=4 (разом з їхніми нащадками)) повинні завершити виконання перед тим як ID=2 буде виконаний.

Інтерпретація плану виконання: Приклад 2

Цей запит повертає імена, імена департаментів та адреси співробітників, чиї департаменти розташовані в Сіетлі та мають менеджера.

Для зручності форматування на малюнок додано ще одну нумерацію. Стовпець зліва представляє ID стовпець праворуч - PID. План виконання запиту показує дві операції NESTED LOOP JOIN. Інтерпретуємо план виконання згідно з методом поданого вище:

1. Починаємо згори. ID=0
2. Опускаємося вниз за операціями, поки не дійдемо до тієї, що робить дані, але нічого не споживає. У даному випадку, ID 0,1,2, та 3 споживають дані. ID=4 - перша зверху операція, яка споживає ресурсів, але виробляє дані. Це перше джерело даних. INDEX RANGE SCAN поверне ROWID рядків, які будуть використані для повернення даних із таблиці LOCATIONS (ID=3)
3. Подивимося на братів і сестер цього джерела рядків, які знаходяться з ним на одному рівні у дереві. Брати та сестри, які знаходяться на тому ж рівні, мають ID=3 і ID=5. ID = 5 має нащадок - ID = 6, який буде виконано перед ним. Це операція сканування INDEX RANGE SCAN по іншому індексу, що повертає ROWID, які будуть використовуватися для отримання даних з таблиці DEPARTMENTS в процесі виконання ID=5.
4. Після виконання операції-нащадок управління передається його предку. Наступною буде виконуватися операція NESTED LOOPS з ID=2 для об'єднання отриманих даних.
5. Тепер, коли батьківська операція всі її нащадки виконані, піднімаємося по дереву і дивимося, чи є у вищестоящої операції брати і сестри, що з нею однією рівні. ID=2 перебуває в одному рівні з операцією ID=7 яка має нащадок ID=8. Цей нащадок буде виконано першим. INDEX UNIQUE SCAN буде виконаний для отримання рядків ROWID, які потім будуть використані для отримання даних з таблиці EMPLOYEES в операції ID=7.
6. переміщуємося на рівень вище після того, як будуть оброблені всі операції на поточному рівні та їх нащадки. Остання виконуватиметься операція об'єднання NESTED LOOPS з ID=1, після виконання якої результат буде передано ID=0.
7. Порядок виконання операції наступний: 4-3-6-5-2-8-7-1-0

Нижче представлено докладний описплану виконання:

Спочатку виконується внутрішній вкладений цикл застосувавши LOCATIONS як провідну таблицю, використовуючи доступ за індексом на стовпець CITY. Операція виконується, оскільки ви шукаєте департаменти лише у Сіетлі.

Результат об'єднується з таблицею DEPARTMENTS, використовуючи індекс на стовпці LOCATION_ID для з'єднання. Результат першої операції об'єднання є провідним джерелом даних другого вкладеного циклу.

Друга операція об'єднання досліджує індекс на стовпці EMPLOYEE_ID таблиці EMPLOYEES. Ця операція може виконуватися, оскільки система знає (з першої операції об'єднання) ID всіх менеджерів департаментів Сіетлу. У цьому випадку виконується UNIQUE SCAN, оскільки сканування виконується за індексом первинного ключа.

Спочатку система хешує таблицю T3 на згадку (ID=3)

Потім система хешує таблицю T1 на згадку (ID=5)

Потім починається сканування таблиці T2 (ID-6)

Система бере рядок з T2 та досліджує T1 (T1.i=T2.i)

Якщо рядок вижив, система шукає їх у T3 (T1.i=T3.i)

Якщо рядок вижив, система передає його наступній операції.

Система видає максимальне значенняіз попереднього набору результатів.

Порядок виконання наступний: 3-5-6-4-2-1

Порядок об'єднання: T1-T2-T3

Читання більш комплексних планів виконання

План показаний малюнку зліва побудований за запитом зі словника даних. Він дуже довгий, тому дуже складно застосувати попередні методи інтерпретації знаходження першої операції.

Ви можете скоротити план, щоб зробити його більш читабельним. Справа на малюнку показаний той самий план виконання, тільки скорочений. Як показано малюнку, це легко зробити з допомогою Enterprise manager чи SQL*Developer. Як видно на малюнку план включає операцію об'єднання двох гілок. Знання про словник даних дозволяють зрозуміти що дві гілки відповідають dictionary-managed та localy-managed табличним просторам. Знання бази даних дозволяють зрозуміти, що у базі даних немає dictionary-managed табличних просторів. Таким чином, якщо є проблема, вона знаходиться у другій гілці. Для отримання підтвердження своїх припущень, потрібно подивитися на інформацію про план та статистику виконання кожного джерела рядків для того, щоб визначити частину плану, яка споживає найбільше ресурсів. Потім потрібно розгорнути ту гілку, де виявлено проблеми. Для використання цього методу ви повинні додатково використовувати статистику виконання, яка може бути знайдена у поданні V$SQL_PLAN_STATISTICS або у звіті tkprof, генерованому з файлу трасування. Наприклад, tkprof підсумовує час для кожної батьківської операції, який вона витрачає на виконання плюс час виконання всіх операцій-нащадків.

Огляд плану виконання

Коли ви налаштовуєте SQL вираз в оточенні OLTP, мета полягає в тому, щоб почати з таблиці, для якої застосовується фільтр з найвищою селективністю. Це означає, що менше рядків буде передано наступній операції. Якщо наступним кроком є ​​з'єднання, мається на увазі, що менше рядків буде з'єднуватися. Необхідно також переконатися, що шляхи доступу до даних є оптимальними. У процесі дослідження плану виконання зверніть увагу на таке:

• План побудований так, що найкращий фільтр застосовано до провідної таблиці
• Порядок з'єднання побудований так, щоб наступному кроку передавалося найменша кількість рядків (тобто порядок з'єднання повинен йти до кращих фільтрів, що поки ще не використані)
• Метод join відповідає кількості рядків, які передаються для об'єднання. Наприклад, NESTED LOOP з'єднання з використанням індексу може бути не оптимальним, коли повертається багато рядків.
• Уявлення використовуються ефективно. Подивіться на SELECT список, щоб визначити, у яких місцях необхідно звернення до подання.
• Відсутні операції декартового твору таблиць (навіть з дрібними таблицями).
• Кожна таблиця читається ефективно: розглядайте предикати SQLвиразів стосовно кількості рядків у таблиці. Детально подивіться підозрілі операції, наприклад full table scan для таблиць великою кількістюрядків, які є у предикаті.

Повне сканування таблиць може ефективно застосовуватися для невеликих таблиць або для застосування конкретного типуз'єднання (наприклад HASH JOIN) для рядків, що повертаються.

Якщо будь-яка з перерахованих вище умов не є оптимальною, необхідно переписати SQL або переглянути індекси, доступні для таблиці.

План виконання сам собою не може дати інформацію про ефективність виконання запиту. Наприклад, висновок EXPLAIN PLAN може показувати використання індексу, але це ще не означає, що вираз працює ефективно. Іноді використання індексу може бути дуже неефективним.

Для визначення оптимального плануВиконання потрібно отримати вихідний план, а потім оптимізувати його для більш оптимального виконання за допомогою тестування. У процесі зміни плану виконання слід стежити споживанням системних ресурсів.