Третье издание книги Тома Кайта: Oracle для профессионалов, включающее особенности Oracle 12.
1-9. 1-9 главы - внутренняя работа субд oracle
10. Глава 10 - таблицы
11. Глава 11 - индексы
12. Глава 12 - типы данных
13. Глава 13 - секционирование
14. Глава 14 - параллельное выполнение
15. Глава 15 - загрузка и выгрузка данных
понедельник, 30 мая 2016 г.
четверг, 14 апреля 2016 г.
Ядро oracle
на основе одноименной книги Дж. Льюиса
1. Согласованное чтение
2. Восстановление данных
3. Принцип кэширования блоков в память при чтении
4. Парсинг запросов
5. RAC - кластер экземпляров oracle
6. Блокировки и защелки
1. согласованное чтение
общий принцип:
a. транзакция 1 изменяет данные:
* в буферный кэш скидывается грязный блок
** в блоке данных записывается список недавних транзакий ITL (включая сейчас выполняющуюся)
** если транзакция сейчас выполняется, то также проставляется признак блокировки строки в блоке
* процесс lgwr пишет:
** в redo журнал повтора - новое значение, новый scn, список ITL
** в undo журнал отката - старое значение и старый scn
* процесс dbwr пишет:
** асинхронно с задержкой записывает данные в блок базы
*** сразу после commit записывается не более 30% данных
*** остальная часть записывается отложенно при следующем select из строк этой таблицы.
Детектировать можно через события "db block change, consisten gets - examination"
Т.е. не стоит удивляться, что первый select после большого update будет выполняться очень долго.
b. транзакция 2 читает данные:
* считываются данные из буферного кэша или напрямую с диска
* просматривается список ITL на наличие незавершенных транзакций
* в любом случае (не зависимо от ITL) сверяется SCN транзакции и SCN блока (ITL незавершенной транзакции)
** если SCN блока/ITL оказывается больше запрашиваемого, то данные берутся из сегментов отката UNDO через ссылку из ITL
*** поиск по ITL может продолжиться и дальше рекурсивно, если SCN запроса опять меньше SCN из undo
*** если данные в undo не находятся, то это является причиной ошибки "snapshot too old"
* в случае недавнего обновления блока, строка может оказаться помеченной как сейчас обновляемая и со старым SCN в списке ITL выполняется операция отложенная очистка:
** берутся данные из UNDO сегмента, смотрится, что транзакция подтверждена
** сбрасывается флаг блокировки в строке блока и ITL (что повторно генерирует redo логи при select таблицы)
** в случае отсутствия блока в буферном кэше и чтения с диска дополнительно изменяется номер SCN на максимальный (т.к. отсутствие блока в кэше говорит об однозначно последней версии на диске)
Примечание: как многоверсионность сделана в Postgree:
1. Согласованное чтение
2. Восстановление данных
3. Принцип кэширования блоков в память при чтении
4. Парсинг запросов
5. RAC - кластер экземпляров oracle
6. Блокировки и защелки
1. согласованное чтение
общий принцип:
a. транзакция 1 изменяет данные:
* в буферный кэш скидывается грязный блок
** в блоке данных записывается список недавних транзакий ITL (включая сейчас выполняющуюся)
** если транзакция сейчас выполняется, то также проставляется признак блокировки строки в блоке
* процесс lgwr пишет:
** в redo журнал повтора - новое значение, новый scn, список ITL
** в undo журнал отката - старое значение и старый scn
* процесс dbwr пишет:
** асинхронно с задержкой записывает данные в блок базы
*** сразу после commit записывается не более 30% данных
*** остальная часть записывается отложенно при следующем select из строк этой таблицы.
Детектировать можно через события "db block change, consisten gets - examination"
Т.е. не стоит удивляться, что первый select после большого update будет выполняться очень долго.
b. транзакция 2 читает данные:
* считываются данные из буферного кэша или напрямую с диска
* просматривается список ITL на наличие незавершенных транзакций
* в любом случае (не зависимо от ITL) сверяется SCN транзакции и SCN блока (ITL незавершенной транзакции)
** если SCN блока/ITL оказывается больше запрашиваемого, то данные берутся из сегментов отката UNDO через ссылку из ITL
*** поиск по ITL может продолжиться и дальше рекурсивно, если SCN запроса опять меньше SCN из undo
*** если данные в undo не находятся, то это является причиной ошибки "snapshot too old"
* в случае недавнего обновления блока, строка может оказаться помеченной как сейчас обновляемая и со старым SCN в списке ITL выполняется операция отложенная очистка:
** берутся данные из UNDO сегмента, смотрится, что транзакция подтверждена
** сбрасывается флаг блокировки в строке блока и ITL (что повторно генерирует redo логи при select таблицы)
** в случае отсутствия блока в буферном кэше и чтения с диска дополнительно изменяется номер SCN на максимальный (т.к. отсутствие блока в кэше говорит об однозначно последней версии на диске)
Примечание: как многоверсионность сделана в Postgree:
Устаревшие строки (после delete/update) хранятся в том же месте, где основная таблица. Определение версии используются дополнительные идентификаторы в самой строке. При update выполняются действия: * Создается строка с новыми данными * От старой строки создается указатель на новую * У старой строки проставляется: ** xmin - идентификатор транзакции от старой версии строки ** xmax - идентификатор транзакции от новой версии строки Т.е. при select читаются строки у которых нет новой версии (xmax), т.е. актуальные сейчас строки. Если в этот момент идет модификация, то старые строки будут существовать до полного окончания, а после будут очищены фоновым vacuum. + быстрое чтение, без обращения к стороннему логу - любое изменение генерирует новую строку, из-за чего нужно перестраивать все индексы на таблице, даже если изменялось поле не из индексов - большой поток изменений в репликации
четверг, 24 марта 2016 г.
Oracle: распределение (Distrib) данных в параллельных запросах
Основная мысль, которую надо понять при работе с параллельными запроса: "At most one data distribution can be active at the same time" - только одно распределение данных ( PQ Distrib / PX SEND ) может работать одновременно.
Это является причиной появления операций HASH JOIN BUFFERED или BUFFER SORT в параллельных планах, именно они сбивают с мысли человека привыкшего к последовательным не параллельным запросам.
Может сложиться ошибочное мнение о том что при выполнении HASH JOIN закончился hash area size и oracle свопит промежуточные данные на диск/память или делается какаято сортировка, чтобы потом выполнить merge join.
В реальности, как я уже сказал ранее, на параллельные запросы накладывается дополнительное условие: только одно распределение данных ( PQ Distrib / PX SEND ) может работать в один момент.
Это вынуждает складировать промежуточные данные других потоков до конца выполнения распределения, что является причиной появления буферных операций HASH JOIN BUFFERED и BUFFER SORT.
Рассмотрим на примере:
Создадим 2 таблицы:
create table t2
compress
as
select
rownum as id
, mod(rownum, 1000) + 1 as fk
, rpad('x', 100) as filler
from
dual
connect by
level <= 1000000
;
exec dbms_stats.gather_table_stats(null, 't2')
-- Create a copy of T2
create table t4
compress as
select * from t2;
insert /*+ append */ into t4
select
1000000 + rownum as id
, 1000 + mod(rownum, 1000) + 1 as fk
, rpad('x', 100) as filler
from
dual
connect by
level <= 1000000
;
commit;
exec dbms_stats.gather_table_stats(null, 't4')
alter table t2 parallel 2;
alter table t4 parallel 2;
Oracle может выполнить hash join 3 разными способами.
Способ 1 с HASH JOIN BUFFERED:
explain plan for
select * from (
select /*+ no_merge use_hash(a b) no_cpu_costing leading(a b) pq_distribute(b, hash, hash) */
a.filler as a_filler, b.filler as b_filler
from
t2 a
, t4 b
where
a.fk = b.fk
)
where rownum > 1;
select * from table(dbms_xplan.display(format=>'ALLSTATS ALL ADVANCED'));
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000M| 96G| 56606 | | | |
| 1 | COUNT | | | | | | | |
|* 2 | FILTER | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10002 | 1000M| 96G| 56606 | Q1,02 | P->S | QC (RAND) |
| 5 | VIEW | | 1000M| 96G| 56606 | Q1,02 | PCWP | |
|* 6 | HASH JOIN BUFFERED | | 1000M| 195G| 56606 | Q1,02 | PCWP | |
| 7 | PX RECEIVE | | 1000K| 100M| 175 | Q1,02 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 1000K| 100M| 175 | Q1,00 | P->P | HASH |
| 9 | PX BLOCK ITERATOR | | 1000K| 100M| 175 | Q1,00 | PCWC | |
| 10 | TABLE ACCESS FULL| T2 | 1000K| 100M| 175 | Q1,00 | PCWP | |
| 11 | PX RECEIVE | | 2000K| 200M| 359 | Q1,02 | PCWP | |
| 12 | PX SEND HASH | :TQ10001 | 2000K| 200M| 359 | Q1,01 | P->P | HASH |
| 13 | PX BLOCK ITERATOR | | 2000K| 200M| 359 | Q1,01 | PCWC | |
| 14 | TABLE ACCESS FULL| T4 | 2000K| 200M| 359 | Q1,01 | PCWP | |
-----------------------------------------------------------------------------------------------------
Хинты заданы, чтобы точно гарантировать последовательность работы.
Через хинт pq_distribute(b, hash, hash) мы задаем способ обмена данными между потоками - в данном случае hash.
Используя мониторинг становится ясна последовательность выполнения (Timeline):
1. PX SEND HASH :TQ10000 - левая таблица целиком хэшируется и отправляется в :TQ10002
2. PX SEND HASH :TQ10001 - правая таблица также целиком хэшируется и отправляется в :TQ10000.
Так же до этапа HASH JOIN происходит откидывание несоответствующих хэшей правой таблицы (Randolf подтверждает это трассировкой и рассчетами на основании количества строк в правой hash таблице)
Дополнительная затраченная память отображается в столбце Memory.
3. PX SEND QC (RANDOM) :TQ10002 - происходит соединение таблиц используя хэши левой и правой таблицы + параллельная отправка промежуточных данных на следующий этап.
Такая последовательность действий обеспечивает нам 1 рабочий PX SEND одновременно.
Способ 2: HASH JOIN альтернативный план, когда хэш правой таблицы передается постепенно и не буферизуется.
Чтобы получить его, достаточно добавить агрегирующую функцию (count/min/max) - что заблокирут передачу промежуточных данных на уровень выше.
explain plan for
select count(*) from (
select /*+ no_merge use_hash(a b) no_cpu_costing leading(a b) pq_distribute(b, hash, hash) */
a.filler as a_filler, b.filler as b_filler
from
t2 a
, t4 b
where
a.fk = b.fk
);
select * from table(dbms_xplan.display(format=>'ALLSTATS ALL ADVANCED'));
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 56043 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | | | Q1,02 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,02 | PCWP | |
| 5 | VIEW | | 1000M| | 56043 | Q1,02 | PCWP | |
|* 6 | HASH JOIN | | 1000M| 7629M| 56043 | Q1,02 | PCWP | |
| 7 | PX RECEIVE | | 1000K| 3906K| 175 | Q1,02 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 1000K| 3906K| 175 | Q1,00 | P->P | HASH |
| 9 | PX BLOCK ITERATOR | | 1000K| 3906K| 175 | Q1,00 | PCWC | |
| 10 | TABLE ACCESS FULL| T2 | 1000K| 3906K| 175 | Q1,00 | PCWP | |
| 11 | PX RECEIVE | | 2000K| 7812K| 359 | Q1,02 | PCWP | |
| 12 | PX SEND HASH | :TQ10001 | 2000K| 7812K| 359 | Q1,01 | P->P | HASH |
| 13 | PX BLOCK ITERATOR | | 2000K| 7812K| 359 | Q1,01 | PCWC | |
| 14 | TABLE ACCESS FULL| T4 | 2000K| 7812K| 359 | Q1,01 | PCWP | |
-----------------------------------------------------------------------------------------------------
1. PX SEND HASH :TQ10000 - левая таблица целиком хэшируется и отправляется в :TQ10002
2. PX SEND HASH :TQ10001 - правая таблица построчно хэшируется и отправляется в :TQ10000.
Эта операция не требует дополнительных затрат памяти.
3. HASH JOIN - происходит построчное соединение таблиц используя хэши левой и правой таблицы.
4. PX SEND QC (RANDOM) :TQ10002 - отправка результата дальше, выполняется после полного выполнения этапа 2, т.е. после полного выполнения HASH JOIN этапа 3.
Способ 3: BUFFER SORT альтернативный план, в целом похожий на HASH JOIN BUFFERED
select * from (
select /*+ no_merge use_hash(a b) no_cpu_costing leading(a b) pq_distribute(b, none, broadcast) */
a.filler as a_filler, b.filler as b_filler
from
t2 a
, t4 b
where
a.fk = b.fk
)
where rownum > 1;
select * from table(dbms_xplan.display(format=>'ALLSTATS ALL ADVANCED'));
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000M| 96G| 56606 | | | |
| 1 | COUNT | | | | | | | |
|* 2 | FILTER | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10001 | 1000M| 96G| 56606 | Q1,01 | P->S | QC (RAND) |
| 5 | VIEW | | 1000M| 96G| 56606 | Q1,01 | PCWP | |
|* 6 | HASH JOIN | | 1000M| 195G| 56606 | Q1,01 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 1000K| 100M| 175 | Q1,01 | PCWC | |
| 8 | TABLE ACCESS FULL | T2 | 1000K| 100M| 175 | Q1,01 | PCWP | |
| 9 | BUFFER SORT | | | | | Q1,01 | PCWC | |
| 10 | PX RECEIVE | | 2000K| 200M| 359 | Q1,01 | PCWP | |
| 11 | PX SEND BROADCAST | :TQ10000 | 2000K| 200M| 359 | Q1,00 | P->P | BROADCAST |
| 12 | PX BLOCK ITERATOR | | 2000K| 200M| 359 | Q1,00 | PCWC | |
| 13 | TABLE ACCESS FULL| T4 | 2000K| 200M| 359 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------
Меняем метод рассылки данных на BROADCAST - копирование данных во все потоки.
В этом случае хэши правой таблицы не создаются, вместо этого данные помещаются в BUFFER SORT и используются при выполнении HASH JOIN
1. PX SEND BROADCAST :TQ10000 - правая таблица целиком пересылается в BUFFER SORT
Надо заметить, что BUFFER SORT в этом случае ничего не сортирует, а только буферизирует данны.
Объем потребленной памяти = размер правой таблицы * параллелизм (из-за broadcast во все потоки).
2. HASH JOIN - происходит полное соединение таблиц.
4. PX SEND QC (RANDOM) :TQ10001 - отправка результата дальше по мере готовности.
Объем памяти в BUFFER SORT не растет во время выполнения, что подтверждает предположение - правая таблица целиком помещается в буфер сразу на первом этапе.
Вывод: обычный hash join лучше, когда памяти мало, т.к. нет операций с temp/памятью.
Если памяти достаточно, то hash join buffered предпочтительней, т.к. oracle может сразу перейти к следующему этапу запроса и получить большую степерь параллелизма.
воскресенье, 14 февраля 2016 г.
Oracle: адаптивные технологии оптимизации запросов
- Оптимизация запросов со связанными bind переменными
- Cardinality feedback
- Dynamic Sampling
- Oracle 12: Адаптивные планы
Оптимизация запросов со связанными bind переменными
1. При первом разборе происходит полный разбор запроса (hard parse)
План запроса помещается в глобальный кэш БД с определенным sql_id
2. При повторном выполнении происходит частичный разбор (soft parse)
Происходит только синтаксический разбор, проверки прав доступа и проверки bind переменных. Что для разных вариаций sql_id создает дочерние child_sql_id
Из-за такого механизма работы Oracle вытекает частая проблема oltp систем, где существует огромное число маленьких запросов, отличающихся друг от друга только фильтрами или параметрами. Это приводит к быстрому вытеснению планов из кэша и их последующему повторному hard parse.
В итоге может оказаться, что большую часть времени БД занимается разбором запросов, а не собственно их выполнением.
Отсюда вывод: по возможности используйте bind переменные в вариациях одного запроса, замен константных фильтров, т.к. это даст нам только один план запроса (child_sql_id) при разных значениях переменных на равномерно распределенном столбце.
Я не зря сказал ранее "на равномерно распределенном столбце", т.к. с bind переменными есть проблема: по умолчанию Oracle не знает какие данные будут переданы в запрос и из-за этого может сгенерить неверный план запроса.
Посмотрим на примере по умолчанию. Создадим таблицу с неравномерно распределенным столбцом "n" (9 строк со значением = 1, и 1млн-9 строк со значением 2):
create table t as select level as id, case when level < 10 then 1 else 2 end as n from dual connect by level < 1000000; create index t_i on t(n); begin dbms_stats.gather_table_stats(user,'T',method_opt=>'for columns n size 1'); end;Столбец не имеет гистограмм, но есть статистика по уникальным значениям. Построим план запроса с bind переменной = 1:
explain plan for select * from t where n = :n;
select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500K| 3906K| 508 (2)| 00:00:07 |
|* 1 | TABLE ACCESS FULL| T | 500K| 3906K| 508 (2)| 00:00:07 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"=TO_NUMBER(:N))
Oracle закономерно ожидает в результате половину таблицу и выбирает full scan, хотя мы то знаем, что тут был бы лучше Index scan.К счастью с 11 версии Oracle может заглядывать в значения bind переменных и подбирать под них нужные планы.
Для этого соберем гистограмму с 2 вершинами и повторим эксперимент:
begin dbms_stats.gather_table_stats(user,'T',method_opt=>'for columns n size 2'); end; var n number; exec :n := 1 select count(*) from t where n = :n; select * from table(dbms_xplan.display_cursor(format=>'allstats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 4qjwcfhq4s9vt, child number 2 ------------------------------------- select count(*) from t where n = :n Plan hash value: 4142320527 ------------------------------------------- | Id | Operation | Name | E-Rows | ------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | SORT AGGREGATE | | 1 | |* 2 | INDEX RANGE SCAN| T_I | 185 | -------------------------------------------Oracle сгенерировал новый child_sql_id под новое значение bind переменной и выбрал правильный доступ по индексу.
Данный план был закеширован в глобальную память и если прямо сейчас выполнить заново с параметром 2, то мы получим тотже план (child number 2).
Замечу что на этом этапе уже надо смотреть план уже выполненного запроса, т.к. oracle не умеет показывать план и заглядывать в bind переменные, но при реальном выполнении запроса значения bind переменных смотрятся.
exec :n := 2 select count(*) from t where n = :n; select * from table(dbms_xplan.display_cursor(format=>'allstats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 4qjwcfhq4s9vt, child number 2 ------------------------------------- select count(*) from t where n = :n Plan hash value: 4142320527 ------------------------------------------- | Id | Operation | Name | E-Rows | ------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | SORT AGGREGATE | | 1 | |* 2 | INDEX RANGE SCAN| T_I | 185 | -------------------------------------------
но oracle пометит этот запрос на пересмотр, т.к. план совсем не сошелся с реальными данными и при последующем применении сгенерирует новый child_sql_id (child number 3) под нашу bind переменную:
exec :n := 2
select count(*) from t where n = :n;
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 4qjwcfhq4s9vt, child number 3
-------------------------------------
select count(*) from t where n = :n
Plan hash value: 2966233522
--------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS FULL| T | 999K|
--------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N"=:N)
Из всего этого можно сделать вывод, что вопреки частому заблуждению, Oracle умеет генерировать правильные планы по bind переменным, но делает это не сразу, а при повторном вызове и при наличии гистограммы.
Второе: реальный план запроса с bind переменными можно узнать только во время или после выполнения запроса, т.к. "explain plan" не подсматривает в bind переменные.
Cardinality feedback
Ora Blog
Oracle мониторит и исправляет следующии "estimated rows" оценки на основе реальных "actual rows"
* Single table cardinality (after filter predicates are applied)
* Index cardinality (after index filters are applied)
* Cardinality produced by a group by or distinct operator
Dynamic Sampling
Ora Blog
Применимо для запросов со сложными предикатами фильтрации, которые дают существенную ошибку оптимизатора.
Включение dynamic sampling в зависимости от параметра пробирует от 32 блоков таблицы на предикаты фильтрации и определяем реальный лучший план.
Oracle 12: Адаптивные планы
Смена плана во время выполнения запроса. На основе кол-ва данных полученных из шагов запроса генерируется оптимальный план следующего шага.
Адаптивность может сменить:
* Тип соединения NL <-> HJ
* тип parallel distribution
* Bitmap Index Pruning
пятница, 29 января 2016 г.
Oracle: оптимизация параллельных запросов
Один из простых способов ускорения запросов - это их расспараллеливание.
Это делается достаточно просто через:
* Хинт
* Через установки сессий:
- N число потоков
В теории этого достаточно, чтобы ускорить запрос в разы.
Но есть ряд ситуаций в которых параллельность наоборот мешает.
Для начала разберемся с терминологией - посмотрим на параллельный план с join 2 таблиц:
Таблица T2 имеет особенность: fk_id_skew неравномерно заполнен и имеет перекос в сторону 1 - она встречается значительно чаще других.
Итак, выполнил простой запрос:
* regexp_replace в этом запросе нужен, чтобы данные отбирались не мгновенно и были видны в статистике затраты CPU.
* Хинты вставлены, чтобы запрос в плане выглядел также как написан тут.
Время выполнения выполнения запроса = 49сек.
Добавим хинт parallel(8) замен no_parallel.
Время выполнения = 8с, что в 6 раз быстрей.
Разберем для понимания план запроса:
Основопологающие фазы:
* PX BLOCK ITERATOR - чтение таблицы частями в несколько потоков
* PX SEND - 1 поток посылает данные другому. Важно знать, что только один producer (PX SEND) может быть активен в одно время, что накладывает ограничения на параллельный план выполнения, подробней: Вторая часть по распределению данных в параллельных запросах
** RANGE - данные будут разбиты на диапазоны (часто при сортировке)
** HASH - диапазон данных на основе их хэша (hash join, group by)
** RANDOM - случайная отправка
** BROADCAST - отправка таблицы во все потоки (часто на маленькой таблице, совместно с последующей ROUND ROBIN правой таблицы. Может быть проблемой производительности, если левая таблица значительно больше, чем указано в статистике, т.к. данные дублируются во все потоки)
** ROUND ROBIN - данные отправляются в потоки по кругу
Про способы распределения данных по потокам нужно поговорить отдельно:
** P->S - параллельность в последовательное выполнение (узкое место или конец запроса - вторая из основных причин замедления параллельного запроса)
** PCWP - параллельность с родителем: сканируем таблицу и сразу делаем join с другой
** PCWC - наоборот: передаем фильтр из внешнего потока и применяем при сканировании
* PX RECEIVE - получение данных из одного параллельного потока в другой
* PX SEND QC - отправка данных координатору
* PX COORDINATOR - приемник всех параллельных запросов
* TQ - Номер потока
Мы рассмотрели идеальный случай распараллеленого запроса. Остановимся подробней на причинах замеделений:
1. Событие "P->S - параллельность в последовательное выполнение"
2. PX SEND skew - Перекос данных
и ускорения:
3. Bloom filter
4. Partition wise
Это делается достаточно просто через:
* Хинт
/*+ parallel(N) */
* Через установки сессий:
alter session enable parallel dml; --для insert ALTER SESSION FORCE PARALLEL QUERY PARALLEL N;
- N число потоков
В теории этого достаточно, чтобы ускорить запрос в разы.
Но есть ряд ситуаций в которых параллельность наоборот мешает.
Для начала разберемся с терминологией - посмотрим на параллельный план с join 2 таблиц:
create table t_1
compress
as
select /*+ use_nl(a b) */
rownum as id
, rpad('x', 100) as filler
from
(select /*+ cardinality(1e5) */ * from dual
connect by
level <= 1e5) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b
;
create table t_2
compress
as
select
rownum as id
, case when rownum <= 5e5 then mod(rownum, 2e6) + 1 else 1 end as fk_id_skew
, rownum as fk_id_uniform
, rpad('x', 100) as filler
from
(select /*+ cardinality(1e5) */ * from dual
connect by
level <= 1e5) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b
;
--соберем статистику
begin
dbms_stats.gather_table_stats(user, 't_1');
dbms_stats.gather_table_stats(user, 't_2');
end;
/
- Запрос 1Таблица T2 имеет особенность: fk_id_skew неравномерно заполнен и имеет перекос в сторону 1 - она встречается значительно чаще других.
select COUNT(*) cnt, fk_id_skew from t_2 GROUP BY fk_id_skew ORDER BY cnt desc;
CNT FK_ID_SKEW
---------- ----------
1500000 1
1 22
1 30
1 34
....
- Запрос 2Итак, выполнил простой запрос:
select count(t_2_filler) from (
select /*+ monitor
no_parallel
leading(t_1 t_2)
use_hash(t_2)
no_swap_join_inputs(t_2)
*/
t_1.id as t_1_id
, t_1.filler as t_1_filler
, t_2.id as t_2_id
, t_2.filler as t_2_filler
from t_1
, t_2
where
t_2.fk_id_uniform = t_1.id
and regexp_replace(t_2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t_1.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
);
- Запрос 3* regexp_replace в этом запросе нужен, чтобы данные отбирались не мгновенно и были видны в статистике затраты CPU.
* Хинты вставлены, чтобы запрос в плане выглядел также как написан тут.
Время выполнения выполнения запроса = 49сек.
Добавим хинт parallel(8) замен no_parallel.
Время выполнения = 8с, что в 6 раз быстрей.
Разберем для понимания план запроса:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'PARALLEL')); ------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 213 | 619 (2)| 00:00:08 | | | | | 1 | SORT AGGREGATE | | 1 | 213 | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 213 | | | Q1,02 | P->S | QC (RAND) | | 4 | SORT AGGREGATE | | 1 | 213 | | | Q1,02 | PCWP | | |* 5 | HASH JOIN | | 100K| 20M| 619 (2)| 00:00:08 | Q1,02 | PCWP | | | 6 | PX RECEIVE | | 2000K| 202M| 245 (2)| 00:00:03 | Q1,02 | PCWP | | | 7 | PX SEND HASH | :TQ10000 | 2000K| 202M| 245 (2)| 00:00:03 | Q1,00 | P->P | HASH | | 8 | PX BLOCK ITERATOR | | 2000K| 202M| 245 (2)| 00:00:03 | Q1,00 | PCWC | | | 9 | TABLE ACCESS FULL| T_1 | 2000K| 202M| 245 (2)| 00:00:03 | Q1,00 | PCWP | | | 10 | PX RECEIVE | | 2000K| 204M| 371 (1)| 00:00:05 | Q1,02 | PCWP | | | 11 | PX SEND HASH | :TQ10001 | 2000K| 204M| 371 (1)| 00:00:05 | Q1,01 | P->P | HASH | | 12 | PX BLOCK ITERATOR | | 2000K| 204M| 371 (1)| 00:00:05 | Q1,01 | PCWC | | | 13 | TABLE ACCESS FULL| T_2 | 2000K| 204M| 371 (1)| 00:00:05 | Q1,01 | PCWP | | -------------------------------------------------------------------------------------------------------------------- План 1
Основопологающие фазы:
* PX BLOCK ITERATOR - чтение таблицы частями в несколько потоков
* PX SEND - 1 поток посылает данные другому. Важно знать, что только один producer (PX SEND) может быть активен в одно время, что накладывает ограничения на параллельный план выполнения, подробней: Вторая часть по распределению данных в параллельных запросах
** RANGE - данные будут разбиты на диапазоны (часто при сортировке)
** HASH - диапазон данных на основе их хэша (hash join, group by)
** RANDOM - случайная отправка
** BROADCAST - отправка таблицы во все потоки (часто на маленькой таблице, совместно с последующей ROUND ROBIN правой таблицы. Может быть проблемой производительности, если левая таблица значительно больше, чем указано в статистике, т.к. данные дублируются во все потоки)
** ROUND ROBIN - данные отправляются в потоки по кругу
Про способы распределения данных по потокам нужно поговорить отдельно:
Стоит заметить, что данные бьются по значениям в столбцах строк, а не просто по строкам.
Это нужно, чтобы один и тотже диапозон данных из разных таблиц попал в один поток для join.
Если бы Oracle делал не так, то в 1 поток могли бы попасть совершенно разные данные и join нельзя было бы совершить.
На это стоит обратить внимание, т.к. это может являться и причиной замедлений выполнения параллельного запроса при сильном перекосе данных (О причинах замделенния параллельных запросов дальше)
** P->S - параллельность в последовательное выполнение (узкое место или конец запроса - вторая из основных причин замедления параллельного запроса)
** PCWP - параллельность с родителем: сканируем таблицу и сразу делаем join с другой
** PCWC - наоборот: передаем фильтр из внешнего потока и применяем при сканировании
* PX RECEIVE - получение данных из одного параллельного потока в другой
* PX SEND QC - отправка данных координатору
* PX COORDINATOR - приемник всех параллельных запросов
* TQ - Номер потока
Мы рассмотрели идеальный случай распараллеленого запроса. Остановимся подробней на причинах замеделений:
1. Событие "P->S - параллельность в последовательное выполнение"
2. PX SEND skew - Перекос данных
и ускорения:
3. Bloom filter
4. Partition wise
вторник, 5 января 2016 г.
Oracle: разные заметки
Небольшая рубрика с заметками по Oracle за конец 2015 года, каждой из которых в отдельности недостаточно для полной статьи.
- Row SCN
- Смещение high water mark
- Возможные pragma в pl-sql
- Рандомный доступ к данным
- _optimizer_max_permutations - максимальное количество перестановок для оптимального плана
- attribute clustering и zone map (Oracle 12)
- Oracle in-memory (Oracle 12)
- insert all
- Партиционирование без Oracle Enterprise
- Преобразованный sql (oracle 12)
- keep pool: таблица всегда будет в кэше
- PIPE-line функции
- over RANGE fn(t_timekey) PRECEDING
- partition join outer
- Типы блокировок
- Виды изоляций в oracle
среда, 23 сентября 2015 г.
ORACLE: основы стоимостной оптимизации
- Общие понятия: селективность, кардинальность, стоимость.
- Стоимость табличного доступа и системная статистика
- Стоимость индексного доступа.
- Гистограммы.
- Проблемы расчета селективности: Секционированные таблицы, значения поумолчанию, текстовые ключи, bind переменные и зависимые/коррелируемые столбцы.
- Селективность соединения таблиц
- Стоимость соединения таблиц: Nested Loops, Hash Join, Merge join
- Стоимость внешней сортировки.
- Кардинальности set операторов.
- Определение последовательности соединения таблиц.
Общие понятия
Селективность = (макс значение - мин значение отобранных данных)/(макс значение - мин значение всех данных)
в общем случае = отобранный интервал / весь интервал
Весь интервал определяется на основе статистики таблицы, это столбец 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ГБ.
Такой размер имеет ряд минусов:
Мое решение: необходимо уменьшить размер партиции.
Я обычно пользуюсь таким решением:
Размер партиции можно взять с небольшим запасом (1МБ):
Минимальный размер такой таблицы вырастет в сравнении с плоской незначительно на (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 партиционированную таблицу.
Этой командой произойдет обмен данными между таблицами: партиция заполнится данными таблицы T_TBL_PT, а таблица данными пустой партиции T_TBL.
Аналогично можно делать обмены с субпартициями
4. Узнать имя партиции по произвольному фильтру
Такое может понадобится, допустим, для сбора статистики только по нужной секции, т.к. в dbms_stat нужно указать физическое имя.
Если известно значение в колонке секций, то нужно воспользоваться конструкцией:
5. System партицирование
Нет указания колонки при создании, из-за этого нужно указывать конкретную партицию при вставке или выборке
6. Reference партицирование
Возможность создания детализированной таблицы с наследованием партицирования от родительской:
Цена этому увеличение нагрузки в 10 раз: https://jonathanlewis.wordpress.com/2018/03/19/reference-costs/
7. Изменение параметров партиции на основании статистики использования
На основании статистики использования партиций (DBA_HEAT_MAP_SEG_HISTOGRAM) можно включить компрессию
Начиная с 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: Своя агрегатная функция
Метки:
mult,
ODCIAggregate,
oracle,
sql,
sum
Хотел бы рассказать как в Oracle создать свою агрегатную функцию наподобие SUM или AVG.
Делается это с помощью интерфейса ODCIAggregate.
Он имеет 4 метода:
Нам необходимо реализовать 4 эти метода:
1. Initialize - Данный метод выполняет определенные действия перед началом вычислений.
2. Iterate - Собственно метод, выполняющий операцию над очередным значением из массива.
3. Merge - Метод, необходимый в случае распараллеливания выполнения расчета, когда весь массив бьется на части, а потом рассчитанные по отдельности они объединяются. Так вот этот метод выполняет объединение пары результатов.
4. Terminate - Метод, заканчивающий расчеты и выдающий результат.
Кроме того необходимо объявить функцию, которая будет вызывать расчет агрегатной функции.
Разберем на примере агрегатной функции перемножения значений, назовем ее MULT.
1. Создаем новый объект с набором необходимых свойств и методов:
2. Описываем реализации объекта.
3. Создаем pl/sql функцию, вызывающую наш объект.
4. Вызываем нашу новую агрегатную функцию.
5. Смотрим результат.
Подробное описание можно почитать на хабре: https://habrahabr.ru/company/postgrespro/blog/351008/
Делается это с помощью интерфейса 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 первый столбцы индекса наоборот должны содержать наиболее селективные данные, чтобы 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, чтобы узнать оптимальный уровень компрессии.
Скрипт определения индексов для сжатия со списком колонок.
Формула определения необходимости сжатия колонки: произведение кол-ва уникальных значений в текущей и предыдущих колонках < 200 (значение случайное)
Индексы сортируются по значению: кол-во строк в индексе / произведение кол-ва уникальных значений в текущей и предыдущих колонках * кол-во колонок для сжатия
Таким образом вначале будут индексы, которым сильней необходимо сжатие
Предварительный результат сжатия индекса можно определить средствами Oracle:
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 высокоселективная правая часть.
При запросе вида
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 большую часть времени будет выполняться позиционирование головки винта, чем собственно чтение данных.
** Prefetching (Oracle 10) - читает в буферный кэш смежные данные, в надежде, что они пригодятся
Чем хуже фактор кластеризации (на основе статистики), тем больше блоков читается за раз (multy block read)
** batching (Oracle 11-12) - накапливается rowid и читает их потом скопом и многопоточно (multy block read)
Чем хуже фактор кластеризации (на основе реальных запросов из индекса-таблицы), тем больше блоков читается за раз (mbrc)
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 индекс содержит значения пересечения левой и правой таблицы:
10. Bitmap и Star Transformation
Оптимизация, при котором join заменяется на AND комбинацию bitmap индексов:
Они будут объединены через BITMAP AND в один и будут использоваться для фильтрации sales:
* Очевидно для оптимизации процессов соединения таблиц.
* Но есть и другое, не менее важное, предназначение:
Если таблица фактов в схеме «звезда» ссылается (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В этом случае группа одинаковых данных N первых колонок будут сжаты в одну запись в индексе, а ROWID самих записей будут помещены в вспомогательную структуру. Уменьшение числа листов индекса соответственно ускорит доступ к данным.
AAAPvCAAFAAAAFaAAa
AAAPvCAAFAAAAFaAAg
AAAPvCAAFAAAAFaAAl
Online,2
AAAPvCAAFAAAAFaAAm
Online,3
AAAPvCAAFAAAAFaAAq
AAAPvCAAFAAAAFaAAt
Хочу заметить, что данный способ применим только для низко селективных столбцов. Если данные достаточно уникальны, то создание доп. структур на хранение записей только создаст дополнительные накладные расходы!
Так же стоит заметить, что без 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 = ?Если первый столбец достаточно селективен, что встречается чаще (см. п.2), то Skip scan использоваться не будет.
union all
select * from T WHERE gender = 'M' AND EMAIL = ?
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
Подписаться на:
Сообщения (Atom)