понедельник, 4 февраля 2030 г.

Скахин Алексей / pihel

Скахин Алексей, pihel Биография:
Родился в городе Вологда 9 марта 1987 года. В 2004 году завершил обучение в средней школе №12 города Вологда. В 2009 году окончил ВоГУ (бывший ВоГТУ) по специальности программное обеспечение. Тема дипломного проекта: "Синтез виртуальной среды с применением скалярных и аналитических функций возмущения и трехмерных массивов вокселей". С 2013 года проживаю в городе Санкт-Петербург.

В бывшем радиолюбитель: 3ий взрослый разряд по радиотелеграфии, радиолюбитель в кв диапазоне. Позывной ra1qkj.

О себе:
В свободное от работы время люблю играть в волейбол, кататься на горных лыжах и велосипеде, путешествовать на машине.

Навыки:
1. PHP: занимаюсь с 2004 года
 а. Bitrix - интеграция, интернет магазины, информационные порталы, государственные порталы)
 b. Drupal - интеграция, разработка модулей
2. JS: JQuery, ExtJS
3. C++: библиотека QT, C++ Builder
4. VBS: автоматизация рутины (авто сборка ПО, создание копий приложений и т.д.)
5. СУБД: Oracle PlSql, MySql, MS SQL (OLTP и OLAP), SqLite
6. Linux: пользователь Fedora и Ubuntu
7. Управление версиями: SVN, GIT, MS VSS
8. Остальное: FastReport, Excel (ActiveX, XML), Open(Libre)Office (XML, UNO), SVG/VML (raphaeljs, extjs), Flex, ITIL

Места работы:
5. Сигма: разработчик баз данных Oracle ( sql, pl/sql, Oracle BI, dwh ), 2015 - 2016
4. Tops Consulting: разработчик баз данных Oracle ( sql, pl/sql ), 2013 - 2015
3. Макси: разработка системы управления предприятием, программист (C++, Oracle, Pl/Sql) 2010-2013
2. Rstyle Softlab ОПР ДСУП: разработка системы управления предприятием, старший программист (rsl/vbs/fast report/ms sql) 2008-2010
1. ВНКЦ ЦЭМИ РАН: разработка внутренней информационной системы (php/js/mysql) 2007-2008 г.
0. Фриланс - web направление 2003-...

понедельник, 30 мая 2016 г.

Том Кайт: Oracle для профессионалов

Третье издание книги Тома Кайта: Oracle для профессионалов, включающее особенности Oracle 12.

1-9. 1-9 главы - внутренняя работа субд oracle
10. Глава 10 - таблицы
11. Глава 11 - индексы
12. Глава 12 - типы данных
13. Глава 13 - секционирование
14. Глава 14 - параллельное выполнение
15. Глава 15 - загрузка и выгрузка данных

четверг, 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 на максимальный (т.к. отсутствие блока в кэше говорит об однозначно последней версии на диске)

четверг, 24 марта 2016 г.

Oracle: распределение (Distrib) данных в параллельных запросах

Эта заметка осмысление статьи http://oracle-randolf.blogspot.ru/2012/12/hash-join-buffered.html по неочевидной операции HASH JOIN BUFFERED в параллельных запросах.

Основная мысль, которую надо понять при работе с параллельными запроса: "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):
HASH JOIN BUFFERED
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 |            |
-----------------------------------------------------------------------------------------------------
hash join
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
explain plan for
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
buffer sort
1. PX SEND BROADCAST :TQ10000 - правая таблица целиком пересылается в BUFFER SORT
Надо заметить, что BUFFER SORT в этом случае ничего не сортирует, а только буферизирует данны.
Объем потребленной памяти = размер правой таблицы * параллелизм (из-за broadcast во все потоки).
2. HASH JOIN - происходит полное соединение таблиц.
4. PX SEND QC (RANDOM) :TQ10001 - отправка результата дальше по мере готовности.

Объем памяти в BUFFER SORT не растет во время выполнения, что подтверждает предположение - правая таблица целиком помещается в буфер сразу на первом этапе.
buffer sort 106sec


Вывод: обычный hash join лучше, когда памяти мало, т.к. нет операций с temp/памятью.
Если памяти достаточно, то hash join buffered предпочтительней, т.к. oracle может сразу перейти к следующему этапу запроса и получить большую степерь параллелизма.

воскресенье, 14 февраля 2016 г.

Oracle: кеширование планов со связанными bind переменными

Эта заметка краткий пересказ статьи https://habrahabr.ru/company/postgrespro/blog/275755/

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 переменные.

пятница, 29 января 2016 г.

Oracle: оптимизация параллельных запросов

Один из простых способов ускорения запросов - это их расспараллеливание.
Это делается достаточно просто через:
* Хинт
/*+ parallel(N) */

* Через установки сессий:
alter session enable parallel dml;
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.
Время выполнения = , что в 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 поток посылает данные другому
 ** RANGE - данные будут разбиты на диапазоны (часто при сортировке)
 ** HASH - диапазон данных на основе их хэша (hash join, group by)
 ** RANDOM - случайная отправка
 ** ROUND ROBIN - данные отправляются в потоки по кругу

Про способы распределения данных по потокам нужно поговорить отдельно:

Стоит заметить, что данные бьются по значениям в столбцах строк, а не просто по строкам.
Это нужно, чтобы один и тотже диапозон данных из разных таблиц попал в один поток для join.
Если бы Oracle делал не так, то в 1 поток могли бы попасть совершенно разные данные и join нельзя было бы совершить.
На это стоит обратить внимание, т.к. это может являться и причиной замедлений выполнения параллельного запроса при сильном перекосе данных (О причинах замделенния параллельных запросов дальше)

 ** P->P - данные из одной параллельной группы передаются в другую параллельную группу
 ** 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

среда, 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;

четверг, 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