Коли Використовувати Індекс, Інші СУБД, Бази даних, статті

[Від гл.редактора OM / RE А.Бачіна: Публікація цієї статті має якусь передісторію, яку я вкраце хочу повідати нашим читачам.
В журналі Oracle Magazine (весна 1995) Кері Міллсап (Cary Millsap), Грег Шаллхамер (Craig Shallahamer) і Міша Адлер (Micah Adler) опублікували в статтю “Predicting the Utility of the Nonunique Index.” [Millsap і Al 1993] (“Коли використовувати неунікальний індекс”). Ця стаття була переведена на російську мову і опублікована в нашому журналі “Мир Oracle”, який виходив ще у паперовому виданні. Інтернету в нашій країні ще не було (віриться з трудом, але чиста правда!), тому стаття збереглася лише в архівах і пам’яті багатьох наших читачів, як прекрасне джерело правильного підходу до використання індексів. Всі останні роки мені хотілося заново перекласти і Переопубліковать цю статтю, щоб розробники і АБД нового покоління познайомилися з правильним підходом до цієї проблеми. Але коли справа дійшла до справи, виявилося, що ні в кого з доступних адресатів не зберігся англійський варіант цієї статті. Навіть у самого автора, Кері Міллсап. Коли я до нього звернувся, він порадив перекласти і опублікувати новий її варіант, в якому сумно зазначив [4] Можливе неувага до первісного тексту. Я постарався його в цьому переконати, послав йому scan-копію статті та обкладинки журналу … Він був нам вдячний і дозволив перекладати і публікувати статті з сайту компанії Hotsos Enterprises, ніж ми є дещо, з вдячністю ще не раз скористаємося. Спасибі, Кері!
]

===***===***===***===

[Від редакції OM / RE: На сайті корпорації Oracle з’явилася Oracle ACE (http://www.oracle.com/technology/community/oracle_ace/index.html) – “Алея слави”, тобто галерея найбільш прославлених Oracle-авторів, серед яких заслужене місце займає автор даної статті Cary Millsap. З цієї “Алеї слави” взята публікує тут фотографія автора статті.]

 

Резюме

Коли слід використовувати індекс? Більше десяти років розробники додатків Oracle використовували просте rule of thumb (правило великого пальця) – емпіричне правило для наближених розрахунків, щоб вирішити, чи використовувати неунікальний індекс (non-unique index). Однак, в повсякденній роботі ми не рідко зустрічаємося з проблемами продуктивності, викликаними використанням цього емпіричного правила. В цій статті я викладаю наступні результати наших досліджень:


 

Коли використовувати індекс: Традиційний Рада

В одному або будь-якому іншому вигляді, але стандартна рекомендація, чи треба використовувати індекс, принаймні, починаючи з версії Oracle 5, звучала наступним чином:

Використовуйте індекс, коли запит повертає менше ніж x% рядків таблиці.

Рисунок 1 ілюструє поняття, коли якийсь поріг у x% діє як точка балансу продуктивності Oracle в порівнянні діапазонною сканування індексу і повного сканування таблиці, здійснюваного по шляхах доступу. Цей графік пов’язує час відповіді R (Зазвичай виражається в секундах) в пропорції до Pr рядків таблиці, які повертаються за дану операцію запиту.

 

Рисунок 1. Час відповіді R в секундах як процентна функція Pr повертаються рядків таблиці. Пунктирна лінія при R = 6.75 (червона лінія, якщо ви бачите це в кольорі) є часом відповіді при повному перегляді таблиці. Безперервна (синя) лінія – час відповіді діапазонною сканування індексу, який повертає Pr відсотків рядків даної таблиці.

Час відповіді при плані виконання, що повертає r рядків при повному перегляді таблиці, є приблизно постійним, незалежно від того, r – Це один рядок або загальна кількість рядків у таблиці. Однак, час відповіді діапазонною сканування індексу збільшується у міру того, як наростає обсяг результуючих вихідних рядків [1]. Відсоток pr = x – Граничне значення pr, Коли час відповіді повного перегляду таблиці і діапазонною сканування індексу порівнюються. При значенні pr < x діапазонні сканування індексу має кращу продуктивність. При значенні pr > x кращу продуктивність надає повний перегляд таблиці.

Тим не менш, у цій лінії міркування є велика проблема. Будь-яке правило типу великого пальця щодо індексів ненадійно, якщо існує балансовий відсоток типу x.

 

Чому правило великого пальця ненадійно

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



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

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

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

Наші випробування і практичний досвід показали, що позиція 1) є істинною навіть для дуже маленьких таблиць. Запит, що повертає один рядок, більш ефективний, коли виконується з використанням індексу, ніж за допомогою повного перегляду таблиці, навіть якщо таблиця містить тільки один рядок. Багато людей, з якими ми обговорили це, висловили здивування таким результатом. Цей результат також суперечить цілком конкретної рекомендації Oracle: “малі таблиці не вимагають індексів” [Oracle 2001a]. Малі таблиці можуть не вимагати наявності індексів, але індекси на малих таблицях можуть зробити вашу систему значно ефективнішою і, отже, значно більш масштабованої [2].

Отже, ми приймаємо позицію 1), але на позиції 2) починаються великі проблеми. Іноді набагато дешевше прочитати 100% рядків таблиці, використовуючи індекс, ніж при повному скануванні таблиці.

Приклад: Уявімо таблицю з ім’ям interface, яка займає (high-water mark – вища відмітка використання простору) 10,000 блоків. Хоча в своєму історичному минулому таблиця interface містила сотні тисяч рядків, сьогодні таблиця включає тільки 100 рядків. Ці рядки довільно розсіяні по 30 блокам таблиці. Припустимо, що таблиця має первинний ключ на стовпці з назвою id, на якому, звичайно, побудований індекс (з ім’ям id_u1). І далі нам треба виконати наступний запит:

             select id, date, status from interface i ;

Якщо цей запит виконувати за допомогою повного перегляду таблиці, то буде потрібно 10,000 LIO-дзвінків Oracle. Ми можемо злегка переробити цей запит, щоб дозволити Oracle виконувати його, використовуючи індекс. Якщо id – числовий стовпець і всі значення id – невід’ємні цілі числа, то наступний запит виводить бажаний набір рядків за допомогою індексу:

     select /*+ index(i id_u1) */ id, date, status from interface i where id> -1 ;


Цей запит зажадає менше 40 LIO-дзвінків Oracle. Час відповіді складе приблизно 10,000 / 40, тобто в 250 разів краще при використанні індексу, ніж при вибірці 100% рядків з таблиці за допомогою повного її перегляду.

Існує багато різних гачків і закарлюк (all sorts of hooks and crooks), які можна досліджувати на цьому прикладі. Наприклад, якщо б фраза select містила тільки id або count(id) (Що може бути отримано з інформації індексу навіть без звернення до сегменту даних), то перегляд за індексом був би ще швидше.

Отже, щоб бути придатним у випадках подібних цьому, емпіричне правило (великого пальця) для будь-якого відсотка проіндексованих рядків має допускати можливість, що використання індексу може бути більш ефективно, ніж повний перегляд таблиці навіть для тих запитів, які повертають всі 100% рядків таблиці. На малюнку 2 показаний цей феномен.

 

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

Існує багато випадків, коли засновані на відсотках емпіричні правила є ненадійними. Є також велика проблема, пов’язана з висловленою раніше постулатом 3). Ця проблема ще покаже себе в ході подальшого викладу.

 

Нерівномірно еволюціонує ознака x

Згадана велика проблема емпіричного правила індексації полягає в тому, що немає чіткої ясності, яке значення x повинно використовувати. Якщо простежити історію рекомендацій для x в документації Oracle, то ви знайдете наступне: [3]


 







Версія

Oracle


Рекомендоване значення x по документації Oracle

5

6

7

8

8i

9i

10-15

10-15

2-4

2-4

15

15

Положення навіть гірше, ніж показано в таблиці. Якщо пам’ять мені не зраджує, ранній випуск виробничої документації Oracle7 містив рекомендацію для x як “1-15 відсотків”. Я був вражений тим, наскільки широкий був діапазон. Якщо ж заглибитися в це питання, деякі з моїх друзів з Oracle Applications development дуже переконливо говорили, що у своїх додатках вони часто спостерігали значення x більше 40.

Багато людей вважають, що причина, по якій гойдається (wiggling) x, полягає в тому, що Oracle продовжує удосконалювати роботу оптимізатора (optimizer). Але це не загальна дійсна причина. Причина ж того, що значення x став таким об’єктом, що рухається (moving target), в тому, що автори рекомендацій не зуміли виявити справжні параметри, які дають збалансоване значення.

Критичний параметр – це число блоків Oracle нижче найвищої точки заповнення (high-water mark) таблиці, яку можна ігнорувати при використанні індексу [4]. Шлях побудови правила створення індексу, яке перевершить емпіричне правило великого пальця і ​​яке зробить життя більш легкої, повинен включати питання: “Який план виконання потребуватиме менше число блоків Oracle, які повинні бути переглянуті? ”

Для будь-якого джерела рядків, з більш ніж одним рядком, індекс у багато разів дозволяє Вам скоротити PIO-виклики. Число PIO-дзвінків для блоків даних, яке ігнорується при залученні індексу, залежить від наступного:




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

select id, date from shipment where flag=”x”


 

Притча про індексатора

Давайте розкриємо важливість концепції, званої селективністю блоків за допомогою історії. Мова піде про …


Екстраординарна частота слова “the”, ймовірно, зробить цю роботу абсолютно нездійсненним навіть при наявності індексу. “Давайте подивимося, де є” the “… Ах да,” the “є на першій сторінці.” Добре, що ви відзначили першу “the” сторінку в індексі. Тоді клацнете по індексу для першої сторінки. Ви розташуйте слово після першого “the”. Потім ви повертаєтеся до індексу, щоб знайти наступну сторінку, на якої з’являється “the” – це також сторінка один. Ви будете ходити вперед і назад, поки ви не відвідаєте кожну сторінку всіх множин книжкових часів. Ви будете клацати по книзі назад і вперед стільки багато раз, що її обкладинка, ймовірно, повністю зноситься.

Тепер уявімо, що існує Reader”s Digest Large Print for Easier Reading (Довідник Читача для Великого Видання для більш легкого читання) цієї Brief History of Humanity (Короткої Історії Людства). Далі уявімо собі, що основна книга надрукована літерами по 72 пункту. Тому Brief History of Humanity містить тільки по 20-30 слів на сторінці. І хоча слово “the” досить загальне і фактично з’являється на кожній сторінці звичайної книги, воно вже не досить звичайно, щоб з’являтися на кожній сторінці довідника Large Print. У цих нових умовах індекс має дуже велику корисність для нашого невеликого проекту “find the word after the” the “” (“знайти слово після” the “”), тому що тепер індекс дозволяє нам пропускати більшу кількість сторінок.

 

Це – 72-пунктовий шрифт. Довідник Large Print for Easier Reading для книги Brief History of Humanity містить набагато менша кількість посилань, ніж до кожної сторінки стандартного розміру.

 

Розгадка міфу

Параметри, які впливають на корисність індексу при діапазоні скануванні, при якому потрібно rowid-доступ до таблиці, наступні:


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


 

Що ж тепер?

Моя вам порада:

Забудьте все про правила індексації типу великого пальця, заснованих на відсотках.

Насправді немає ніякого діапазону відсотків, що дасть вам надійний результат. Є запити, які повертають 1% або менше число рядків таблиці, які більш ефективно виконуються повним переглядом таблиці, ніж з використанням індексу. І існують запити, які повертають всі 100% рядків таблиці, які більш ефективно виконуються за допомогою індексу. Але якщо ви наполягаєте на виборі значення для x, Я рекомендую знайти таке значення, яке одночасно є менше 1% і більше або дорівнює 100%. Так як ніяке таке число не існує, я рекомендую, щоб ви повністю переключили свою увагу в іншу сторону від правил індексації типу великого пальця, заснованих на відсотках.

Технологія оптимізації Oracle виконала довгий шлях, починаючи з впровадження заснованого на вартості (cost-based) оптимізатора Oracle (це було дуже добре в Oracle8i). Все що потрібно від Вас – це визначити, які створити індекси. Ядро Oracle тільки тоді буде використовувати створені вами індекси, коли це ефективно. Але створення індексу, який ніколи не буде добре використовуватися, – тільки трата і місця, і часу [6]. Тому ви повинні самі вирішувати, створювати індекс чи ні? Відповідь у селективності блоків.

 Селективність блоків

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

 – Визначення селективності рядків (row selectivity)

Селективність блоків можна визначати, аналогічно задаючи в фразі where предикат відносини числа блоків даних, що містять принаймні один рядок, що відповідає умові предиката (b), до загального кількістю блоків даних нижче high-water mark (B):

 – Визначення селективності блоків (block selectivity)

Різниця між селективністю рядків і селективністю блоків досить суттєво, тому що селективність блоків майже завжди гірше – часто багато гірше – селективності рядків. Раніше на прикладі таблиці shipment ми бачили flag =”x”. Для цього предиката виходить селективність рядків 1%, а селективність блоків 100%.

Ви можете обчислити селективність рядків і селективність блоків, використовуючи SQL-скрипт з наступного прикладу, який ми назвали hds.sql [Holt 2002].

1 rem $Header: /usr/local/hotsos/RCS/hds.sql,v 1.8 2002/01/07 18:12:27 hotsos Exp $
2 rem Copyright (c) 2000-2002 by Hotsos Enterprises, Ltd. All rights reserved.
3 rem Author: jeff.holt@hotsos.com
4 rem Notes: Hotsos data selectivity using a full table scan for the row count.
5
6 define v_substr7 = “substr(rowid,15,4)//substr(rowid,1,8)”
7 define v_substr8 = “substr(rowid,7,9)”
8 define v_over = “substr(“”&_O_RELEASE””,1,1)”
9
10 col dummy new_value v_substr
11
12 set termout off heading on pause off
13
14 select decode(&v_over, “7”, “&v_substr7”, “&v_substr8”) dummy
15 from dual;
16
17 set termout on verify off feedback off pages 10
18
19 accept p_town prompt “TableOwner : ”
20 accept p_tname prompt “TableName : ”
21 accept p_clst prompt “ColumnList : ”
22 accept p_where prompt “WhereClause: ”
23 accept p_pgs prompt “PageSize : ”
24
25 variable fblks number
26
27 declare
28 tblks number;
29 tbytes number;
30 ublks number;
31 ubytes number;
32 luefid number;
33 luebid number;
34 lublk number;
35 begin
36 sys.dbms_space.unused_space(
37 upper(“&p_town”), upper(“&p_tname”), “TABLE”,
38 tblks, tbytes, ublks, ubytes, luefid, luebid, lublk, null
39 );
40 :fblks := tblks – ublks;
41 end;
42 /
43
44 col blks form 9,999,999,999 heading “Table blocks below hwm/(B)” just c
45 col nrows form 999,999,999,999 heading “Table rows/(R)” just c new_value v_nrows
46
47 select :fblks blks, count(*) nrows
48 from &p_town..&p_tname;
49
50 col bs form a17 heading “Block selectivity/(pb = b/B)” just c
51 col nblks form 9,999,999,999 heading “Block count/(b)” just c
52 col rs form a17 heading “Row selectivity/(pr = r/R)” just c
53 col nrows form 999,999,999,999 heading “Row count/(r)” just c
54
55 set pause on pause “More: ” pages &p_pgs
56
57 select &p_clst,
58 lpad(to_char(count(distinct &v_substr)/:fblks*100,”990.00″)//”%”,17) as bs,
59 count(distinct &v_substr) nblks,
60 lpad(to_char(count(*)/&v_nrows*100,”990.00″)//”%”,17) rs,
61 count(*) nrows
62 from &p_town..&p_tname &p_where
63 group by &p_clst
64 order by bs desc;

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

Приклад: система має таблицю з ім’ям po.cs_ec_po_items. Наша мета полягає в тому, щоб оптимізувати кілька подоперацій запиту, які у фразі where використовують предикат ec_po_id =:vas . Що вийде, якщо ми створимо індекс на стовпці ec_po_id? Ми можемо використовувати скрипт hds.sql, щоб отримати справжню інформацію про розподіл даних за різними значеннями ec_po_id:

 

Вихідні дані скрипта hds.sql відсортовані за убуванням селективності блоків. Лістинг звичайно містить тисячі рядків, але все найгірші дані (worst-case data) – в даному випадку представляють найбільш цікаву частину – знаходяться нагорі. Тому ми звичайно обриваємо лістинг hds.sql після видачі однієї-двох сторінок.

Зауважимо, що для цієї таблиці має місце чудова селективність рядків для кожного значення ec_po_id. “Найгірше” значення селективності рядків – тільки 0.54% [7]. Це означає, що тільки півпроцента рядків таблиці має значення ec_po_id = “8”. Однак стовпець селективності блоків представляє нам зовсім іншу історію. Селективність блоків ec_po_id = “8” складає 63.50%. Це означає, що майже двох третинах блоків таблиці міститься принаймні по одному рядку, для який ec_po_id = “8”.

Чи повинні ми створити індекс на ec_po_id? Можна витратити півдня чи більше, обчислюючи “back of the envelope” (“швидко і легко визначається”) відповідь, намагаючись вирахувати за формулами витрати плану виконання. Але оптимізатор Oracle може зробити цю роботу за вас. Найбільш точний і, в кінцевому рахунку, найменш віднімає час метод для визначення відповіді полягає в тому, щоб виконати тестування на фактичній базі даних Oracle. Кращий спосіб визначити відносні витрати двох планів виконання полягає в тому, щоб виконати їх на якомусь тестових даних з установкою опції sql_trace=true. Якщо потрібна велика детальність в частині, скажімо, використання інших (не-CPU) механізмів, яких задіє Oracle протягом виконання запиту, то протрассіруйте виконання з використанням Oracle-події 10046 на рівні 8 [Hotsos 2002]. Якщо потрібно більшу кількість даних про те, чому оптимізатор вибрав такий план, який сам і зробив, то протрассіруйте виконання з Oracle-подією з випадком 10053 [Lewis 2001].

З лістингу hds.sql ми дізнаємося граничні умови, які потрібно перевірити. Наприклад, ми тепер знаємо, що при тестуванні слід відповісти на наступні запити:


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


Коли використовується інструмент подібно скрипту hds.sql, спостерігається один з трьох варіантів:



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

  2. Селективність блоків кожного значення настільки низька, що ви напевне не хочете створити індекс для стовпчика.

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

Рішення у випадках 1 і 2 очевидні. А ситуація 3, ймовірно, саме та, в якій ви перебуваєте найбільш часто. Користувачі вартісного оптимізатора Oracle до релізу 7.3 стояли перед жорстким вибором. Якщо індекс не створювався, то був великий ризик низької продуктивності при деяких значеннях у фразі where; якщо індекс створювався, то був ризик низької продуктивності для інших значень. Новітні версії вартісного оптимізатора Oracle роблять життя набагато простіше. Якщо в наші дні ви регулярно виконуєте свої обов’язки зі збору статистики [8], Подібна ситуація набагато менш імовірна, і помилкове створення мало придатного індексу заподіє екстремальні витрати (torture – катування) вашим користувачам.

Приклад: Уявімо собі, що секціонірованная таблиця містить стовпець id з наступним розподілом даних:

 

Показане тут розподіл даних сильно перекошене (highly skewed). Тепер видамо наступний запит до цієї таблиці:

select name from division d where id=:a1

Без гістограм вартісної оптимізатор може припустити, що є десять різних значень id, кожен id відповідає за приблизно 1/10 рядків таблиці. Це припущення змусить його згадати хорошу ідею використовувати індекс на стовпці id. І так було б до тих пір, поки :a1 != “01”.

Сила гістограмного (histogram-based) оптимізації полягає в тому, що належним чином реалізований [9] Гістограмного оптимізатор помітить, коли: a1 = “01” і не буде намагатися використовувати індекс на id. Без гістограмного оптимізації розробник додатків повинен або



  1. оптимізувати запит так, щоб було ефективно, якщо: a1 = “01”, але вкрай неефективно інакше [10]; Або

  2. ви повинні написати процедурну логіку, яка використовує одне SQL-пропозиція для звичайних значень і інше SQL-пропозиція для рідкісних значень. Oracle General Ledger генерує динамічні SQL-пропозиції, використовуючи метод 2 для функцій Financial Statement Generator. Це розумно, але водночас і безлад (a mess).

Значення не часто розподіляються довільним чином

Недавня документація по Oracle призводить припущення, що “рядки в таблиці впорядковані довільно (randomly ordered) щодо шпальти, на якому базується запит” [Oracle 2001b]. Це припущення трохи спрощує написання Oracle-документації, але це робить ця рада Oracle менш корисним, ніж він міг би бути.

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


Приклад: таблиця shipment має стовпець стану, званий shipped, який приймає значення “y”, Тоді і тільки тоді, коли пункт замовлення був відвантажений (shipped). Оскільки замовлення мають тенденцію вирушати, грубо кажучи, в тій же послідовності, що були введені, таблиця shipment через якийсь час має гарну природну кластеризації за значеннями shipped=”n”, Як це показано на малюнку 3. Кластеризація рядків з shipped=”n” покращує корисність індексу при пошуку рядків з shipped=”n” .

 

Рисунок 3. Значення стовпця стану мають тенденцію до природної кластеризації.

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



Приклад: таблиця address має стовпець з ім’ям state, Який містить двох літерний код штату або провінції. В додатку, який використовує цю таблицю, немає ніяких очевидних відносин між часом, коли була вставлена ​​рядок клієнта, і значенням state клієнта. Отже, фізичний розподіл кожного значення state практично однорідний. Хоча state =”TX” Поправді, можливо, тільки для одного рядка з 30, лише деякі блоки таблиці не мають жодного рядка з state =”TX”. Малюнок 4 показує цю ситуацію.

 

[Блок містить принаймні одну рядок з state = “TX”
Блок не містить жодного рядка, для який state =”TX” ]

Рисунок 4. Індекс на state має низьку корисність для state =”TX “.


Використання тут індексу по с state, Ймовірно, було б неефективно для пошуку будь-якого “відомого” (“popular”) коду штату. Але якщо, наприклад, є один або більше штатів з багато меншою кількістю рядків, ніж наявна блоків в таблиці address, І якщо Ви часто шукаєте коди таких штатів і використовуєте гістограми, тоді створення індексу в state, Ймовірно, допоможе вашому додатком.

Стовпці Status іноді можуть самостійно групуватися природним чином. Але при відсутності будь-якого штучного зовнішнього впливу стовпці type в більшості мають тенденцію до однорідного фізичному розподілу. Існує кілька типів впливу на фізичне зберігання даних в таблиці. Можна наказати певну фізичну впорядкованість даними, використовуючи:


Без потреби не припускайте, що розподіл ваших даних випадково (random). З’ясуйте це за допомогою hds.sql. Будь-які прийоми, розпорядчі фізичну впорядкованість, принесуть як вигоди, так і витрати вашому бізнесу. Якщо зміна фізичного розподілу даних одночасно допомагає максимізувати чистий прибуток вашої компанії, потік готівки (cash flow) і повернення інвестицій, тоді робіть це [Goldratt 1992].

 

Висновок

Багато джерел вчать, що рішення по індексації треба приймати на базі аналізу предиката селективності рядків у фразі where. Ще гірше, коли деякі джерела обговорюють застосування індексації в термінах селективності рядків для всього стовпця, що повністю ігнорує можливість його асиметричності. Однак селективність рядків – Ненадійне підстава для рішення про створення індексу. Кращий спосіб пом’якшувати ризик полягає в тому, щоб перевірити фактичну продуктивність SQL-пропозиції на перевірених тестових даних. Інструмент, подібний скрипту hds.sql, який видає інформацію про селективності блоків, Підвищує надійність і ефективність вашого випробування, розкриваючи критичні значення стовпця, на якому ви збираєтеся перевірити продуктивність.

Вартісний (cost-based) оптимізатор Oracle робить простішим відповідь на питання, чи треба будувати індекс, оскільки він виробляє більш просунуті рішення по використанню індексів, ніж це може зробити синтаксичний (rule-based) оптимізатор. Але для реалізацій, які все ще покладаються на синтаксичний оптимізатор Oracle, розуміння важливості селективності блоків може бути життєво важливо для продуктивності додатків Oracle. Як тільки визначаться характеристики селективності блоків, необхідно виключити пасивний підхід до фізичного впорядкованості ваших даних. Багато можливостей, привнесених в СУБД Oracle, починаючи з випуску 7.3, спрощують ваші дії по зберіганню даних у фізичній впорядкованості, що забезпечує чудову продуктивність.

Примітки:

[1] Джерело рядків – Це просто якийсь підмножина рядків таблиці (можливо, рядки всієї таблиці, у випадках повного перегляду таблиці). Висновок результату запиту часто розглядається як операція вичерпання джерела рядків (as the operation”s resulting row source). У складному плані виконання запиту Oracle, подальша (child) операція плану виконання передасть свій закінчився джерело рядків своєму батькові (to its parent) для подальшої обробки (типу з’єднання або фільтрації).

[2] В Oracle8i ми знайшли, що створення і використання індексу на таблиці sys.dual суттєво зменшує число LIOS, потрібних, щоб вибрати її єдиний рядок. Ми спостерігали щодо 10:1 відповідне скорочення часу відповідей на запити до dual. Якщо ваш додаток виконує, наприклад, мільйони LIO-запитів в день до однорядковим посилальним таблицями, то за нашими даними, індексуючи такі таблиці, ви зможете заощадити ці мільйони LIOS в день і задіяти приблизно на 90% CPU менше для тих же самих запитів.

[3] Будь ласка, не плутайте цю таблицю з діаграмами, які співвідносять зміни x з модифікаціям ядра Oracle. Наприклад, [Niemiec 1999 (38, 318)] вказує, що домінуючий фактор у зміні значення x – Номер версії Oracle. Моя ж мета полягає в поясненні того, що реальний рівень (the actual break-even) істотно залежить від інших параметрів і фактично незалежний від номера версії Oracle.

[4] Craig Shallahamer, Micah Adler і я ще в 1993 [Millsap та ін 1993] Написали статтю в Oracle Magazine про знаходження істинних параметрів, які впливають на x. Craig, Micah і я може бути єдині люди в світі, які читали цю статтю. Нещодавно переглянувши її заново, я більш чітко зрозумів, чому вона не потрапила ні в одне з більшого числа загальнодоступних видань, з якими я коли-небудь працював. Скажімо, в одній з рядків цієї статті була наведена наступна формула:

 

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

[5] Без сумніву сортування тексту Brief History of Humanity (Коротка Історія Людства) в алфавітному порядку за словами зруйнувала б значення тексту, як сенс нашого запиту типу “find the word that follows the word” the “” (“знайти слово, яке слід за словом “<певний артикль>” “).

[6] …, Поки цей індекс не буде потрібно, щоб задіяти умова обмеження (constraint definition).

[7] Не можна говорити, що 0.54% – найгірша селективність для рядків таблиці, дивлячись тільки на наведений тут лістинг. Вихід скрипта hds.sql сортується по зменшенню селективності блоків. Це НЕ увазі зменшується впорядкованості селективності рядків. (Для доказу зауважимо, що значення селективності рядків в показаному тут лістингу, не з’являються в строго спадному порядку.) Щоб визначити найгірше значення селективності по рядках в лістингу в hds.sql, треба дослідити повний вихідний потік цього скрипта.

[8] Це означає, що треба регулярно запускати fnd_stats, якщо ви – менеджер Oracle Applications, або пакет dbms_stats в інших випадках.

[9] Це довга історія, гістограмного оптимізація Oracle пішла крізь важкі компромісні рішення. До Oracle9i використання пов’язаних змінних перешкоджало гістограмного оптимізації. Це дуже сумно, оскільки в загальному випадку програма, яка не використовує пов’язані змінні, не може масштабуватися на збільшення користувацьких обчислень. В Oracle9i оптимізатор робить майже те, що ми хочемо: він “peeks” (“дивиться”) на контент, що відноситься до пов’язаної змінної, і виробляє рішення, засноване на гістограмі. Однак, при наступних після першого виконаннях розділяється (shared) SQL-пропозиції, щоразу береться план першої оптимізації сесії. Тобто, якщо перше виконання запиту використовує :a1 = “01”, То друге (і кожне наступне) виконання буде застосовувати оптимальний план: a1 = “01”, навіть якщо шукається :a1 = “07”.

Добра вість в тому, що не обов’язково використовувати зв’язані змінні для тих предикатів у фразі where, які мають невеликий областю значущості. Наприклад, використовуючи літеральние значення в sex=”m”, Предикат хороший, тому що він породжує тільки два різних варіанти розділяється SQL-пропозиції в бібліотечному кеші. Якщо ж літеральние значення використовувати в чомусь подібному order_id = “1289942” , Ефект був би катастрофічним, тому що це потенційно змусить задіяти тисячі майже ідентичних, але тим не менш різних SQL-пропозицій, використовуючи бібліотечний кеш.

[10] Один з старомодних способів оптимизирования такого запиту полягає в тому, щоб переписати його подібно наступного:

                  select name from division d where nvl(id,id)=:a1

Ця техніка запобігає Oracle від використання будь-якого індексу з id в якості його префікса. Більш сучасний спосіб зробити це полягає в тому, щоб визначити вказівку (hint), наприклад:

                  select /*+ full(d) */ name from division d where id=:a1

Починаючи з Oracle8i, Ми можемо виконати один крок покращення: ми можемо оптимізувати запит, маніпулюючи його stored outline (зберігається план виконання). Ця чудова нова особливість дозволяє нам оптимізувати пропозицію без отримання доступу до вихідного його коду (without requiring access to the statement”s source code).

 

Посилання


Подяки

Насамперед висловлюю подяку Джефу Холт (Jeff Holt), компанія Hotsos, за великий внесок в цю роботу, за його дослідження, тестування, коректуру, перевірку фактів, а також дієву перевірку використаних в цьому матеріалі принципів. Через дев’ять років після нашої дискусії в Buckhorn Exchange в Денвері (Denver) я хотів би знову подякувати Craig Shallahamer (Грег Шаллахамер) і Micah Adler (Міша Адлер) за хорошу бесіду про принципи індексного селективності. Спасибі також Steve Adams (Стів Адамс), Jonathan Lewis (Джонатан Льюїс), Bjorn Engsig (Бьорн Енгсіг), Dominic Delmolino (Домінік Делмоліно), Mogens Norgaard (Могенс Норгаард) і Zach Friese (Зак Фрайз) за їх конструктивну зворотний зв’язок протягом підготовки цього матеріалу. Нарешті, я вдячний студентам нашої Hotsos Clinic, які допомогли нам підвищити якість відомостей про селективності індексів.

Про автора

Cary Millsap (Кері Міллсап) – співвласник компанії Hotsos Enterprises, Ltd., Діяльність якої спрямована на самостійне підвищення кваліфікації адміністраторів баз даних Oracle у всьому світі за допомогою формування навчальної аудиторії (classroom education) – www.hotsos.com, через яку надаються інформація, програмне забезпечення, сервіси та консультантская послуги. Cary Millsap – автор Optimal Flexible Architecture (Оптимальна Гнучка Архітектура), творець оригінального інструментарію APS, Hotsos проектувальника програмного забезпечення і розробника, редактор hotsos.com, творець і основний викладач Hotsos Clinic з проблем діагностики продуктивності систем Oracle (Oracle ® System Performance Problem Diagnosis).

Протягом десяти років К. Міллсап в якості провідного експерта по системної продуктивності працював в корпорації Oracle, де він заснував System Performance Group (Група системної продуктивності) і був її віце-президентом. Він навчив тисячі Oracle-консультантів, аналітиків підтримки, розробників і клієнтів оптимальному використанню технологій Oracle, завдяки їх фіксованим опису, навчання і публічних про них виступах. Ще працюючи в Oracle, К. Міллсап поліпшив продуктивність більш ніж 100 клієнтських системах, включаючи (за прямою вказівкою президента Oracle) кілька негативно розвиваються ситуацій. Він працював в глобальному консультаційному комітеті Oracle з регулювання (the Oracle Consulting global steering committee), де він відповідав за розгортання сервісного обслуговування у всьому світі.

Схожі статті:


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

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*