Стабілізація плану оптимізатора в Oracle 8i/9i, Інші СУБД, Бази даних, статті

Дізнайтеся, як за допомогою “збережених шаблонів” (stored outlines) можна підвищити продуктивність програми, навіть якщо не можна змінювати його вихідний код, систему індексації та параметри конфігурації системи …

Інструментальні засоби: Для спрощення експериментів, у статті розглядається тільки простий SQL-і PL / SQL-код, що виконується в сеансі SQL * Plus. Читачеві необхідні будуть привілеї, які типовим кінцевим користувачам зазвичай не надають, але, в іншому, знадобиться лише знання основ мови SQL. Стаття починається з опису можливостей версії Oracle 8i, але потім автор переходить до Oracle 9i, в якому з’явилося ряд додаткових можливостей генерації збережених шаблонів і роботи з ними.

Чорний хід в чорний ящик

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

Починаючи з Oracle 8.1, вам більше не треба переписувати SQL-оператори, щоб додати підказки – можна передати оптимізатору підказки, не змінюючи код. Ця можливість відома як використання збережених шаблонів (Stored Outlines) або стабілізація плану оптимізатора (Plan Stability), причому, сонвная її ідея досить проста: ви зберігаєте в базі даних інформацію типу: “якщо зустрічається SQL-оператор типу XXX, то перед його виконанням треба вставити ось такі підказки в наступних місцях … ”

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

Є декілька проблем в Oracle 8i, які при цьому доведеться якось обходити (вони практично вирішені в Oracle 9i), але, в загальному випадку, використовувати збережені шаблони дуже легко. У цій статті описані деякі можливості, які при цьому відкриваються.

Огляд основних можливостей

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

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

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

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

Попередні дії

Створюємо користувача з привілеями: create session, create table, create procedure, create any outline і alter session. Підключаємося від імені цього користувача і виконуємо наступні оператори для створення таблиці:

create table so_demo (
n1 number,
n2 number,
v1 varchar2(10)
);
insert into so_demo values (1,1,”One”);
create index sd_i1 on so_demo(n1);
create index sd_i2 on so_demo(n2);
analyze table so_demo compute statistics;

Тепер необхідно створити процедуру, що обертається до цієї таблиці. Створюємо сценарій c_proc.sql, Що містить наступний код:

create or replace procedure get_value (
i_n1 in number,
i_n2 in number,
io_v1 out varchar2
)
as
begin
select v1
into io_v1
from so_demo
where n1 = i_n1
and n2 = i_n2;
end;
/

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

wrap iname=c_proc.sql

У відповідь ви повинні отримати:

Processing c_proc.sql to c_proc.plb

Замість виконання сценарію c_proc.sql для генерації процедури, виконайте неочевидний сценарій c_proc.plb, І ви виявите, що ніяких слідів використаного SQL-оператора в поданні user_source немає.

Що хоче зробити додаток?

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

У цьому невеликому тесті повний перегляд, швидше за все, є найефективнішим способом виконання запиту, але, припустимо, ми довели, що більш висока продуктивність досягається, коли сервер Oracle використовує план на базі одностолбцових індексів і опції and-equal. Як змусити сервер виконувати запит саме так, не додаючи в код підказки оптимізатору?

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

Що, на вашу думку, має робити програму?

Щоб змусити сервер Oracle робити те, що потрібно нам, необхідно пройти три етапи:


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

Отже, почнемо сеанс і виконаємо наступну команду:

alter session set create_stored_outlines = demo;

Потім виконаємо невеликий анонімний блок, що викликає процедуру, наприклад:

declare
m_value varchar2(10);
begin
get_value(1, 1, m_value);
end;
/

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

alter session set create_stored_outlines = false;

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

select name, category, used, sql_text
from user_outines
where category = “DEMO”;
NAME CATEGORY USED
—————————— —————————— ——-
SQL_TEXT
———————————————————————
SYS_OUTLINE_020503165427311 DEMO UNUSED
SELECT V1 FROM SO_DEMO WHERE N1 = :b1 AND N2 = :b2
select name, stage, hint
from user_outline_hints
where name = ” SYS_OUTLINE_020503165427311″;
NAME STAGE HINT
—————————— ———- ——————————
SYS_OUTLINE_020503165427311 3 NO_EXPAND
SYS_OUTLINE_020503165427311 3 ORDERED
SYS_OUTLINE_020503165427311 3 NO_FACT(SO_DEMO)
SYS_OUTLINE_020503165427311 3 FULL(SO_DEMO)
SYS_OUTLINE_020503165427311 2 NOREWRITE
SYS_OUTLINE_020503165427311 1 NOREWRITE

Як бачите, є категорія, demo, Що включає всього лише один зберігається шаблон, а подивившись на sql_text для цього шаблону можна побачити щось схоже на SQL-оператор у вихідному PL / SQL-коді, але не точно збігається з ним. Це суттєво, оскільки сервер Oracle буде розглядати можливість використання зберігається шаблону тільки якщо збережене значення sql_text дуже близько до тексту SQL-оператора, який потрібно виконати. Фактично, в Oracle 8i тексти повинні збігатися буквально, і спочатку це було великою проблемою при експериментах з збереженими шаблонами.

За лістингу видно, що зберігаються шаблони представляють собою набір підказок, що описують дії сервера Oracle, який він виконав (або повинен виконати) при виконанні відповідного SQL-оператора. Даний план використовує повний перегляд таблиці. Чи не правда, сервер Oracle використовуємо чимало підказок, щоб гарантувати виконання настільки простої дії, як повний перегляд таблиці? ..

Зверніть увагу, що зберігається шаблон завжди відноситься до певної категорії, в даному випадку, до категорії demo, Яку ми поставили у вихідній команді alter session. Якщо у вихідній команді просто вказати true замість demo, Збережені шаблони виявляться в категорії на ім’я default.

Збережені шаблони теж мають імена, і ці імена повинні бути унікальними в усій базі даних. Ім’я шаблона не може збігатися з ім’ям іншого шаблону, навіть згенерованого іншим користувачем. Фактично, у шаблонів немає власників, – є тільки творці. Якщо хтось створив зберігається шаблон, відповідний виконуваному мною надалі SQL-оператору, сервер Oracle застосує відповідний набір підказок до мого тексту, навіть якщо ці підказки позбавлені сенсу в контексті моєї схеми. (Це дає нам кілька абсолютно нових можливостей для формування збережених шаблонів, і заслуговує окремої статті). Можна зауважити, що коли сервер Oracle автоматично генерує збережені шаблони, імена мають простий формат і включають тимчасову позначку (час створення) з точністю до мілісекунди.

Продовжуючи процес “настройки” нашого проблематичного SQL-оператора, ми вирішуємо, що якщо додати подстказку /*+ and_equal(so_demo, sd_i1, sd_i2) */, Сервер Oracle буде використовувати необхідний нам план виконання, так що, тепер ми явно створює зберігається шаблон таким чином:

create or replace outline so_fix
for category demo on
select /*+ and_equal(so_demo, sd_i1, sd_i2) */ v1
from so_demo
where n1 = 1
and n2 = 2;

Цей оператор створює хоранімий шаблон з явно заданим ім’ям so_fix в категорії demo. Вид зберігається шаблону можна отримати, повторивши запити до user_outlines і user_outline_hints з додаванням умови name = “SO_FIX”.

NAME                           CATEGORY                 USED
—————————— ——————— ———
SQL_TEXT
—————————————————————
SO_FIX DEMO UNUSED
select /*+ and_equal(so_demo, sd_i1, sd_i2) */ v1
from so_demo
where n1 = 1
and n2 = 2
NAME STAGE HINT
—————————— ———- ——————————–
SO_FIX 3 NO_EXPAND
SO_FIX 3 ORDERED
SO_FIX 3 NO_FACT(SO_DEMO)
SO_FIX 3 AND_EQUAL(SO_DEMO SD_I1 SD_I2)
SO_FIX 2 NOREWRITE
SO_FIX 1 NOREWRITE

Зверніть увагу, зокрема, що рядок FULL(SO_DEMO) замінена рядком AND_EQUAL(SO_DEMO SD_I1 SD_I2), Що і було потрібно.

Тепер треба “поміняти місцями” ці два збережених шаблону. Ми хочемо, щоб сервер Oracle використовував наш новий список підказок при виконанні оператора з вихідним текстом, і для цього доведеться вдатися до трюку. Подання user_outlines і user_outline_hints створені на основі двох таблиць (ol$ і ol$hints, Відповідно), що належать схемою outln, І ми збираємося змінювати ці таблиці безпосередньо; для цього підключаємося від імені користувача outln або користувача, який має привілей зміни цих таблиць.

На щастя, таблиці outln не мають включених декларативних обмежень цілісності посилань. Нам на руку те, що взаємозв’язок між таблицями ol$ (Шаблони) і ol$hints (Підказки) задається на ім’я шаблона (яке зберігається в стовпці ol_name). Тому, особливо уважно перевіряючи імена, ми можемо поміняти підказки в збережених шаблонах, міняючи місцями імена в таблиці ol$hints наступним чином:

update outln.ol$hints
set ol_name =
decode(
ol_name,
“SO_FIX”,”SYS_OUTLINE_020503165427311″,
“SYS_OUTLINE_020503165427311″,”SO_FIX”
)
where ol_name in (“SYS_OUTLINE_020503165427311″,”SO_FIX”);

Вас може бентежити безпосереднє зміна даних, настільки близьких до ядра сервера Oracle, особливо з урахуванням коментарів у посібниках – але така зміна, фактично, санкціоновано документом Metalink Note: 92202.1 від 5 червня 2000 року. Однак у цьому документі не сказано, що може знадобитися й інший оператор update, Що гарантує узгодженість кількості підказок у кожному з збережених шаблонів з самими підказками. Якщо його не забезпечити, може виявитися, що деякі з збережених шаблонів пошкоджені або знищені в ході експорту / імпорту.

 update outln.ol$ ol1
set hintcount = (
select hintcount
from ol$ ol2
where ol2.ol_name in
(“SYS_OUTLINE_020503165427311″,” SO_FIX”)
and ol2.ol_name != ol1.ol_name
)
where
ol1.ol_name in
(“SYS_OUTLINE_020503165427311″,”SO_FIX”);

Після заміни можна підключитися в новому сеансі, зажадати використовувати збережені шаблони, повторно виконати процедуру і завершити сеанс. За допомогою sql_trace знову можна буде дізнатися, як же сервер Oracle фактично обробляв SQL-оператори. Щоб вимагати від сервера Oracle використовувати (змінений) шаблон, виконайте команду:

alter session set use_stored_outline = demo;

Переглянувши файл трасування, ви повинні виявити, що для виконання SQL-оператора тепер використовується план з and_equal. (Якщо ви використовуєте утиліту tkprof для обробки та вивчення файлу трасування, то можете виявити в результатах дві що суперечать один одному плану. Перший, правильний, план повинен показувати, що використовується and_equal, А другий, швидше за все, буде показувати повний перегляд таблиці, оскільки зберігається шаблон міг і не використовуватися коли утиліта tkprof виконувала explain plan для протрассировать SQL-оператора).

Від розробки – до впровадження

Тепер, коли нам вдалося створити потрібний шаблон, необхідно перенести його в виробниче середовище. Є безліч невеликих корисних особливостей збережених шаблонів, які при цьому знадобляться. Наприклад, можна перейменувати зберігається шаблон, експортувати його з сервера розробника, імпортувати у виробничій системі, перевірити, що і там він працює правильно, помістивши його в категорію “test”, А потім перевести у виробничу категорію. При цьому знадобляться команди:

alter outline SYS_OUTLINE_020503165427311 
rename to AND_EQUAL_SAMPLE;
alter outline AND_EQUAL_SAMPLE
change category to PROD_CAT;

Для експортування шаблону з середовища розробки у виробничу систему можна скористатися можливістю додавати конструкцію where у файлі параметрів експорту, що дозволяє створити такий файл:

userid=outln/outln
tables=(ol$, ol$hints, ol$nodes) # ol$nodes
існує тільки у версії 9

file=so.dmp
consistent=y # Дуже важливо
rows=yes
query=”where ol_name = “”AND_EQUAL_SAMPLE”””

Додаткові можливості Oracle 9

Є безліч інших деталей, які необхідно враховувати при роботі з збереженими шаблонами. Збережені шаблони у версії Oracle 8i мають ряд неприємних і обмежують можливості особливостей. На щастя, багато проблем вирішені в Oracle 9.

Самою тривіальної і очевидною проблемою при використанні збережених шаблонів в Oracle 8 було те, що вони могли використовуватися лише якщо збережений текст в точності збігався з надійшли. В Oracle 9 застосовується “Нормалізація”, послабляє це вимога збігу; тексти операторів перед порівнянням перетворюються в верхній регістр і всі зайві пробіли забираються. Це підвищує ймовірність, що незначно відрізняються SQL-оператори зможуть використовувати той же зберігається шаблон.

Є також ряд проблем з більш складними планами виконання, що включають декілька блоків запитів – корпорація Oracle вирішила їх в Oracle 9, додавши третю таблицю в схему outln, ol$nodes. Вона допомагає серверу Oracle розбивати на частини список підказок в таблиці ol$hints і враховувати їх у відповідних фрагментах оброблюваного SQL-оператора. Це, звичайно, добре, але може вплинути на стратегію заміни підказок одного зберігається шаблону на підказки іншого, оскільки в таблиці ol$hints з’явилися додаткові деталі про довжину і зміщенні фрагментів тексту. При переході на Oracle 9 доведеться використовувати інші методи створення збережених шаблонів, такі як окремі схеми із спеціально підібраними наборами даних або відсутніми індексами, або уявленнями з вбудованими підказками, імена яких збігаються з іменами таблиць у тексті настроюваних операторів.

Також в Oracle 9 з’явилися додаткові можливості створення необхідних збережених шаблонів, в тому числі, попередня версія пакету, що дозволяє безпосередньо редагувати збережені шаблони. Важливіше, однак, що з’явилася можливість більш безпечної роботи з планами, що зберігаються у виробничій системі. Хоча у виробничому середовищі експериментувати не любить ніхто, іноді виробнича система – єдине місце, де можна отримати реальні розподілу та обсяги даних, що дозволяють знайти оптимальний план виконання проблемного SQL-оператора. В Oracle 9 можна створити власну копію таблиць outln і вибрати в них “загальнодоступні” збережені шаблони для “приватних” експериментів, не ризикуючи зробити один з експериментальних збережених шаблонів видимим для коду кінцевого користувача. Особисто я виконував би таку настройку тільки в крайньому випадку, але цілком можу уявити собі ситуацію, коли вона може знадобитися. Менш небезпечно буде експериментувати в окремій тестовій системі або в системі розробника, а там ця можливість дозволить виконувати незалежне тестування.

Проблеми

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

По-перше, в Oracle 8i стандартний пароль для outln (Схеми, якій належать таблиці, що використовуються для розміщення збережених шаблонів) широко відомий, а сама обліковий запис має дуже небезпечні привілеї. Обов’язково поміняйте пароль цього облікового запису. В Oracle 9i ви виявите, що ця обліковий запис заблоковано.

По-друге, таблиці, які використовуються для розміщення збережених шаблонів, створюються в табличному просторі system. У виробничій системі виявиться, що при створенні збережених шаблонів ви використовуєте дуже багато простору в табличному просторі system. Має сенс перенести ці таблиці в інше табличний простір, переважно, – спеціально для них створене. На жаль, одна з таблиць містить стовпець типу long, Тому для перенесення в нове табличне простір, ймовірно, доведеться використовувати exp/imp.

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

Висновок

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

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

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

Джонатан Льюїс (Jonathan Lewis) – незалежний консультант з більш ніж 17-річним досвідом проектування та налаштування баз даних Oracle. Він – автор книги “Optimizing Oracle 8i“, Опублікованій видавництвом Addison-Wesley, розробник і ведучий семінару” Optimizing Oracle – Performance by Design “, а також упорядник списку ЧаВО The Co-operative Oracle Users” FAQ, Який можна знайти на сайті www.jlcomp.demon.co.uk.

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


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

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

Ваш отзыв

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

*

*