Том Кайт: про поділ

Том Кайт розглядає спільне використання курсорів, розщеплення великих таблиць і надійне видалення рядків.


Питання. Я трохи здивований вашою фразою: "Якщо ви будете використовувати параметр CURSOR_SHARING = SIMILAR, ви можете зменшити кількість згенерованих планів виконання, а, з іншого боку, ви можете мати точно таке ж кількість планів ".


Що впливає на кількість згенерованих планів? Я думав, що якщо в параметрі CURSOR_SHARING встановити значення SIMILAR, то оптимізатор замінить всі літерали на змінні зв'язування:
SYS_B _? – Це те, що ми бачимо в нашому сервері бази даних.


Відповідь. Параметр CURSOR_SHARING використовується в сервері Oracle Database для управління "автоматичним зв'язуванням" в SQL-операторах. Сервер може в запиті SELECT * FROM TABLE WHERE COL = "літерал" замінити "літерал" на змінну зв'язування, і предикат буде виглядати так: WHERE COL =: "SYS_B_0". Це дозволяє багаторазово використовувати згенерований план виконання запиту, що, можливо, призведе до кращого використання розділяється пулу і зменшення повних розборів (hard parses), виконуваних системою. Параметр CURSOR_SHARING може мати одне з трьох значень:



Давайте спочатку розглянемо, що буде відбуватися при виконанні однакового набору запитів з цими трьома значеннями параметра. Запити будуть простими: SELECT * FROM DUAL WHERE DUMMY = <щось>, де замість <щось> будуть використовуватися літерали "A" і "B". Потім я, використовуючи динамічне представлення продуктивності V $ SQL, подивлюся в поділюваному пулі, скільки для кожного запиту було створено курсорів. На лістингу 1 показана установка трьох значень параметра CURSOR_SHARING, виконання операторів SELECT і вміст стовпця SQL_TEXT подання V $ SQL, що показує фактичні SQL-оператори, які були використані в запитах.

SQL> alter session set cursor_sharing=exact;
Session altered.

SQL> select * from dual CS_EXACT where dummy = “A”;
no rows selected

SQL> select * from dual CS_EXACT where dummy = “B”;
no rows selected

SQL> alter session set cursor_sharing=force;
Session altered.

SQL> select * from dual CS_FORCE where dummy = “A”;
no rows selected

SQL> select * from dual CS_FORCE where dummy = “B”;
no rows selected

SQL> alter session set cursor_sharing=similar;
Session altered.

SQL> select * from dual CS_SIMILAR where dummy = “A”;
no rows selected

SQL> select * from dual CS_SIMILAR where dummy = “B”;
no rows selected

SQL> select sql_text
2 from v$sql
3 where sql_text like "select * from dual CS% where dummy =%"
4 order by sql_text;

SQL_TEXT
———————————————————
select * from dual CS_EXACT where dummy = “A”
select * from dual CS_EXACT where dummy = “B”
select * from dual CS_FORCE where dummy = :”SYS_B_0″
select * from dual CS_SIMILAR where dummy = :”SYS_B_0″


 

Лістинг 1.Значення параметра CURSOR_SHARING: EXACT, FORCE і SIMILAR.

Як видно на лістингу 1, з встановленим параметром CURSOR_SHARING = EXACT (встановлюється за умовчанням) для кожного унікального SQL-оператора, який я виконую, у поданні V $ SQL створюється нова запис – Виконується повний розбір оператора і для нього створюється новий план виконання. У разделяемом пулі можуть перебувати сотні і тисячі дуже схожих запитів, які відрізняються тільки літералами, використовуваними в SQL-операторах. Це означає, що в додатку не використовуються змінні зв'язування, і це також означає, що сервер бази даних змушений виконувати повний розбір практично кожного запиту, який, у свою чергу, не тільки споживає багато процесорного часу, але також приводить і до зменшення масштабованості. Сервер не може одночасно виконувати повний розбір сотень і тисяч SQL-операторів, тому додаток призупиняється, очікуючи доступу до поділюваного пулу. Один з головних чинників зменшення масштабованості в сервері бази даних – не використання змінних зв'язування. Це було причиною появи а сервері Oracle8i Release 2 (8.1.6) параметра CURSOR_SHARING = FORCE.


Зі встановленим параметром CURSOR_SHARING = FORCE сервер бази даних, як видно на лістингу 1, згенерував в поділюваному пулі тільки один розділяється запит, замінивши "A" і "B" на: "SYS_B_0" і зробивши курсор спільно використовуваних багатьма сеансами, яким він був потрібен. Взагалі кажучи, всі сеанси багаторазово використовували б тільки один план запиту. Це перетворило б повний розбір в частковий розбір (soft parse), який буде споживати менше ресурсів і одночасно збільшувати масштабованість системи, забезпечуючи більший паралелізм, оскільки часткового розбору у порівнянні з повним розбором потрібно менше "замикатися" розділяється пул (використовувати певний тип блокування).


Тим не менше, розглядаючи приклад на лістингу 1, ви можете припустити, що установка значень FORCE і SIMILAR призводить до одного й того ж результату, оскільки генеруються однакові плани. Отже, яке ж різниця між цими двома установками? Для того щоб показати цю відмінність, мені буде потрібно інший приклад, але спочатку я розповім про це. Коли в параметрі CURSOR_SHARING встановлено значення SIMILAR, сервер Oracle Database замінює все літерали на змінні зв'язування (так само як і при встановленні значення FORCE), але в цьому випадку сервер робить ще одну річ – він дивиться на кожен замінний літерал і задається питанням: "Чи можуть різні значення цієї змінної зв'язування призводити до генерації різних планів?" Наприклад, використання предиката WHERE X = 6 увазі, що бажано виконувати повний перегляд, а використання предиката WHERE X = 5 увазі, що бажано виконувати перегляд діапазону за індексом, сервер бази даних розпізнає такі ситуації і генерує різні плани. У випадку різних планів мінлива зв'язування позначається як ненадійна (unsafe) і її значення додається до сигнатурі цього запиту, так що для повторного використання цього курсору необхідно не тільки мати схожі SQL-оператори, але і таке ж значення цієї конкретної змінної зв'язування.


Саме тому установка значення SIMILAR може, а, з іншого боку, не може зменшувати кількість згенерованих планів, які можна побачити в поділюваному пулі. Щоб показати це, я створю таблицю з деякими даними, що мають дуже асиметричний розподіл, так що, коли я виконую запит з предикатом WHERE ID = 1, сервер Oracle Database захоче використовувати індекс стовпця ID, а коли я виконую запит з предикатом WHERE ID = 99, сервер Oracle Database не захоче використовувати індекс. На лістингу 2 показано створення таблиці з даними, що мають асиметричний розподіл, індексу, а також плани виконання запитів до цих даних.

SQL> create table t
2 as
3 select decode(rownum,1,1,99) ID,
4 all_objects.*
5 from all_objects
6 /
Table created.

SQL> create index t_idx on t (id);
Index created.

SQL> begin
2 dbms_stats.gather_table_stats
3 ( ownname => USER,
4 tabname => “T”,
5 method_opt => "for all indexed columns size 254",
6 cascade => TRUE
7 );
8 end;
9 /
PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from t where id=1;

Execution Plan
————————————————– ——————————–
0 SELECT STATEMENT Optimizer = ALL_ROWS (Cost = 2 Card = 1 Bytes = 96)
1 0 TABLE ACCESS (BY INDEX ROWID) OF "T" (TABLE) (Cost = 2 Card = 1 Bytes = 96)
1 лютому INDEX (RANGE SCAN) OF "T_IDX" (INDEX) (Cost = 1 Card = 1)

SQL> select * from t where id=99;

Execution Plan
————————————————– ——————————-
0 SELECT STATEMENT Optimizer = ALL_ROWS (Cost = 197 Card = 48028 Bytes = 4610688)
1 0 TABLE ACCESS (FULL) OF "T" (TABLE) (Cost = 197 Card = 48028 Bytes = 4610688)

Лістинг 2:Створення таблиці з даними, що мають асиметричний розподіл, та індексу, а також плани виконання запитів до цих даних.

Таблиця T, показана на лістингу 2, містить стовпець ID, дані якого мають дуже асиметричний розподіл – більшість значень дорівнює 99, і тільки один запис містить значення, рівне 1. Після того, як я створив індекс і зібрав статистику по таблиці (з генерацією гістограм по індексованих стовпцю, тому оптимізатор знає, що дані мають асиметричний розподіл), я бачу, що оптимізатор воліє перегляд діапазону за індексом, коли ID = 1, і повний перегляд, коли ID = 99.


Тепер, давайте виконаємо запити до цієї таблиці з різним предикатами ID =, використовуючи спочатку установку CURSOR_SHARING = FORCE, а потім EXACT, як показано на лістингу 3 (я вже знаю, чого слід очікувати від установки CURSOR_SHARING = EXACT – для кожного унікального значення стовпця ID буде генеруватися окремий план).

SQL> alter session set cursor_sharing=force;
Session altered.
SQL> select * from t CS_FORCE where id = 1;
1 row selected.
SQL> select * from t CS_FORCE where id = 50;
no rows selected
SQL> select * from t CS_FORCE where id = 99;
48029 rows selected.
SQL> select * from t CS_FORCE where id = 1;
1 row selected.
SQL> select * from t CS_FORCE where id = 50;
no rows selected
SQL> select * from t CS_FORCE where id = 99;
48029 rows selected.
SQL> alter session set cursor_sharing=similar;
Session altered.
SQL> select * from t CS_SIMILAR where id = 1;
1 row selected.
SQL> select * from t CS_SIMILAR where id = 50;
no rows selected
SQL> select * from t CS_SIMILAR where id = 99;
48029 rows selected.
SQL> select * from t CS_SIMILAR where id = 1;
1 row selected.
SQL> select * from t CS_SIMILAR where id = 50;
no rows selected
SQL> select * from t CS_SIMILAR where id = 99;
48029 rows selected.
SQL> select sql_text
2 from v$sql
3 where sql_text like “select * from t CS% where id = %”
4 order by sql_text;
SQL_TEXT
————————————————
select * from t CS_FORCE where id = :”SYS_B_0″
select * from t CS_SIMILAR where id = :”SYS_B_0″
select * from t CS_SIMILAR where id = :”SYS_B_0″
select * from t CS_SIMILAR where id = :”SYS_B_0″

Лістинг 3:Значення FORCE, SIMILAR і дані з асиметричним розподілом.

Як видно на лістингу 3, коли встановлено CURSOR_SHARING = FORCE, генерується один і тільки один план виконання. Це, фактично, план "на всі випадки життя", і в цьому випадку в ньому використовується перегляд діапазону за індексом (оскільки оптимізатор для генерації плану обов'язково повинен був використовувати змінну зв'язування, а в першому розібраному запиті використовувався предикат ID = 1).


Однак, як видно на лістингу 3, коли встановлено CURSOR_SHARING = SIMILAR, генеруються три плани, оскільки оптимізатор виявив, що для пошуку за значеннями стовпця ID використовуються різні значення цього стовпця, що можуть призводити до генерації різних планів (цю інформацію дає йому статистика, збір якої показаний на лістингу 2). Отже, фактичне значення змінної зв'язування було додано до сигнатурі цього плану запиту, і лише запит з точно такою ж сигнатурою міг знову використовувати цей план. У цьому полягала мета виконання кожного із запитів два рази – показати, що можливе повторне використання курсору. У поданні V $ SQL немає шести запитів, є тільки чотири. При установці CURSOR_SHARING = SIMILAR повторне використання курсору не гарантується навмисно.


Отже, чи означає це, що при установці CURSOR_SHARING = SIMILAR для будь-якого унікального набору літералів буде генеруватися новий план? Ні, я вже показував на лістингу 1 приклад з таблицею DUAL, коли використовувалися пропозиції WHERE DUMMY = "A" і WHERE DUMMY = "B". Новий план генерується тільки тоді, коли підстановка змінної зв'язування вважається ненадійною. Використовуючи приклад з лістингу 2, коли виконувалося тільки ненадійне скріплення за стовпцем ID, я виконаю запит з цього колонку і по деякому іншому стовпцю, але не буду змінювати в предикаті значення стовпця ID, то побачу повторне використання курсору, як це показано на лістингу 4.

SQL> alter session set cursor_sharing=similar;
Session altered.

SQL> select * from t CS_SIMILAR where id=1 and object_id=1;
no rows selected
SQL> select * from t CS_SIMILAR where id=1 and object_id=2;
no rows selected
SQL> select * from t CS_SIMILAR where id=1 and object_id=3;
no rows selected

SQL> select sql_text
2 from v$sql
3 where sql_text like "select * from t CS% where id =% and object_id =%"
4 order by sql_text;

SQL_TEXT
————————————————– ———————–
select * from t CS_SIMILAR where id =: "SYS_B_0" and object_id =: "SYS_B_1"

Лістинг 4:Установка CURSOR_SHARING = SIMILAR.

Як показано на цьому лістингу, я зраджував літерали в предиката зі стовпцем OBJECT_ID, але не зі стовпцем ID. Оптимізатор розпізнав, що значення стовпця OBJECT_ID надійні, і йому для різних значень цього стовпця в предиката не потрібно генерувати різні плани, тому він не додавав до сигнатурі курсору значення цього стовпця. Тільки тоді, коли в предикаті використовуються різні значення стовпця ID, будуть генеруватися нові плани.


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


Розщеплення великої таблиці

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

Range        Start        End
—— —— —–
0 1 1000
1 1001 2000
2 2001 3000

Відповідь. Це щось подібне до того, що я часто реалізовую, називаючи це "саморобним паралелізмом" (do-it-yourself parallelism). Ідея полягає в тому, щоб розбити велику таблицю на деяке число неперекривающіхся діапазонів, запустивши декілька паралельних процесів.


Це просто зробити, використовуючи вбудовану функцію NTILE (). Якщо потрібно розбити подання словника даних ALL_OBJECTS на вісім неперекривающіхся діапазонів з приблизно однаковим числом рядків у кожному, можна використовувати наступний код:

SQL> select min(object_id) min,
2 max(object_id) max,
3 count(*) cnt,
4 nt
5 from
6 (
7 select object_id,
8 ntile(8) over
9 (order by object_id) nt
10 from all_objects
11 )
12 group by nt;

MIN MAX CNT NT
—– —– —– —-
2 6811 6005 1
6812 13117 6005 2
13118 19122 6005 3
19123 25127 6005 4
25128 31132 6005 5
31133 37142 6004 6
37143 44620 6004 7
44621 98225 6004 8

8 rows selected.


Багато разів я повторював на сайті asktom.oracle.com: аналітика – це круто. Аналітика – найкраща річ, що з'явилася в мові SQL після введення ключового слова SELECT.


Проблема з каскадним видаленням

Питання. Припустимо, у мене є дві таблиці, T1 (батьківська) і T2 (дочірня), яка пов'язана з батьківського таблицею зовнішнім ключем з пропозицією каскадного видалення: REFERENCES T1 (X, Y, Z. ..) ON DELETE CASCADE. Рядки можуть вилучатися з таблиці T1, в цьому випадку з таблиці T2 також видаляються дочірні рядка. Точно так само рядки можуть видалятися безпосередньо з таблиці T2, але без відповідного видалення батьківських рядків з таблиці T1.


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

Відповідь. Для мене це просто захист даних. Ви не хочете видаляти рядки безпосередньо з дочірньої таблиці, отже, нікому не надавайте об'єктну привілей DELETE на цю таблицю. Це можна зробити легко:

SQL> create table p
2 ( x int primary key );
Table created.

SQL> create table c
2 ( x references p
3 on delete cascade );
Table created.

SQL> insert into p
2 values ( 1 );
1 row created.

SQL> insert into c
2 values ( 1 );
1 row created.

SQL> grant select, delete
2 on p to scott;
Grant succeeded.

SQL> grant select
2 on c to scott;
Grant succeeded.

SQL> connect scott/tiger
Connected.

SQL> delete from ops$tkyte.c;
delete from ops$tkyte.c
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> delete from ops$tkyte.p;
1 row deleted.

SQL> select * from ops$tkyte.c;
no rows selected


Ви можете сказати: "Так, але адміністратор бази даних може увійти в систему і видалити ці рядки …". Я ж скажу: "Так, але адміністратор бази даних може обійти все що завгодно, встановлене, – все що завгодно, ну і що "? Власник схеми може зробити те ж саме, тому ви просто захищаєте дані, щоб ніхто (на рівні додатків) не міг видалити їх.


Чи є способи зробити це за допомогою тригерів? Так, але писати їх досить складно (і їх також можна обдурити).
Засоби захисту роблять це чисто.


Фактично, якщо ви використовували мову PL / SQL і ніколи не надавали ніяких об'єктних привілеїв INSERT / UPDATE / DELETE, подумайте, наскільки "безпечно" ви могли зробити це!


Ведучий даної колонки Том Кайт (Tom Kyte) працює в корпорації Oracle з 1993 року. Кайт – віце-президент Oracle, який очолює групу Oracle Public Sector, він автор книг "Expert Oracle: 9i and 10g Programming Techniques and Solutions" (Apress, 2005), "Effective Oracle by Design" (Oracle Press, 2003) і "Expert One on One: Oracle" (Apress, 2003) (Прим. пер. Є російський переклад: Oracle для професіоналів. Книга 1. Архітектура та основні особливості. Книга 2. Розширення можливостей і захист. – ДіаСофт.).

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


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

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

Ваш отзыв

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

*

*