Extended Oracle


ОПТИМИЗАЦИЯ СТРУКТУРЫ ТАБЛИЦЫ ДЛЯ РАЗРАБОТКИ И ТЕСТИРОВАНИЯ ИСТОРИЧЕСКИХ/ОБЫЧНЫХ БАЗ ДАННЫХ.

Используемые понятия и постановка проблемы:
1. База данных (production) хранящая исторические данные - множество таблиц хранящая данные в партициях (partition) по бизнес дню/месяцу и т.д. Т.е для каждого дня/месяца создается партиция для хранения данных.
2. Данный подход позволяет работать и с обычными базами данных , не историческими. Ниже будет показан такой подход, он более простой
3. Среда разработки/тестирования - множество таблиц структурно/логически на уровне колонок и типа данных соответствующих production таблицам, но организованные в тестовой базе данных в схемах аналогичным production.
Например:
Операционная(Production) база данных имеет схемы:
DB_OWNER - схема для хранения всех таблиц и представление (view) на таблицу
ABC_JOBS - схема для подсоединения всех аппликаций и работы с таблицами INSERT/SELECT/UPDATE/DELETE данных в таблицах через их представление (view).
Среды разработок для операционной базы данных предъявлены в отдельной базе данных несколькими срезами, к примеру
DEV1_DB_OWNER 
DEV1_ABC_JOBS
.....
DEVm_DB_OWNER 
DEVm_ABC_JOBS

В реальной жизни это необходимо потому как в разработке участвуют несколько независимых групп разработчиков, которым необходимы отдельные среды для работы. Практика использования, при явных плюсах, такого подхода показала крайне большую громоздкость и значительные усилия по его поддержанию. Особенно это заметно для Datawerhouse, когда у вас сотни таблиц и партиции организованны так, что данные по одному дню хранятся в отдельной партиции. А если к этому еще добавляется несколько срезов в одной базе данных , то системный каталог Oracle разрастается до огромных размеров и если надо, к примеру, выполнить ALTER SYSTEM FLUSH SHARED POOL, то это иногда занимает часы. Добавление и удаление партиций для следующего месяца во всех срезах тоже задачка и громоздкая и достаточно длительная. Плюс ко всему нужно постоянно следить за тем чтобы логическая структура всех таблиц во всех срезах соответствовала операционной(production) базе данных. Не маловажным фактором считается также не оптимальное использование дискового пространства

Описанный далее подход позволяет уменьшить все перечисленные трудности за счет немного другой, физической организации базы данных для разработки и тестирования, при этом не меняя исходный текст программ, но увеличивая некую небольшую нагрузку на ORACLE DBA/DA, при имлиментации изменений в операционную базу данных.

Итак. Организация срезов для ****_JOBS схем остается прежней, т.е.
DEV1_ABC_JOBS
DEV2_ABC_JOBS
.....

DEVm_ABC_JOBS 

Но основная схема DB_OWNER создается одна и она соотвествует логически операционной базе данных на текущий момент, т.е. перечень таблиц, колонок с их типами данных и т.д. А вот физическая структура таблиц меняется следующим образом.
Допустим у нас в production есть таблица
CREATE TABLE DB_OWNER.AF_T
(
  C1          NUMBER,
  C2          NUMBER,
  BUS_DATE  DATE
)
PARTITION BY RANGE (BUS_DATE)
(  
  PARTITION P_AF_20160410  values less than ('11-APR-2016')
  ,  
  PARTITION P_AF_20160411 values less than ('12-APR-2016')
  ,  
  PARTITION P_AF_20160412 values less than ('13-APR-2016')
  ,  
......
);

тогда в базе данных для разработки с тремя , к примеру, срезами DEV1, DEV2, DEV3 мы создаем таблицу со следующей физической структурой
CREATE TABLE DB_OWNER.AF_T
(
  C1          NUMBER,
  C2          NUMBER,
  BUS_DATE  DATE,
  KEY         VARCHAR2(16 BYTE)
)
PARTITION BY LIST (KEY)
(  
  PARTITION P_DEV1_1 VALUES ('DEV1_1')
     ,  
  PARTITION P_DEV1_2 VALUES ('DEV1_2')
     ,  
  PARTITION P_DEV2_1 VALUES ('DEV2_1')
     ,  
  PARTITION P_DEV2_2 VALUES ('DEV2_2')
     , 
  PARTITION P_DEV3_1 VALUES ('DEV3_1')
     ,  
  PARTITION P_DEV3_2 VALUES ('DEV3_2')
);

В данном примере предполагается, что для начала мы создаем срезы для тестирования по двум различным BUS_DATE.
Следующей этап состоит в создании таблицы ссылок между BUS_DATE, названием среза и партиции по каждой таблицы.
Таблица: REF_DEV
BUS_DATE             KEY              ENV_NAME         TABLE_NAME                                                       
11-APR-2016              DEV1_1           DEV1             AF_T                                                             
12-APR-2016              DEV1_2           DEV1             AF_T                                                             
11-APR-2016              DEV2_1           DEV2             AF_T                                                             
12-APR-2016              DEV2_2           DEV2             AF_T 
11-APR-2016              DEV3_1           DEV3             AF_T                                                             
12-APR-2016              DEV3_2           DEV3             AF_T     
......
Далее создаютса VIEW по одному на каждый срез и каждую таблицу.

CREATE OR REPLACE VIEW AF_DEV1
AS SELECT A.* FROM AF_T A,REF_DEV B 
WHERE
A.KEY=B.KEY
AND B.TABLE_NAME='AF_T'
AND B.ENV_NAME = 'DEV1';

CREATE OR REPLACE VIEW AF_DEV2
AS SELECT A.* FROM AF_T A,REF_DEV B 
WHERE
A.KEY=B.KEY
AND B.TABLE_NAME='AF_T'
AND B.ENV_NAME = 'DEV2';

CREATE OR REPLACE VIEW AF_DEV3
AS SELECT A.* FROM AF_T A,REF_DEV B 
WHERE
A.KEY=B.KEY
AND B.TABLE_NAME='AF_T'
AND B.ENV_NAME = 'DEV3';

И далее SYNONYM
create or reaplace synonym DEV1_ABC_JOBC.AF for DB_OWNER.AF_DEV1;
create or reaplace synonym DEV2_ABC_JOBC.AF for DB_OWNER.AF_DEV2;
create or reaplace synonym DEV3_ABC_JOBC.AF for DB_OWNER.AF_DEV3;

Для поддержки , к примеру, INSERT команды вам необходимо создать TRIGGER на соотвествующий VIEW.

CREATE OR REPLACE TRIGGER AF_DEV2_TRG
   INSTEAD OF INSERT ON AF_DEV2
   BEGIN
		CASE :NEW.BUS_DATE
			WHEN '11-APR-2016' THEN INSERT INTO AF_T VALUES(:NEW.C1,:NEW.C2,:NEW.BUS_DATE,'DEV2_1');
			WHEN '12-APR-2016' THEN INSERT INTO AF_T VALUES(:NEW.C1,:NEW.C2,:NEW.BUS_DATE,'DEV2_2');
			ELSE dbms_output.put_line('No such BUS date');
		END CASE;
   END;
   /

Теперь если вы под-соединитесь к срезу DEV1 вы можете выполнять команды INSERT/SELECT используя BUS_DATE колонку в основной таблице AF_T.

При имлипментации в production вам необходимо будет создать таблицу AF_T используя выше указанный DDL для CREATE TABLE и постоить VIEW следующего вида
CREATE OR REPLACE VIEW AF_V AS SELECT A.* FROM AF_T A;
и
create or replace PUBLIC synonym AF for DB_OWNER.AF_V;

Понятно, что код программы при таком подходе не меняется.

ЗАМЕЧАНИЯ:
1. Понятно, что если у вас закончился цикл разработки или вам необходимо использовать другой BUS_DATE, то вам надо сделать изменения в таблице REF_DEV и если неоходимо то перестроить TRIGGER AF_DEV2_TRG
2. Понятно, что если вам не хватает 2-х BUS_DATE или же вам нужно дополнительный срез для разработки/тестирования, то вам необходимо вначале добавить соответствующую партицию в таблицу , дополнить таблицу REF_DEV новой ссылкой, создать новое VIEW, если это новый срез и обновить/создать новый TRIGGER, если необходимо.
3. Синхронизировать таблицы с production в таком подходе намного проще чем с множеством таблиц в нескольких срезах.
4. Если несколько групп разработчиков работает с одной и той же таблицей используя различные срезы, то понятно что логическая структура таблицы должна соответствовать всем изменениям для различных групп, а через VIEW для каждого среза можно высвечивать колонки только для данной группы разработчиков в данном срезе.
5. При описании ситуации в 4 , если вдруг новая дополнение к таблице требует использование колонки NOT NULL, то эта проблема легко обходится для другой группы разработчиков, не использующих новую колонку , дополнением DEFAULT value для новой колонки. Очевидно , что при имлиментации в production DEFAULT нужно будет убрать. Собственный практический опыт показывает , что такая ситуация крайне редка.
6. Выше описанная достаточно сложная структура таблиц использующая партиции по BUS_DATE. Для обычных таблиц , нужно убрать колонку
BUS_DATE в REF_DEV таблице и соотвественно изменим VALUES для LIST партиций в основной таблице.
добавлено: 08 май 16 просмотры: 1068, комментарии: 0



TIME-SERIES. ****. ПРОДОЛЖЕНИЕ.

Данный блог есть продолжение предыдущего: "TIME-SERIES. ПАРТИЦИИ. NO INDEX. ПАРАЛЛЕЛЬНОЕ ЧТЕНИЕ."
В дальнейшем материале пойдет обсуждение различных идей по создания партиций и организации возможностей получения данных без особого изменения программ по выборы данных (SELECT ...) с пользованием вышеуказанных идей по разделения огромных таблиц на партиции по времени заполняемых в реальном режиме.
Один из подходов описан в первой части и состоит в создании LIST PARTITION для основной таблицы по ключу PART_KEY. Процесс заполнения такой таблицы можно явным образом упростить если использовать возможность ORACLE по созданию партиции на псевдо-колонку (Pseudocolumn)
CREATE TABLE	SQH_AF1
(
.....
......
  part_key                     AS (round((to_char(CREAT_REC_TIME,'SSSSS')- 25200)/(57600 - 25200 )*5999,0)+1)
)
PARTITION BY LIST (part_key)
(
PARTITION P_1  VALUES(1)
...
...
);

В данном случае предполагается что заполнение таблицы будет начато с 7:00 и закончится в 16:00. Количество партиций - 6000. Разумно дополнить таблицу SQH_AF1 двумя дополнительными партициями:
alter table SQH_AF1 add partition P_NULL values( NULL);
alter table SQH_AF1 add partition P_OTHER values(DEFAULT);

Это ,по-сути дела, время работы TRADING SYSTEM в системе продаж стаков/опционов и т.д. В дальнейшем данные будут перекаченны в Datawarehouse, а данные из системы удаляются и система готова для следующего дня.

Выше был обозначен подход по чтению данных использую принцип распараллеливания, но в реальности написанные приложения не хотелось бы менять и оставить чтение используя предикат BETWEEN по времени CREAT_REC_TIME не используя индекс на данную колонку. Особенно это актуально для работы с Oracle Exadata Database Machine. Для поддержки такого подхода необходимо создать управляющую таблицу и VIEW, которые позволят правильно использовать партиции для чтения. Итак управляющая таблица
CREATE TABLE AF_CTL
(
  CREAT_REC_TIME  DATE,
  PART_KEY    NUMBER, 
  CONSTRAINT AF_CTL_PK
  PRIMARY KEY
  (CREAT_REC_TIME)
  ENABLE VALIDATE
)
ORGANIZATION INDEX
;

Пример данных в такой таблицы:
CREAT_REC_TIME			PART_KEY
_____________________ 	________
12/16/2015 4:32:19 PM	159
12/16/2015 4:32:20 PM	159
12/16/2015 4:32:21 PM	159
12/16/2015 4:32:22 PM	159
12/16/2015 4:32:23 PM	160
12/16/2015 4:32:24 PM	160
12/16/2015 4:32:25 PM	160
12/16/2015 4:32:26 PM	160
12/16/2015 4:32:27 PM	160

и VIEW

create or replace view SQH_AF_v
as
select a.* from SQH_AF1 a, af_ctl b
where a. CREAT_REC_TIME= b.CREAT_REC_TIME
and a.part_key= b.part_key;

Понятно , что управляющую таблицу AF_CTL нужно готовить каждый день для следующего дня используя ТУЖЕ формулу для вычисления PART_KEY по CREAT_REC_TIME, в данном случае это part_key=((round((to_char(CREAT_REC_TIME,'SSSSS')- 25200)/(57600 - 25200 )*5999,0)+1)) для каждой секунды от 7:00 до 16:00. Ну или заполним один раз эту таблицы менять в ней только ДЕНЬ,МЕСЯЦ и ГОД.

Используя view SQH_AF_v вместо таблицы мы легко и БЫСТРО находим данные по времени. ORACLE через view определяет необходимую партицию, что видно в EXPLAIN PLAN для SELECT запроса.
SELECT *
  FROM SQH_AF_v 
 WHERE CREAT_REC_TIME  between TO_DATE('12/16/2015 16:21:50', 'MM/DD/YYYY HH24:MI:SS') and TO_DATE('12/16/2015 16:30:45', 'MM/DD/YYYY HH24:MI:SS')

Plan
SELECT STATEMENT  ALL_ROWS Cost: 2,049,852  Bytes: 276  Cardinality: 1  					
	9 HASH JOIN  Cost: 2,049,852  Bytes: 276  Cardinality: 1  				
		6 PART JOIN FILTER CREATE SYS.:BF0000 Cost: 2,049,852  Bytes: 276  Cardinality: 1  			
			5 NESTED LOOPS  Cost: 2,049,852  Bytes: 276  Cardinality: 1  		
				2 STATISTICS COLLECTOR  	
					1 INDEX RANGE SCAN INDEX (UNIQUE) AF_CTL_PK Cost: 2  Bytes: 11,770  Cardinality: 535  
				4 PARTITION LIST ITERATOR  Cost: 2,049,850  Bytes: 254  Cardinality: 1  Partition #: 6  Partitions determined by Key Values	
					3 TABLE ACCESS STORAGE FULL TABLE SQH_AF1 Cost: 2,049,850  Bytes: 254  Cardinality: 1  Partition #: 6  Partitions determined by Key Values
		8 PARTITION LIST JOIN-FILTER  Cost: 2,049,850  Bytes: 64,723,010  Cardinality: 254,815  Partition #: 8  Partitions accessed #:BF0000			
			7 TABLE ACCESS STORAGE FULL TABLE SQH_AF1 Cost: 2,049,850  Bytes: 64,723,010  Cardinality: 254,815  Partition #: 8  Partitions accessed #:BF0000		

Данный подход к построению партиций очевидным образом не учитывает колебания скорости заполнения таблицы в течении дня. Мало того, что процесс заполнения носит стохастический характер, но он еще и явно не стационарен, т.е, в основном, в районе 9:00 и 14:00 имеются пики активности и где-то с 12:00 до 13:00 спад активности. Это будет приводить, к тому, что т.к. у нас партиции разделены по времени на равные промежутки, то количество рядов в каждой партиции будет существенно отличаться, что приведет в свою очередь к существенной разнице по времени запроса для пиковых времен к непиковым.
Идея состоит том чтобы каким-то образом уравнять количество рядов в каждой партиции, за счет длительности времени разделения на партиции. Т.е. N(i) = Rate[t(i+1) - t(i)] ~ N(j) = Rate[t(j+1) - t(j)] ~ N(Average) = NumberOfRowsForWholeDay/NumberOfPartitions. Исходя из этой идеи возможны два подхода:
1. Взять за основу некий "типичный" день, посчитать для него величины N(Average) и Rate(t). Далее исходя из этих значений несложно сделать программу по заполнения управляющей таблицы, где очевидным образом интервалы времени по разным партициям будут разные и неким образом партиции будут отражать поведение Rate(t). Для пиковых значений интервалы партиций будут короче ,а для непиковых длиннее. Понятно что заполнение основной таблицы с данными надо уже будет на основании значений из управляющей таблицыгде он основании времени CREAT_REC_TIME будет определятся PART_KEY, для каждого значения.
2. Второй подход состоит в разработки некого блока "предсказания" как будет себя вести Rate(t) на основе реальных статистических данных на текущий момент и на основании этого заполнять управляющую таблицу.
добавлено: 01 янв 16 просмотры: 1202, комментарии: 0



TIME-SERIES. ПАРТИЦИИ. NO INDEX. ПАРАЛЛЕЛЬНОЕ ЧТЕНИЕ.


Современный бизнес все больше и больше требует фиксации тех или иных событий в более точные моменты времени. К примеру в торговых операциях на биржах уже вводят time series, когда время транзакции учитывается с точностью до нано-секунд. Это приводит в драматическому увеличению количества данных ,с одной стороны, и более внимательному отношения к вопросу создания индексов на таблицу,с другой, и т.д.
Хотелось бы сделать важное дополнение и именно в начале. Уже в результате тестов на реальных данных оказалось, что ниже приведенный подход является единственно возможным решением для проблемы INSERT данных в таблицу при определенных условиях. Т.к. заполнение таблицы в ситуациях когда есть высокая скорость генерации данных возможно только при использовании multi-threaded с batch/bulk INSERT. При наличии индекса на колонку со временем, не важно это Java Time in nonosecond или ORACLE TIMESTAMP(9), будет сразу наблюдаться проблема с WAIT Events "enq: TX - index contention" и/или "buffer busy waits" на блоки хранящие данные для индекса.
SELECT event, object_name , count(*) Count_Waits, sum(TIME_WAITED)
  FROM DBA_HIST_ACTIVE_SESS_HISTORY a, dba_objects b
 WHERE TO_CHAR (SAMPLE_TIME, 'dd-mon-yyyy') = '&date'
 and CURRENT_OBJ#= object_id
AND TO_CHAR (SAMPLE_TIME, 'hh24:mi:ss') >= '&start_time'
AND TO_CHAR (SAMPLE_TIME, 'hh24:mi:ss') <= '&end_time'
AND USER_ID IN (SELECT DBA_USERS.USER_ID
                 FROM DBA_USERS
                 WHERE USERNAME = 'username')
and event is not null
and SQL_OPNAME ='INSERT'
group by  event, object_name 
order by 4 desc;

EVENT				OBJECT_NAME	COUNT(*)	SUM(TIME_WAITED)
buffer busy waits			T1_I2		6,155		205,783,582 
buffer busy waits			T2_I2		6,091		203,179,256
enq: TX - index contention		T2_I2		2,943		92,015,679
enq: TX - index contention		T1_I2		2,832		78,973,463
enq: CT - CTWR process start/stop	T2_I2		74		66,759,638
enq: CT - CTWR process start/stop	T1_I2		59		53,363,220
block change tracking buffer space	T1_I2		743		51,947,549
buffer busy waits			T3_I7		23,974		51,702,292
.....
T1_I2 -- Индекс на Java Time  ORACLE NUMBER(20)
T3_I7 -- Индекс на колонку хранящую время в формате ORACLE TIMESTAMP (9) 


Даная идея состоит в разделении времени, скажем с 7 утра до 16 на короткие промежутки времени , скажем 5 секунд, назначении каждому временному интервалу своей партиции по используя определенную хэш функцию, которую можно использовать и для чтения данных, причем делаю это параллельно. Т.к. зачастую при чтении данных в time series используют SQL для поиск данных за определенный промежуток времени. Для этого создается INDEX на колонку time series и оптимайзер используя RANGE INDEX SCAN ищет данные. Не забываем , что в этом случае ORACLE, по крайней мере, читает данным поблочно и последовательно и если промежуток времени относительно большой и данных много то операция по времени и ресурсам будет затратная.
В данном подходе INDEX не используется, что дает определенный дополнительные преимущества при заполнении таблицы, т.к. не надо перестраивать INDEX каждый раз, и чтение можно производить используя параллельные процессы и чтение само по себе будет много-блочным, т.к. будет чтение целой партиции, т.е. FULL TABLE SCAN.
Ниже приведена данная идея представленная графически.


Картинка с другого сайта.

Одна из возможных хэш функций , что может быть использованная
ROUND((T{now} - T{start_time})/((T{end_time} -T{start_time})/(Nbr_Part - 1)) +1
где
T{start_time} - Начало времени заполнения таблицы в секундах, к примеру 7:00=3600*7
T{end_time} - Конец времени заполнения таблицы секундах, к примеру 16:00=16*3600
T{now} - текущее время в секундах, к примеру 9:56:35.545624354= 9*3600+56*60+35
Nbr_Part - количество патриций, к примеры 6000

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

Ниже приведены результату теста:
1. Поиск данных используя ИНДЕКС
SQL> select *
  2   from SQH_Af1
  3   where CREAT_REC_TIME >= to_timestamp('9/8/2015 4:34:00.000000','mm/dd/yyyy HH24:MI:SS.FF')
  4   and  CREAT_REC_TIME < to_timestamp('9/8/2015 4:35:09.000000','mm/dd/yyyy HH24:MI:SS.FF') ;

1807513 rows selected.
Elapsed: 00:04:03.23


Statistics
----------------------------------------------------------
        133  recursive calls
          0  db block gets
    1479340  consistent gets
    1359494  physical reads
          0  redo size
  168128440  bytes sent via SQL*Net to client
    1325972  bytes received via SQL*Net from client
     120502  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
    1807513  rows processed

SQL>

Plan
SELECT STATEMENT  ALL_ROWS Cost: 180,617  Bytes: 154,445,652  Cardinality: 259,137  		
	2 TABLE ACCESS BY INDEX ROWID TABLE PERF01_SBT_JOBS.SQH_AF1 Cost: 180,617  Bytes: 154,445,652  Cardinality: 259,137  	
		1 INDEX RANGE SCAN INDEX AF_I1 Cost: 189  Cardinality: 259,137  
		
2. Поиск данных используя вышеизложенный подход

SQL> select *
  2   from SBT_QUOTE_HIST_Af2 a
  3   where PART_VALUE in (194);

153163 rows selected.

Elapsed: 00:00:05.15

Execution Plan
----------------------------------------------------------
Plan hash value: 2871801456

------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                    |   161K|    27M|   160   (7)| 00:00:02 |       |       |
|   1 |  PARTITION LIST SINGLE|                    |   161K|    27M|   160   (7)| 00:00:02 |   194 |   194 |
|   2 |   TABLE ACCESS FULL   | SBT_QUOTE_HIST_AF2 |   161K|    27M|   160   (7)| 00:00:02 |   194 |   194 |
------------------------------------------------------------------------------------------------------------



Statistics
----------------------------------------------------------
       4310  recursive calls
          1  db block gets
      35548  consistent gets
       2597  physical reads
          0  redo size
   14482002  bytes sent via SQL*Net to client
     112781  bytes received via SQL*Net from client
      10212  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
     153163  rows processed		

Необходимо отметить, что основная идея состоит в том что бы выбирать данные используя параллельные работы (PARALLEL THREADS), поэтому тут приведена статистика ТОЛЬКО для одной из работ (THREAD). Поэтому количество выбранных рядов разнится , 1807513 в первом тесте и 153163 во втором. Т.е. если будет использовано 12 параллельных работ, то будет выбрано тоже самое количество рядов при том же времени в 5 секунд , вместо 4 минут, используя индекс.
добавлено: 22 ноя 15 просмотры: 975, комментарии: 1



К ВОЗМОЖНОСТИ ПАРАЛЛЕЛЬНОГО ВЫПОЛНЕНИЯ КОМАНДЫ DELETE В ORACLE И MYSQL/MARIADB БЕЗ ИСПОЛЬЗОВАНИЯ PAR

ORACLE имеет возможность выполнение параллельного выполнения команды DELELE используя HINT PARALLEL. Но из практического опыта выполнения данной команды следует его ограниченность в динамическом выполнении (количество параллельных Threads) и достаточно больших системных издержек,а также возможностях динамического управления количества удаляемых рядов за один "прогон", т.е. это можно осуществить, но каждый раз необходимо читать таблицу полностью ( run FULL table scan), что приводит драматически к потери производительности.

В данной работе будет рассмотрен общий подход и идеи как можно выполнять независимое параллельное удаление рядов из таблицы, причем количество параллельных потоков выполнения (Threads) можно динамически менять ,в зависимости от количества удаляемых рядов/строк и скажем требуем времени выполнения всей работы вцелом или же ресурсов выделяемых на выполнения работы.

Весь подход предполагает три основных этапа: на основании логики удаления тех или иных рядов строится SELECT по выборы идентификации этих рядов которые нужно удалить; создается и заполняется таблица с идентификационными номерами удаляемых рядов из основной таблицы и запускают параллельно работы по удалению рядов.

1. Допустим нам надо удалить определенное количество рядов из таблицы с деталями , скажем ОRDER_DTL, на основании удаленных уже рядов из мастер таблицы ORDER_MASTER. Связь между таблицами идет через ORDER_ID атрибут. Понятно, что взаимосвязь между таблицами ODRED_MASTER & ORDER_DTL есть "One-To-Many". Каждая из таблиц обладает колонками ORDER_MASTER_ID & ODRED_DTL_ID с уникальными значениями в них.

Здесь и далее мы будем использовать ORACLE и потом я сделаю дополнения связанные с MySQL/MariaDB.
Создаем таблицу ORDER_DTL_DEL:

create table ORDER_DTL_DEL
as
select x.*,dense_rank() over (order by block_id) block_group_nbr from (
select rownum rn, ri, (dbms_rowid.rowid_block_number(ri)||'-'||dbms_rowid.rowid_relative_fno (ri)) Block_code
from
(
select /*+ PARALLEL (ORDER_DTL,4) */ rowid ri, from ORDER_DTL
where ORDER_ID not in (select ORDER_ID from ODRED_MASTER)
)
) x
order by 4;

Т.о. мы создали таблицы, где у нас будут находится ROWID рядов, что нам надо удалить из таблицы ORDER_DTL, код блока где этот ряд находится (Block_code) и номер разбивки всех блоков. Основная идея последнего состоит в том , что бы при выполнении команды DELETE в разных Thread у нас не попадались удаляемые ряды из одного и того же блока. Тем самым мы значительно уменьшаем вероятность Block's Lock.

2. Для удаления рядов из ORDER_DTL таблицы по ROWID мы использовали следующий код, позволяющий делать BULK DELETE некими "порциями" по 100000 рядов. Число 100000 величина чисто эмпирическая в данном случае и была выбрана из конкретной задачи по удалению. Основанная идея ее состоит в том чтобы найти оптимальное время, в пересчете на время удаления одного ряда. То что эта величина существует было видно из эксперимента. Удаление скажем, 50000 рядов и 200000 в пересчете на на одно удаление дала величину большую , чем при удалении 100000. Все зависит от железа, а точнее скорости CPU, наличие и количества индексов на таблице ORDER_DTL, распределения рядов по блокам и в особенности блоков индексов. Очевидно, что если надо производить удаление рядов в конце дня, я данные вставлялись в таблицу в течении дня и естественным образом отсортированные по времени, то чтение и UPDAET index block требует совсем другого времени, чем если бы данные были отреставрированные другим образом. И более того, прежде чем UPDATE index BLOCK, база данных (ORACLE) должна отсортировать новые значения по блокам и потом уже работать с обновлением INDEX block. Время сортировка является величиной явно не линейной, т.е. сортировка 100000 рядов и 50000 рядов не в два раза больше, посему и можно найти некую эмпирическую оптимальную величины для BULK DELETE.
Итак вот этот код:
Create or replace procedure ORDER_DTL_DEL(THR Number, THR_SIZE Number)
as
TYPE t_id IS TABLE OF ROWID
INDEX BY BINARY_INTEGER;


v_id t_id;

CURSOR c1
IS
select ri from ORDER_DTL_DEL
where block_group_nbr > (thr-1)*THR_SIZE
and block_group_nbr <= (thr)*THR_SIZE;

BEGIN
OPEN c1;

LOOP
FETCH c1 BULK COLLECT INTO v_id LIMIT 100000;

EXIT WHEN v_id.COUNT = 0;

FORALL i IN v_id.FIRST .. v_id.LAST
DELETE FROM ORDER_DTL
WHERE ROWID = v_id (i);

COMMIT;
DBMS_OUTPUT.put_line (
'deleted rows: '
|| v_id.COUNT
|| ' at: '
|| TO_CHAR (SYSDATE, 'yyyy/mm/dd hh24:mi:ss'));
END LOOP;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('encountered an exception: ' || SQLERRM);
END;
/
3. Далее идет код , который позволяет удалить 9800000 рядов используя 10 параллельно выполняемых работ.
Declare
jName VARCHAR2(2);
BEGIN
FOR i IN 1..10 LOOP
jName := 'J' || TO_CHAR(i);
dbms_job.submit(jName, 'PLSQL_BLOCK',
'begin ORDER_DTL_DEL('||TO_CHAR(i)||', 85000); end;',
start_date=>SYSDATE);
END LOOP;
DBMS_JOB.RUN(jName);
COMMIT;
END run_parallel;
/
Здесь величина 85000 была получина из Мах(block_group_nbr)/< среднее число рядов в блоке >/< количество Threads(10) >. Понятно что величина 85000*< среднее число рядов в блоке >*< количество параллельных Threads(10) > должно быть больше или равно количеству удаляемых рядов (9800000).

Замечания.
1.В данных примерах используются методы хранения данных в блоках для ORACLE ( rowid) и ORACLE system package (dbms_rowid, dbms_job), чтов общем-то не обязательно. Если брать в качестве rowid -> ODRED_DTL_ID и иметь уникальный индекс на эту колонку, а в качестве block_group_nbr -> значение хаш функции на ODRED_DTL_ID ( к примеру mod(ODRED_DTL_ID, 10)+1 ) то вполне данный подход можно использовать для баз данных MySQL/MariaDB.
2. Для параллельного выполнения можно легко приспособить JAVA.
3. Наилучшая производительность будет достигнута если до того как выполнять работу по DELETE рядов, сделать все индексы UNUSABLE, удалить ряды и перестроить индексы используя опцию PARALLEL.
добавлено: 27 ноя 14 просмотры: 2313, комментарии: 0



Один подход к распараллеливанию приложений использующих Oracle базу данных


Описание проблемы.

Имеется огромная база данных D1, где хранятся данные загружаемые каждый день и все таблицы имеет партиции по дням. Также имеется другая база данных D2, где хранятся обработанные данные из первой базы для создания различных репортов и представления обработанных данных через DUI или в Интернете.
В данном конкретном случае надо загрузить из таблиц D1 данные в таблицу на D2, причем бизнес логика программы предполагает, в своей основе, построения цикла и выборку данных из JOIN двух таблиц из D1. Ну, скажем что-то в таком виде:
	
Parameter1:=<1>
LOOP  < parametr2= 1,2,3,4,5,6>
INSERT INTO T_SUM
….
SELECTGROUP FUNCTION
...
FROM T1@D1 T1, T2@D1 T2
WHERE
…
T1.PROD_ID=T2.PROD_ID
AND 
T1.TRADE_TIME  between parameter1 and pararameter1+pararameter2
…..
;
pararameter1:= pararameter1+pararameter2
…….
COMMIT;
End LOOP;

Т.к. прежде всего у нас имеется сложный JOIN двух таблиц на D1, функция группировки и INSERT на D2, то Oracle наверняка перепишет SQL на две части: сначала выберет данные из таблицы T1, потом из таблицы T2 и потом уже будет уже делать JOIN этих таблиц на D2 с использованием функции группировки. Хотя таблицы Т1 и Т2 не так уже и велики, Т1 где-то порядка 15 млн. рядов и Т2 порядка 7 млн. рядов все же копирование данных на D2 займет время. Но, самое большое время занимает сам по себе JOIN, который в данном конкретном случае возвращает порядка 90 млн. записей, которые получаются используя HASH JOIN двух таблиц, да еще нужно для всех этим 90 млн. записей применить групповую функция, что приводит к дополнительному использованию различного вида сортировок и как результат активному использованию сегментов временного табличного пространства. В конкретном примере 25 Gb временного табличного пространства не хватало для обработки всех данных и программа вываливалась, проработав более 20 часов.

РЕШЕНИЕ.

Основная идея решения данной проблемы состоит в разделении нашей апллкации на отдельные независимые части и обработка данных параллельно – APPLICATION PARTITIONS. Кроме того, разделив данные на независимые части мы уменьшаем объем данных для JOIN и обработки групповой функцией, мы еще и можем использовать намного эффективнее SORT AREA в памяти ORACLE. Так, если, мы будем обрабатывать все в одной TREAD нам будет доступно только 2 Gb КЭШи, если же будем использовать 4 THREAD, каждая из которых будет отдельной сессией в ORACLE, нам будет доступны 8 Gb кэш. Это все как раз и дает огромный выигрыш в скорости обработки данных и получении результата в течении 15 минут, без использования дискового пространства для организации временных сегментов и проведения сортировки и хеширования данных.
Как видно из самого JOIN у нас идет установление связи между двумя таблицами по ключу PROD_ID в виде

T1.PROD_ID=T2.PROD_ID

Это ключевой момент как мы можем разделить нашу аппликация на независимые части. Сделаем на базе D2 таблицы T1_HASH и T2_HASH , скажем с 4 партициями каждая, по ключу на колонку PROD_ID. Партиции будут типа HASH.
CREATE TABLE T1_HASH
(PROD_ID  NUMBER,..
)
PARTITION BY HASH(PROD_ID)
(PARTITION  TREAD_01,
PARTITION  TREAD_02,
PARTITION  THREAD_03,
PARTITION THREAD_04);

CREATE TABLE T2_HASH
(PROD_ID  NUMBER,..
)
PARTITION BY HASH(PROD_ID)
(PARTITION TREAD_01,
PARTITION TREAD_02,
PARTITION THREAD_03,
PARTITION THREAD_04);

Запускаем два независимых процесса , параллельно, и копируем данные из Т1@D1 и T2@D1 во вновь созданные таблицы T1_HASH@D2 и T2_HASH @D2. Пример приминения HASH PARTITION приведен на Рис. 1.:

Картинка с другого сайта.

Где ряды со значением PROD_ID = 1,5,9 …. будут, находится в партиции THREAD_01, а ряды со значение PROD_ID=3,7,11 …в партиции THREAD_03 и т.д.
Т.о. мы можем теперь стартовать 4 процесса параллельно, как показано на Рис. 2.

Картинка с другого сайта.

Сама аппликация будет использовать номер THREAD как параметр, скажем в таком виде:
Parameter1:=<1>
LOOP  < parametr2= 1,2,3,4,5,6>
INSERT INTO T_SUM
….
SELECTGROUP FUNCTION
...
FROM T1_HASH PARTITION (THREAD_&1)  T1, T2_HASH PARTITION (THREAD_&1) T2
WHERE
…
T1.PROD_ID=T2.PROD_ID
AND 
T1.TRADE_TIME  between parameter1 and pararameter1+pararameter2
…..
;
pararameter1:= pararameter1+pararameter2
…….
COMMIT;
End LOOP;


ДОПОЛНЕНИЯ

1.Как было указанно выше, основной идей разделения аппликации на партиции была связка двух таблиц ,к примеру, по ключу PROD_ID. Очевидно, что при JOIN ДВУХ и более таблиц по большему числу колонок необходимо хеширование по всем ключам связи. К примеру, если мы имеем JOIN в виде
……..
T1.PROD_ID=T2.PROD_ID
AND
T1.CUSTOMER_NAME=T2.CUSTOMER_NAME
………
То таблицы на D2 будут выглядеть следующим образом:
CREATE TABLE T1_HASH
(PROD_ID  NUMBER,
CUSTOMER_NAME VARCHAR2,..
)
PARTITION BY HASH(PROD_ID, CUSTOMER_NAME)
(PARTITION  TREAD_01,
PARTITION TREAD_02,
PARTITION THREAD_03,
PARTITION THREAD_04);

CREATE TABLE T2_HASH
(PROD_ID  NUMBER,
CUSTOMER_NAME VARCHAR2,..
)
PARTITION BY HASH(PROD_ID, CUSTOMER_NAME)
(PARTITION TREAD_01,
PARTITION TREAD_02,
PARTITION THREAD_03,
PARTITION THREAD_04);

2. Идея Application Partition для JOIN , скажем 3 таблиц, при связывании их по разным ключам приведена на Рис.3

Картинка с другого сайта.
Рис.3
Здесь исходный JOIN был в виде
INSERT INTO T_SUM
….
SELECTGROUP FUNCTION
FROM T1@D1 T1, T2@D1 T2,T3@D1 T3
WHERE
…
T1.PROD_ID=T2.PROD_ID
AND 
T2.ORDER_ID=T3.ORDER_ID
AND
TRADE_TIME  between parameter1 and pararameter1+pararameter2
…..
;
pararameter1:= pararameter1+pararameter2
….

Если у кого-то возникнут любые вопросы по конкретному применению этой идеи буду рад помочь.
добавлено: 29 мар 13 просмотры: 1222, комментарии: 0



Некоторые подходы к упорядочиванию данных (Часть2)

В данной части я буду продолжать излагать некоторые идеи по упорядочиванию данных , которые я использую на практике и которые дают неплохие результаты с т.з. производительности огромных баз данных (VLDB).
Для начала небольшое отступление от основной темы , но затрагивающая подходы далее излагаемые. Я был слегка удивлен работаю более внимательно с построением хэш партиций в Oracle, точнее с тем как Oracle назначает то или иное хэш значение использую свою внутреннюю, скрытую от пользователя, хэш функцию. Оказывается, что Oracle функция ORA_HASH(), работает совсем не так чем функция используемая Oracle при построении хэш партиций. Я имею ввиду следующую ситуацию. К примеру если у вас есть 10 различных значений и вы используете 10 значений для хэш, то очевидно, что каждому значению хэш от 1 до 10 у вас будет соответствовать единственное изначальное значение. Так и работает функция ORA_HASH(). Но так не работает внутренняя функция Oracle, при назначении хэш значения при использовании хэш партиций. И у вас могут попасть в одну партицию различные значения, которые по логике вещей и при использовании функции ORA_HASH() туда попадать не должны. Что я обнаружил, так это то, что внутренняя функция Oracle работает, так как мне хотелось бы, только со значениями кратными степени 2, т.е. со значениями типа 2,4,8,16 и т.д.

В связи с этим я дополнил некоторые подходы , описанные мною в предудущем сообщении. Прежде всего я попробовал упорядочить данные не только по двум колонкам ,а по всем колонкам используемых в индексе и создал количесво партиций кратным 2.
Итак
CREATE TABLE SQH_AF_HASH
(
 ...
 UNDER_SEC_SYM VARCHAR2(10),					
 EXER_PRICE NUMBER(10,3),					
 CLASS_SYM VARCHAR2(10),				
 EXPR_DATE DATE,					
 PUT_CALL_CODE CHAR(1),
 PART_KEY VARCHAR2(20)
)
PARTITION BY HASH (CLASS_SYM,UNDER_SEC_SYM,EXER_PRICE,EXPR_DATE)
PARTITIONS <NUM_PART_HASH>;

Где под <NUM_PART_HASH> я использовал различные значения степени 2 для изучения "степени" упорядоченности данных. Как и ранее я использовал SQL , приведенный в 1-ой части, для исследования распределения данных по блокам. Результаты шриведенны ниже в таблице Табл. 6

Табл. 6

Тест Кол-во блоков небходимых прочитать
Естественно Упорядоченные 17473
NUM_PART_HASH = 256 1561
NUM_PART_HASH = 128 2962
NUM_PART_HASH = 64 5427
Вообщем почти линейная зависимость.

Далее я хочу предложить и описать идею как бы объединяющую Подход N3 из предыдущей части и вышеописанный. Часто бывает так, в данном случае вопрос вытек из реальной ситуации, что надо как-бы работать и быстро получать результаты под двум возможным критерия. Один, связан с обработкой данных по целому классу и второй получения данных в деталях. Говоря конкретно, необходимо с одной стороны агрегировать данные по UNDER_SEC_SYM, а с другой получить более конкретные данные в деталях ну типа что-то вроде этого
select  * from
 SQH_AF_*****					
where UNDER_SEC_SYM='SPY'
and trade_date='12-DEC-2012'					
and EXER_PRICE=142					
and CLASS_SYM ='SPY'					
and EXPR_DATE ='16-MAR-2013'					
and PUT_CALL_CODE='C';

Т.к. данные загружаются ежедневно, объемы я уже указывал, то была предложена некая динамическая модель упорядочивания данных. Для загрузки данных для конкретного дня создаются новые партиции по дню и символу
(UNDER_SEC_SYM) с хэш сабпартициями (SUBPARTITION) по (CLASS_SYM,UNDER_SEC_SYM,EXER_PRICE,EXPR_DATE). Причем можно использовать два динамических подхода. Первый состоит в том, что для загрузки по дню и символу будут выделены специальные партиции для ПЕРВЫХ заранее определяемых, скажем 10 из соображений даже не знаю каких, символов, а все остальные будут загружаться в отдельную партицию. Либо возможен другой подход. Определяется ПОРОГОВОЕ значение количества загружаемых рядов по символам и потом прежде чем создать партиции для загрузки определяется список символов попадающих под ОПРЕДЕЛЕННЫЙ критерий выбора, для выделения отдельной партиции. Скажем кол-во рядов для символа должно быть не менее 10 млн рядов на при загрузки текущего дня. Понятно , что такой список символов и их количество будет варьироваться дня ото дня. Ну, к примеру, что-то вроде этого, на TRADE_DATE='DD-MON-YYYY':

UNDER_SEC_SYM КОЛ-ВО рядов
---------- ----------
AAPL 92419782
SPY 53049416
IWM 25982964
GLD 18934107
ISRG 16941749
NFLX 16090382
PCLN 13994121
GOOG 13992741
FAS 13090826
DIA 11048924
AAFF 10048924
а все остальные будут загружаться в отдельную партицию.

Для работы с такой конструкцией нам понадобится, прежде всего,вспомогательная таблица, позволяющая соотносить для каждого TRADE_DATE и символа используемую партицию.
CREATE TABLE AF_LIST_HASH_REF
   (TRADE_DATE date ,
   UNDER_SEC_SYM  VARCHAR2(10 BYTE),
   PART_KEY NUMBER(16));

В рассматриваем примере данные в таблице имеют вид:
Табл. 7

TRADE_DATE UNDER_SEC_SYM PART_KEY
12/24/2012 SPY 2012122401
12/24/2012 SPX 2012122402
12/24/2012 SLV 2012122403
12/24/2012 SLB 2012122404
12/24/2012 SSO 2012122405
12/24/2012 OTHER 2012122450

И соотвественно основная таблица:
 CREATE TABLE SQH_AF_R_HASH
(
  TRADE_DATE              DATE                  NOT NULL,
  SQH_UID      NUMBER(20)            NOT NULL,
  .....
  UNDER_SEC_SYM           VARCHAR2(10 BYTE),
  CLASS_SYM               VARCHAR2(10 BYTE),
  EXPR_DATE               DATE,
  EXER_PRICE              NUMBER(20,2),
  PART_KEY 					NUMBER(16)
  )
  partition by range(part_key)
SUBPARTITION BY HASH (CLASS_SYM,UNDER_SEC_SYM,EXER_PRICE,EXPR_DATE)
(PARTITION P_20121224_1 VALUES LESS THAN (2012122402)
SUBPARTITIONS 64,
PARTITION P_20121224_2 VALUES LESS THAN (2012122403)
SUBPARTITIONS 16,
PARTITION P_20121224_3 VALUES LESS THAN (2012122404)
SUBPARTITIONS 16,
PARTITION P_20121224_4 VALUES LESS THAN (2012122405)
SUBPARTITIONS 16,
PARTITION P_20121224_5 VALUES LESS THAN (2012122406)
SUBPARTITIONS 16,
PARTITION P_20121224_OTHER VALUES LESS THAN (2012122451)
SUBPARTITIONS 256
);

Количество SUBPARTITIONS , понятное дело, чисто субъективное и опирается исключительно на опыт и некие соображения описанные выше.
Очевидно, что в партиции зашифрована дата поступления данные (TRADE_DATE) и некий порядковый номер UNDER_SEC_SYM в таблице AF_LIST_HASH_REF куда вставленны ряды в порядке убывания количества рядов по UNDER_SEC_SYM, в данном примере под вышеописанный критерий для данного дня 24-DEC-2012 попало 5 UNDER_SEC_SYM.
Перед загрузкой следующего дня 25-DEC-2012 данные анализируютса, автоматически с помощью специальный утилиты, и данные в таблице AF_LIST_HASH_REF перед загрузкой основной таблицы имеют вид:
Табл. 8

TRADE_DATE UNDER_SEC_SYM PART_KEY
12/24/2012 SPY 2012122401
12/24/2012 SPX 2012122402
12/24/2012 SLV 2012122403
12/24/2012 SLB 2012122404
12/24/2012 SSO 2012122405
12/24/2012 OTHER 2012122450
12/25/2012 AAPL 2012122501
12/25/2012 SPY 2012122502
12/25/2012 IWM 2012122503
12/25/2012 GLD 2012122504
12/25/2012 ISRG 2012122505
12/25/2012 NFLX 2012122506
12/25/2012 PCLN 2012122507
12/25/2012 GOOG 2012122508
12/25/2012 OTHER 2012122550

И соотвественно команды добавления партицию имеют вид:
alter table SQH_AF_R_HASH ADD PARTITION
 P_20121225_1 VALUES LESS THAN (2012122502)
SUBPARTITIONS 16;

alter table SQH_AF_R_HASH ADD PARTITION
 P_20121225_2 VALUES LESS THAN (2012122503)
SUBPARTITIONS 16;
.....
.....
alter table SQH_AF_R_HASH ADD PARTITION
 P_20121225_8 VALUES LESS THAN (2012122509)
 SUBPARTITIONS 16;
 
 alter table SQH_AF_R_HASH ADD PARTITION
 P_20121225_OTHER VALUES LESS THAN (2012122551)
SUBPARTITIONS 256; 

Для удобства работы было создана функция:
 CREATE OR REPLACE function F_af_list_hash(P_UNDER_SEC_SYM varchar2,P_trade_date date) return NUMBER IS
v_PART_KEY NUMBER :=NULL;
BEGIN
select PART_KEY into v_PART_KEY from AF_LIST_HASH_REF
where 
trade_date=P_trade_date
and
UNDER_SEC_SYM=P_UNDER_SEC_SYM;
  return v_PART_KEY;
exception
  when no_data_found then
     return to_char(P_trade_date,'YYYYMMDD')||to_char(50);
end;
/

Тогда основной SQL по выборки данных из основной таблицы будет выглядеть
 
 WITH af AS (select F_af_list_hash ('SLB', '24-DEC-2012') bb from dual)
  SELECT а.*
     FROM SQH_AF_R_HASH а, af
    WHERE PART_KEY =af.bb
    and ... 
	and ...
	...
 ;
 

возможно, конечно , создать полноценную LOOK-UP таблицу вида
create table AF_LIST_HASH_REF1 as 
select /*+ PARALLEL(a , 8) */ distinct trade_date,UNDER_SEC_SYM,
( CASE UNDER_SEC_SYM
    WHEN 'SPY' THEN 2012122401
    WHEN 'SPX' THEN 2012122402
        WHEN 'SLV' THEN 2012122403
            WHEN 'SLB' THEN 2012122404
                WHEN 'SSO' THEN 2012122405
                ELSE 2012122450
                END 
                ) part_kye
from SQH_AF_R_HASH a;

И пополнять таблицу AF_LIST_HASH_REF1 каждый день. В этом случае можно создать VIEW
CREATE OR REPLACE FORCE VIEW AF_F1 as 
select
 b.TRADE_DATE             
 ,a.SBT_QUOTE_HIST_UID     
....
..... 
 ,a.CLUSTR_CODE 
 ,b.part_key
from SQH_HIST_AF_R_HASH a, AF_LIST_HASH_REF1 b
where
a.part_key=b.part_key
;

В таком случае
select * from af_f1
where
UNDER_SEC_SYM='SJM'
and    TRADE_DATE='24-DEC-2012';

Дает EXPLAIN PLAN , где видно что мы БУДЕТ РАБОТАТЬ с ОДНОЙ партицией, а не с целой таблицей.
Plan
SELECT STATEMENT  ALL_ROWSCost: 75,206  Bytes: 1,720,834,802  Cardinality: 8,353,567  				
	5 HASH JOIN  Cost: 75,206  Bytes: 1,720,834,802  Cardinality: 8,353,567  			
		1 TABLE ACCESS FULL TABLE D03_SBT_OWNER.AF_LIST_HASH_REF1 Cost: 2  Bytes: 29  Cardinality: 1  		
		4 PARTITION RANGE SUBQUERY  Cost: 74,749  Bytes: 8,871,488,508  Cardinality: 50,121,404  Partition #: 3  Partitions accessed #KEY(SUBQUERY)		
			3 PARTITION HASH ALL  Cost: 74,749  Bytes: 8,871,488,508  Cardinality: 50,121,404  Partition #: 4  Partitions accessed #1 - #LAST	
				2 TABLE ACCESS FULL TABLE D03_SBT_OWNER.SBT_QUOTE_HIST_AF_R_HASH Cost: 74,749  Bytes: 8,871,488,508  Cardinality: 50,121,404  Partition #: 4  Partitions determined by Key Values

При таком подходе результаты теста,а именно "Кол-во блоков небходимых прочитать"=2636
добавлено: 24 мар 13 просмотры: 1199, комментарии: 0



Некоторые подходы к упорядочиванию данных(Часть1)

Работая с огромными объемами данных загружаемыми ежедневно в таблицу Datawarehouse мы сталкиваемся с крайне неприятной задачей связанной с производительностью обработки этих данных в дальнейшем. Создание индекса на те или иные колонки помогает этому, но не всегда в достаточной мере. Крайне простой пример иллюстрирующий это. Допустим в таблицы, ежедневно , загружается порядка 6-7 млрд. строк и дальнейшая обработка этой таблицы состоит в извлечении данных и , скажем, агрегировании их по тому или иному критерию. Мы здесь будем рассматривать проблему извлечения данных ,а не их дальнейшей обработки. Так вот, допустим у вас создан индекс для убыстрения извлечения данных и он оптимален, но надо выбрать, согласно индексу, скажем 30000 рядов из тех самых 6-7 млрд. строк. Что это означает? Это означает , что надо произвести, как правило, 30000 физических чтений (physical reads), что уже само по себе , с т.з. времени обработки данных, задачка трудоемкая. Рассматривая с этой т.з. задачку извлечения данных сразу же возникает идея. А как бы так сделать , что бы скажем вместо 30000 физических чтений сделать, ну, хотя бы 10000. Т.к. Oracle используя индекс читает данные по блочно, 30000 физических чтений означает , что у нас данные расположились так, что в каждом блоке находится по ОДНОМУ ряду необходимых нам данных. Если же мы каким-то образом УПОРЯДОЧИМ данные, что будет , скажем так, 3 ряда необходимых данных в блоке, то очевидно что нам надо будет сделать в 3 раза меньше физических чтений, т.е. 10000 physical reads. Указанный ниже подход как раз и является одним из подходов по УПОРЯДОЧИВАНИЯ данных для их быстрейшего извлечения.
Данные загружаемые в Datawarehouse, обычно упорядочены естественным образом, ПО ВРЕМЕНИ их поступления в базу данных. Наше же идея состоит в том, чтобы их бы каким-то образом упорядочить по ДРУГОМУ КРИТЕРИЮ, согласно их дальнейшего использования. Сразу же напрашивается простое логическое решение: загрузить данные в таблицу использую ORDER BY по колонкам, которые потом будут учувствовать в индексе, используемого для извлечения данных. Все хорошо, одна маленькая загвоздка, ORDER BY на таблице в 6-7 млрд. строк «сожрет» все ресурсы и будет работать крайне долго, если вообще отработает.
Для начала, хочу предложить SQL для проверки распределения рядов по блокам, в соответствии перечню колонок используемых в индексе.


select cn Num_of_rows_per_Block,count(*)  Num_of_Blocks from (					
select  DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) BLOCK_NUMBER, count(*) cn from
 SQH_AF_MIX  a					
where UNDER_SEC_SYM='SPY'					
and EXER_PRICE=142					
and CLASS_SYM ='SPY'					
and EXPR_DATE ='16-MAR-2013'					
and PUT_CALL_CODE='C'					
group by  DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid))					
group by cn					
order by 1;

Пример результата выборки:
Таб.1

Num_of_rows_per_Block Num_of_Blocks
1 8238 8238
2 3723 7446
3 1713 5139
4 881 3524
5 436 2180
6 213 1278
7 108 756
8 55 440
9 21 189
10 14 140
11 7 77
13 4 52

15413 29459
Очевидно, что SUM(Num_of_rows_per_Block(i)* Num_of_Blocks(i)) даст нам количество рядов которое выбирается из таблицы согласно фильтру.
where UNDER_SEC_SYM='SPY'					
and EXER_PRICE=142					
and CLASS_SYM ='SPY'					
and EXPR_DATE ='16-MAR-2013'					
and PUT_CALL_CODE='C'

Для нашего примера эта величина равна 29459.
А количество блоков которое необходимо при этом прочитать есть SUM( Num_of_Blocks(i)) и в нашем примере соответствует величине 15413.

Итак есть таблица загружаемая ежедневно огромным количеством данных, упорядоченных естественным образом ,по времени , и необходимо создать механизм который бы на этапе загрузки этих данных в таблицу упорядочивал эти данные в соответствии с индексом который будет использоваться в дальнейшем для основных аппликаций и репортов.
В дальнейшем для всех экспериментов я буду использовать одну и туже, в логическом плане, исходную таблицу, но упорядоченную различным образом и один и тот же SQL с фильтром указанным выше. Табл.1 показывает исходное распределение данных, т.е данных упорядоченных естественным образом, по времени.
Итак , исходная таблица имеет вид:
CREATE TABLE SQH_AF_MIX  
(
 ...
 UNDER_SEC_SYM VARCHAR2(10),					
 EXER_PRICE NUMBER(10,3),					
 CLASS_SYM VARCHAR2(10),				
 EXPR_DATE DATE,					
 PUT_CALL_CODE CHAR(1)
 );

Имеющая порядка 1 млрд рядов загруженных в порядке поступления, т.е. упорядоченная по времени и индекс
CREATE BITMAP INDEX SQH_AF_MIX_B2 ON SQH_AF_MIX
(UNDER_SEC_SYM , EXER_PRICE ,CLASS_SYM, EXPR_DATE, PUT_CALL_CODE)

Подход N1.
Упорядочивание по UNDER_SEC_SYM и EXER_PRICE используя хеширование и 1000 партиций
CREATE TABLE SQH_AF_1000
(
 ...
 UNDER_SEC_SYM VARCHAR2(10),					
 EXER_PRICE NUMBER(10,3),					
 CLASS_SYM VARCHAR2(10),				
 EXPR_DATE DATE,					
 PUT_CALL_CODE CHAR(1),
 PART_KEY VARCHAR2(20)
 )
 PARTITION BY HASH (PART_KEY)
PARTITIONS 1000;

Далее для работу с основной таблицей создаем вспомогательную таблицу:
CREATE TABLE AF_REF_PART
(
  UNDER_SEC_SYM  VARCHAR2(100 BYTE),
  EXER_PRICE     NUMBER(20,8),
  PART_KEY       VARCHAR2(30) , 
  CONSTRAINT AF_REF_PART_PK
  PRIMARY KEY
  (UNDER_SEC_SYM, EXER_PRICE)
  ENABLE VALIDATE
)
ORGANIZATION INDEX;

Вносим данные в таблицу, использую SQL
insert into AF_REF_PART(UNDER_SEC_SYM,EXER_PRICE,PART_KEY)
select UNDER_SEC_SYM,EXER_PRICE,UNDER_SEC_SYM||to_char(EXER_PRICE) from
(
select /*+ PARALLEL(a,8) */distinct UNDER_SEC_SYM,EXER_PRICE  from  SQH_AF_MIX  a
)
;

Строим VIEW позволяющее работать ТОЛЬКО со специфической партицией в зависимости от значения UNDER_SEC_SYM и EXER_PRICE:
create view SQH_AFV as
select * from SQH_AF_1000
where (UNDER_SEC_SYM,
EXER_PRICE,
PART_KEY) in (select * from AF_REF_PART);
[/REATE BITMAP INDEX SQH_AF_1000_B2 ON SQH_AF_1000
(CLASS_SYM, EXPR_DATE,  PUT_CALL_CODE)
  LOCAL;

Загрузка данных осуществлялась с.о :
INSERT INTO SQH_AF_1000
(.
UNDER_SEC_SYM ,
  EXER_PRICE ,
  PART_KEY  )
SELECT..
UNDER_SEC_SYM ,
  EXER_PRICE ,
  UNDER_SEC_SYM||to_char(EXER_PRICE)
FROM  SQH_AF_MIX  ;

В результате такого хэширования и разбивки данных по партициям получилось следующее распределние данных по блоком, согласно вышеописаннонного SQL:

Таб 2

Num_of_rows_per_Block Num_of_Blocks
1 59 59
2 78 156
3 74 222
4 116 464
5 140 700
6 179 1074
7 170 1190
8 198 1584
9 180 1620
10 216 2160
11 180 1980
12 202 2424
13 193 2509
14 159 2226
15 141 2115
16 100 1600
17 73 1241
18 69 1242
19 64 1216
20 37 740
21 27 567
22 21 462
23 19 437
24 15 360
25 8 200
26 7 182
27 7 189
28 5 140
29 3 87
31 2 62
32 2 64
35 2 70
36 1 36
40 1 40
41 1 41

2749 29459
Подход N2.
Тоже самый подход , что и Подход N1, но количество hash партиций равно 100 , вместо 1000.

Таб 3

Num_of_rows_per_Block Num_of_Blocks
1 1594 1594
2 1215 2430
3 978 2934
4 707 2828
5 566 2830
6 473 2838
7 376 2632
8 323 2584
9 232 2088
10 164 1640
11 97 1067
12 86 1032
13 65 845
14 36 504
15 23 345
16 17 272
17 10 170
18 7 126
19 4 76
20 7 140
21 3 63
22 5 110
23 4 92
24 3 72
26 2 52
28 1 28
33 1 33
34 1 34

7000 29459

Подход N3.
Идея данного подхода состоит не столько в нахождении в преимуществе по отношения к Подходу 1 или2 , сколько в иллюстрации другой возможности упорядочивания данных и возможно для других целей он как раз и будет наиболее целесообразным. Итак создаем таблицу с 3200 LIST PARTITIONS на UNDER_SEC_SYM. Т.о. мы распределили все данные как бы по «разным корзинам». Понятное дело что делаем последней партицию DEFAULT , где можем хранить до поры до времени данные вне существующего перечня UNDER_SEC_SYM. Можно, скажем раз в неделю, проверять наличие данных в DEFAULT партиции и дополнять существующий перечень партицией новыми.
Таб 4

Num_of_rows_per_Block Num_of_Blocks
1 8391 8391
2 3724 7448
3 1727 5181
4 873 3492
5 420 2100
6 207 1242
7 114 798
8 50 400
9 18 162
10 14 140
11 6 66
13 3 39

15547 29459
Результаты данного эксперимента по упорядочиванию данных приведены ниже в Табл. 5. SQL для теста был использован следующего вида:
select  * from
 SQH_AF_*****					
where UNDER_SEC_SYM='SPY'					
and EXER_PRICE=142					
and CLASS_SYM ='SPY'					
and EXPR_DATE ='16-MAR-2013'					
and PUT_CALL_CODE='C';

Табл. 5

Тест Кол-во выбранных рядов Физических Чтений Время Выполнения
Естественно Упорядоченные 29459 15592 00:35.2
Hash=1000(Подход N1) 29459 2757 00:04.3
Hash=100(Подход N2) 29459 7032 00:10.5
3200 UNDER_SEC_SYM(Подход N3) 29459 15678 00:35.4

Результаты теста показывают явное преимущество по упорядочиванию данных. Хотя Подход N3 и не показывает никаких преимуществ , для данного типа SQL, все же данный подход имеет интересное реальное использование. Его основными преимуществами являются:
1. Легкость дополнения новых партиций для новых UNDER_SEC_SYM
2. Экономиста время на построения индекса на UNDER_SEC_SYM для убыстрения выборки данных
3. Нет необходимости анализировать таблицу, что тоже экономит время
4. Явное преимущество чтения данных, т.к. за один физическое чтение (Physical Reads) ORACLE читает несколько блоков , в соответствии с INI ORACLE parameters, а не один в случае использования индекса.
добавлено: 17 мар 13 просмотры: 1372, комментарии: 0



Балансировка нагрузки( load balance) при использовании секционирования аппликаций Application Parti

Предлагается опробованный подход к обработки данных в Datawarhouse при работе с огромными массивами данных в таблицах и использовании параллельных работ (Application Partition) при их обработки.
Постановка задачи. Имеется несколько работ работающих параллельно и необходимо распределить объем работ между ними так, чтобы была соблюдена балансировка между параллельными работами. В данном случае под балансировкой понимается ВРЕМЯ окончания работы, чтобы оно было примерно одинаковое.

Здесь, пока, будет рассмотрен конкретный более простой пример, но основная идея будет показана воочию. Более сложные подходы я надеюсь осветить позже , когда покажу как можно РАСПАРАЛЛЕЛИВАТЬ сложные аппликации с использование различных подходов сегментирования таблиц (partitioning) в Oracle. Особенно это актуально при использовании Oracle Exadata Database Machine. Системные механизмы Oracle для распараллеливания слишком общи и имеют большой overhead. Знание же конкретных данных и конкретного приложения позволяет НАМНОГО эффективнее использовать идею распараллеливание и Oracle обладает всеми механизмами, чтобы такое осуществить на практике.

Итак, есть таблица загружаемая каждодневно в Datawarehouse. Даная таблица имеет partition по TRADE_DATE и subpartition by cluster_code. По сути дела таблица разделена по дню загрузки и данные по каждому дня сегментированы еще по определенному коду cluster_code представляющим из себя код внешней базы данных. Таких баз данных будет 30. Перегрузка из каждой база данных в Datawarehouse идет в течении дня независимо в свою subpartition, определяемой своим уникальным cluster_code. После загрузки запускается приложение, которое обрабатывает эти данные и загружает их в Datamart, в определенную таблицу, используя данные независимо по каждому cluster_code . Datamart является отдельной базой данных находящейся в другом регионе и было принято решение для обработки данных использовать 4 работы которые обрабатывают данные с Datawarhouse и загружают обработанные данные в таблицу на Datamart, которая имеет partition by thread_nbr. Причем было выбрана идея использовать только 4 работы, а не , скажем 30, для того чтобы не перегружать сервера Datawerehouse and Datamart. Возникает задача как распределить работу по обработки 30 subpartitions между 4 thread(job, etc) так чтобы они закончились примерно в одно и тоже время.
Была предложена следующая идея. Создается таблица QEUE_SUBCLUST_RUN с колонками

Table_name,
Subpartition_name,
Bytes,
Thread_nbr (1,2,3,4)
Status (NULL or R)
Step_nbr (1, …., 30)

Далее используя SQL на Datawarehouse

Insert (Table_name, Subpartition_name, Bytes)
select segment_name, partition_name,bytes from dba_segments
where partition_name like '%20130214%'
and segment_name='SQH'
order by 3 desc;

заполняются первые 3 колонки таблицы QEUE_SUBCLUST_RUN. На первом шаге назначаются первые 4 subpartition для обработки 4-мя Threads, изменяется STATUS from NULL -> ‘R’ and STEP from NULL-> 1. Далее после окончанию первой из 4-х освободившейся Thread , к примеру, Thread 4, назначается ей следующая по счету subpartition , идущая под номером 5 и изменяется STATUS from NULL -> ‘R’ and STEP from NULL->2 and Thread_nbr from NULL -> 4. И т.д., по мере освобождения Thread ей назначается следующая из очереди и ведется контроль выполнения (Thread_nbr ,STATUS, Step_nbr).

Пример QEUE_SUBCLUST_RUN:


Table name SUBPARTITION_NAME BYTES Thread_NBR Status Step
SQH D_CSQH_20130211_CL_C_09 7314866176 1 R 1
SQH D_CSQH_20130211_CL_C_04 6174015488 2 R 1
SQH D_CSQH_20130211_CL_C_01 4630511616 3 R 1
SQH D_CSQH_20130211_CL_C_22 4227858432 4 R 1
SQH D_CSQH_20130211_CL_C_10 3825205248 4 R 2
SQH D_CSQH_20130211_CL_C_11 3422552064 2 R 2
SQH D_CSQH_20130211_CL_C_21 3154116608 4 R 3
SQH D_CSQH_20130211_CL_C_15 2952790016 1 R 1
SQH D_CSQH_20130211_CL_C_03 2885681152 2 R 3
SQH D_CSQH_20130211_CL_C_18 2818572288 3 R 2
SQH D_CSQH_20130211_CL_C_06 2684354560 NULL NULL NULL
SQH D_CSQH_20130211_CL_C_17 2684354560 NULL NULL NULL
SQH D_CSQH_20130211_CL_C_07 2348810240 NULL NULL NULL

….
…..

К примеру, Thread_nbr=4 выполнила работы по обработке «..CL_C_22», «..CL_C_10», «..CL_C_21» и т.д.
Это полностью динамическая система, т.к. каждый день может иметь различное кол-во данных и последовательность subpartition будет разная. Второе, окончание работ не всегда зависит от объема исходных данных, и данная система позволяет динамически отслеживать окончание работы и назначение новой. Третье. Данная система легко позволяет rerun работу если у нас произошел сбой скажем не всего приложения ,а только одной из Thread, скажем Thread_nbr=4. Для этого достаточно сделать truncate partition nbr=4 в таблице на Datamart и обработать только subpartitions, с которыми работала Thread_nbr=4.
добавлено: 17 фев 13 просмотры: 1674, комментарии: 2



Многoмерное секционирование (partition) данных в Oracle

Одна из мощнейших опций Oracle состоит в возможности секционирования данных в таблице (partitioning), что позволяет существенно увеличить производительность баз данных и построить более оптимально и красиво логическую и физическую структура таблицы и индексов. При этом в Oracle есть существенное ограничение. Вы может создать ТОЛЬКО два уровня секционирования ( partition – subpartition). Иногда этого не достаточно и хотелось бы создать более глубокое секционирование данных, скажем 3-х уровневое, 4-х и более.
Я покажу на конкретном примере как легко можно это обойти. Данный подход позволяет создать ЛЮБОЕ кол-во вложенного секционирования, ограниченного лишь нашим воображением , целесообразностью и ограничениями самого Oracle.

ПОСТАНОВКА ЗАДАЧИ.
Есть процесс репликации данных из множества баз данных одновременно в одну, при этом данные должны быть хранится по дням недели и разделятся для дальнейшего использования по времени. Количество данных в течении одного дня может достигать более 25 млрд. строк, потому для дальнейшей обработки желательно из разделять по времени поступления в таблицу. Данная задача реальная. Обобщенно говоря, данная система должна поддерживать торговую площадку на бирже в режиме 24/7. Огромный объем есть следствие электронных торгов , а именно сохранения торговых котировок (Trading Quotes) в течении дня. Т.о. говоря языком Oracle у нас есть:

TRD_DB_ID -> 1,…,30 – ID базы данных из которых идет репликация. Таких баз данных , в данном примере, 30
DAY_OF_WEEK_ID -> 1,..5 – ID дня недели
TIME_SLICE_ID -> 1,…,48 – ID периода времени. Все сутки делятся на получасовые интервалы.

Основанная идея состоит в том чтобы разделить огромный объем данных на независимые части, дабы позволить производить репликацию параллельно (TRD_DB_ID), использовать опять же параллельно и независимо перекачку данных в DATEWARHOUSE (TIME_SLICE_ID) и возможность поддерживать торговлю в течении всего дня и всей недели. Огромное удобство такого разделения заключается еще в том , что можно делать рестарт любого процесса перекачки независимо от других и выполнять команду TRUNCATE независимо и если надо параллельно для нескольких партиций одновременно, что существенно сократит время подготовки базы данных для последующего использования.
Налицо проблема. Необходимо 3 уровня секционирования данных. Oracle позволяет делать только 2. Если посмотреть, как Oracle создает partition table with subpartition, на физико-логическом уровне, то легко увидеть, что каждая subpartition есть не что иное, как отдельный объект. И основная идея работы с таким объектом состоит в том , что разработчик не задумывается с какой именно subpartition/partition он работает. Для этого ему достаточно указать в
SELECT …. FROM …
WHERE
значение ключей subpartition/partition и ORACLE автоматически будет работать ТОЛЬКО с этой subpartition/partition и не будет использовать ПОЛНОГО сканирование таблицы (FULL TABLE SCAN). Очевидно, что сканирование 25 млрд. рядов не реально в принципе, если скажем такую таблицу, использует аппликация, работающая в реальном времени.

РЕШЕНИЕ
Будем использовать ту же идею, что и Oracle. Будем создавать LIST партиции, где в основе ключа будет комбинироваться значение всех трех независимых переменных. Скажем
PART_22340 – будет означать, что в данной партиции мы собираемся хранить данные для 2-го дня недели, из базы данных #23 , для 40-го периода времени суток (19:30 – 20:00). Очевидно, что надо создать N=30*5*48=7200 партиций.

CREATE TABLE SQH_AF
(C_1 ,
…..
C_N,
PART_KEY INTEGER(10)
)
partition by list (part_key)
(
partition PART_10101 values (10101),
partition PART_10102 values (10102),
partition PART_10103 values (01013),
…..
partition PART_53047values (53047),
partition PART_53048 values (53048)
);

Далее создаем вспомогательную таблицу
CREATE TABLE AF_REF
(
PART_KEY INTEGER(10),
DAY_OF_WEEK_ID INTEGER(1),
TRD_DB_ID INTEGER(2),
TIME_SLICE_ID INTEGER(2)
)
;
CREATE UNIQUE INDEX AF_REF_U1 ON AF_REF
(DAY_OF_WEEK_ID , TRD_DB_ID , TIME_SLICE_ID );
;
Которая заполняется очевидным образом
*****************************************************

PART_KEY DAY_OF_WEEK_ID TRD_DB_ID TIME_SLICE_ID
_________ _______________ _________ ______________
10101 1 1 1
10102 1 1 2
…..
53047 5 30 47
53048 5 30 48
********************************************************************************
И далее строится VIEW
create or replace view SQH_AF_V
as
select
a.C_1,
a.C_2,

a.C_N,
b.PART_KEY, b.DAY_OF_WEEK_ID, b. TRD_DB_ID, b.TIME_SLICE_ID

from SQH_AF a, AF_ref b
where
a.part_key=b.part_key;

Понятно, что INSERT разумно производить, зная предварительно значение всех переменных для определения PART_KEY. А вот SELECT, используя SQH_AF_V.
Ниже приведен EXPLAIN PLAN Показывающий , что Oracle будет работать с конкретной партицией а не со всей таблицей

select * from SQH_AF_V
where
TRD_DB_ID =1
and
TIME_SLICE_ID =4
and
DAY_OF_WEEK_ID =1;


Plan
SELECT STATEMENT ALL_ROWS Cost: 1 Bytes: 216 Cardinality: 4
5 NESTED LOOPS Cost: 1 Bytes: 216 Cardinality: 4
2 TABLE ACCESS BY INDEX ROWID TABLE DBA_ADMIN.AF_REF Cost: 1 Bytes: 46 Cardinality: 1
1 INDEX UNIQUE SCAN INDEX (UNIQUE) DBA_ADMIN.AF_REF_U1 Cost: 0 Cardinality: 1
4 PARTITION LIST ITERATOR Cost: 0 Bytes: 32 Cardinality: 4 Partition #: 4 Partitions determined by Key Values
3 TABLE ACCESS FULL TABLE DBA_ADMIN.SQH_AF Cost: 0 Bytes: 32 Cardinality: 4 Partition #: 4 Partitions determined by Key Values
добавлено: 03 фев 13 просмотры: 8031, комментарии: 1