среда, 23 сентября 2015 г.

ORACLE: основы стоимостной оптимизации

На основе книги "ORACLE - основы стоимостной оптимизации" - автор Дж. Льюис.

Общие понятия


Селективность = (макс значение - мин значение отобранных данных)/(макс значение - мин значение всех данных)
в общем случае = отобранный интервал / весь интервал
Весь интервал определяется на основе статистики таблицы, это столбец destiny - разряженность: обратный показатель от кол-ва уникальных значений = 1/num_distinct.
Если на столбце есть гистограмма, то рассчитывается на основании ее, как сумма квадратов частоты нечастно встречающихся значения / (кол-во не пустых строк * кол-во не пустых нечастых строк)
т.е. на таблице с 2000 строк, где 1000 строк имеют одно значение, то без гистограммы destiny = 1/1001
с гистограммой = 1*1 / (2000 * 1) = 1/2000 в итоге получается, что запрос по гистограмме получится более селективным = отбираемый интервал * destiny, что справедливо, т.к. кроме уникальных значение будут использоваться гранулы гистограммы.

Итоговая селективность = селктивность * доля NULL полей

Особенности расчета:
* при расчете селективности при выходе за high_value (верхнее или нижне значение) - селективность линейно падает, т.е. не сразу обнуляется.
отсюда проблема : в динамичной oltp бд следуюем смещать high_value заранее перед началом нового дня, т.к. статистика собирается вконце дня, а запросы выше high_value уже идут
* когда селективность падает меньше числа строк таблицы, то селектиновсть = 1 / число строк таблицы

Селективности логических операций:
* and = селективность 1 * селективность 2
* or = селективность 1 + селективность 2 - (селективность 1 * селективность 2 )
* not = 1 - селективность 1

Кардинальность ( число отобранных записей ) = селективность * общее число записей

Стоимость = необходимое кол-во операций выраженное в стандартных единицах

пятница, 19 июня 2015 г.

ORACLE: Оптимизация работы секционированных таблиц

1. Важность указания STORAGE INITIAL - размера партиции по умолчанию.

Начиная с Oracle 11 размер любой партиции, представленной на диске, по умолчанию равен 8МБ, даже если она не имеет в себе никаких данных (До 11 версии размер по умолчанию был 65КБ).
Отсюда важность указания STORAGE INITIAL при создании партиционированной таблицы.

Рассмотрим на примере. Таблица из 80 млн записей партиционирована RANGE-INTERVAL по месяцу и субпартиционирована LIST по региону. Месячные интервалы с 2013 года - это 6+12=18, и каждая партиция по 100 субпартиций регионов.

Отсюда не сложно высчитать минимальный размер такой таблицы 18*100*8МБ= 140ГБ.
Что явный перебор при исходном размере таблицы без секций = 2ГБ.

Такой размер имеет ряд минусов:
  • Сложность переноса бд (экспорта/импорта) - база будет весить терабайты.
  • Ограниченность tablespace по размеру: Таблиц много, в DWH такая таблица должна быть продублирована несколько раз (Таблица источник - зеркало, Преобразованная таблица, Итоговая фактическая таблица). Доступное пространство быстро закончится.
  • !Увеличение времени чтения партиции с диска при сканировании по партициям.

Мое решение: необходимо уменьшить размер партиции.
Я обычно пользуюсь таким решением:
--размер блока в байтах
select value from v$parameter where name = 'db_block_size';
VALUE                                                                          
--------------------------------------------------------------------------------
8192       

--узнаю реальный средний физический размер партиции
select AVG(blocks*8192/1024/1024) as mb, COUNT(*) from SYS.ALL_TAB_SUBPARTITIONS WHERE TABLE_NAME = :TAB;

        MB   COUNT(*)
---------- ----------
    0,6846       3404

Размер партиции можно взять с небольшим запасом (1МБ):
CREATE TABLE T_TBL
STORAGE(INITIAL 1048576 NEXT 1048576 )
PARTITION BY RANGE (DT_COL) INTERVAL (NUMTOYMINTERVAL(1,''MONTH''))
SUBPARTITION BY LIST(REG_COL)
SUBPARTITION TEMPLATE
...

Минимальный размер такой таблицы вырастет в сравнении с плоской незначительно на (1-0,68/1)*100% = 32%

Замечу один минус:
Совсем маленькой партиции тоже не стоит делать - это увеличить число физических обращений к диску. Т.к. для сканирования секции понадобится не одно обращение, а несколько.



2. Настройка параметров PCTFREE и PCTUSED для уменьшения размера таблицы
Размер таблицы можно значительно уменьшить, если задать эти параметры:
PCTFREE - % блока под резерв изменений. Если таблица редко обновляется (или только чистится и заполняется заново), то этот параметр можно ставить близким к 0.
PCTUSED - % блока под данные, если размер данных в блоке превышает этот %, то новые данные туда не будут вставляться. Если опять же данные редко обновляются, то для максимального уплотнения этот параметр можно ставить ближе к 100%.

+ Уменьшение размера таблицы сопоставимо изменению параметров PCTFREE/PCTUSED
- При update строки с увеличением его размера, в случае нехватки свободного места в блоке (PCTFREE) строка целиком будет перенесена из текущего блока в новый. В старом блоке будет проставлена ссылка на новое расположение. Т.е. при чтении данных из таблицы нужно будет выполнить дополнительное рекурсивное чтение, что значительно увеличит стоимость запроса.
- При большом числе параллельных сессии к одному блоку сильно разрастается информация о заинтересованных сессиях к строкам блока (ITL). Если место в блоке закончится, то невозможно будет расширить ITL, что приведет к ошибке обновления блока.


3. Быстрое обновление таблиц через PARTITION EXCANGE.

Частая задача в DWH - инкрементальное обновление данных. Допустим нужно залить новые данные за прошедший месяц.
Традиционный способ INSERT /*+ APPEND */ достаточно медленный.
Есть альтернативный, выполняющийся доли секунды.

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

Пример перекидки из промежуточной таблицы в RANGE-INTERVAL партиционированную таблицу.
--создаем интервальную партицию, если ее еще нет
LOCK TABLE T_TBL PARTITION FOR (TO_DATE('...','DD.MM.YYYY')) IN SHARE MODE;

--обмениваемся партициями
ALTER TABLE T_TBL EXCHANGE PARTITION FOR (TO_DATE('...','DD.MM.YYYY')) WITH TABLE T_TBL_PT WITHOUT VALIDATION;

Этой командой произойдет обмен данными между таблицами: партиция заполнится данными таблицы T_TBL_PT, а таблица данными пустой партиции T_TBL.

Аналогично можно делать обмены с субпартициями
ALTER TABLE T_TBL EXCHANGE SUBPARTITION FOR (TO_DATE('<партиция>','DD.MM.YYYY'),<субпартиция>) WITH TABLE T_TBL_PT WITHOUT VALIDATION;


4. Узнать имя партиции по произвольному фильтру
Такое может понадобится, допустим, для сбора статистики только по нужной секции, т.к. в dbms_stat нужно указать физическое имя.
select uo.subobject_name from user_objects uo where uo.data_object_id = dbms_rowid.rowid_object(:ROWID);

execute immediate('select /*+ FIRST_ROWS(1) */ MAX( (select /*+ NO_UNNEST */ uo.subobject_name from user_objects uo where dbms_rowid.rowid_object(t.rowid) = uo.data_object_id ) ) as part_name
     from '||P_TABLE||' partition for ('||p_some_value||') t
    where t.'||V_SOME_COL||' = '||p_some_value||' and rownum = 1') INTO v_part;

Если известно значение в колонке секций, то нужно воспользоваться конструкцией:
select * from T_TBL PARTITION FOR (:P_PART_VAL)

5. System партицирование
Нет указания колонки при создании, из-за этого нужно указывать конкретную партицию при вставке или выборке

6. Reference партицирование
Возможность создания детализированной таблицы с наследованием партицирования от родительской:
  create table orders (id integer, sm number, odate date, CONSTRAINT opk PRIMARY KEY (id) PARTITION BY RANGE(odate) (...);
  create table order_items (oid integer, sm number, CONSTRAINT ofk FK to orders) PARTITION BY REFERENCE(ofk)
В строках нет даты, она автоматом подтягивается по фк из заголовка и партицируется по ней.
Цена этому увеличение нагрузки в 10 раз: https://jonathanlewis.wordpress.com/2018/03/19/reference-costs/

7. Изменение параметров партиции на основании статистики использования
На основании статистики использования партиций (DBA_HEAT_MAP_SEG_HISTOGRAM) можно включить компрессию
 ALTER TABLE T MODIFY [PART] ILM ADD POLICY COMPRESS ADVANCED ROW AFTER 30 DAYS OF NO MODIFICATION;

четверг, 21 мая 2015 г.

Oracle: Своя агрегатная функция

Хотел бы рассказать как в Oracle создать свою агрегатную функцию наподобие SUM или AVG.

Делается это с помощью интерфейса ODCIAggregate.
Он имеет 4 метода:
  • Initialize
  • Iterate
  • Merge
  • Terminate

Нам необходимо реализовать 4 эти метода:
1. Initialize - Данный метод выполняет определенные действия перед началом вычислений.
2. Iterate - Собственно метод, выполняющий операцию над очередным значением из массива.
3. Merge - Метод, необходимый в случае распараллеливания выполнения расчета, когда весь массив бьется на части, а потом рассчитанные по отдельности они объединяются. Так вот этот метод выполняет объединение пары результатов.
4. Terminate - Метод, заканчивающий расчеты и выдающий результат.

Кроме того необходимо объявить функцию, которая будет вызывать расчет агрегатной функции.

Разберем на примере агрегатной функции перемножения значений, назовем ее MULT.

1. Создаем новый объект с набором необходимых свойств и методов:
create type MultImpl as object
(
  mult_value NUMBER, --свойство, где хранится результат

  static function ODCIAggregateInitialize(sctx IN OUT MultImpl) 
    return number,
  member function ODCIAggregateIterate(self IN OUT MultImpl, 
    value IN number) return number,
  member function ODCIAggregateTerminate(self IN MultImpl, 
    returnValue OUT number, flags IN number) return number,
  member function ODCIAggregateMerge(self IN OUT MultImpl, 
    ctx2 IN MultImpl) return number
);
/

2. Описываем реализации объекта.
create or replace type body MultImpl is 
static function ODCIAggregateInitialize(sctx IN OUT MultImpl) 
return number is 
begin
  --кладем в начальное значение NULL (если положить 0, то результатом перемножения всегда будет 0)
  sctx := MultImpl(NULL);
  return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT MultImpl, value IN number) return number is
begin
  --перемножаем наше свойство с новым значением из выборки
  self.mult_value := nvl(self.mult_value,1) * NVL(value,0);
  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN MultImpl, 
    returnValue OUT number, flags IN number) return number is
begin
  --возвращаем результат из свойства
  returnValue := self.mult_value;
  return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT MultImpl, ctx2 IN MultImpl) return number is
begin
  --в случае объединения - также перемножаем результаты 2 потоков
  self.mult_value := nvl(self.mult_value,0) * nvl(ctx2.mult_value,0);
  return ODCIConst.Success;
end;
end;
/

3. Создаем pl/sql функцию, вызывающую наш объект.
CREATE FUNCTION mult (input NUMBER) RETURN NUMBER 
PARALLEL_ENABLE AGGREGATE USING MultImpl;

4. Вызываем нашу новую агрегатную функцию.
with t as (
select 2 as n from dual union all
select 3 from dual union all
select 4 from dual)
SELECT SUM(n), mult(n) FROM t;

5. Смотрим результат.
    SUM(N)    MULT(N)
---------- ----------
         9         24 

Подробное описание можно почитать на хабре: https://habrahabr.ru/company/postgrespro/blog/351008/

вторник, 28 апреля 2015 г.

Некоторые особенности индексного поиска в Oracle

1. Зачем создавать индексы на ссылочных полях (FK – foreign key) ?

* Очевидно для оптимизации процессов соединения таблиц.
* Но есть и другое, не менее важное, предназначение:
Если таблица фактов в схеме «звезда» ссылается (FK) на таблицу измерений, и на ссылочном поле нет индекса, то DML (Insert / Update / Delete ) операция над таблицей измерений заблокирует таблицу фактов на изменение целиком.
Если на ссылочном поле есть индекс, то произойдет блокировка только нужных строк из таблицы фактов, тех которые затрагивает изменение в таблице измерений.
Пример: таблица с данными продаж (факты) и таблица кассовых мест (измерения), с некоторой периодичностью происходит изменение списка кассовых мест, то на ссылочном поле кассовое место / таблица продаж, желательно создать индекс.
Если этого не сделать, то данные в продажах будут заблокированы до commit / rollback обновления списка кассовых мест.

2. Compressed index

Сжатие индексов может пригодится, если индекс состоит из нескольких столбцов, несколько первых из которых мало селективны.
В этом случае при создании индекса можно указать:
compressed N
где N – кол-во колонок. Пример: индекс по 3 полям, первые 2 из которых малоселективны (online, status)
Индекс без сжатия
Online,0,AAAPvCAAFAAAAFaAAa
Online,0,AAAPvCAAFAAAAFaAAg
Online,0,AAAPvCAAFAAAAFaAAl
Online,2,AAAPvCAAFAAAAFaAAm
Online,3,AAAPvCAAFAAAAFaAAq
Online,3,AAAPvCAAFAAAAFaAAt
Индекс со сжатием
Online,0
AAAPvCAAFAAAAFaAAa
AAAPvCAAFAAAAFaAAg
AAAPvCAAFAAAAFaAAl
Online,2
AAAPvCAAFAAAAFaAAm
Online,3
AAAPvCAAFAAAAFaAAq
AAAPvCAAFAAAAFaAAt
В этом случае группа одинаковых данных N первых колонок будут сжаты в одну запись в индексе, а ROWID самих записей будут помещены в вспомогательную структуру. Уменьшение числа листов индекса соответственно ускорит доступ к данным.
Хочу заметить, что данный способ применим только для низко селективных столбцов. Если данные достаточно уникальны, то создание доп. структур на хранение записей только создаст дополнительные накладные расходы!
Так же стоит заметить, что без compressed первый столбцы индекса наоборот должны содержать наиболее селективные данные, чтобы Oracle за меньшее кол-во операций мог дойти до уникальных данных.

3. Advanced compress - oracle 12

Advanced compress - новый шаг развития компрессии индексов в Oracle 12.
Oracle автоматически подбирает размер компрессии на уровне каждого блока индекса, но порядок столбцов нужно все также соблюдать самостоятельно.

Пример:
0. Обычный индекс
1. Индекс с компрессией 1 - занимает 3200 блоков
2. Индекс с компрессией 2 - занимает 2816 блоков
3. ADVANCED LOW - занимает 2816 блоков, т.е. Oracle самостоятельно подобрал уровень компрессии = 2
4. ADVANCED LOW - но в начале плохосжимаемый селективный столбец. Oracle не применяет компрессии, выходит теже 3584 блоков.
Т.е. за порядком столбцов все также надо следить, можно только не делать analyze index, чтобы узнать оптимальный уровень компрессии.
drop table t$t purge;
  
create table t$t as select 1 c1, round( dbms_random.VALUE(1, 10)) c2, level as c3
from dual connect by level < 1000000;
  
create index idx_t$t_0      on t$t(c1, c2, c3, 1);
create index idx_t$t_1      on t$t(c1, c2, c3, 2)  COMPRESS 1;
create index idx_t$t_2      on t$t(c1, c2, c3, 3)  COMPRESS 2;
create index idx_t$t_low    on t$t(c1, c2, c3, 4)  COMPRESS ADVANCED LOW;
create index idx_t$t_c3_low on t$t(c3, c1, c2, 5)  COMPRESS ADVANCED LOW;

 
select segment_type, segment_name, bytes, blocks from SYS.USER_SEGMENTS where segment_name like '%T$T%';

SEGMENT_TYPE       SEGMENT_NAME             BYTES     BLOCKS
------------------ ----------------------- ---------- ----------
TABLE              T$T                      18874368       2304
INDEX              IDX_T$T_0                29360128       3584
INDEX              IDX_T$T_1                26214400       3200
INDEX              IDX_T$T_2                23068672       2816
INDEX              IDX_T$T_LOW              23068672       2816
INDEX              IDX_T$T_C3_LOW           29360128       3584

 6 rows selected 

Скрипт определения индексов для сжатия со списком колонок.
Формула определения необходимости сжатия колонки: произведение кол-ва уникальных значений в текущей и предыдущих колонках < 200 (значение случайное)
Индексы сортируются по значению: кол-во строк в индексе / произведение кол-ва уникальных значений в текущей и предыдущих колонках * кол-во колонок для сжатия
Таким образом вначале будут индексы, которым сильней необходимо сжатие
with t as (
  select /*+ MATERIALIZE */ I.TABLE_NAME, i.index_name, I.NUM_ROWS, C.COLUMN_POSITION, C.COLUMN_NAME, s.NUM_DISTINCT,
    CASE WHEN
     -- EXP (SUM (LN ( col )) ==  MULTPL(col)
     EXP (SUM (LN (s.NUM_DISTINCT)) OVER(PARTITION BY I.TABLE_NAME, i.index_name ORDER BY C.COLUMN_POSITION))  <= 200
    THEN 
      1
    END as NEED_COMPRESS
  from dba_indexes i 
  join DBA_IND_COLUMNS c on C.INDEX_OWNER = i.OWNER AND C.INDEX_NAME = i.index_name AND C.TABLE_NAME = I.TABLE_NAME
  join DBA_TAB_COL_STATISTICS s on S.OWNER = i.OWNER AND S.TABLE_NAME = I.TABLE_NAME AND S.COLUMN_NAME = C.COLUMN_NAME
  WHERE i.OWNER = :OWN AND i.COMPRESSION = 'DISABLED' AND i.INDEX_TYPE = 'NORMAL'
  AND i.LEAF_BLOCKS > 0
  order by I.TABLE_NAME, i.index_name, C.COLUMN_POSITION
)
select TABLE_NAME, index_name, NUM_ROWS, NUM_DISTINCT, COMPR_COLS, COMPR_FACTOR
from (
  select TABLE_NAME, index_name, MAX(NUM_ROWS) as NUM_ROWS, 
    ROUND( EXP (SUM (LN (CASE WHEN NEED_COMPRESS = 1 THEN NUM_DISTINCT END))) ) as NUM_DISTINCT,
    LISTAGG(CASE WHEN NEED_COMPRESS = 1 THEN COLUMN_NAME END, ', ') WITHIN GROUP (ORDER BY COLUMN_POSITION) as COMPR_COLS,
    ROUND( MAX(NUM_ROWS) / SUM(CASE WHEN NEED_COMPRESS = 1 THEN NUM_DISTINCT END) ) * COUNT(DISTINCT CASE WHEN NEED_COMPRESS = 1 THEN COLUMN_POSITION END) as COMPR_FACTOR,
    COUNT(DISTINCT CASE WHEN NEED_COMPRESS = 1 THEN COLUMN_POSITION END) as COLUMN_COUNT
  from t
  WHERE NEED_COMPRESS > 0
  GROUP BY TABLE_NAME, index_name
)
order by COMPR_FACTOR DESC nulls last, TABLE_NAME, index_name
FETCH FIRST 500 ROWS ONLY;

Предварительный результат сжатия индекса можно определить средствами Oracle:
analyze index "IDX" validate structure;
select * from index_stats;
Последние 2 столбца index_stats дадут информацию о желательном уровне сжатия и % уменьшения индекса после компрессии.

4. reverse index

Обычный btree index, но данные этого индекса развернуты наоборот.
Такой индекс хорошо подходит для последовательно генерируемых данных, к примеру для первичного ключа, создаваемого по sequence.
Если использовать обычный индекс то данные будут писаться последовательно в блоки, что может увеличить конкуренцию за диск при вставке или выборке.
Пример:
Первичный ключ:
* Обычный индекс: 12345, 12346, 12347
Данные пишутся на диск последовательно.
* Реверсивный индекс: 54321, 64321, 74321
Видно, что reverse данные хорошо будут раскиданы по диску.
Что должно уменьшить число событий «buffer busy» и «read by other session»

5. Skip scan

Уникальная фича Oracle, которой нет почти ни у одной субд. Возможность поиска по правой части индекса.
Skip scan индекса может быть применен, если первые (левые или лидирующие) столбцы малоселективны. Тогда Oracle может создать логические подиндексы для каждой записи левой части индекса.
Пример:
Индекс по полям: GENDER, EMAIL – GENDER малоселективная левая часть индекса, EMAIL высокоселективная правая часть.
При запросе вида
SELECT * FROM T WHERE EMAIL = ?
Будет использоваться Skip scan, и запрос на внутреннем уровне будет преобразован к виду:
select * from T where gender = 'F' AND EMAIL = ?
union all
select * from T WHERE gender = 'M' AND EMAIL = ?
Если первый столбец достаточно селективен, что встречается чаще (см. п.2), то Skip scan использоваться не будет.

6. Способы доступа к индексу.

a. index scan - последовательное чтение по связанному списку. Данные в этом случае будут отсортированы по индексу.
b. index fast full scan - многоблочно читается сегмент целиком и выбираются блоки индекса. Может быть применено только если not null фильт или колонка. В этом случае данные получаются неотсортированными по индексу.
c. index range scan - сканирование по диапазону. Может использоваться только одно условие отличное от =


7. Характеристики индексов.

a. clustering factor index - как записи в индексе соответствуют (упорядочены) данным в таблице
* в идеале clustering factor = числу блоков, т.е. при чтении из индекса не надо прыгать по разным блокам
* если clustering factor = числу строк, то при запросе из индекса будет много переходов в разные блоки, что приведет к огромному числу чтений


Дополнительная информация по внутреннему устройству индекса в статье Oracle: реализация btree индекса

8. Оптимизация доступа по индексу при Nested Loops.

** Обычный NL без оптимизации (Oracle 9)
Наиболее долгая операция при поиске по индексу, это рандомный доступ (scaterred) к таблице по rowid из последовательного индекса (sequencial).
В случае hdd большую часть времени будет выполняться позиционирование головки винта, чем собственно чтение данных.
----------------------------------------------
| Operation                 |  Name    |  Rows |
------------------------------------------------
| SELECT STATEMENT          |          |   225 |
|  NESTED LOOPS             |          |   225 |
|   TABLE ACCESS BY INDEX RO|T2        |    15 |
|    INDEX FULL SCAN        |T2_I1     |    15 |
|   TABLE ACCESS BY INDEX RO|T1        |     3K| 
|    INDEX RANGE SCAN       |T1_I1     |     3K|
------------------------------------------------

** Prefetching (Oracle 10) - читает в буферный кэш смежные данные, в надежде, что они пригодятся
Чем хуже фактор кластеризации (на основе статистики), тем больше блоков читается за раз (multy block read)
-----------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      0 |        |
|   1 |  TABLE ACCESS BY INDEX ROWID  | T1    |      1 |     15 |
|   2 |   NESTED LOOPS                |       |      1 |    225 | --225 строк, но всего 15 запросов из T1_I1 (блоки читаются не по одному, а по mbrc за раз)
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2    |      1 |     15 |
|   4 |     INDEX FULL SCAN           | T2_I1 |      1 |   3000 |
|*  5 |    INDEX RANGE SCAN           | T1_I1 |     15 |     15 |
-----------------------------------------------------------------

** batching (Oracle 11-12) - накапливается rowid и читает их потом скопом и многопоточно (multy block read)
Чем хуже фактор кластеризации (на основе реальных запросов из индекса-таблицы), тем больше блоков читается за раз (mbrc)
-----------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |
|   1 |  NESTED LOOPS                 |       |      1 |    225 | --накапиливается несколько rowid
|   2 |   NESTED LOOPS                |       |      1 |    225 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2    |      1 |     15 |  -- выполняется параллельный селект
|   4 |     INDEX FULL SCAN           | T2_I1 |      1 |   3000 |
|*  5 |    INDEX RANGE SCAN           | T1_I1 |     15 |     15 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T1    |    225 |     15 |  -- выполняется параллельный селект
-----------------------------------------------------------------

Batching на HDD диске может дать 10 кратное ускорение, а на SSD до 2 раз.

9. Bitmap, bitmap join index.

https://docs.oracle.com/database/121/DWHSG/schemas.htm#DWHSG9042
+ Содержит NULL
+ Лучше использовать на столбцах с небольшим числом уникальных значений
Т.к. размер растет от числа значений:
* по X будут все возможные значений в колонке
* по Y сами строки
+ Главное преимущество: возможность комбинирования нескольких индексов при AND, OR, NOT
- при вставке блокируется часть со вставляемым значением целиком

Bitmap join индекс содержит значения пересечения левой и правой таблицы:
CREATE BITMAP INDEX sales_cust_gender_bjix
ON sales(customers.cust_gender)
FROM sales, customers
WHERE sales.cust_id = customers.cust_id;

Sales.rowid: gender(M) gender(F)
Sales.rowid1 0         0
Sales.rowid2 0         1
Sales.rowid3 1         0
...


10. Bitmap и Star Transformation

Оптимизация, при котором join заменяется на AND комбинацию bitmap индексов:
SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
   SUM(s.amount_sold) sales_amount
FROM sales s, times t, customers c, channels ch
WHERE s.time_id = t.time_id
AND   s.cust_id = c.cust_id
AND   s.channel_id = ch.channel_id
AND   c.cust_state_province = 'CA'
AND   ch.channel_desc in ('Internet','Catalog')
AND   t.calendar_quarter_desc IN ('1999-Q1','1999-Q2')
GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
Запрос будет преобразован к виду:
SELECT ... FROM sales
WHERE time_id IN
  (SELECT time_id FROM times 
   WHERE calendar_quarter_desc IN('1999-Q1','1999-Q2'))
   AND cust_id IN
  (SELECT cust_id FROM customers WHERE cust_state_province='CA')
   AND channel_id IN
  (SELECT channel_id FROM channels WHERE channel_desc IN('Internet','Catalog'));
При этом должны быть индексы на полях: sales.time_id, sales.cust_id, sales.channel_id.
Они будут объединены через BITMAP AND в один и будут использоваться для фильтрации sales:
SELECT STATEMENT
 SORT GROUP BY
  HASH JOIN
   TABLE ACCESS FULL                          CHANNELS
   HASH JOIN
    TABLE ACCESS FULL                         CUSTOMERS
    HASH JOIN
     TABLE ACCESS FULL                        TIMES
     PARTITION RANGE ITERATOR
      TABLE ACCESS BY LOCAL INDEX ROWID       SALES
       BITMAP CONVERSION TO ROWIDS
        BITMAP AND
         BITMAP MERGE
          BITMAP KEY ITERATION
           BUFFER SORT
            TABLE ACCESS FULL                 CUSTOMERS
           BITMAP INDEX RANGE SCAN            SALES_CUST_BIX
         BITMAP MERGE
          BITMAP KEY ITERATION
           BUFFER SORT
            TABLE ACCESS FULL                 CHANNELS
           BITMAP INDEX RANGE SCAN            SALES_CHANNEL_BIX
         BITMAP MERGE
          BITMAP KEY ITERATION
           BUFFER SORT
            TABLE ACCESS FULL                 TIMES
           BITMAP INDEX RANGE SCAN            SALES_TIME_BIX
Если у нас bitmap join индекс, то еще лучше, не будет операции выборки из таблицы:
           BUFFER SORT
            TABLE ACCESS FULL                 CUSTOMERS
           BITMAP INDEX RANGE SCAN            SALES_CUST_BIX
будет одно объединение индексов:
         BITMAP AND
         BITMAP INDEX SINGLE VALUE            SALES_C_STATE_BJIX
         BITMAP MERGE

четверг, 5 февраля 2015 г.

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

среда, 4 февраля 2015 г.

Домашняя бухгалтерия на 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