Том Кайт: про завантаження даних і екстента, Інші СУБД, Бази даних, статті

Наш експерт розповідає про завантаження даних в режимі прямого доступу і оптимізує використання екстентів.

Питання. Ми завантажуємо в базу даних масу файлів (понад 49000), використовуючи утиліту SQL * Loader, що працює в паралельному режимі і режимі прямого завантаження. Наші табличні простору управляються локально, розмір екстентів визначений як 4 MB. Завантаження мають різні розміри, а їх середній розмір дорівнює приблизно 380 KB. Ми бачимо, що сервер Oracle Database для кожного завантаження виділяє новий екстент (число екстентів в таблиці = числа завантажуються в таблицю файлів) замість того, щоб використовувати в екстента доступне простір.

Чи можете ви пояснити, як утиліта SQL * Loader, що працює в паралельному режимі і режимі прямого завантаження, виділяє екстенти?

Відповідь. Це питання задається часто, а відповідь на нього досить складний. На щастя, я вже дав на нього повну відповідь у книзі Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions (Apress, 2005). У цій відповіді пояснюється, як операції прямого доступу використовують існуюче простір (коротко кажучи, вони його не використовують). Так що, в даному випадку, сервер Oracle Database буде прагнути виділити 49 000 екстентів розміром 4 MB і завантажити в кожен з них близько 380 KB даних!

Після пояснення, як сервер Oracle Database виділяє простір при виконанні операцій прямого доступу, я покажу два можливі вирішення цієї проблеми. Одне рішення – використання набагато меншого уніфікованого розміру екстентів (uniform size), інше (більш просте) – автоматичне визначення і контроль розмірів екстентів сервером Oracle Database (для створення таких табличних просторів використовується пропозицію AUTOALLOCATE), при цьому сервер обрізає екстенти до мінімально можливого розміру.

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

Паралельні DDL-оператори та обрізання екстентів

У паралельних DDL-операторах і паралельних операціях, таких як завантаження в прямому режимі в утиліті SQL * Loader, використовуються операції прямого доступу (direct path operations). Тобто, дані не розміщуються в кеші буферів, а відразу ж записуються на диск; такі операції, як CREATE TABLE AS SELECT будуть створювати нові екстенти і записувати дані безпосередньо в них (дані потрапляють на диск прямо із запиту). Кожен сервер паралельного виконання (parallel execution server), що виконується в операції CREATE TABLE AS SELECT, пише в свій власний екстент. Оператор вставки в прямому режимі INSERT / * + APPEND * / (Direct path insert) пише дані за межами маркера максимального рівня заповнення сегмента (HWM, high-water mark), і знову кожен сервер паралельного виконання пише в свій власний набір екстентов, які ніколи не будуть використовуватися іншими серверами паралельного виконання. Тому, якщо ви виконуєте паралельний оператор CREATE TABLE AS SELECT і для створення таблиці використовуєте чотири сервера паралельного виконання, ви будете мати принаймні чотири екстента (може бути навіть більше). Коли якийсь сервер паралельного виконання полнстью заповнює свій екстент, він створює новий власний екстент.

Все це здається цілком нормальним, але при завантаженні великих обсягів даних у сховищах даних це може привести до втрати простору. Припустимо, ви хочете завантажити 1010 MB даних (близько 1 GB) в табличне простір з екстента розміром 100 MB. Ви вирішуєте для завантаження цих даних використовувати 10 серверів паралельного виконання. Кожен сервер при запуску створює свій власний екстент розміром 100 MB (Усього їх буде 10) і заповнює його. Кожен сервер повинен завантажити 101 MB, тому після заповнення першого екстента йому потрібно створити інший екстент (розміром 100 MB), в якому буде використовуватися 1 MB простору. Тепер ви маєте 20 екстентів, 10 з яких заповнені повністю, а в 10 в кожному екстента використовується 1 MB простору, всього ж виділено, але не використовується, 990 MB. Це простір може бути використано в подальших операціях звичайного доступу, але тепер же ви маєте 990 MB втраченого простору. Ось де може стати в нагоді обрізання екстентів. Сервер Oracle Database буде намагатися обрізати останній екстент кожного сервера паралельного виконання до найменшого можливого розміру.

Обрізання екстентів і табличні простору, керовані за допомогою словника даних

Якщо ви використовуєте успадковані табличні простору, керовані за допомогою словника даних, сервер Oracle Database зможе перетворити екстенти розміром 100 MB, в яких міститься тільки 1 MB даних, в екстенти розміром 1 MB. На жаль, тут (в табличних просторах, керованих за допомогою словника даних) фрагментіруется вільний простір: за 99 MB вільного простору слід 1 MB зайнятого простору, потім – 99 MB вільного простору і т.д. Зрештою, виділення наступних 100 MB може виявитися неможливим. (Надалі я не буду більше зупинятися на цих табличних просторах, керованих за допомогою словника даних.)

Обрізання екстентів і локально керовані табличні простору

Приступимо до локально керованим табличним просторам (LMT, locally managed tablespace). Для створення таких табличних просторів можна використовувати два типи пропозицій: UNIFORM SIZE – кожен екстент завжди має точно однаковий розмір, і AUTOALLOCATE – сервер Oracle Database визначає розмір кожного екстента, використовуючи внутрішній алгоритм. Обидва ці підходи прекрасно вирішують проблему фрагментації вільного простору. Проте роблять вони це дуже по-різному.

У підході UNIFORM SIZE зовсім не використовується обрізання екстентів – все екстенти мають однаковий розмір і ніхто з них не може бути більше або менше

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

Розглянемо різницю між двома типами локально керованих табличних просторів. Для цього нам потрібен реальний приклад. Ми створимо зовнішню таблицю, яка буде використовуватися для паралельної завантаження даних з прямим доступом. Навіть якщо ви для такого завантаження і раніше використовуєте утиліту SQL * Loader, інформація цього розділу цілком застосовна – для фактичного завантаження даних ви повинні тільки вручну написати керуючі файли. Отже, для того, щоб вивчити обрізання екстентів, нам потрібно створити визначення зовнішньої таблиці, виконати завантаження з різними варіантами умов і проаналізувати результати.

Підготовка. Спочатку нам потрібно створити зовнішню таблицю. У мене є успадкований керуючий файл утиліти SQL * Loader, який я використовував для завантаження даних:

LOAD DATA
INFILE “/tmp/big_table.dat”
<11p class=”bodycopy” >INTO TABLE big_table
REPLACE
FIELDS TERMINATED BY “/”
(
id, owner, object_name, subobject_name, object_id, data_object_id,
object_type, created, last_ddl_time, timestamp, status, temporary,
generated, secondary
)

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

$ sqlldr big_table/big_table
big_table.ctl
external_table=generate_only
SQL*Loader: Release 10.1.0.3.0 –
Production on Mon Jul 11 14:16:20 2005
Copyright (c) 1982, 2004, Oracle.
All rights reserved.

Зверніть увагу, параметр EXTERNAL_TABLE передається в утиліту SQL * Loader. В цьому випадку він змушує цю утиліту не завантажувати дані, а записати в протокольний файл оператор CREATE TABLE. Цей оператор показаний на лістингу 1. (Показаний скорочений оператор – щоб зменшити приклад, я викреслив повторювані елементи.)







Лістинг 1: скорочений оператор CREATE TABLE.

CREATE TABLE “SYS_SQLLDR_X_EXT_BIG_TABLE”
(
“ID” NUMBER,
.
.
.
“SECONDARY” VARCHAR2(1)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1
BADFILE “SYS_SQLLDR_XT_TMPDIR_00000″:”big_table.bad”
LOGFILE “big_table.log_xt”
READSIZE 1048576
FIELDS TERMINATED BY “/” LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
“ID” CHAR(255)
TERMINATED BY “/”,
.
.
.
“SECONDARY” CHAR(255)
TERMINATED BY “/”
)
)
location
(
“big_table.dat”
)
REJECT LIMIT UNLIMITED)

Все, що ми тепер повинні зробити – трохи відредагувати цей оператор: змінити, можливо, імена каталогів тощо:

SQL> create or replace directory
2 my_dir as “/tmp/”
3 /
Directory created.






Лістинг 2: оператор CREATE TABLE.

SQL> CREATE TABLE “BIG_TABLE_ET”
2 (
3 “ID” NUMBER,
.
.
.
16 “SECONDARY” VARCHAR2(1)
17 )
18 ORGANIZATION external
19 (
20 TYPE oracle_loader
21 DEFAULT DIRECTORY MY_DIR
22 ACCESS PARAMETERS
23 (
24 RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1
25 READSIZE 1048576
26 FIELDS TERMINATED BY “/” LDRTRIM
27 REJECT ROWS WITH ALL NULL FIELDS
28 )
29 location
30 (
31 “big_table.dat”
32 )
33 )REJECT LIMIT UNLIMITED
34 /
Table created.

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

SQL> alter table big_table_et PARALLEL;
Table altered.

Обрізання екстентів в локально керованих табличних просторах з екстента уніфікованого розміру і екстента автоматично визначається розміру. І це все, що ми повинні зробити для підготовки до завантаження. Тепер ми хочемо порівняти управління простором в локально керованих табличних просторах з екстента уніфікованого розміру, рівного 100 MB, і з екстента автоматично визначається розміру. Спочатку ми створимо табличне простір з екстента уніфікованого розміру – LMT_UNIFORM:

SQL> create tablespace lmt_uniform
2 datafile
3 “/u03/ora10gr1/lmt_uniform.dbf”
4 size 1048640K reuse
5 autoextend on next 100m
6 extent management local
7 uniform size 100m;
Tablespace created.

Тепер ми створимо табличне простір з екстента автоматично визначається розміру – LMT_AUTO:

SQL> create tablespace lmt_auto
2 datafile
3 “/u03/ora10gr1/lmt_auto.dbf”
4 size 1048640K reuse
5 autoextend on next 100m
6 extent management local
7 autoallocate;
Tablespace created.

Початковий розмір файлу даних кожного табличного простору дорівнює 1 GB (плюс 64 KB, використовуваних для управління зберіганням; або ж 128 KB, якщо використовуються блоки розміром 32 KB). Ці файли даних будуть автоматично розширюватися (autoextend) порціями розміром 100MB. Ми збираємося завантажити файл:

$ ls -lag big_table.dat
-rw-rw-r– 1 tkyte 1067107251 …

У цьому файлі знаходиться 10000000 записів. Спочатку скриптом big_table.sql була створена таблиця big_table, яка потім була вивантажена в плоский файл скриптом flat.sql, який доступний на сайті asktom.oracle.com/tkyte/flat/index.html. Тепер цей файл ми завантажимо в кожне табличний простір, використовуючи режим паралельної прямого завантаження: SQL> create table uniform_test 2 parallel 3 tablespace lmt_uniform 4 as 5 select * from big_table_et; Table created. SQL> create table autoallocate_test 2 parallel 3 tablespace lmt_auto 4 as 5 select * from big_table_et; Table created.

У моїй системі з чотирма центральними процесорами цей оператор CREATE TABLE виконувався з використанням восьми серверів паралельного виконання та одного координатора. Я перевірив це (під час виконання цих операторів), виконавши запит (лістинг 3) до одного з динамічних уявлень продуктивності, пов’язаних з паралельним виконанням, – V $ PX_SESSION.







Лістинг 3: запит до подання V $ PX_SESSION.

SQL> select sid, serial#, qcsid, qcserial#, degree
2 from v$px_session;
SID SERIAL# QCSID QCSERIAL# DEGREE
———- ———– —— ———– ———
137 17 154 998 8
139 13 154 998 8
141 17 154 998 8
150 945 154 998 8
161 836 154 998 8
138 8 154 998 8
147 15 154 998 8
143 41 154 998 8
154 998 154
9 rows selected.

Зверніть увагу, при завантаженні таблиць UNIFORM_TEST і AUTOALLOCATE_TEST ми просто вказуємо пропозицію PARALLEL, а ступінь паралелізму вибирає сам сервер Oracle Database. В даному випадку я на машині – Єдиний користувач (доступні всі ресурси), і сервер Oracle Database за замовчуванням використовують вісім серверів паралельного виконання – це кількість залежить від числа центральних процесорів (чотири) і значення параметра PARALLEL_THREADS_PER_CPU (за замовчуванням в ньому встановлена ​​двійка).

Стовпці SID і SERIAL # – ідентифікатори сеансів паралельного виконання, а стовпці QCSID і QCSERIAL # – ідентифікатори координатора паралельного виконання запиту. Отже, маючи вісім серверів паралельного виконання, нам хочеться дізнатися, як було використано простір. Це можна зробити швидко, виконавши показаний на лістингу 4 запит до подання USER_SEGMENTS.







Лістинг 4: запит до подання USER_SEGMENTS.

SQL> select segment_name, blocks, extents
2 from user_segments
3 where segment_name in ( “UNIFORM_TEST”, “AUTOALLOCATE_TEST” );
SEGMENT_NAME BLOCKS EXTENTS
———————————————
UNIFORM_TEST 204800 16
AUTOALLOCATE_TEST 145592 714

У нас використовуються блоки розміром 8 KB, тому на лістингу 4 показано, що розмір таблиці AUTOALLOCATE_TEST приблизно на 462MB або на 70 відсотків менше розміру таблиці UNIFORM_TEST. Якщо ми подивимося на фактично використане простір, показане на лістингу 5 (використовується широко відома процедура show_space, яку створив Том Кайт, – oracle.ukrsat.com/tutorial/openxs.php?n=82прим. пер.), Ми можемо побачити, що таблиці споживають приблизно однаковий обсяг фактичного простору (якщо відняти блоки в списках вільних блоків таблиці UNIFORM_TEST – 59224 блоків), але обсяг простору, необхідний для табличного простору з екстента уніфікованого розміру, значно большеc. Причина полягає в невиконанні обрізання екстентів. Це легко побачити на лістингу 6. Розмір кожного екстента таблиці UNIFORM_TEST дорівнює 100 MB.







Лістинг 5: використовуване простір.

SQL> exec show_space (“UNIFORM_TEST” );
Free Blocks………………………………………………..59,224
Total Blocks………………………………………………204,800
Total Bytes………………………………………….1,677,721,600
Total MBytes………………………………………………..1,600
Unused Blocks…………………………………………………..0
Unused Bytes……………………………………………………0
Last Used Ext FileId…………………………………………….6
Last Used Ext BlockId……………………………………………9
Last Used Block…………………………………………….12,800
PL/SQL procedure successfully completed.
SQL> exec show_space(“AUTOALLOCATE_TEST” );
Free Blocks……………………………………………………16
Total Blocks………………………………………………145,592
Total Bytes………………………………………….1,192,689,664
Total MBytes………………………………………………..1,137
Unused Blocks…………………………………………………..0
Unused Bytes……………………………………………………0
Last Used Ext FileId…………………………………………….8
Last Used Ext BlockId…………………………………………..41
Last Used Block…………………………………………………8
PL/SQL procedure successfully completed.






Лістинг 6: екстенти таблиці UNIFORM_TEST.

SQL> select segment_name, extent_id, blocks
2 from user_extents where segment_name = “UNIFORM_TEST”;
SEGMENT_NAME EXTENT_ID BLOCKS
——————————————–
UNIFORM_TEST 0 12800
UNIFORM_TEST 1 12800
UNIFORM_TEST 2 12800
UNIFORM_TEST 3 12800
UNIFORM_TEST 4 12800
UNIFORM_TEST 5 12800
UNIFORM_TEST 6 12800
UNIFORM_TEST 7 12800
UNIFORM_TEST 8 12800
UNIFORM_TEST 9 12800
UNIFORM_TEST 10 12800
UNIFORM_TEST 11 12800
UNIFORM_TEST 12 12800
UNIFORM_TEST 13 12800
<11p class=”bodycopy” >UNIFORM_TEST 14 12800

UNIFORM_TEST 15 12800
16 rows selected.

Видати список всіх 714 екстентів таблиці AUTOALLOCATE_TEST – порожня трата часу, так що давайте подивимося на них у сукупності, як це показано на лістингу 7. Це загалом відповідає тому, що зазвичай спостерігається при виділенні простору в локально керованих табличних просторах, створених з пропозицією AUTOALLOCATE. Екстенти розміром 8, 128 і 1024 блоків – “нормальні” екстенти; ми завжди спостерігаємо їх при роботі з екстента автоматично визначається розміру. Однак інші екстенти – ненормальні екстенти, зазвичай таких екстентів ми не спостерігаємо. Це відбувається через обрізання екстентів. Коли якийсь з серверів паралельного виконання завершив свою частину завантаження, він взяв свій останній екстент розміром 8 MB (1024 блоків) і обрізав його, що призвело до появи шматочка невитраченого простору. Одному з інших серверів паралельного виконання може знадобитися простір і він може використовувати цей вільний шматочок. У свою чергу, як тільки ці інші сеанси паралельного виконання завершать обробку своїх власних завантажень, вони обріжуть свої останні екстенти, залишаючи шматочки вільного простору.







Лістинг 7: екстенти таблиці AUTOALLOCATE_TEST.

SQL> select segment_name, blocks, count(*)
2 from user_extents
3 where segment_name = “AUTOALLOCATE_TEST”
4 group by segment_name, blocks
5 /
SEGMENT_NAME BLOCKS COUNT(*)
——————————————–
AUTOALLOCATE_TEST 8 128
AUTOALLOCATE_TEST 128 504
AUTOALLOCATE_TEST 240 1
AUTOALLOCATE_TEST 392 1
AUTOALLOCATE_TEST 512 1
AUTOALLOCATE_TEST 656 1
AUTOALLOCATE_TEST 752 5
AUTOALLOCATE_TEST 768 1
AUTOALLOCATE_TEST 1024 72
9 rows selected.

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

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

SQL> alter session enable
2 parallel dml;
Session altered.
SQL> insert /*+ append */
2 into UNIFORM_TEST
3 select * from big_table_et;
10000000 rows created.
SQL> insert /*+ append */
2 into AUTOALLOCATE_TEST
3 select * from big_table_et;
10000000 rows created.
SQL> commit;
Commit complete.

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







Лістинг 8: порівняння використання простору.

SQL> exec show_space( “UNIFORM_TEST” );
Free Blocks………………………………………………..118,463
Total Blocks……………………………………………….409,600
Total Bytes…………………………………………..3,355,443,200
Total MBytes…………………………………………………3,200
Unused Blocks……………………………………………………0
Unused Bytes…………………………………………………….0
Last Used Ext FileId……………………………………………..6
Last Used Ext BlockId……………………………………….281,609
Last Used Block……………………………………………..12,800
PL/SQL procedure successfully completed.
SQL> exec show_space( “AUTOALLOCATE_TEST” );
Free Blocks…………………………………………………….48
Total Blocks……………………………………………….291,184
Total Bytes…………………………………………..2,385,379,328
Total MBytes…………………………………………………2,274
Unused Blocks……………………………………………………0
Unused Bytes…………………………………………………….0
Last Used Ext FileId……………………………………………..8
Last Used Ext BlockId……………………………………….140,025
Last Used Block………………………………………………….8
PL/SQL procedure successfully completed.

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


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

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

Ваш отзыв

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

*

*