Oracle: быстрая вставка данных в таблицу

Уменьшение времени пакетной (для olap/dwh) вставки данных:
Отличительная особенность 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

Домашняя бухгалтерия на PHP 5, SQLite 3, ExtJs 4

Домашняя бухгалтерия на ExtJS 4, PHP 5, SQLite 3.
Демо - http://demobuh.skahin.ru
Пароль и логин: admin

Описание возможностей и онлайн использование: http://buh.skahin.ru

Разработка и исходный код: https://github.com/pihel/cash

ORACLE: Ускорение pl/sql циклов и пользовательских функций

Продолжаю тему оптимизации запросов ORACLE, хотелось бы коснуться циклов по SQL запросам в PLSQL.
Возможно, многим это уже известно, но все же.
Чаще всего запросы в plsql пишутся следующим образом:

PROCEDURE increase_salary (
   department_id_in   IN employees.department_id%TYPE,
   increase_pct_in    IN NUMBER)
IS
BEGIN
   FOR employee_rec
      IN (SELECT employee_id
            FROM employees
           WHERE department_id =
                    increase_salary.department_id_in)
   LOOP
      UPDATE employees emp
         SET emp.salary = emp.salary + 
             emp.salary * increase_salary.increase_pct_in
       WHERE emp.employee_id = employee_rec.employee_id;
   END LOOP;
END increase_salary;

У такого подхода есть существенный минус - для каждой записи из SELECT, ORACLE приходится менять контекст выполнения с SQL на PLSQL.
Такого рода операцию можно выполнить без смены контекста 2 способами:
* UPDATE без цикла - в простом варианте ДА, но не все циклы так просты.
* Использовать BULK COLLECT и FORALL
С BULK COLLECT и FORALL запрос получится следующий:

CREATE OR REPLACE PROCEDURE increase_salary (
  department_id_in   IN employees.department_id%TYPE,
  increase_pct_in    IN NUMBER)
IS
  TYPE employee_ids_t IS TABLE OF employees.employee_id%TYPE
          INDEX BY PLS_INTEGER; 
  l_employee_ids   employee_ids_t;
BEGIN
  SELECT employee_id
     BULK COLLECT INTO l_employee_ids
    FROM employees
   WHERE department_id = increase_salary.department_id_in;

  FORALL indx IN 1 .. l_employee_ids.COUNT
     UPDATE employees emp
        SET emp.salary =
                 emp.salary
               + emp.salary * increase_salary.increase_pct_in
      WHERE emp.employee_id = l_employee_ids (indx);
END increase_salary;

Разберем код:
* Создаем Тип "employee_ids_t" - это ассоциативный массив, где ключ PLS_INTEGER, а значение = employees.employee_id%TYPE
* l_employee_ids - это переменная типа employee_ids_t
* BULK COLLECT INTO - поместить отобранные select идентификаторы в коллекцию l_employee_ids. Помещаются все записи разом, в отличии от обычного INTO (одна запись)
* Конструкция FORALL - выполняет UPDATE столько раз, сколько записей в коллекции l_employee_ids и используя данные из нее.

Стоит заметить, что FORALL это не цикл, а конструкция языка, так что он имеет ряд особенностей:
* Внутри FORALL может быть только 1 DML запрос. Если нужно несколько запросов, то нужно использовать несколько FORALL
* При выполнении FORALL не происходит переключения контекста. Весь UPDATE выполняется за 1 раз, что дает значительное преимущество в скорости.

Стоит заметить, что время на смену контекста также расходуется при вызове пользовательских функций в DML запросах.
К примеру:

FUNCTION betwnstr (
   string_in      IN   VARCHAR2
 , start_in       IN   INTEGER
 , end_in         IN   INTEGER
)
   RETURN VARCHAR2 
 
....

SELECT betwnstr (last_name, 2, 6) 
  FROM employees
 WHERE department_id = 10

* Контекст будет сменен столько раз, сколько записей отберется в SELECT
Чтобы избежать смены контекста, можно :
* Попробовать объявить функцию как INLINE "PRAGMA INLINE "
* Или как используемую только в DML "PRAGMA UDF"
* Но лучший вариант в данном случае - отказаться от функции и сознательно денормализировать запрос до чистого SQL.

На основе http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html