Формування збережених шаблонів в Oracle 9

У попередній статті я розглядав збережені шаблони і описав один механізм "обману" системи для отримання необхідного зберігається шаблону. Я також підкреслив, що використання цього методу в Oracle 9 пов'язане з певним ризиком, оскільки детальність подання інформації істотно зросла. У даній статті, яка продовжує ту ж тему, я представлю законний спосіб маніпулювання ххранімимі шаблонами, який можна використовувати як в Oracle 8, так і в Oracle 9. Фактично ця стаття заснована на експериментах, що проводилися в стандартно встановлених версіях Oracle 8.1.7.0 і Oracle 9.2.0.1.


Огляд


Що робити, якщо відомо, як істотно прискорити роботу оператора ЯМД, додавши кілька підказок, але немає доступу до вихідного коду, в якому можна було б вставити ці підказки?


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


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


Можна переглянути список збережених SQL-операторів м підказок, які з ними пов'язані, з допомогою пари запитів, представлених на рис. 1.

select name, used, sql_text
from user_outlines
where category = “DEFAULT”;

select stage, node, hint
from user_outline_hints
where name = "{одне з імен}";


Рис. 1. Перегляд збережених шаблонів.


У попередній статті я виклав ідею, як "обійти" систему шляхом створення зберігається шаблону законними методами, а потім – зміни таблиць у схемі outln за допомогою пари SQL-операторів, які заміняють отриманий результат на дані зберігається шаблону, створеного для аналогічного оператора, але з необхідними підказками.


Там же я вказав, що цей метод був цілком безпечний у Oracle 8, але може привести до проблем в Oracle 9 у зв'язку із змінами в новій версії.


У даній статті розглядаються ці зміни і описується законний спосіб реєстрації необхідного набору підказок для проблемних запитів в таблицях outln.


Зміни


Якщо підключитися до схеми outln (Яка за умовчанням в Oracle 9 заблокована) і подивитися список наявних таблиць, виявиться, що в Oracle 9 добавлена одна таблиця. У схему входять наступні таблиці:

ol$ – SQL-оператори
ol$hints – Підказки
ol$nodes – Блоки запиту

Третя таблиця – нова; вона використовується для прив'язки списку підказок до різних блокам під (внутрішньо переписаної) версії SQL-запиту. Також можна виявити, що список підказок (ol$hints) Доповнено подпробностямі про довжину і зміщення фрагментів тексту.


Стовпці всіх трьох таблиць представлені на рис. 2, причому, нові стовпці, що з'явилися у версії Oracle 9, помічені зірочками.

ol$

OL_NAME VARCHAR2(30)
SQL_TEXT LONG
TEXTLEN NUMBER
SIGNATURE RAW(16)
HASH_VALUE NUMBER
HASH_VALUE2 NUMBER ***
CATEGORY VARCHAR2(30)
VERSION VARCHAR2(64)
CREATOR VARCHAR2(30)
TIMESTAMP DATE
FLAGS NUMBER
HINTCOUNT NUMBER
SPARE1 NUMBER ***
SPARE2 VARCHAR2(1000) ***

ol$hints

OL_NAME VARCHAR2(30)
HINT# NUMBER
CATEGORY VARCHAR2(30)
HINT_TYPE NUMBER
HINT_TEXT VARCHAR2(512)
STAGE# NUMBER
NODE# NUMBER
TABLE_NAME VARCHAR2(30)
TABLE_TIN NUMBER
TABLE_POS NUMBER
REF_ID NUMBER ***
USER_TABLE_NAME VARCHAR2(64) ***
COST FLOAT(126) ***
CARDINALITY FLOAT(126) ***
BYTES FLOAT(126) ***
HINT_TEXTOFF NUMBER ***
HINT_TEXTLEN NUMBER ***
JOIN_PRED VARCHAR2(2000) ***
SPARE1 NUMBER ***
SPARE2 NUMBER ***

ol$nodes (Нова таблиця у версії 9)

OL_NAME VARCHAR2(30)
CATEGORY VARCHAR2(30)
NODE_ID NUMBER
PARENT_ID NUMBER
NODE_TYPE NUMBER
NODE_TEXTLEN NUMBER
NODE_TEXTOFF NUMBER


Рис. 2. Таблиці у схемі outln.


Пара нюансів впадає в око відразу – подання, створені на базі цих таблиць, які не включають масу корисної інформації. Хоча в таблиці ol$hints і з'явилося 10 нових стовпців, визначення подання user_outline_hints не змінилося. Фактично, це подання і в Oracle 8 було занадто урізаним, не включаючи, зокрема, вельми інформативний стовпець hint#.


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


Також можна виявити, що сигнатура (значення стовпця signature) В Oracle 9 відрізняється від відповідного значення в Oracle 8. Це пов'язано з принциповою стратегічною зміною в новій версії, спрямованим на підвищення ймовірності використання збережених шаблонів. У Oracle 8 зберігається шаблон використовувався тільки якщо SQL-оператор збігався з оператором у шаблоні буквально, з точністю до пробілу, регістру символів і перекладу рядка. У Oracle 9 правила збігу ослаблені, так що тексти операторів порівнюються після видалення повторюваних "пробільних символів" та приведення тексту до одного регістру. Наприклад, наступні два оператори будуть використовувати один і той самий шаблон.

select * from t1 where id = 5;

SELECT *
FROM T1
WHERE ID = 5;


Це стратегічне зміна призвело до зміни сигнатури для SQL-оператора, для якого спочатку генерується план. Тому при перекладі бази даних з сервера Oracle 8 на Oracle 9 доведеться перегенеріровать збережені шаблони, – інакше може виявитися, що вони більше не використовуються. (Насправді, пакет outln_pkg з псевдонімом dbms_outln включає спеціальну процедуру update_signatures для вирішення цієї проблеми).


Саме ж суттєва зміна в таблицях версії 9, однак, полягає в набагато більш детальному описі тексту запиту та об'єктів, які він зачіпає. Перш ніж читати далі, виконайте оператори в прикладі, представленому на рис. 3, і перегляньте вміст таблиці ol$hints.

drop table t1;

create table t1
nologging
as
select
rownum id,
rownum n1,
object_name,
rpad(“x”,500) padding
from
all_objects
where
rownum <= 100;

alter table t1
add constraint t1_pk primary key (id);

create index t1_i1 on t1(n1);

analyze table t1 compute statistics;

create or replace outline demo_1 on
select * from t1
where id = 5
and n1 = 10;


Рис. 3. Код прикладу.


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


Якщо виконати запити, представлені на рис. 1, до плану demo_1, Створеному в результаті цим прикладом, можна виявити, що з ним пов'язано шість наступних підказок:

 	STAGE  NODE  HINT
3 1 NO_EXPAND
3 1 ORDERED
3 1 NO_FACT(T1)
3 1 INDEX(T1 T1_PK)
2 1 NOREWRITE
1 1 NOREWRITE

Як і очікувалося, четверта рядок показує, що для доступу до таблиці використовується індекс по первинному ключу (T1_Pk). Але що нам робити з цим збереженим шаблоном, якщо насправді необхідно, щоб сервер Oracle використав не унікальний індекс T1_I1? В ідеалі хотілося б змінити цей зберігається шаблон так, щоб рядок:

3 1 INDEX(T1 T1_PK)

була замінена рядком:

3 1 INDEX(T1 T1_I1)

Нові можливості


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


Однак при перегляді опису пакету і читанні посібників виявляється, що в пакет входять тільки такі процедури, пов'язані з "редагуванням":

CREATE_EDIT_TABLES
DROP_EDIT_TABLES
CHANGE_JOIN_POS

Перші дві процедури дозволяють створювати і видаляти локальні копії таблиць, що звичайно перебувають у схемі outln. Третя дозволяє змінювати порядок з'єднання таблиць у збереженому плані. Немає жодної процедури, що дозволяє просто змінити одну підказку. В даний час цей пакет здається практично марним, але він з часом, безсумнівно, стане більш "просунутим".


Запасний варіант, звичайно ж, пов'язаний з безпосереднім зміною таблиць! Якщо підключитися від імені користувача outln і вивчити вміст таблиці ol$hints (На базі якої побудовано подання user_outline_hints), Можна спробувати виконати наступне зміна:

update ol$hintsset 	hint_text = “INDEX(T1 T1_I1)”
where ol_name = “demo_1”
and hint# = 4;

Знову підключившись до тестової схемою, скинувши вміст розділяється пулу і включивши використання збережених шаблонів:

connect test_user/test
alter system
flush shared_pool;
alter session
set use_stored_outline=true;

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


Старі методи (1)


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


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


Перший спосіб (спочатку описаний, наскільки я знаю, Томом кайтом в його книзі “Expert One on One: Oracle”) ("Oracle для професіоналів" у моєму перекладі на російську – прим. перекладача) пов'язаний із заміною таблиць уявленнями, що містять необхідні підказки.


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

Create or replace view t1 as
Select /*+ index(t1,t1_i1) */ *
from test_user.t1;

Після створення цього подання, використовуємо цю схему для "перекомпіляції" існуючого шаблону за допомогою команди:

alter outline demo_1 rebuild;

Врахуйте, що для успішного виконання цієї команди потрібна привілей alter any outline.


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


Чому цей спосіб працює? Тому що зберігаються шаблони не належать ніякої схемою. При пересоздании шаблону на ім'я demo_1 у новій схемі, ім'я T1 позначає локальне уявлення, що містить підказку, тому сервер Oracle враховує цю підказку в реальному плані виконання, і, отже, в шаблоні. Якщо звернутися до подання user_outline_hints, Можна виявити, що критична рядок дійсно має вигляд:

	 3      1  INDEX(T1 T1_I1)

На жаль, можна також помітити, що тепер в уявленні є три рядки виду:

     2      1  NOREWRITE
1 2 NOREWRITE
1 1 NOREWRITE

Спочатку таких рядків було тільки дві:

     2     1  NOREWRITE
1 1 NOREWRITE

Ми також додали підказку, що застосовується для "Stage 1, Node 2"(" Стадія 1, Пункт 2 "). Я не беруся стверджувати, що точно знаю, що це означає, але це має бути пов'язано з тим, що при аналізі та оптимізації запиту з іншої схеми сервер Oracle виконав додатковий крок, перетворюючи посилання на виставу в посилання на базову таблицю.


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


Старі методи (2)


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



  1. Створимо нову схему.
  2. Створимо таблицю T1 у цій схемі.
  3. Створимо ТІЛЬКИ індекс T1_I1.
  4. Перебудуємо шаблон в цій схемі.

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


Однак у цьому методі є і прихована проблема, на цей раз, трохи більш тонка. Повертаючись до рис. 2, на якому представлені визначення нових стовпців, що з'явилися в Oracle 9, – як ви гадаєте, яка інформація міститься в стовпці user_table_name? Там зберігається уточнене ім'я таблиці; тобто:

 {Ім'я користувача}. {Ім'я_таблиці}

У нашому випадку це дозволить серверу Oracle зрозуміти, що таблиця T1, Фактично, належить до нової схеми, а не вихідної. Хоча сервер Oracle і використовує отриманий зберігається шаблон, інформації в таблиці достатньо, щоб він міг зрозуміти, що план застосовується не для того об'єкта.


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


Безпечний спосіб


Здається, є тільки один спосіб згенерувати зберігається шаблон, використання якого в тривалій перспективі не пов'язане з ризиком – треба бути якомога чесніше. Генерувати шаблон потрібно в тій же схемі і для тих же об'єктів.


У нашому випадку, треба видалити індекс по первинному ключу, згенерувати план, а потім замінити первинний ключ!


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


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


Наприклад: у запасний базі даних цілком можна буде видалити первинний ключ, щоб уникнути сканування (unique scan) відповідного індексу. Якщо після цього сервер Oracle не почне використовувати інший індекс автоматично, можна всіма можливими способами обманювати його, наприклад:



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


Висновки


Інформація, що записується в зберігається шаблон в Oracle 9, набагато більш "вразлива", ніж в Oracle 8. Раніше "змінювати" шаблони було порівняно просто і безпечно. Колишні методи працюють, але великий об'єм додаткової інформації, яка збирається в Oracle 9, дозволяє припустити, що в майбутньому їх використання пов'язане з ризиком.


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

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


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

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

Ваш отзыв

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

*

*