Том Кайт: о загрузке данных и экстентахИсточник: oracle Том Кайт
Наш эксперт рассказывает о загрузке данных в режиме прямого доступа и оптимизирует использование экстентов. Вопрос. Мы загружаем в базу данных массу файлов (свыше 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. (Показан сокращенный оператор - чтобы уменьшить пример, я вычеркнул повторяющиеся элементы.)
Все, что мы теперь должны сделать - немного отредактировать этот оператор: изменить, возможно, имена каталогов и т.п.: SQL> create or replace directory 2 my_dir as '/tmp/' 3 / Directory 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.
Обратите внимание, при загрузке таблиц UNIFORM_TEST и AUTOALLOCATE_TEST мы просто указываем предложение PARALLEL, а степень параллелизма выбирает сам сервер Oracle Database. В данном случае я на машине - единственный пользователь (доступны все ресурсы), и сервер Oracle Database по умолчанию использует восемь серверов параллельного выполнения - это количество зависит от числа центральных процессоров (четыре) и значения параметра PARALLEL_THREADS_PER_CPU (по умолчанию в нем установлена двойка). Столбцы SID и SERIAL# - идентификаторы сеансов параллельного выполнения, а столбцы QCSID и QCSERIAL# - идентификаторы координатора параллельного выполнения запроса. Итак, имея восемь серверов параллельного выполнения, нам хочется узнать, как было использовано пространство. Это можно сделать быстро, выполнив показанный на листинге 4 запрос к представлению USER_SEGMENTS.
У нас используются блоки размером 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.
Выдать список всех 714 экстентов таблицы AUTOALLOCATE_TEST - пустая трата времени, так что давайте посмотрим на них в совокупности, как это показано на листинге 7. Это в общем соответствует тому, что обычно наблюдается при выделении пространства в локально управляемых табличных пространствах, созданных с предложением AUTOALLOCATE. Экстенты размером 8, 128 и 1 024 блоков - "нормальные" экстенты; мы всегда наблюдаем их при работе с экстентами автоматически определяемого размера. Однако остальные экстенты - ненормальные экстенты, обычно таких экстентов мы не наблюдаем. Это происходит из-за обрезания экстентов. Когда какой-то из серверов параллельного выполнения завершил свою часть загрузки, он взял свой последний экстент размером 8 MB (1 024 блоков) и обрезал его, что привело к появлению кусочка неизрасходованного пространства. Одному из других серверов параллельного выполнения может потребоваться пространство и он может использовать этот свободный кусочек. В свою очередь, как только эти другие сеансы параллельного выполнения завершат обработку своих собственных загрузок, они обрежут свои последние экстенты, оставляя кусочки свободного пространства.
Итак, какой подход вы должны использовать? Если ваша задача - выполнять параллельную прямую загрузку настолько часто, насколько возможно, я предлагаю использовать режим автоматического определения размера экстентов. Параллельные прямые операции, такие как эта, не используют пространство перед маркером максимального заполнения сегмента (пространство в списках свободных блоков). Значит, если у вас к тому же отсутствуют обыкновенные вставки в эти таблицы, то в экстентах унифицированного размера постоянно будет содержаться дополнительное свободное пространство, которое никогда не будет использовано. Если только вы не установите очень маленький унифицированный размер экстентов, вы будете наблюдать снижение эффективности использования пространства, также помните - это потерянное пространство присоединено к сегменту и оно будет просматриваться при полном просмотре таблицы. Чтобы продемонстрировать это, давайте еще один раз выполним параллельную прямую загрузку данных в наши уже существующие таблицы, используя те же входные данные: 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 все больше и больше данных, используя параллельные прямые операции, использование пространства со временем становится все более и более плохим. Нам нужно или же существенно уменьшить унифицированный размер экстентов или же использовать режим автоматического определения размера экстентов. В этом режиме со временем может создаваться все больше и больше экстентов, но использование пространства, благодаря обрезанию экстентов, будет более лучшим.
|