Загружаем большие объемы данных в oracle

При создании адекватных тестовых данных принято использовать интерфейсы самого приложения, его api, формы, библиотеки и т.д... Но когда необходимо создавать большие массивы тестовых данных, стандартные интерфейсы могут оказаться черезчур медлительными.

Например , вы хотите заполнить биллинговую БД десятью миллионами клиентов, которые тянут за собой другие десятки миллионов сопутствующих записей в разных таблицах схемы (речь в данном посте идет именно о реляционных субд).

Каждая реляционная субд имеет свои механизмы дампа, заливки, импорта , экспорта... Даже простецкий sqlite имеет свой  .dump.

Я бы хотел поделиться некоторыми аспектами создания больших объемов тестовых данных в субд Oracle.

Существует несколько вариантов:

Самый неудачный вариант - с наскока написать скрипт, который бы генерировал данные с помощью обычного DML. Подобное решение будет работать долго и потреблять слишком много ресурсов субд, связанных с ведением redo-логов, распуханием сегмента отката, фиксацией изменений (commit).

Другой вариант - использовать штатные оракловые утилиты бекапа и восстановления : imp , exp , которые очень шустро работают. Но этот вариант применим в ситуации, когда у вас уже имеется эталонная схема с нужным количеством данных - вы просто экспортируете (в бинарный файл) из нее все или часть данных и импортируете в нужную схему из полученного бинарного дампа. Например, таким образом, можно слить данные с боевой БД заказчика, разумеется, если он дает на это добро. К тому же использовать этот метод рекомендуется для работы с БД до 50 - 60 GB.

Ну и наиболее подходящий вариант - использование SQL*Loader.
Метод относительно быстр , гибок в настройке хоть и имеет свои особенности. Загрузка осуществляется оракловой утилитой sqlldr.
Ей на вход необходимо передать обязательно: управляющий файл и файл с данными, и необязательно  - файл пераметров.

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

Файл с данными - файл, сформированный внешней программой/скриптом и который содержит данные, которые надо загрузить. Формат файла может быть как текстовый так и бинарный. Как именно интерпретировать записи этого файла - указывается в управляющем файле. Если файл текстовый, то разделители записей, признаки конца записи (для мультистрочных записей) - все это также регулируется в управляющем файле.

Файл с параметрами - текстовый файл , в котором указываются параметры командной строки. Например sqlldr PARFILE=parameters.par 

Что же делает imp , sqlldr быстрыми? В первую очередь то, что они умеют использовать такую возможность Oracle, как метод "прямой вставки".

Прямая вставка - это почти тот же insert, который быстрее обычного за счет того , что при его использовании не тратится время на коммиты, redo-логи, большинство ограничений (кроме unique, primary key и not null) и insert-триггеры.
Чтобы sqlldr использовал метод прямой вставки, ему необходимо передать в командной строке или файле параметров опцию DIRECT=Y.
Кроме того, прямую вставку можно еще и распараллелить.
К слову, такой метод вставки можно использовать и самостоятельно.

Вот пример параллельного (в  2 процесса)  и нежурналируемого копирования данных из одной таблицы в другую.

ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+parallel(new_users,2)*/ INTO users NOLOGGING
SELECT * FROM old_users

Наибольший выигрыш от распараллеливания  загрузки достигается на тех серверах, где более одного CPU.

Ну и напоследок несколько советов о том, как сделать процесс загрузки более оптимальным:

1. подготовьте место да диске, где будут лежать dbf-ы новой базы, достаточное для будущего объема данных (посмотрите сколько занимают dbf-ы старой =)
2. отключите индексы в таблицах , которые собираетесь заполнять
3. используйте прямую и по возможности параллельную вставку
4. отключите архиватор, если включен (напарывался на ситуацию, когда архиватор отжирал место почти равное тому, что занимала новая база - это совершенно ненужное при генерации тестовых данных архивирование!):

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE OPEN;


Если же архиватор все таки не был отключен и отожрал место на диске архивными dbf-ами, то их можно легко удалить.
Для этого в папке с архивными dbf-ами удалить все ненужные архивы и после этого с помощью rman избавиться от соответствующих записей в БД:

rman target sys@dbsid

crosscheck archivelog all;

delete noprompt expired archivelog all;

Хорошую подборка проверенных советов по оптимизации именно SQL*Loader можно найти здесь

На полноту охвата темы не претендую, но , надеюсь, у того кто с описанными вкратце механизмами не знаком , появится вектор, куда копать в случае необходимости :)