Наш эксперт рассказывает о загрузке данных в режиме прямого доступа и оптимизирует использование экстентов.
Вопрос. Мы загружаем в базу данных массу файлов (свыше 49 000), используя утилиту 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 и для создания таблицы используете четыре сервера параллельного выполнения, вы будете иметь по крайней мере четыре экстента (может быть даже больше). Когда какой-то сервер параллельного выполнения полнстью заполняет свой экстент, он создает новый собственный экстент.
Все это кажется вполне нормальным, но при загрузке больших объемов данных в хранилищах данных это может привести к потере пространства. Предположим, вы хотите загрузить 1 010 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 ...
В этом файле находится 10 000 000 записей. Первоначально скриптом 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, которую создал Том Кайт, - http://oracle.ukrsat.com/tutorial/openxs.php?n=82 - прим. пер.), мы можем увидеть, что таблицы потребляют примерно одинаковый объем фактического пространства (если вычесть блоки в списках свободных блоков таблицы UNIFORM_TEST - 59 224 блоков), но объем пространства, необходимый для табличного пространства с экстентам унифицированного размера, значительно больше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 |
Выдать список всех 714 экстентов таблицы AUTOALLOCATE_TEST - пустая трата времени, так что давайте посмотрим на них в совокупности, как это показано на листинге 7. Это в общем соответствует тому, что обычно наблюдается при выделении пространства в локально управляемых табличных пространствах, созданных с предложением AUTOALLOCATE. Экстенты размером 8, 128 и 1 024 блоков - "нормальные" экстенты; мы всегда наблюдаем их при работе с экстентами автоматически определяемого размера. Однако остальные экстенты - ненормальные экстенты, обычно таких экстентов мы не наблюдаем. Это происходит из-за обрезания экстентов. Когда какой-то из серверов параллельного выполнения завершил свою часть загрузки, он взял свой последний экстент размером 8 MB (1 024 блоков) и обрезал его, что привело к появлению кусочка неизрасходованного пространства. Одному из других серверов параллельного выполнения может потребоваться пространство и он может использовать этот свободный кусочек. В свою очередь, как только эти другие сеансы параллельного выполнения завершат обработку своих собственных загрузок, они обрежут свои последние экстенты, оставляя кусочки свободного пространства.
|
ЛИСТИНГ 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. |