Как ускорить insert oracle

Обновлено: 02.07.2024

День жестянщика

Начнем с разбора ошибок, обычно допускаемых новичками, впервые сталкивающимися с Oracle. Обработка данных мониторинга может предусматривать достаточно сложную логику, но об этом мы будем говорить позже. Пока просто попробуем сохранить полученные данные в следующую таблицу:

Автоматизируем генерацию ID триггером, как это советуют многие online-источники:

И начнем кодить:

Запустив на выполнение этот код, получаем количество вставок, выполняемых в секунду:

Вроде бы неплохо. Но явно меньше 9000. Что мы делаем не так?

Довольно многое (пункты даны в порядке убывания влияния на производительность):

Мы провоцируем hard parse, передавая каждый раз новый вариант запроса (у такого подхода есть и другие недостатки, такие как возможность получения sql injection или заполнение библиотечного кэша, но нам хватит и первой причины, чтобы больше никогда так не делать)
Создавая сессию, JDBC по умолчанию устанавливает включенной настройку auto commit (ниже мы посмотрим, к чему это приводит с точки зрения производительности)
Вызов триггера приводит к переключению контекстов SQL и PL/SQL (это не очень существенно при вставке одиночных записей, но весьма заметно при выполнении массовых вставок)
При массовой вставке, sequence имеет смысл кэшировать (но это не наш случай, поскольку мы вставляем по одной записи из одного потока)

Хочу обратить внимание, что теперь текст запроса заканчивается точкой с запятой. По ней Oracle определяет, что мы собираемся выполнять не SQL, а PL/SQL-код.

Запускаем на выполнение:

Лучше, но все равно мало.

Быстрее, выше, сильнее!

Что можно сделать чтобы ускорить вставку? Для начала стоит отключить auto commit. При работе с Oracle, слишком частая фиксация транзакций это не только накладные расходы, но и (в некоторых случаях) хороший шанс спровоцировать ошибку ORA-01555.

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

Какого размера должна быть транзакция при массовой вставке данных? Универсального ответа, разумеется, нет. До определенного предела, действует правило — чем больше размер транзакции, тем быстрее сохраняются данные. В этой статье я не буду экспериментировать с размером транзакций, а просто выполню вставку всех строк в одной транзакции.

Результат улучшился почти в два раза:

Чтобы улучшить его еще больше, можно вспомнить о временных таблицах:

Поскольку мы убрали из таблицы столбец id, внесем изменения в реализацию пакета:

Так как вставка данных во временную таблицу не фиксируется в REDO-логе (не требуется восстанавливать эти данные при сбое), это ведет к закономерному снижению накладных расходов:

Если использовать DML-запросы вместо обращения к пакету, можно довести это значение до 2000. Неужели это предел?

Разумеется нет! Сейчас, когда мы практически свели на нет накладные расходы связанные с фиксацией транзакций, мы упираемся в другое бутылочное горлышко — в количество запросов, передаваемых по сети. Чтобы решить эту проблему, были разработаны bulk-запросы, позволяющие передавать несколько однотипных изменений в одном запросе.

Вносим необходимые изменения в код:

Запускаем на выполнение:

И не замечаем никакой разницы. Почему?

Дело в том, что bulk-и работают только для DML-запросов (insert, update, delete). Если мы пытаемся вызывать bulk-запросом хранимую процедуру, JDBC эмулирует bulk, отсылая по сети ровно то-же самое количество запросов, что было бы без него.

Исправим этот досадный промах:

И запустим код на выполнение снова:

Ха, мы определенно идем на рекорд! Доведя BULK_SIZE до 100, можно получить и вовсе волшебное значение:

Итак, мы научились очень быстро вставлять данные во временную таблицу и… терять их при commit-е, так ничего с ними и не сделав.

Где логика?

Сейчас добавим. Для начала спроектируем схему данных:

Пусть у нас имеется список устройств test_device и список параметров test_parameter. Текущее значение каждого параметра будет храниться в test_state, а в test_history будем записывать хронологию изменения значений. Для упрощения кода, будем считать что параметры не могут принимать значение null.

Кроме того, мы будем обрабатывать значения двух типов, заданных в test_parameter_type. Тип параметра будет определять, в каком случае выполняется добавление записи в test_history:

Тип 'default' будем использовать (в частности) для хранения оперативного состояния устройств, запись в test_history добавляется при любом изменении значения
Тип 'uptime' используем для хранения времени безостановочной работы, значение в test_history добавляется из test_state в том случае, если новое значение меньше предыдущего (то есть устройство перезагружалось)

Далее, добавим в пакет процедуру массовой обработки значений:

Можно заметить, что запросы получились не простые, но сложные запросы это именно то, что Oracle умеет выполнять лучше всего.

Внесем изменения в код:

и запустим его на выполнение:

Увы, чуда не произошло. Необходимость сохранения данных в журналируемых таблицах сыграла свою роль. Но 5000 записей в секунду, в любом случае, гораздо лучше 600. Теперь мы знаем максимальную скорость, с которой мы можем сохранять данные в Oracle (естественно на выбранном для тестов сервере).

Если нам требуется более высокая скорость обработки или меньшее время обработки каждого запроса, имеет смысл смотреть с сторону InMemory DB. Но это тема для другого разговора.

Готовы ли вы потерять свои данные?

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

Использование InMemory или NoSQL DB не изменит эту картину радикально. Хотя эти СУБД предусматривают защиту данных (например при помощи REDO-лога в случае Oracle TimesTen), добиться максимальной производительности можно только отключив эту защиту (такая возможность предоставляется очень часто).

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

Уменьшение времени пакетной (для olap/dwh) вставки данных:
Отличительная особенность olap: вставка одна, но очень большая.

1. Делаем таблицу не логируемой.
Что уменьшит затраты на вставку в redo log.
* Может не сработать, если в базе включено FORCE_LOGGING = YES

2. Добавляем /*+ append */ в insert операцию
* Данные добавляются в конец таблицы, вместо попытки поиска пустых мест.
* Данные пишутся напрямую в data файлы, минуя буферный кэш.

3. Отключаем constraint, trigger на таблице и явно вставляем значения в default колонки.
Замечу, что если надо ускорить вставку, то надо отключать FK на самой таблице, а если удаление, то FK на других таблицах, которые указывают на нашу.

4. Распараллеливаем запрос хинтом /*+ PARALLEL (8) */
Не забываем включать параллельность для DML, чтобы параллелился и insert, а не только select.

5. Если распаралеллить вставку нельзя, к примеру из-за доступа по dblink.
Можно физически распаралелить вставку через несколько одновременных вставок кусками части данных из источника.
Сделать это можно через dbms_parallel.
Очень хорошо подходит для одновременного копирования нескольких таблиц или если таблица партиционирована.
При вставке в одну таблицу незабываем про ограничения хинта append из п.2

6. Удаляем index и foreign key с внешних таблиц.
Пришлось именно удалять, т.к.
* DISABLE можно делать только у функциональных индексов
* UNUSABLE можно сделать на всех индексах, но DML запросы все равно будут валиться на UNIQUE index
http://docs.oracle.com/cd/B13789_01/server.101/b10755/initparams197.htm
Ничего страшного в этом нет, восстановление индексов заняло 5 минут по 10 млн записей, что все равно лучше 4 часов вставки.
Удаляем все, включая Prmary Key. Но тут не забываем, что каскадно удалятся и все FK. Их надо будет потом восстановить, ну или PK придется пожертвовать и оставить.
7. Делаем кэшируемым Sequence.
Если в insert используется sequence, то делаем его кэшируемым.
С "CACHE 50000" мне удалось сократить время вставки 10 млн записей с 50 минут до 5. Это в 10 раз!
При кэширумом sequence последовательность заранее подготавливает числа и хранит в памяти, а это значит, что накладных расходов обмена становится меньше.

8. IOT таблица
Если на таблице один индекс, который покрывает большую часть столбцов, то ее можно конвертировать в IOT таблицу. Так мы уменьшаем число обслуживаемых объектов до 1. Что уменьшает число буферных чтений с 3 (2 чтения индекса + 1 чтения таблицы) при любых DML/select до 2 (2 чтения индекса).

Уменьшение времени распределенной/многопользовательской (oltp) вставки данных:
отличительной особенности вставок в oltp является то, что их очень много, каждая из них создает микроскопическую нагрузку, но все вместе могут создать большое кол-во событий ожиданий (busy wait). Рассмотрим отдельно как обойти эти ожидания:

1. увеличение числа списка свободных блоков (free_list при создании таблицы)
+ уменьшение конкуренции за поиск свободных блоков за счет распараллеливания вставки
- раздувание таблицы, т.к. когда заканчивается free_list1, то он не будет использовать свободные блоки из free_list2, а выделит новые поверх HWM
- увеличивает фактор кластеризации индексов, т.к. данные физически раскидываются по разным местам таблицы, а не идут последовательно

2. сделать индекс реверсивным, если нет возможности отключить при вставке
+ уменьшение конкуренции за вставку данных в индекс, т.к. последовательные реверсивные данные будут использовать разные блоки индекса
- увеличение фактора кластеризации из-за разброса данных
- нельзя будет использовать range scan (сканирование по диапазону) индекса, т.к. в индексе уже не сами данные, а их инвертированные значения
Стоит заметить о факторе класетризации: чаще всего в oltp системе он не очень важен, т.к. доступ к данным идет по конкретному значению к одному конкретному блоку. Т.е. здесь нет скачков по разным блокам, как при сканировании по диапазону.

3. использование хинта append_values
+ запись данных не будет использовать free_list, а будет просто писаться поверх HWM
- разрастание таблицы

4. секционирование таблицы, таким образом, чтобы параллельные вставки шли в физически разные секции таблицы.
Т.е. секционирование по первичному ключу или по дате не подходит, нужно по какомуто столбцу, которые присутствует во всех вставках ежедневно и имеет одинаковый разброс.

5. Выполнение вставки используя prepared statement
что позволит исключить парсинг SQL перед его выполнением.

6. Вставка строк блоками (executeBatch)
Что позволит снизить задержки на network lookup - время на установку соединения и передачу данных по сети.

7. 7п. из пакетной вставки - кэшируемый индекс

8. остальные способы из пакетной вставки, если они применимы в текущей ситуации

Я бы хотел знать, как лучше всего выполнить следующие вставки.

У меня есть таблицы t1 , t2 , t3 и x1 , x2 и x3 :
В таблице t1 - примерно 400000 строк
В таблице t2 - примерно 1000000 строк
В таблице t3 - примерно 200000 строк

Таблицы x1 , x2 , x3 первоначально пусты - данные добавляются в них каждый месяц. Сейчас для добавления данных я использую 3 вложенных цикла FOR . Основным является внешний цикл, в котором выбираются все записи из таблицы t1 и вставляются в x1 . Первичный ключ таблицы x1 берется из последовательности с помощью триггера, срабатывающего перед вставкой. Значение последовательности возвращается в переменную с помощью конструкции returning into .

Следующий вложенный цикл выбирает все записи из таблицы t2 , у которых значение в столбце id соответствует значению id (первичному ключу) из x1 , и вставляет их в таблицу x2 (снова генерируя первичный ключ в триггере и возвращая его в переменную). Значение последовательности из предыдущего цикла вставляется в x2 , чтобы обеспечить связь между x1 и x2 .

Последний цикл во многом аналогичен - в таблицу x3 вставляются все записи из t3 , у которых значение в столбце id соответствует значению id (первичному ключу) из x2 , а значение последовательности из предыдущего цикла вставляется в x3 , создавая связь между x2 и x3 .

Итак, используется следующий код:

Не будет ли лучше использовать массивы и множественные вставки?

Ответ Тома Кайта

Нет ли способа получше.

Верите или нет, но лучше всего будет использовать просто три оператора insert . Вы получите потрясающее ускорение. И ресурсов потребуется намного меньше. Я создам три таблицы t1 , t2 , t3 следующим образом:

Затем я содаю ваши таблицы x1 , x2 , x3 :

Я создал таблицы x1 и x1a , x2 , x2a и т.д. - таблицы x1a , x2a . отличаются наличием индексов, что позволяет продемонстрировать мой подход к решению этой задачи.

По таблицам x1 , x2 , x3 я создаю триггеры:

Хотя использование триггеров и является самым худшим способом. Вот как я бы загружал данные в таблицы x1 , x2 , x3 :

Подобный оператор alter sequence необходимо выполнять перед интенсивным использованием последовательности, - вас удивит, НАСКОЛЬКО это повысит производительность.

Вот и весь процесс загрузки -- эти операторы делают все необходимое. Теперь ваш способ:

Мой вариант работает почти в 20 раз быстрее (причем приходится поддерживать два дополнительных индекса!). Но, что еще важнее:

Я просто показываю, что по некоторым показателям разница огромна. Посмотрите на значение REDO SIZE , посмотрите, насколько меньше требуется внутренних блокировок (показатели LATCH)! Это принципиальное отличие.

Просто используйте 3 оператора insert , и все. Когда приходится писать процедурный код, остановитесь и задайте себе вопрос: "Зачем я это делаю?"

Вопросы читателя от 13 июня 2003 года

У меня возникли два вопроса относительно твоих операторов insert :

Вопрос 1: Зачем нужна конструкция " x1.newid > &S " в конструкции where при вставке в таблицу x2a ?

Вопрос 2: Почему конструкция where " x2.oldid = t3.fk_t2; " отсутствует при вставке в таблицу x3a ? Аналогичное условие, " x1.oldid = t2.fk_t1; ", указано при вставке в таблицу x2a ?

Ответ Тома Кайта

Вопрос 1: Я предположил, что эта таблица X1 накапливается со временем и, поскольку таблица X1 будет содержать данные за последний месяц, а также данные за текущий месяц, значение x1.oldid будет дублироваться.

Добавление условия по x1.newid ограничивает просмотр таблицы X1 только вновь добавленными данными.

Вопрос 2: Вы очень внимательны.

Это была ошибка копирования (неверно выбрана граница блока :). Правильно будет так:

Обратите внимание, что в исходном тексте нет ни ' ; ', ни ' / ' -- я потерял последнюю строку.

Прекрасный пример

Ты используешь в своем примере непосредственную вставку ?

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

Ответ Тома Кайта

Да, да и еще раз - да.

Комментарий читателя от 15 июня 2003 года

Кажется, я чего-то не понимаю! Если это действие необходимо выполнить в пакете, что придется изменить в твоем примере - я не понимаю, что это значит &S в конструкции where ?

Ответ Тома Кайта

&S - значение последовательности перед началом множественной вставки. В PL/SQL-процедуре это может выглядеть так:

Я пишу преобразование данных в PL/SQL, который обрабатывает данные и загружает их в таблицу. Согласно Профилировщику PL/SQL, одной из самых медленных частей преобразования является фактическая вставка в целевую таблицу. Таблица имеет один индекс.

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

insert into mytable values r_myRow;

Кажется, что я могу получить производительность делаем следующее:

выключите вход во время вставки
вставить несколько записей одновременно

являются ли эти методы целесообразными? Если да, то каков синтаксис?

гораздо лучше вставлять несколько сотен строк за раз, используя таблицы PL/SQL и FORALL для привязки к инструкции insert. Подробнее об этом см. здесь.

также будьте осторожны с тем, как вы строите таблицы PL/SQL. Если это вообще возможно, предпочитайте вместо этого делать все ваши преобразования непосредственно в SQL, используя " INSERT INTO T1 SELECT . "поскольку выполнение операций по строкам в PL / SQL по-прежнему будет медленнее, чем SQL.

в любом случае вы также можете использовать direct-path вставки с помощью INSERT /*+APPEND*/ , который в основном обходит кэш БД и непосредственно выделяет и записывает новые блоки в файлы данных. Это также может сократить объемы рубок, в зависимости от того, как вы его используете. Это также имеет некоторые последствия, поэтому, пожалуйста, прочитайте тонкой ручной первый.

наконец, если вы усекаете и перестраиваете таблицу, возможно, стоит сначала удалить (или пометить непригодные), а затем перестроить индексы.

Если падение индекса не ускоряет вещи достаточно, вам нужно Oracle SQL * Loader:

Предположим, вы взяли eid, ename,sal, job. Поэтому сначала создайте таблицу как:

теперь введите данные:-

отбросьте индекс, затем вставьте строки, затем повторно создайте индекс.

основные моменты, котор нужно рассматривать для вашего если бы добавить подсказку, как это непосредственно добавлять в таблицу вместо использования freelist. Если вы можете позволить себе отключить ведение журнала, чем использовать append с подсказкой nologging, чтобы сделать это
используйте массовую вставку вместо итерации в PL/SQL
используйте sqlloaded для загрузки данных непосредственно в таблицу, если вы получаем данные из файла feed

возможно, один из ваших лучших вариантов-избегать Oracle как можно больше на самом деле. Я сам был озадачен этим, но очень часто процесс Java может превзойти многие утилиты Oracle, которые либо используют OCI (read: SQL Plus), либо займут так много вашего времени, чтобы получить право (read: SQL*Loader).

Это не мешает вам использовать определенные подсказки (например,/добавить/).

Я был приятно удивлен каждый раз, когда я обратился к этому виду решение.

вот мои рекомендации по быстрой вставки.

Trigger-отключить все триггеры, связанные с таблицей. Включить после завершения вставки.

Index-Drop Index и повторно создайте его после завершения вставки.

Stale stats-повторно проанализируйте статистику таблиц и индексов.

индекс де-фрагментации-при необходимости перестроить индекс Не использовать журнал-вставить с помощью INSERT APPEND (только Oracle). Этот подход очень рискованный подход, нет журналов повтора поэтому вы не можете выполнить откат-сделайте резервную копию таблицы перед запуском и не пытайтесь использовать живые таблицы. Проверьте, имеет ли ваша БД аналогичную опцию

параллельная вставка: запуск параллельной вставки ускорит работу.

Использовать Массовую Вставку Ограничения-не так много накладных расходов во время вставок, но все же хорошая идея проверить, если он все еще медленный даже после шага 1

Репутация: 30
Cтаршой

есть у меня процедура и в ней такие строки

Задачка - как мне так результат селекта(2 млн строк) инсертить в физ. таблицу максимально быстро?

Нашел функцию FORALL - на сайте оракла пишут, что ускоряет работу с DML. как мне переписать свою процедуру под FORALL

Репутация: 18
Дух

Разница между процедурой и insert into from select в твоём случае заключается в том что процедура коммитила каждые 1000 записей. Соответственно процедура будет работать дольше, т.к. коммит достаточно трудоемкая операция.
про FORALL вот тут не плохо расписано.

ЗЫ: если нужно быстро и не обязательно коммитить, я бы добавил хинт /*+ append */, т.е. примерно вот так: insert /*+ append */ into test_table select * from source_table;

Репутация: 276
Старожил

Разница между процедурой и insert into from select в твоём случае заключается в том что процедура коммитила каждые 1000 записей.

Стоит еще добавить, что курсоры - штука не очень-то и быстрая.

Репутация: 30
Cтаршой

про /*+ append*/ - да, у меня так сработало за 980 секунд. это оч. долго

Репутация: 18
Дух

про /*+ append*/ - да, у меня так сработало за 980 секунд. это оч. долго

Репутация: 30
Cтаршой

с FORALL не так просто, я вот и спрашиваю.
В примерах - операция над одной таблицей. у меня же select запрос из нескольких заджойненных таблиц, и инсёрт в третью..

такой код оперирует с таблицей dept.

моя же процедура:

если можно, скажите как мне переделать своё под FORALL

>а сколько работало без него и процедура?
сейчас работает пока, как закончит напишу.

Репутация: 22
Дух

1) Для insert . select не нужен for all
2) Коммит в цикле ох как нечасто нужен на самом деле. В книге Кайта это расписано.
3) Если Вас не устраивает скорость запроса, выложите в этой теме его план и полный текст запроса

Репутация: 30
Cтаршой

Скорость запроса меня устраивает. около 15 секунд - возврат всех 2 млн. строк.

Долго происходит сам процесс инсёрта, из выборки в таблицу.

Репутация: 22
Дух

Ясно. Если у Вас Oracle Enterprise Edition и устроит чтобы этот запрос хорошо нагрузил сервер(это может быть плохо когда сервером пользуются другие пользователи), то можно посмотреть в сторону параллельного выполнения

Apr 25 2009, 12:46

Репутация: 47
Активист

во-первых: зачем нужен здесь DML ? ведь можно обойтись чистым SQL (insert . select . )
во-вторых: если нужен всеже DML (ну там в цикле происходят какието сложные заморочки), то можно поступить следущим образом, а именно создать несколько переменных аля массивов (в оракле они называются бинари табле и соотв. тип) - для каждого поля из запроса отдельный массив и тогда реально можно воспользоваться FORALL в инсерте и получить груповой инсерт - это и правда ускорит сам инсерт, но вот сколько памяти отожрут 2 мил. строк - тут возможно нужно будет делать опять как и делалось цикл со счетчиком и выгржать масивы ну скжем по 10000 строк + очищать потом их
С коммитом тут сложнее: буфер отката тоже может отожрать много памяти поэтому можно поиграться как будет лучше если после групового инсерта комитить его или один коммит в конце .

если остались непонятки пишите уточню .

Репутация: 30
Cтаршой

может я шибся, но по моему insert update delete - это dml - data manipulating language.. (ну это я так..)

Читайте также: