Отличительная особенность olap: вставка одна, но очень большая.
1. Делаем таблицу не логируемой.
Что уменьшит затраты на вставку в redo log.
ALTER TABLE T NOLOGGING* Может не сработать, если в базе включено FORCE_LOGGING = YES
2. Добавляем /*+ append */ в insert операцию
* Данные добавляются в конец таблицы, вместо попытки поиска пустых мест.
* Данные пишутся напрямую в data файлы, минуя буферный кэш.
Стоит заметить один нюанс при вставке с хинтом append из разных сессий в одну таблицу. Так делать нельзя, т.к. direct path вставка блокирует все остальные сессий к этой таблице: http://docs.oracle.com/cd/B19306_01/server.102/b14231/tables.htm#sthref2260 . Только одна сессия может одновременно осуществлять direct path вставку в одну таблицу. Т.к. чтобы обойти буферный кэш, сначала нужно скинуть все грязные данные из кэша на диск.
3. Отключаем constraint, trigger на таблице и явно вставляем значения в default колонки.
Замечу, что если надо ускорить вставку, то надо отключать FK на самой таблице, а если удаление, то FK на других таблицах, которые указывают на нашу.
4. Распараллеливаем запрос хинтом /*+ PARALLEL (8) */
Не забываем включать параллельность для DML, чтобы параллелился и insert, а не только select.
ALTER SESSION ENABLE PARALLEL DML;
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 придется пожертвовать и оставить.
ALTER TABLE T DROP CONSTRAINT PK CASCADE
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. остальные способы из пакетной вставки, если они применимы в текущей ситуации
Если знаете еще способы ускорить insert - пишите в комментариях.
В продолжении: быстрая вставка данных в партиционированные таблицы http://blog.skahin.ru/2015/06/oracle.html
Комментариев нет:
Отправить комментарий