Том Кайт: Про явних і неявних курсорів,
розмірах екстентів і складних інтервалах
(On the Explicit, Size, and Complex. By Tom Kyte)
Наш експерт відповідає на питання про курсором, екстенти і інтервалах.
Питання: Чи дійсно, що починаючи з СУБД Oracle7 Release 7.3, неявні курсори оптимізуються і не виконують подвійний вибірки? Крім того, чому наступний неявний курсор працює швидше показаного нижче явного курсору, якщо у таблиці T є індекс по стовпцю X, тоді як в іншому випадку явний курсор працює швидше?
Відповідь: Неявний курсор:
Я почну з короткого визначення, щоб всі розуміли, що з себе представляють неявні і явні курсори.
Загалом, неявний курсор - це курсор, який програміст "явно« не оголошує, що не відкриває, не вибирає з нього і не закриває його; ці операції є неявними. Отже, в прикладі, наведеному вище, запит SELECT X INTO Y - неявний курсор. Для нього не існує визначення "cursor імя_курсора is.". У другому прикладі, з іншого боку, показаний класичний явний курсор. Програміст явно оголошує його, відкриває, вибирає з нього і закриває його.
Отже, дійсно, неявні курсори в PL / SQL швидше явних курсорів, і стали вони швидше перед випуском СУБД Oracle7 Release 7.3. Насправді, у мене є набір тестів, який показує, що це дійсно так і в СУБД Oracle7 Release 7.1 (ці тести см. В asktom.oracle.com/
tkyte / ivse.html). Причина, по якій неявні курсори швидше (як неявні курсори курсорних циклів FOR, так і неявні курсори в операторах SELECT INTO), полягає в тому, що в цьому випадку машині PL / SQL потрібно інтерпретувати і виконувати набагато менше, ніж ви програмного коду. Загалом, чим більше коду PL / SQL можна "сховати", тим швидше він буде працювати. Для неявного курсора, показаного вище, потрібно один рядок коду PL / SQL; для явного курсору потрібно принаймні три рядки коду, а якщо робити "правильно", фактично буде потрібно шість рядків коду. Ваш явний код не виконує всієї роботи неявного курсора, який перевіряє, що ви гарантовано отримаєте одну і тільки один рядок. У вашому явному коді відсутня багато з того, що ви повинні зробити. Для акуратного порівняння двох ваших прикладів курсорів ваш явний код повинен мати більше рядків:
Якби це був ваш явний курсор, ви виявили б, що явний курсор повільніше у всіх випадках, навіть у вашому прикладі з індексом або без індексу.
Я створив таблицю з великою кількістю блоків; це робиться за допомогою установки параметра pctfree 99, яка резервує 99 відсотків простору блоків для подальших оновлень блоків ( "вільний простір"). Отже, навіть при тому, що кількість даних в таблиці невелике, сама таблиця є досить великий. Крім того, я вставив в таблицю значення 1, 2, 3. до 29 264 в значній мірі розмістилися по порядку. Так, значеніеX = 1 знаходиться в "першому" блоці таблиці, а значення X = 29,000 - досить близько до останнього блоку таблиці.
Це пояснює поведінку ваших курсорів: оператор SELECT INTO перевіряє наявність другого рядка, тоді як ваш явний курсор цього не робив. Якщо Ви порівнюєте яблука з яблуками - або робите другу явну вибірку, або додаєте предикат "rownum = 1" до оператора SELECT INTO - ви виявите, що обидва курсора виконують той же самий обсяг роботи.
Питання: Для нашого нового додатка ми спроектували базу даних і створили модель даних. Ми навіть встановили розміри таблиць та визначили параметри зберігання для кожної таблиці. Але тепер наші фахівці з адміністрування бази даних говорить нам, що вони нададуть нам три табличних простору: TS_small (табличне простором невеликого розміру) з однаковим розміром екстентів, рівним 160 КБ, TS_med (табличний простір середнього розміру) з однаковим розміром екстентів, рівним 5 МБ , і TS_large (табличний простір великого розміру) з однаковим розміром екстентів, рівним 160 МБ. Вони кажуть нам, що потрібно створювати таблиці, розмір яких буде менше 5 МБ, в табличному просторі TS_small, таблиці, розмір яких буде менше 160 МБ, в TS_med і таблиці, розмір яких буде більше 160 МБ, в TS_large. Крім того, вони не хочуть, щоб ми використовували будь-які параметри зберігання на рівні таблиць. Вони кажуть, що те ж саме повинно бути і для індексів. Це не виглядає для мене розумним, тому що таблицю з оціненим розміром 120 МБ ми повинні розміщувати в табличному просторі TS_med, а для цього потрібно 24 екстента! Адміністратори бази даних стверджують, що численні тести довели, що таке проектування забезпечує найбільшу продуктивність і запобігає фрагментації. Моє питання: чи дійсно вони мають рацію? Я турбуюся про об'єкти з такою великою кількістю екстентів.
Відповідь: Добре, виглядає, як ніби вони читали веб-сайт "Запитайте Тома" (asktom.oracle.com) і матеріали дискусійних груп за інтересами в Інтернеті і знайшли хорошу рекомендацію. Дивлячись на їх числа, я бачу, що таблиця розміром до 5ГБ матиме 32 або менше екстентів. З огляду на, що сотні (або навіть більше) екстентів не впливатимуть на продуктивність виконання операторів мови маніпулювання даними (DML), я сказав би, що вони зробили чудову роботу.
Обидва з їх припущень правильні: фрагментація табличних просторів буде неможлива, а продуктивність буде оптимальною. Давайте розглянемо кожне твердження.
Те, що фрагментація неможлива, перевірити просто. Табличний простір, кероване за допомогою словника даних (dictionary-managed tablespace), фрагментируется через екстентів різного розміру. Табличний простір, кероване за допомогою словника даних, могло б мати тисячі екстентів, вільних і використовуваних, кожен з яких міг би мати різний розмір. Тепер, ви починаєте знищувати і створювати об'єкти в цьому табличному просторі і з часом у вас утворюється в ньому велику кількість "дірок" різного розміру (вільний простір). Так, ви могли перевірити табличний простір, кероване за допомогою словника даних, і визначити розмір вільного простору в ньому, виявивши 500 вільних МБ. Але коли ви намагаєтеся створити таблицю з розміром початкового екстента, рівним 40 МБ ступінь, то отримуєте помилку - неможливо виділити перший екстент. Як це могло трапитись? Ви маєте 500 вільних МБ, правильно? Так, добре, але на жаль ці 500 МБ знаходяться в безлічі екстентів, кожен з яких має розмір менший ніж 40 МБ! Отже, ви маєте велику непридатне для використання вільний простір - ваше табличний простір фрагментировано. Тепер, розгляньте локально кероване табличний простір (locally managed tablespace) з екстенти однакового розміру. Тут кожен екстент має один і той же розмір, що і всі інші екстенти, без винятку. Якщо ви знаходите, що маєте 500 вільних МБ, я можу запевнити вас, що ви будете здатні виділити новий екстент в цьому табличному просторі, так як кожен вільний екстент за визначенням може використовуватися для вашого об'єкта.
tkyte / extents.html і asktom.oracle.com/
tkyte / extents2.html. в кожному з яких міститься досить тривалий обговорення по цій темі. Тридцять два екстента для ваших об'єктів - це чудово, взагалі не буде ніякого впливу на продуктивність. Фактично, так як локально керовані табличні простору набагато більш ефективні при розподілі простору, ніж табличні простору, керовані за допомогою словника даних, їх використання підвищить продуктивність, а не навпаки.
Питання: Я використовую пакет DBMS_JOB і хочу спланувати запуск завдань кожні 15 хвилин з понеділка по п'ятницю, починаючи з 6:00 ранку і до 6:00 вечора. Як це можна зробити? Я не можу обчислити інтервал, який передається в пакет.
Відповідь: Добре, для того щоб обчислювати складні інтервали для пакета DBMS_JOB, я люблю використовувати новий (починаючи з Oracle8i Release 2) оператор CASE. Наприклад, наступний оператор CASE повертає правильний інтервал для вашої специфікації:
Оператор CASE забезпечує більшу гнучкість при генерації складного значення, яке потрібно вам. На жаль, пакет DBMS_JOB дозволяє вам використовувати тільки такі інтервали, довжина яких не перевищує 200 символів, і навіть якщо ви "стиснете" показаний вище оператор, ви виявите, що його довжина приблизно дорівнює як мінімум 300 символів. Отже, ви не можете використовувати його безпосередньо у виклику пакета DBMS_JOB. Моє рішення полягає в наступному: чи я створив би уявлення NEXT_DATE, щоб оператор select * from next_date повертав час наступного запуску завдання, або я реалізував би вищезгаданий оператор в функції PL / SQL, яка повертає дату. Якщо я використовував уявлення, мій виклик пакета DBMS_JOB міг би виглядати приблизно так: /
Питання: Ми маємо кілька таблиць, секціонованих за часом (бюджетний рік). Що ви порекомендуєте, в якості кращого підходу, щоб мати доступ до історичних даних в режимі "тільки для читання", а доступ до поточними даними - в режимі "читання-запис"?
Відповідь: Дійсно, цього достатньо просто добитися. Табличний простір може перебувати в режимі "тільки для читання" або в режимі "читання-запис". Якщо ви використовуєте окреме табличний простір для кожної секції (або, принаймні, зберігайте історичні секції в окремому табличному просторі, відмінному від поточного), ви, щоб зробити його доступним тільки в режимі "тільки для читання", можете просто виконати оператор ALTER TABLESPACE <имя табличного пространства> READ ONLY. Кінцеві користувачі не зможуть модифікувати це табличний простір і, фактично, ви зможете економити значний час, що витрачається на його резервування, оскільки ви повинні будете створювати його резервну копію тільки один раз (якщо тільки ви не перекладайте його в режим "читання-запис" і вносите його, в такому випадку ви, природно, повинні знову створювати його резервну копію).
Фактично, ви можете навіть перемістити це табличний простір на носії тільки для читання, такі, як компакт-диски, запобігаючи цим можливість його модифікації.
Питання: Поясніть, будь ласка, що таке recursive calls, db block gets і т.д.
Сервер Oracle підтримує таблиці, використовувані для внутрішніх операцій. Якщо серверу Oracle потрібно змінити ці таблиці то в системі генерується SQL-оператор, який в свою чергу генерує рекурсивні виклики.
Коротше кажучи, рекурсивні виклики в основному являють собою SQL-оператори, виконані від імені вашого SQL-оператора. Так, наприклад, якщо ви повинні були виконати синтаксичний розбір запиту, ви, можливо, повинні були виконати деякі інші запити, щоб отримати інформацію зі словника даних. Це і були б рекурсивні виклики. Управління простором, перевірка правил розмежування доступу, виклик процедур PL / SQL з SQL-операторів - все це призводить до виконання рекурсивних викликів.
- DB Block Gets (читання блоків бази даних). Кількість блоків, отриманих в режимі CURRENT.
ЧИТАЙТЕ документацію Oracle9i:
Oracle9i Database Performance Tuning Guide and Reference
otn.oracle.com/docs/products/oracle9i/
doc_library / release2 / server.920 / a96533 / autotrac.htm # 14931
ЗАПИТУЙТЕ ТОМА
asktom.oracle.com
Том Кайт - віце-президент Oracle Government, Education, and Health group - відповідає на найбільш важкі питання, пов'язані з технологією баз даних Oracle. Найбільш яскраві матеріали цього форуму публікуються в даній колонці.
ВИВЧАЙТЕ курсори:
asktom.oracle.com/
- Consistent Gets (узгоджені читання) Скільки раз для блоку вимагалось виконання операцій узгодженого читання.
В основному, це буде сукупним розміром вашого результуючого набору.
- Bytes Received via SQL * Net from Client (байти, отримані від клієнта по SQL * Net). Загальна кількість байтів, отриманих від клієнта по мережі.
В основному, це - розмір вашого запиту, в тому вигляді, в якому він передається по мережі
- SQL * Net Roundtrips to / from Client (взаємні обміни по SQL * Net з клієнтом). Загальна кількість мережевих повідомлень, посланих або отриманих від клієнта.
В основному, це - число ваших взаємодій з сервером для отримання відповіді. Як тільки ви в SQL * Plus збільшите значення системної змінної ARRAYSIZE, ви побачите, що це число для операторів SELECT, які повертають багато рядків, зменшується (менше взаємних обмінів, оскільки кожне витяг N рядків - взаємний обмін). Коли ви зменшите значення ARRAYSIZE, ви побачите, що це число збільшується.
- Sorts (memory) (сортування в пам'яті). Число операцій сортування, які були повністю виконані в оперативній пам'яті і не зажадали записи на диск.
Ви мало що можете зробити краще в порівнянні з сортуванням в пам'яті, може бути тільки зовсім виключивши сортування. Сортування зазвичай виникають під час виконання запитів, які в специфікаціях умов вибірки містять операції з'єднання таблиць.
- Sorts (disk) (сортування на диску). Число операцій сортування, які зажадали принаймні одного запису на диск. Сортування, які вимагають дискового введення-виведення, інтенсивно споживають ресурси. Спробуйте збільшити значення параметра ініціалізації SORT_AREA_SIZE.
- Rows Processed (рядків оброблено). Загальна кількість рядків, повернутих вашим оператором SELECT або модифікованих оператором INSERT, UPDATE або DELETE.