Іноді мені доводиться чути, що Oracle дуже складний в налаштуванні, «ось ××× - зовсім інша справа». Перестати звертати увагу на подібні заяви мені допоміг випадок.
Коли я читав спецкурс по Oracle, мені довелося зробити для студентів навчальну базу даних. Один зі студентів видав запит select * from anytable і заявив, що клієнтська машина почала «пригальмовувати». Я запитав його, скільки записів він витягнув з таблиці. Він подивився і сказав «Ого!» Після цього претензій до продуктивності Oracle не виникало. А вилучено було всього-то 20 000 записів - для Oracle кількість просто смішне. Нещодавно я прочитав в технічному завданні вимога зберігати 3 000 000 000 (три мільярди) записів.
В «двох кілословах» мені хотілося б розповісти про проблеми, які мені зустрічалися і про те, як я їх вирішував. Можливо, якісь речі будуть пояснені занадто докладно - сміливо їх пропускайте. Можливо, щось залишиться незрозумілим - питайте, постараюся відповісти. Можливо, щось залишиться неврахованих і моє рішення виявиться не кращим - пишіть, будь-які зауваження прийму з вдячністю.
Отже, завдання гранично проста: скопіювати дані з однієї таблиці в іншу. Що зробить щасливий володар звичайної бази даних. Правильно, він зробить ось так:
Зрозуміло, в Oracle ця команда теж спрацює, але при копіюванні великої кількості записів хотілося б якось прискорити процес. Ось ми і будемо прискорювати.
Direct-load insert
Див. Oracle concepts. Chapter 25 # 132; Direct-load INSERT # 147;
Як відомо, місце під дані в таблиці поступово збільшується, але ніколи не зменшується, а то й видати сервера явною команди на стиск таблиці. Традиційно команда insert пробігає по вже виділеним блокам таблиці, шукає вільне місце і намагається записати туди дані. Нові блоки виділяються тільки після того, як заповнені всі наявні.
Ідея direct-load insert полягає в тому, щоб передавати серверу не сире дані, а вже готові сформовані блоки - йому залишиться тільки записати ці блоки в таблицю. Зрозуміло, при такому підході використані блоки не заповнюються - або заповнюються повністю вільні блоки, або до таблиці додаються нові.
Робити direct-load insert вміє утиліта SQL * Loader. що входить в комплект клієнтського ПЗ Oracle, але для цього необхідно, щоб клієнт і сервер працювали на одній і тій же платформі, тобто завантажити, наприклад, таким методом дані з Windows в Solaris не вийде. Для того ж, щоб скористатися цим методом всередині сервера, необхідно скористатися хинти append.
Отже, ось перший крок у вирішенні нашого завдання:
Якщо копіювати дані методом direct-load insert, то годі й говорити зберігати ніякої інформації для відкату, тобто rollback segment практично не росте.
Але не варто забувати, що разом з таблицями змінюються і всілякі допоміжні структури, наприклад, індекси. Їх зміни також пишуться в сегменти відкату. І якщо на таблиці створено, наприклад, 5 індексів, то розмір необхідного їй сегмента відкату зростає в три-чотири рази. Тому якщо обсяг копійованих даних значний у порівнянні з обсягом даних, які вже перебувають в таблиці. має сенс знищити індекси на цільовій таблиці і перебудувати їх після копіювання. Щоб не створювати індекси вручну, спробуємо цей процес автоматизувати. Наведений нижче скрипт створює послідовність команд, які треба буде виконати після копіювання, щоб відтворити індекси:
Новачки можуть дорікнути мені в зайвої складності команд - на це я відповім, що складного нічого немає, проблема не в складності запиту, а в перекладі сухого мови таблиць і уявлень на мову SQL, майже людський. Професіонали, в свою чергу, закинуть мене в тому, що не враховано різні тонкощі, наприклад, partitioned indexes - на це відповім цитатою з Козьми Пруткова: «ніхто не обійме неосяжного *».
Загалом, після того, як результат цього запиту буде збережено, можна знищувати індекси. Запит, що створює скрипт для знищення індексів, зовсім простий:
Хоча, повторюю, ніхто не заважає написати всі необхідні команди вручну.
Очевидно, для прискорення копіювання має сенс відключити первинні і зовнішні ключі. Робити це також варто тільки в тому випадку, коли обсяг копійованих даних значний у порівнянні з обсягом існуючих даних. Формально ключі з індексами ніяк не пов'язані (за винятком індексів, що створюються для підтримки первинних ключів), однак фактично підтримку зовнішніх ключів без відповідних індексів - надто ресурсномістка завдання. Тому якщо прийнято рішення руйнувати індекси, ключі також треба відключити.
Запит, що створює скрипт, який включає / відключає ключі, дуже простий, але для повноти картини приведу його тут:
паралелізм
Досягається це кількома способами. Спосіб перший - вказати при створенні таблиці ступінь паралелізму, допустиму при операціях з цією таблицею.
Див. Oracle8i SQL Reference. Chapter 7 # 132; SQL Statements # 147;
Побічний ефект - можуть распараллелівать деякі команди, які краще б виконувалися послідовно. І навпаки - ваша команда може не распараллеліть.
Другий шлях - вказати хинт parallel безпосередньо в команді. Тут необхідно мати на увазі наступне: по-перше, читання даних і їх запис - різні процеси, і распараллеливать їх треба окремо; по-друге, запис не є распараллеліть, якщо не вирішити це явно командою enable parallel DML. З урахуванням вищесказаного скрипт для копіювання даних набуває такого вигляду:
Тут породжується чотири процесу для запису і чотири - для читання. Якщо запис йде значно повільніше, ніж читання (наприклад, не зруйновані індекси), то можна зробити записуючих процесів більше, ніж тих, хто читає.
І ще одне. Запису не распараллеліть, якщо залишився включеним хоча б один зовнішній ключ, де цільова таблиця є дочірньою. А Oracle 8.0 НЕ распараллеліть запис, якщо читання відбувається по лінку, тобто з іншого примірника Oracle.
Виділення пам'яті
Не треба забувати, що швидкість виділення дискового простору під дані досить сильно впливає на швидкість запису даних.
Як відомо, якщо місце в таблиці закінчується, до неї приписується новий екстент - сукупність поспіль блоків. Розмір цього екстента визначається параметрами next і pctincrease. заданими в команді create table. Якщо екстент маленький, то він виділиться швидко, але виділення відбуватиметься часто. Якщо екстент великий, то виділятися екстенти будуть рідко, але зате довго. Необхідно пам'ятати, що словник даних при зміні блокується монопольно, тобто якщо один з паралельних процесів запросив екстент, а в цей час обробляється запит від іншого процесу, перший встане і буде чекати.
Дати однозначні рекомендації за величиною екстентів неможливо, все залежить від обсягу даних, тобто «ширини» таблиці і кількості записів. Бажано, правда, щоб розміри екстентів у всіх таблицях були кратними - це зменшить фрагментацію табличного простору і прискорить пошук вільного простору.
сегменти відкату
Якщо ви уважно подивилися на картинку, яка ілюструє використання сегментів відкоту, то побачили, що вони використовуються по черзі. Нехай, наприклад, у нас є 4 сегмента - R01, R02, R03 І R04. Тобто якщо транзакція використовувала, наприклад, сегмент R01, то наступної транзакції буде призначений сегмент R02.
Може трапитися, що перша транзакція розширить сегмент відкоту настільки, що він впритул підійде до кордонів табличного простору. Після підтвердження транзакції сегмент буде позначений як вільний, але в розмірах не зменшиться - зменшенням розміру займеться демон трохи пізніше. Другий же транзакції може не вистачити сегмента відкату - просто тому, що йому нікуди рости.
Рішення проблеми существет - досить примусово призначити другий транзакції той же сегмент відкоту, що і першої:
висновок
О, скільки нам відкриттів дивних.
Якщо вам захочеться поділитися зі мною своїми труднощами і радощами, пов'язаними з Oracle - пишіть листи. Якщо ж радості є, а писати лінь, можна роздрукувати на кольоровому принтері і приклеїти на веб-сторінку / монітор / лоб традиційну кнопку 88 × 31:
* Козьма Прутков, «Плоди роздуми». «Думки і афоризми», думка № 3
Користувачі всіх клонів * nix давно вже можуть писати командні файли на будь-якій мові. Для цього треба лише вказати в першому рядку ім'я інтерпретатора. »» »
Командний мова Windows NT - все ще корявий і хитрий, але вже досить потужний інструмент, яким можна і потрібно користуватися. »» »
Іноді мені доводиться чути, що Oracle дуже складний в налаштуванні, «ось ××× - зовсім інша справа». Перестати звертати увагу на подібні заяви мені допоміг випадок. »» »