ЧАВО

A: Рассмотрим организацию, которая получает до 1 Gb писем в день. И хочет иметь оперативный архив глубиной 3 месяца. В этом случае мы рекомендуем выделить под Дозор-Джет 2 сервера:

• Сервер разбора почты. Здесь существенны требования к частоте процессора и объему дисковой подсистемы. Допустимо использовать Intel (linux) сервер.

• Сервер СУБД. Рекомендуем машину класcа Sun Fire, 1Gb оперативной памяти, дисковый массив (raid). 

Это общие слова по архитектуре системы. Если Вы выдержали наши рекомендации, то существуют следующие два варианта: письма хранятся в  СУБД, выделенной только под эту задачу, или СУБД, выполняющей также и другие задачи. 

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

A:  Прочитайте общее описание схемы данных. Описание важнейших параметров(формат xls), часть из них Вам придется изменить. В целом, установка схемы данных выглядит  так: вы правите поставлеямый в составе дозор-джет файл config.sql.template, сохраняете eго под именем config.sql. Сама установка выглядит так:

sqlplus /nolog @create-all 

Но перед ее выполнением обязательно прочитайте руководство по установкеа также новости последнего релиза.

A:  Во первых постараюсь ответить зачем это нужно. Дело в том, что мы храним письма в oracle в объектах типа lob. У  объектов этого типа в  отличии от объектов других типов минимальный размер данных не блок базы данных, а chunk. Выберем большой chunk - будет работать быстро, но много места пропадет. Выберем маленький - медленно будет работать. 

На основе опытной эксплуатации нам удалось выяснить что у средний размер писем в БД - 8kb. Так ли это у Вас вы можете проверить сами:

Теперь можно посмотреть сколько места у вас теряется из-за особенностей хранения писем:

Первый запрос - реальный объем писем

SQL>select sum(dbms_lob.getlength(text_body)) from text_part_t1.

Второй запрос сколько места занято в БД

set serveroutput on;

begin

dba_utils.show_space(p_segname => 'TEXT_BODY_L1', p_type

=> 'LOB');

end;

Вышеуказанные дествия можно проделывать для самих писем (таблица message, сегмент MESSAGE_L1), или для текстовых представлений писем (таблица text_part_t1, сегмент TEXT_BODY_L1)

A: Я написал для этого специальный скрипт, del_messages.sql. Проверьте версию 

-- $Id: del_messages.sql,v 1.3 2003/05/23 11:17:21 dsvolk Exp $

Пользоваться им очень легко:

sqlplus smap/smap

SQL> @del_messages.sql 

Проверьте дату самого раннего письма в БД:

SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24 MI';

SQL> select min(timestamp) from message;

MIN(TIMESTAMP)
-----------------
22-MAY-2003 17 21

Его отличия от стандартной утилиты таковы:

• Скрипт удаляет письма по дате физического попадания в БД, не проверяя никаких меток или прав доступа. Т.е. Вы указываете, сколько дней хранятся письма в БД в переменной скрипта days_ago. Значение 30, обозначает, что все письма, пришедшие за 30 дней до дня запуска скрипта будут удалены. Если Вы запустите скрипт 23 мая 2003 года в 15 часов 16 минут, то будут удалены все письма попавшие в БД до 23 апреля  15 часов 16 минут.

• Используется специально создаваемый для этой цели rollback segment, rb_big. Это ускоряет процесс удаления писем. Проверьте если он у Вас в БД.

SQL> SET transaction USE rollback segment rb_big;
SET transaction USE rollback segment rb_big
*
ERROR at line 1:
ORA-01534: rollback segment 'RB_BIG' doesn't exist

Если вы получили такую ошибку, то  однократно выполните такое действие:

CONNECT sys/&sys_passwd@&smap_tns

create public rollback segment rb_big storage (initial 50M next 50M maxextents unlimited);

alter rollback segment rb_big online;

• Скрипт производит commit каждый 5 тысяч записей. См. параметр commit_num. Я думаю, что это число Вам не надо менять.

Что скрипт не делает :

• Не пишет какие письма он удалил.

Как автоматизировать этот процесс:

• Если у Вас Unix: возьмите shell скрипт del_messages.sh Исправьте имя пользователя и пароль на Ваши. Удалите письма этим скриптом. Проверите дату. Поставьте этот shell скрипт в cron. Убедитесь, что он работает из cron.

A: Что бы оценить сколько места у Вас в БД можно воспользоваться скриптом free_space.sql (тоже что показывает монитор). 

TABLESPACE_NAME                                          Mb
------------------------------ ----------------------------
SMAP13_DICT                                             255
SMAP13_INDEXES                                          700
SMAP13_MESSAGES                                       2,600
SMAP26                                                  430
SMAP26_DICT                                              82
SMAP26_INDEXES                                          235
SMAP26_MESSAGES                                       3,050
SMAPD1                                                  440
SMAPD_DICT                                               82
SMAPD_INDEXES                                           235
SMAPD_MESSAGE1                                          200

Можно также воспользоваться более продвинутым вариантом мониторинга tabspacen.sql c псевдографикой :)). Здесь дается распределением места в датафайлах и даются доп характеристики табличного пространства.  

 
Name                 File Name                                     Size                 Used
-------------------- --------------------------------------------- -------------------- --------------------
SMAP_SERGEY_MESSAGE  /jet2/DVP/oradata/smap_sergey_message_01.dbf         2,048,000K             972,800K
SYSTEM               /jet/oracle/oradata/DVP/system01.dbf                   409,600K             369,856K
TEMP                 /jet/oracle/oradata/DVP/temp01.dbf                   1,024,000K                  72K
TEMP                 /jet2/DVP/oradata/temp02dvp.dbf                        512,000K                 136K
TOOLS                /jet/oracle/oradata/DVP/tools01.dbf                    204,800K                 456K

Tablespace definitions

Name                             Init             Next %Inc              Min              Max Stat Percent full
-------------------- ---------------- ---------------- ---- ---------------- ---------------- ---- ------------------------------
DIMITRI                        40,960           40,960   50                1              505 OnL  |***********                 |
DIMITRI_TMP                    40,960           40,960   50                1              505 OnL  |
DRSYS                          65,536           65,536   50                1    2,147,483,645 OnL  |*                           |
EUL                            40,960           40,960   50                1              505 OnL  |                            |
MPB                         2,097,152        2,097,152    0                1    2,147,483,645 OnL  |                            |
MPB_DICT                      106,496          106,496    0                1    2,147,483,645 OnL  |*****                       |
MPB_INDEXES                 5,242,880        5,242,880    0                1    2,147,483,645 OnL  |**********                  |
MPB_MESSAGES               52,428,800       52,428,800    0                1    2,147,483,645 OnL  |*******                     |
PERFSTAT                       40,960           40,960   50                1              505 OnL  |*************************** |
RBS                           524,288          524,288   50                8            4,096 OnL  |****                        |

Но если Вы удаляете записи из БД, этот скрипт продолжает показывает старые данные. Это потому, что после удаления записей, место освобождается в таблицах, а не в табличных пространствах. Чтобы приблизительно оценить, сколько писем еще сможет поместиться в БД выполните следующую операцию:

smap13@DVP8.1.7> analyze table message compute statistics;

Table analyzed.

smap13@DVP8.1.7> set serveroutput on;
smap13@DVP8.1.7> exec dba_utils.show_space('MESSAGE');
Free Blocks :329
Total Blocks :1280
Total Bytes :10,485,760
Unused Blocks :484
Unused Bytes :3,964,928
Last Used Ext FileId :28
Last Used Ext BlockId:2569
Last Used Block :796 

Free Blocks - сколько блоков Oracle свободно в таблице (1 блок - 8192 байта)

Total Blocks - сколько всего 

Unused Blocks - блоки в которых еще никогда не было записей.

Запишите эти числа. Посчитайте кол-во писем в БД. 

smap13@DVP8.1.7> select count(*) from message;

COUNT(*)
----------
63610

Теперь оценим что 1 письмо занимает 

 select (1280 - 329 - 484 )*8192/63610 from dual 

(1280-329-484)*8192/63610
-------------------------
60.1424933

байт. Значит мы можем еще принять примерно 

select (329 + 484)*8192/60 from dual;

(329+484)*8192/60
-----------------
111001.6

писем.

Надо понимать что это грубая оценка, основанная на предположении о равномерности типов писем в будущем . Т.е. примерно 111 тыс писем. 

Возвращаясь к свободному месту в табличном пространстве. Если его нет вообще, то даже при регулярном удалении писем и перестройке индексов (!) нельзя  исключить ситуации, когда Вам придет писем чуть больше чем Вы ожидали. Поэтому, правильной ситуацией является когда 5% от размера табличного пространства  свободно.  

A: Практика показывает что, в порядка частоты возникновения проблем:

A: При установке системы сразу есть возможность указать, хотите ли Вы выполнять полнотекстовый поиск по типам индексов. При это можно выбирать из двух типов индексов - индекс с использованием Russian Context Optimizer ver 4.0 (RCO) и обычный Context (Oracle Text)  индекс. Давайте разберемся, чем же они различаются и чего это стоит. 

Основное отличие -  RCO позволяет производить поиск и хранение индекса с учетом морфологии русского языка.   Реализован RCO как фильтр в и терминологии Context Options.

SQL> set serveroutput on

SQL> analyze table text_part_t1 estimate statistics sample 10 percent;
SQL> exec dba_utils.show_space ('text_body_l1', user, 'LOB');
Free Blocks :0
Total Blocks :44800
Total Bytes :367,001,600
Unused Blocks :1038
Unused Bytes :8,503,296
Last Used Ext FileId :72
Last Used Ext BlockId:6409
Last Used Block :5364

SQL> select count(*) from text_part_t1;

  COUNT(*)
----------
      9617

RCO строил индекс 27 минут 25 сек, индекс занял 

SQL> exec dba_utils.show_space ('text_part_i1', user, 'INDEX');
============================================================
Space usage of Text Index: SMAP13.TEXT_PART_I1
============================================================
TABLE_NAME = DR$TEXT_PART_I1$I
TABLESPACE NAME = SMAP13_DICT
ALLOCATED BLOCKS = 2860
ALLOCATED BYTES = 23,429,120 ( 22.34Mb)
USED BLOCKS = 2857
USED BYTES = 23,404,544 ( 22.32Mb)
------------------------------------------------------------
TABLE_NAME = DR$TEXT_PART_I1$R
TABLESPACE NAME = SMAP13_DICT
ALLOCATED BLOCKS = 13
ALLOCATED BYTES = 106,496 ( 0.10Mb)
USED BLOCKS = 2
USED BYTES = 16,384 ( 0.02Mb)
------------------------------------------------------------
============================================================
TOTAL ALLOCATED BLOCKS = 2873
TOTAL ALLOCATED BYTES = 23,535,616 ( 22.45Mb)
TOTAL USED BLOCKS = 2859
TOTAL USED BYTES = 23,420,928 ( 22.34Mb) 

Запускаем умышленно не сложный запрос

1188 rows selected.

Elapsed: 00:00:00.43

Обычный запрос был построен за 4 мин 30 сек, и занял место 

SQL> exec dba_utils.show_space ('text_part_i1', user, 'INDEX');
============================================================
Space usage of Text Index: SMAP13.TEXT_PART_I1
============================================================
TABLE_NAME = DR$TEXT_PART_I1$I
TABLESPACE NAME = SMAP13_DICT
ALLOCATED BLOCKS = 4160
ALLOCATED BYTES = 34,078,720 ( 32.50Mb)
USED BLOCKS = 4152
USED BYTES = 34,013,184 ( 32.44Mb)
------------------------------------------------------------
TABLE_NAME = DR$TEXT_PART_I1$R
TABLESPACE NAME = SMAP13_DICT
ALLOCATED BLOCKS = 13
ALLOCATED BYTES = 106,496 ( 0.10Mb)
USED BLOCKS = 2
USED BYTES = 16,384 ( 0.02Mb)
------------------------------------------------------------
============================================================
TOTAL ALLOCATED BLOCKS = 4173
TOTAL ALLOCATED BYTES = 34,185,216 ( 32.60Mb)
TOTAL USED BLOCKS = 4154
TOTAL USED BYTES = 34,029,568 ( 32.45Mb)

Запускаем  запрос: 

31 rows selected.

Elapsed: 00:00:00.11

Число записей изменилось ! Это объясняется тем, что я создавал RCO нормализуя русский язык. 

Выводы:

RCO индекс создается медленнее чем обычный. Думаю, что разница все же составит не в 5 раз, а меньше в реальных условиях. По объему он соотноситься как 2/3 от стандартного и с ростом данных отрыв будет увеличиваться. Соотношение объемов данные/индекс  как 10/1 в реальных условиях, как мне кажется,  сохраниться и во многом зависит от словаря стоп-слов.  

Тем не менее, несмотря на время построения RCO индекса он предоставляет возможности, не воспользоваться которыми обидно и досадно. 

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

Тестирование происходило на машине  HP-UX alpha B.11.00 U 9000/800, 1 CPU 440 MHz,  HDD 2*36 Gb,  RAM 1 Gb.

Есть еще такие данные:

1. За 21 час было проиндексировано 377400, после постройки индекса он создавался 6 часов. 

2. 6,5 Gb объем текстового поля, число записей 147936, объем индекса при этом 5,6 Gb

A: У компании RCO действительно хорошая документация. Там есть все. Есть даже подготовленные скрипты. Но так кратко написано, что иногда хочется разобраться заново самому :((. 

A: По объему во времени удобно использовать файл  mstat.sql. Его вывод выглядит так:

smap13@DVP8.1.7> @mstat      

Date                        Count             Size Kb
------------------------- ------- -------------------
01/08/2003                      7            1,626.57
31/07/2003                   2024           99,487.54
30/07/2003                     12            1,232.17
29/07/2003                   2433           87,991.98
28/07/2003                    112           13,361.84
25/07/2003                      1              234.21
24/07/2003                    861           30,327.06
23/07/2003                      5                1.07
22/07/2003                      6                2.99
21/07/2003                     21              288.82
17/07/2003                      2              241.82
16/07/2003                      5                3.06
15/07/2003                   1004           48,863.03

13 rows selected.

С другой стороны может быть интересно найти самые большие письма по объему за всю историю БД. Это удобно выполнить с помощью скрипта topm.sql

smap13@DVP8.1.7> @topm

  Message Id Date                                  Size Kb
------------ ------------------------- -------------------
        1971 24/07/2003                           2,938.98
         893 15/07/2003                           2,788.63
        1970 24/07/2003                           2,788.63
        5439 31/07/2003                           2,788.63
        4401 29/07/2003                           2,788.63
        3035 29/07/2003                           2,788.63
        6438 31/07/2003                           2,788.63
         896 15/07/2003                           2,691.42
        4404 29/07/2003                           2,691.42

Первая колонка id письма, вторая - дата поступления в БД, третья - объем письма в Кб.

По умолчанию, показывается наибольшие 10 писем. Если Вам это не подходит, измените параметр topN в скрипте.

Вернее будет, как мне отобразить графически результаты запросов СМАП ?

A: Попробую показать на примере распределения писем по объему во времени.

1. Войти как sys, посмотреть в какое значение установлена переменная utl_file_dir 

SQL> conn sys/manager
Connected.
SQL> show parameter utl_file_dir

NAME TYPE VALUE
------------------------------------ ------- --------------------
utl_file_dir string /tmp

У меня в /tmp. Исправить это можно, установив utl_file_dir = <you_directory> в init.ora. Если хотите иметь возможность писать в произвольную директорию  поставьте там *. Это значит, что вы сможете писать в любую директорию, к которой имеет доступ пользователь oracle. 

Проверим:

bear% touch /tmp/text.slk
bear% 

Без ошибок. Ок. 

Установим пакет owa_sylk.

SQL>@owa_sylk.sql

....

Package created.

No errors.

Package body created.

No errors

Поправим gmstat.sql (модернизированная версия mstat.sql) 

define dir='/tmp'  -- Должна быть такая же как и utl_file_dir
define file='mstat.slk' -- имя файла куда писать результат

:sql := 'select trunc(m.timestamp) timestamp, count(*) mcount, sum(dbms_lob.getlength(value))/1024 msize from message m, message_label l 
where l.message = m.id and l.type = 1 group by trunc(m.timestamp) order by 1 asc';     -- А это сам запрос

Запускаем скрипт, полученный файлик mstat.slk (файлик на сервере СУБД !!)  выглядит так 

ID;ORACLE
P;FCourier New;M200
P;FCourier New;M200;SB
P;FCourier New;M200;SUB
F;C1;FG0R;SM1;G
F;C2;FG0R;SM0
F;C3;FG0R;SM0
F;C4;FG0R;SM0
...

Открываем его с помощью Excel, выделяем колонку дат и колонку объема писем, запускаем визард - вот результат