Оперативное сжатие ARCHIVELOG на NTFS

Если Oracle работает под Windows и в ARCHIVELOG режиме (в мирное время не существует достаточных оснований для работы БД в режиме NOARCHIVELOG), и архивлоги лежат в FAST_RECOVERY_AREA на NTFS, то для папки ARCHIVELOG можно установить NTFS-атрибут COMPRESSED.

Архивлоги обычно сжимаются в 2 раза, нагрузка на сервер не возрастает (а иногда снижается, поскольку у процессора обычно есть резерв, а у дисков -- нет).

При этом нельзя делать размер REDO-логов больше 8GB -- когда лог заархивируется, то к файлам больше 8GB на NTFS сжатие не применяется.
Установив атрибут COMPRESSED на папку ARCHIVELOG можно указать размер FAST_RECOVERY_AREA в 2 раза больше, чем объём диска, на котором она расположена.
Это параметр DB_RECOVERY_FILE_DEST_SIZE
http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams065.htm#REFRN10235

Не устанавливайте атрибут COMPRESSED на папку BACKUPSET -- эффект будет хуже, чем если выполнять сжатие средствами RMAN -- (compressed backupset) как написано в посте Оптимальное нарезание файлов бэкапов (откроется в новом окне)

Компрессия файлов ARCHIVELOG (как для BACKUPSET) ожидается в версии 12.

Oracle Spatial - это просто ! Решаем, где будем ужинать сегодня :-)

Начать работу со Spatial -- очень легко. Я потратил на это один день. Но это только "начать", модуль серьёзный, для серьёзных задач -- разбираться придётся глубоко.

Spatial -- пространственные данные (ГИС). Модуль позволяет работать с плоскими и трёхмерными объектами (точки, линии, многоугольники, поверхности и пр.) и выполнять над ними операции -- найти пересечения, найти ближайшие и пр.
Пространственные данные хранятся как тип MDSYS.SGO_GEOMETRY. База при этом не перестаёт быть реляционной
Для информации -- http://ru.wikipedia.org/wiki/Пространственная_база_данных

Я поясню на следующих примерах:

1. Найдем ближайшие рестораны, места развлечений, достопримечательности к трем точкам: дому, офису и заказчику. Координаты своих мест вы наберете сами -- или пользуйтесь моими дефолтными (для обзора модуля этого хватит).
2. Найдем все объекты внутри Садового кольца (изменив условие, получим ВНЕ).

Список объектов далее -- это только пример, этот список неполный и может быть устаревшим. Я собрал его всего лишь из двух источников в Интернете -- из двух KML файлов.

I. Пробуем под любым пользователем
Например, можем создать SPTEST
create user SPTEST identified by SPTEST default tablespace USERS;
grant connect, resource, create view, unlimited tablespace to SPTEST;

II. Выполняем скрипт
Скрипт здесь http://yaroslavbat.narod.ru/sql/spatial_test.sql
sqlplus SPTEST/SPTEST@ORTE_LOCALHOST @spatial_test

Создание таблиц завершено. Делаем отчёты:

III. Ищем ближайшие объекты к дому, офису, заказчику (по три ближайших объекта).

select * from SP_NEAREST_PLACES_V

Выборка отсортирована по типу стартовой точки, типу объекта и возрастанию расстояния в метрах

POINT_ TYP      PLACE_NAME              ADDRESS                 DISTANCE
------ -------- ----------------------- ----------------------- --------
1.Home Развлече Бутик "Конфаэль" на Про ул.Вавилова, д.66 стр.1 3364   
                Нескучный сад           Ленинский проспект      4242   
                Пушкинская набережная   Пушкинская наб.         4456   
       Ресторан Ресторан "Султан"       Орджоникидзе ул., д.3   3032   
                Ресторан "Скай Лонж"    Ленинский пр., д. 32а   3405   
                Кафе "Мята"             Лужнецкий проезд, д. 1А 5537   
2.Offi Развлече Фирменный магазин «Рот  2-й Новокузнецкий переу 895    
                Лужков мост             м. Третьяковская  напро 2076   
                Болотная площадь        Болотная площадь        2224   
       Ресторан Ресторан "Семь пятниц"  Воронцовская ул., 6     1117   
                Ресторан "Море времени" Б. Полянка., д. 51 А/9  1434   
                Ресторан "Балчуг"       Балчуг ул., д. 1        2121   
3.Cust Развлече Патриаршие пруды        Патриаршие пруды        476    
                Московский зоопарк      Б. Грузинская, 1        742    
                Салон приключений и под ул. Тверская, д.22а, ст 913    
       Ресторан Кафе "Воск"             ул. Красина, д. 7, стр. 51     
                Ресторан "China Club"   Красина ул., 21         181    
                Ресторан "Грандъ Алекса Тверская ул., д. 27/1   639    

IV. Получаем список объектов внутри Садового кольца

select * from SP_PLACES_INSIDE_V

Обратите внимание, что в тексте этого VIEW есть лишний DECODE. Но без него не работает. Похоже баг.

where decode (<FUNCTION>, 'TRUE', 'TRUE', 'FALSE', 'FALSE') = 'TRUE'

Исправьте в тексте запроса условие на  = 'FALSE' и получим список ВНЕ Садового кольца

to be continued...

Создаём свою агрегатную функцию

Мы можем разрабатывать собственные агрегатные функции.
Расскажу на реальном на примере:

Задача: посчитать суммарную годовую инфляцию по годам на основании месячных данных.
Статистические данные по инфляции приводятся в процентах по отношению к прошлому месяцу.

Штатные агрегатные функции не подходят, SUM -- суммирует, AVG -- суммирует и делит, нам нужна фукция MUL (multiplication), чтоб перемножить проценты.

Пишем функцию сами:

1. Создаем тип

create type MulImpl as object (
AccValue number,
static function ODCIAggregateInitialize (sctx in out MulImpl)
return number, -- инициализирует значение агрегата перед началом обработки

member function ODCIAggregateIterate (self in out MulImpl, value in number)
return number, -- обновляет значение агрегата для новой строки данных

member function ODCIAggregateTerminate (self in MulImpl, returnValue out number, flags in number)
return number, -- возвращает значение агрегата и завершает обработку

member function ODCIAggregateMerge (self in out MulImpl, ctx2 in MulImpl)
return number -- используется для поддержки параллельных вычислений агрегата, если параллелизма нет -- без неё всё равно не работает
);

2. Создаем тело типа

create or replace type body MulImpl is
static function ODCIAggregateInitialize(sctx in out MulImpl)
return number is
begin
  sctx := MulImpl(1);
  return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self in out MulImpl, value in number) return number is
begin
  self.AccValue := self.AccValue * value;
  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self in MulImpl, returnValue out number, flags in number) return number is
begin
  returnValue := self.AccValue;
  return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self in out MulImpl, ctx2 in MulImpl) return number is
begin
  self.AccValue := self.AccValue * ctx2.AccValue;
  return ODCIConst.Success;
end;

end;
/
Цветом выделены строки, выполняющие собственно расчёт

3. Создаем собственно функцию

create or replace function MUL (input number) return number deterministic aggregate using MulImpl;

Слово DETERMINISTIC означает, что результат функции зависит только от её аргументов и не зависит ни от чего вне функции (не обращается к системной дате, последовательностям, не читает БД и пр.) -- при одних и тех-же аргументах будет один и тот-же результат. Oracle попытается прокешировать эту функцию -- в некоторых случаях результат будет не вычислять, я брать из кэша.

Можно включить фразу PARALLEL_ENABLE (очень актуально для хранилищ), но сейчас у меня нет возможности протестировать массивный параллелизм.

4. Для тестирования создаём таблицу:

create table INFL (MON date, INFL_VAL number)
/

5. Заполняем
(данные взяты отсюда http://ukrstat.gov.ua/operativ/operativ2006/ct/cn_rik/isc/isc_u/isc_m_u.htm):

insert into INFL (MON, INFL_VAL) values (to_date ('01.01.2010', 'DD.MM.YYYY'), 101.8);
insert into INFL (MON, INFL_VAL) values (to_date ('01.02.2010', 'DD.MM.YYYY'), 101.9);
insert into INFL (MON, INFL_VAL) values (to_date ('01.03.2010', 'DD.MM.YYYY'), 100.9);
insert into INFL (MON, INFL_VAL) values (to_date ('01.04.2010', 'DD.MM.YYYY'), 99.7);
insert into INFL (MON, INFL_VAL) values (to_date ('01.05.2010', 'DD.MM.YYYY'), 99.4);
insert into INFL (MON, INFL_VAL) values (to_date ('01.06.2010', 'DD.MM.YYYY'), 99.6);
insert into INFL (MON, INFL_VAL) values (to_date ('01.07.2010', 'DD.MM.YYYY'), 99.8);
insert into INFL (MON, INFL_VAL) values (to_date ('01.08.2010', 'DD.MM.YYYY'), 101.2);
insert into INFL (MON, INFL_VAL) values (to_date ('01.09.2010', 'DD.MM.YYYY'), 102.9);
insert into INFL (MON, INFL_VAL) values (to_date ('01.10.2010', 'DD.MM.YYYY'), 100.5);
insert into INFL (MON, INFL_VAL) values (to_date ('01.11.2010', 'DD.MM.YYYY'), 100.3);
insert into INFL (MON, INFL_VAL) values (to_date ('01.12.2010', 'DD.MM.YYYY'), 100.8);
insert into INFL (MON, INFL_VAL) values (to_date ('01.01.2011', 'DD.MM.YYYY'), 101); 
insert into INFL (MON, INFL_VAL) values (to_date ('01.02.2011', 'DD.MM.YYYY'), 100.9);
insert into INFL (MON, INFL_VAL) values (to_date ('01.03.2011', 'DD.MM.YYYY'), 101.4);
insert into INFL (MON, INFL_VAL) values (to_date ('01.04.2011', 'DD.MM.YYYY'), 101.3);
insert into INFL (MON, INFL_VAL) values (to_date ('01.05.2011', 'DD.MM.YYYY'), 100.8);
insert into INFL (MON, INFL_VAL) values (to_date ('01.06.2011', 'DD.MM.YYYY'), 100.4);
insert into INFL (MON, INFL_VAL) values (to_date ('01.07.2011', 'DD.MM.YYYY'), 98.7);
insert into INFL (MON, INFL_VAL) values (to_date ('01.08.2011', 'DD.MM.YYYY'), 99.6);
insert into INFL (MON, INFL_VAL) values (to_date ('01.09.2011', 'DD.MM.YYYY'), 100.1);
insert into INFL (MON, INFL_VAL) values (to_date ('01.10.2011', 'DD.MM.YYYY'), 100); 
insert into INFL (MON, INFL_VAL) values (to_date ('01.11.2011', 'DD.MM.YYYY'), 100.1);
insert into INFL (MON, INFL_VAL) values (to_date ('01.12.2011', 'DD.MM.YYYY'), 100.2);
commit;

6. Проверяем функцию как агрегатную

select '31.12.' || to_char (trunc (MON, 'Year'), 'YYYY') as YEAR_NUM, to_char (round (MUL (INFL_VAL / 100) * 100, 1), '999G999D0') as ACCUM_INFLATION
from INFL
group by trunc(MON, 'Year')

YEAR_NUM   ACCUM_INFLATION
---------- ---------------
31.12.2010      109.1    
31.12.2011      104.6    

7. Проверяем функцию как аналитическую

select to_char (last_day(MON), 'DD.MM.YYYY') as RMON
, to_char (INFL_VAL, '999G999D0') as MONTH_INFLATION
, to_char (round ((MUL (INFL_VAL / 100) over (order by MON range unbounded preceding) )* 100, 1), '999G999D0') as ACCUMULATED_INFLATION
from INFL
order by MON

Во второй колонке инфляция текущего месяца, в третьей -- накопленная с начала выборки.

RMON       MONTH_INFL ACCUMULATE
---------- ---------- ----------
31.01.2010      101.8      101.8
28.02.2010      101.9      103.7
31.03.2010      100.9      104.7
30.04.2010       99.7      104.4
31.05.2010       99.4      103.7
30.06.2010       99.6      103.3
31.07.2010       99.8      103.1
31.08.2010      101.2      104.3
30.09.2010      102.9      107.4
31.10.2010      100.5      107.9
30.11.2010      100.3      108.2
31.12.2010      100.8      109.1
31.01.2011      101.0      110.2
28.02.2011      100.9      111.2
31.03.2011      101.4      112.7
30.04.2011      101.3      114.2
31.05.2011      100.8      115.1
30.06.2011      100.4      115.6
31.07.2011       98.7      114.1
31.08.2011       99.6      113.6
30.09.2011      100.1      113.7
31.10.2011      100.0      113.7
30.11.2011      100.1      113.8
31.12.2011      100.2      114.1


Если в таблицу загрузить все данные с сайта, то при подсчёте итогов за год мы увидим расхождения в долях процентов с нашей функцией по некоторым годам. Это может быть связано со следующими причинами:
1. Возможно, нам надо в расчётные строки (которые выделил синим)  включить функцию TRUNC или ROUND, чтобы не считать слишком точно.
2. Потому, что на сайте укрстата последняя строка -- это не перемножение месяцев, а декабрь к прошлому декабрю.
3. Менялась методика расчёта.

Подробно -- Oracle® Database Data Cartridge Developer's Guide
http://docs.oracle.com/cd/E11882_01/appdev.112/e10765/toc.htm
Главы 11 и 22
Также использовалась книга "Oracle9/R2. Разработка и эксплуатация хранилищ баз данных", Хоббс, Хилсон, Лоуенд

Не загнётся. Часть 2. Решение

"Архисложная" проблема "уложить 40 миллионов измерений в минуту" решается отказом от Первой нормальной формы БД.

Надо просто транспонировать исходную таблицу. Я попробовал в 200 раз

Там том же компьютере я уложил объём измерений одного часа (40 000 000 * 2 * 60 минут) за 12 минут.
При этом потенциал ускорения не исчерпан, не стал пробовать даже /*+APPEND_VALUES*/

На этот раз, можно уже с уверенностью утверждать, что в этой гипотетической задаче --"Оракл НЕ загнётся, болезный" -- я готов эти измерения и дома принимать, хранить, и обрабатывать.  :-)

Транспонированная таблица не слишком удобна для анализа, но, применив к ней прореживание и нормализацию, получим весьма удобное и плотное хранилище.

Для решения этой задачи, весьма вероятно, можно применять и подходы Big Data (а "сырые" поступившие измерения таковыми и являются).

http://www.oracle.com/us/technologies/big-data/index.html

Не загнётся !

Встретил интересное сообщение в дискуссии
Речь, скорее, идёт о системе типа той, что собирает данные с электросчётчиков в Германии. Несколько десятков миллионов штук, данные отправляются ежеминутно (!). Ораклом там и не пахнет -- загнётся, болезный. Отсюда -- http://www.cnews.ru/news/top/index.shtml?2012/07/06/495657

Последнее утверждение автора мне показалось неочевидным.

Проверим на модели... Будем использовать мой десктоп, выделим Ораклу 1ГБ оперативы, датафайлы на одном диске, редологи -- на другом. Всё остальное по дефолту.

Легенда.
Имеем сервер, на которой поступают данные телеметрии (в нашем случае -- результаты измерений потребляемой мощности, с частотой 1 минута). В Германии 80 млн жителей. Автор пишет, что счетчиков там десятки милиионов штук. Будем считать, что 40 миллионов, что означает по одному счётчику на каждых двух жителей, включая младенцев.

Для заполнения таблиц будем использовать следующую процедуру. Добавляем 10 миллионов строк, измеряем время (многократно, усредняем)

procedure F is
  T timestamp with local time zone default systimestamp;
begin
  for i in 1..10000000
  loop

    insert into MEASUREMENTS1 (SNAPSHOT_TSZ, METER_ID, MEASUREMENT_VALUE)
    values (i, T, 500);
--    insert into MEASUREMENTS2 (SNAPSHOT_ID, METER_ID, MEASUREMENT_VALUE)
--    values (1, i, 500);

  end loop;
  commit;
exception when others then
  rollback;
  raise;
end;

Тест 1. Дефолтная таблица, причем без первичного ключа

create table MEASUREMENTS1
    (SNAPSHOT_TSZ       timestamp (0) with local time zone not null,
     METER_ID           number not null,
     MEASUREMENT_VALUE  number not null)
  pctfree 10 storage   (initial 65536 next 1048576)
  nocache monitoring noparallel logging

Результат -- 5 минут 25 секунд

Тест 2.
Приложение проектировали, данные старались уложить плотно, сессию записи вынесли в отдельную мастер таблицу, т.е. храним не длинный таймстэмп, а короткий ID сессии (минуты снятия показаний) загрузки. PCTFREE -- 0. Тип -- IOT, compress 1

create table MEASUREMENTS2
    (SNAPSHOT_ID        number not null,
     METER_ID           number not null,
     MEASUREMENT_VALUE  number not null,
     constraint MEASUREMENTS2_PKIOT primary key (SNAPSHOT_ID, METER_ID))
organization index compress 1 pctthreshold 50
pctfree 0 storage (initial 32M next 32M)
noparallel logging

Результат -- 6 минут. Это больше, чем в прошлом тесте, но у нас есть первичный ключ, плюс данные лежат плотно, за счёт COMPRESS 1. Плотно -- значит читаться будут быстрее.

Т.о. 40 млн записей будут добавятся за 24 минуты, а нам надо обеспечить 1 минуту, т.е. ускорить процесс в 24 раза.

Но пробовал я это на обычном десктопе с двумя винчестерами, без параллелизма и с 1 ГБ памяти. Причем диски не сильно и напрягались, вообще не слышно. Параллелизма не было, одно ядро процессора -- 100%, второе в айдле.

По моим ощущениям (предположениям), Exadata с таким объемом справится. И добавлять измерения, и прореживать, и отчеты строить, и прогнозы. Ведь писать в датафайлы надо всего лишь со скоростью 10 МБ в секунду (нетто). В чем же тут проблема ? Что должно загнуться ?


В системах подобного рода (SCADA) также используется "прореживание" данных: сегодня нам нужны минутные срезы, завтра -- пятиминутные. Через год -- часовые.
Одна строка измерений во второй таблице занимает 16 байт. В нашем гипотетическом примере за год это будет 305 ТБ БЕЗ "ПРОРЕЖИВАНИЯ".
Без прореживания -- это много.
А с прореживанием можно сократить объем раз в сто или больше. А это уже совсем немного.
Плюс, в Exadata есть гибридная колоночная компрессия.

Как итог, мне трудно согласиться с автором что оракл там "загнётся, болезный"
Подобная задача на Оракле вполне решаема, и всё это можно сделать компактно и аккуратно -- может быть даже и без Exadata.

Для особых скептиков -- посмотрите презентацию CERN
http://canali.web.cern.ch/canali/docs/Compressing_VLDS_Oracle_UKOUG09_LC_CERN.ppt

UPDATE
Есть решение для ускорения работыhttp://yaroslavbat.blogspot.com/2012/07/2.html