Как загубить проект БД (часть 1)

Павел Шендрыгайлов, независимый эксперт

Oracle Magazine RE - Март 2002

Трудишься много ты, да пользы в этом нет.
И. Крылов

Так уж получилось, что много раз меня приглашали на роль эксперта для оценки существующих и помощи в проектировании и разработке новых информационных систем. Часто я находил там какие-то полезные решения и интересные конструкции, но еще чаще было все наоборот. В каждом коллективе приходилось встречаться с людьми разных школ и с различными подходами к созданию ИС.

В различного рода технических журналах периодически появляются статьи с массой советов "как лучше:" и мы уже начали привыкать, что с завидной периодичностью нам говорят правильные вещи, в общем-то, об одном и том же, но немного разными словами. Любой психолог вам скажет, что при длительном воздействии однотипных раздражителей человек перестает на них реагировать. Поэтому я решил пойти от противного и рассказать не о том, как надо делать, а о том, как не надо делать. А заодно показать, к каким последствиям для системы в целом это приводит. Надеюсь, что кому-нибудь эта статья поможет не сделать тех типичных ошибок и не принять плохих решений, которые в ней описаны.

Кому-то эти ошибки покажутся мелочами, но если критичное приложение работает в конкурентном многопользовательском окружении, они прекращают быть мелочами. В лучшем случае они просто снижают общую производительность системы. Давайте будем экономными, и будем оставлять резервы производительности и под дальнейшее увеличение объемов данных, и под возможное расширение круга задач, решаемых системой.

В статье имеется некоторая раздробленность материала, но она обусловлена разнообразием проблем из различных стадий жизненного цикла реальных систем. Ограниченный объем журнальной статьи не позволяет пройти весь этот цикл целиком, тем более это было уже не раз сделано на страницах Oracle Magazine RE. Любая прикладная система является результатом работы большого количества специалистов различных областей. Но зачастую расплачиваться за плохую производительность сданной в эксплуатацию системы приходится одному администратору базы данных. Я повторю слова Адама Джефферсона из Oracle Corporation: "Идеальная команда настройки должна включать специалистов всех стадий жизненного цикла системы, использовать и согласовывать их индивидуальное мастерство, добиваясь целостного подхода к проблеме".

Но речь сегодня пойдет не только и не столько о настройке.

Проектирование и разработка.

Самые серьезные по своим негативным последствиям ошибки делаются на первых стадиях работы над проектом. Никакие модные теории и методики проектирования не приведут к успеху, если не выполнены следующие необходимые условия:

• в успешном завершении проекта заинтересовано высшее руководство компании, и оно готово предоставить все необходимые для его выполнения ресурсы как организационные, так и материальные;
• четко организовано руководство проектом, строго определен его объем и выработаны общие концепции;
• команда разработчиков обладает высокой квалификацией и способна продуктивно общаться с будущими пользователями системы.

Не так давно мне пришлось участвовать в проекте, в котором не было выполнено ни одно из этих условий. Но самое примечательное в этой "истории" то, что формальный руководитель проекта решил опустить за ненадобностью этапы выработки стратегии и анализа. После многочисленных споров и разговоров был разработан перечень работ для конкретных исполнителей и установлены сроки их исполнения. Полученный перечень проблем умещался на половине страницы и охватывал порядка только 10~20% от всего объема проекта. Это мотивировалось простотой и невысокой значимостью опущенных задач. С большим отставанием от намеченных сроков перечисленные задания были выполнены.

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

Отсутствие единой политики.

"Свято место пусто не бывает" - гласит народная мудрость. Если автоматизированная система не предоставляет полноценной возможности работникам подразделений эффективно выполнять свои функции, а IT-службы организации бездействуют, велика вероятность появления каких-либо сторонних приложений, реализующих эти функции. Эти приложения сделают либо работники этого подразделения (даже продвинутые студенты экономических специальностей знают, что такое MS EXCEL, VISUAL BASIC и ODBC), либо они найдут подобный продукт "на стороне".

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

Но теряется самое главное - интеграция, увеличивается количество времени, которое приходится тратить на выполнение дублирующейся работы. К этому добавляется еще один немаловажный фактор: чем больше серверов и прикладных систем функционирует в инфраструктуре предприятия, тем больше в ней потенциальных сбойных мест и тем выше вероятность выхода из строя всей системы как единого организма.

В качестве наглядного примера можно привести практически любой российский банк. Известно, что Центральный банк Российской Федерации очень ревностно относится к своей работе по контролю за деятельностью коммерческих банков. Существует громадное количество отчетов, которыми коммерческие банки регулярно обязаны "делиться" со своим контролирующим органом. Чтобы не заниматься реализацией интеллектуальной загрузки данных в свои "аналитические системы", ЦБ раздает свое программное обеспечение для ввода и первоначального контроля целостности данных. Крайне редко эти программы обладают возможностью "не ручной" загрузки данных.

Так как у ЦБ исторически отсутствовала единая политика автоматизации, то все эти программы реализованы в различных стилях и на разных платформах. Одна может формировать в результате работы текстовый файл, другая .dbf, третья .xls. Сами форматы этих файлов обычно не документированы, что не позволяет коммерческому банку самостоятельно построить интерфейс со своими системами для выгрузки требуемой информации.

К ЦБ можно добавить Пенсионный фонд, Налоговую инспекцию, Управление статистики, Государственный таможенный комитет, каждый из которых требует "свой кусочек информации" и обладает фискальными правами и возможностями L . В результате одна и та же информация, полученная из одной системы, печатается и по несколько раз набивается вручную [возможно с ошибками] в других подсистемах. Следует еще упомянуть об увеличении стоимости обслуживания разнородных "навязанных" систем, о снижение оперативности обработки информации и сложности поддержания целостности информации между такими несвязными подсистемами.

"Выступая на конференции "Цифровая экономика", организованной журналом BusinessWeek, председатель правления и главный исполнительный менеджер корпорации Oracle Ларри Эллисон сделал дерзкое и несколько странное заявление: "Чтобы не дать себя обогнать набирающему силу Интернет-бизнесу, компании должны избавиться от унаследованных от прежних времен информационных технологий и построить информационную инфраструктуру заново:Ваша проблема - весь тот хлам, который вы накопили за последние 20 лет"."[2]

Проблемы протоколов и интерфейсов.

Если вам не досталась девственно чистая в смысле автоматизации организация или если руководители бизнеса не разрешают осуществить совет Ларри Эллисона на практике, не отчаивайтесь , еще не все потеряно. Ну раз уж вещь нельзя выкинуть, надо подумать, как можно ее использовать. Волей неволей придется искать способы взаимодействия разнородных систем и их компонентов. Сразу огорчу вас, проектирование и реализация интерфейсов - дело очень неблагодарное и довольно сложное. И это обусловлено не только, и не столько, техническими [технологическими] средствами, с помощью которых они реализуются, а и сложностью нахождения компромисса между желаемой универсальностью и эффективностью работы интерфейсной системы.

Для реализации связи между автоматизированной банковской системой (Oracle7.3) и системой клиент-банк (Oracle8i) один из наших молодых разработчиков сделал небольшую подсистемку, которая, как он считал, должна была решить все возможные проблемы взаимодействия между Oracle'вскими прикладными задачами. Три таблички на каждом сервере БД (описания серверов, описания типов сообщений и вызовы их обработчиков и наконец, собственно сами тексты сообщений), на них несколько триггеров и парочка пакетов, реализующих внутренние механизмы. Желающий отправить сообщение пишет его в таблицу сообщений, указывает адресат и:по идее забывает об этом сообщении. Принимающая сторона, получив сообщение, по его типу вызывала процедуру обработки, которая выполняла обратное преобразование "строка - набор атрибутов" и совершала с ними требуемые действия.

Первоначально предполагалось, что "синхронизация" сообщений между серверами будет осуществляться с помощью DBMS_JOB, но, видно, у разработчика что-то не заладилось с job'ами. В результате передача сообщений была реализована на триггерах, в которых делался INSERT через DBLINK в удаленную таблицу [Вот так исчезла декларируемая независимость от работоспособности соседнего узла].

Чтобы исключить негативное влияние на взаимодействующие прикладные системы (сделать их транзакционно независимыми) формирование сообщений было оторвано от "бизнес-событий" и отдано UNIX'овскому cron'у, который с завидной методичностью перегружал систему бесполезной информацией. Естественно, полезная информация тоже передавалась, но ее как-то было незаметно на фоне бесполезной. А чтобы сильно не ломать голову, перед передачей новой порции суточной информации вся старая просто удалялась .

"Информативная" часть сообщения представляла собой один атрибут типа VARCHAR2(2000) в котором были конкатенированы символьные строки значений всех атрибутов, предваряемые наименованием атрибута и длиной его значения.

А теперь представьте себе какое-либо простенькое "финансовое сообщение" - платежное поручение или, к примеру, выписку по счету. И в той и в другой системе это сообщение будет представлено в виде одной или нескольких строк в связанных таблицах, примерное общее количество атрибутов в каждом документе около 20. Легко подсчитать количество избыточных действий по "сцепке" значений, преобразованию типов и вычислению LENGTH.

Когда разработчик уже на этапе отладки (!) столкнулся с необходимостью передачи документов, близких по размерам к критическим 2000 байт (минус вся служебная информация), казалось, что система уже близка к идеологическому краху. Однако было принято "логичное" решение, если сообщение не умещается в одной строке, давайте "размажем" его по нескольким. Естественно, это привело к разрастанию количества служебных атрибутов, усложнению процедур и функций, реализующих вызовы подсистемы.

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

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

Во время внедрения этой системы на сервере Oracle работало чуть меньше сотни пользователей. После внедрения система впала в коматозное состояние. Конструкции этого приложения заняли все "хитовые" места в списках Disk Intensive SQL и Buffer Intensive SQL . Периодические перегрузки (от overload) подсистемы ввода/вывода, параметр Buffer Hit Ratio, который обычно колебался около значений 98~99%, упал до рекордно низкой отметки - 50,625. Вследствие низкой эффективности и высокой частоты сеансов обмена предыдущая сессия не успевала отработать, а новая уже пыталась стереть эти строки, чтобы вставить новые. Это привело к серьезным блокировкам. Просчеты в политике блокирования в свою очередь вызывали нарушения целостности данных L .

Требовались неотложные меры по приведению базы данных в работоспособное состояние. Первое, что было сделано - снижена частота запусков сеансов передачи информации и увеличен размер буферного кэша. Это несколько снизило негативное влияние подсистемы на другие приложения. Но самое основное - требовалось полное перепроектирование всей системы интерфейсов.

Было принято решение о построении симметричной системы, основанной на использовании RPC. Для каждого типа сообщений существовала удаленная процедура, выполняющая на принимающей стороне требуемые действия. При временной недоступности удаленного узла планировщик заданий, построенный на основе DBMS_JOB, после истечения тайм-аута использовал ключевую информацию из журнала заданий для выборки параметров требуемых удаленной процедуре.

Таким образом, мы избежали взаимозависимости схем связанных приложений. Одновременно достигли достаточного уровня инкапсуляции. Приложению, использующему этот интерфейс, требовался только один вызов интерфейсной процедуры с передачей ей ключевой информации. Исчезли многочисленные трансформации данных. Появилась возможность гибкой настройки тайм-аутов для различных типов сообщений и реальная многоприоритетность. В случае перевода связанных подсистем на один сервер БД не требовалось глобальной реконструкции приложений, достаточно было в вызовах убрать '@dblink_name'.

Корректное уменьшение сложности приложения не только делает это приложение более эффективным, но и уменьшает вероятность появления ошибок.

Не делать лишних действий.

Что под этим понимается? Каждое ненужное действие вашего приложения увеличивает нагрузку на базу данных и увеличивает сетевой трафик. А в конечном итоге начинаются жалобы пользователей на медленную работу системы. А заканчивается это растущим внутренним сопротивлением персонала и нежеланием использовать ИС в работе.

Одна из моих знакомых программисток очень любила в триггере WHEN-NEW-FORM-INSTANCE делать EXECUTE_QUERY (клиентская часть системы была написана на Developer 2000). Де мол, пользователи глупенькие, они войдут в форму, не увидят своих записей и начнут их набивать заново:

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

SELECT rowid, purch, supplier, dogo,
    ddogo,dog_our, ddod_our, dog_supp,
       ddog_supp,       -- и еще бооооольшая куча столбцов:
  FROM zdogov
 WHERE EXISTS (SELECT *
             FROM ztlp   -- личная папка пользователя
            WHERE usr = user)
      AND idd IN (SELECT idd
                    FROM zlpd    -- договор в личной папке
                   WHERE usr = user
                     AND np IN (SELECT np
                                  FROM ztlp
                                 WHERE usr = user))
       OR
        user IN('OMTR','STANDART') -- "тестовые" пользователи
 ORDER BY ddogo DESC
SELECT STATEMENT
  SORT ORDER BY
    FILTER
      TABLE ACCESS FULL ZDOGOV
      INDEX UNIQUE SCAN PK_ZTLP
      NESTED LOOPS
        TABLE ACCESS BY INDEX ROWID ZTLP
          INDEX UNIQUE SCAN PK_ZTLP
        INDEX UNIQUE SCAN PK_ZLPD

И это даже в том случае, если пользователь хотел всего лишь ввести новый договор:

В триггере POST-QUERY на уровне формы тоже кое-какая прикладная логика (семь различных предложений SELECT, выбирающих наименования из связанных таблиц). Хотя эти выборки используют уникальные индексы, они выполняются для каждой отображаемой строки.

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

Немного спасает то, что если свойство Query All Records установлено в No, то Forms "выфетчивает" не все строки запроса, а порцию строк, равную Query Array Size этого блока. Ведь для выполнения запроса Oracle делает full scan большой таблицы, причем выбранные блоки данных таблицы, скорее всего ненадолго задержатся в кэше, при активной работе других пользователей они будут вытеснены оттуда. Сортировка результата запроса тоже не прибавляет скорости выполнения, особенно если после стадии фильтрации в результате запроса осталось достаточно большое количество строк.

Что можно сделать в данном случае для исправления сложившегося положения?

Во-первых, никогда без надобности не запускать EXECUTE_QUERY без заданных критериев поиска. Я согласен, что для тех пользователей, которые никогда не работали с Oracle Forms, поначалу работа формы в режиме запроса не всегда интуитивно понятна. Но для этого пишутся инструкции и устраиваются курсы обучения. К тому же существует много способов построения форм с программным управлением фразой WHERE блока, что позволяет значительно упростить работу пользователя.

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

• Query All Records. Определяет, должны ли выбираться все записи, удовлетворяющие критерию запроса, или будет выбрано количество записей, равное параметру Query Array Size блока. В подавляющем большинстве случаев при работе с большими объемами данных в форме не следует устанавливать его в Yes. Значение Yes обычно используется в формах, использующих вычисление итогов.
• Query Array Size. Определяет максимальное количество записей, одновременно выбираемое Forms'ом. Маленькое значение параметра увеличивает время отклика формы, а большое значение уменьшает полное время обработки, делая меньшее количество запросов к БД.
• Number of Records Buffered. Определяет минимальное количество строк, буферизуемых в памяти во время выполнения запроса в блоке. Все остальные записи буферизуются во временном файле на диске клиента. Большое значение увеличивает производительность работы формы со значительными объемами данных.
• DML Array Size. Определяет максимальный размер массива для вставки, обновления и удаления строк в БД в один момент времени. Большой размер уменьшает время обработки транзакций, снижая сетевой трафик с БД, но требует большего количества памяти. Оптимальный размер - количество строк, изменяемое пользователем в одной транзакции. Существуют некоторые ограничения на использование больших значений этого параметра [3].

Для более глубокого знакомства с проблемами производительности сети, связанными с Oracle, можно обратиться к [4], где вы найдете большое количество примеров и фактического материала.

В некоторых случаях можно попробовать обойти проблему слишком "тяжелых" POST-QUERY с помощью использования в форме обновляющего представления, построенного на соединении основной таблицы с ее родительскими. Для того чтобы это представление было обновляющим, необходимо, чтобы основная таблица являлась таблицей, сохраняющей ключи (key-preserved table). Это означает, что все столбцы первичного (или уникального) ключа присутствуют в представлении, их значения уникальны и NOT NULL в представлении.

Но следует очень осторожно применять данное решение, так как в этом случае при значительном снижении объема данных, которыми обмениваются клиент и сервер (особенно bytes received via SQL*Net from client), соответственно усложняется базовый запрос блока, что приводит к дополнительному вводу/выводу и увеличению объема требуемой сессии памяти.

Существует некоторое количество правил и ограничений, касающихся обновляющих представлений, но это уже тема другого разговора и для знакомства с ними лучше обратиться к документации Oracle. Там же можно познакомится с механизмом работы INSTEAD OF триггеров, которые могут пригодиться при работе форм с представлениями.

В заключение добавлю, что следует очень осторожно подходить к установке параметров атрибутов при построении формы, так как обновляемыми будут не все столбцы представления, а только относящиеся к таблице, сохраняющей ключи. Для необновляемых столбцов установите свойство Query Only в Yes. Проще всего выяснить, какие столбцы в представлении действительно являются обновляемыми можно с помощью простого запроса:

SELECT column_name, updatable, insertable, deletable
FROM user_updatable_columns
WHERE table_name = '&view_name';

Раз уж мы затронули тему форм и триггеров, стоит сказать еще несколько слов о POST-QUERY. Этот триггер разработчики часто используют для не совсем стандартных методов фильтрации записей в блоке. В триггере проверяется необходимое условие и, если оно истинно, выполняется RAISE FORM_TRIGGER_FAILURE. Таким образом, записи, для которых выполнилось условие, не отображаются в форме.

Но к этому способу надо подходить очень осторожно и, если есть такая возможность, заменять его обычной фильтрацией с помощью фразы WHERE блока. Конечно, все зависит от того, насколько эффективен будет этот запрос. Если велик процент отсеиваемых с помощью триггера записей, сервер Oracle будет заниматься лишней работой по получению результатов запроса, большую часть которых ваше клиентское приложение просто-напросто отсеет. Не стоит забывать и про нагрузку на сеть.

Если вы пользуетесь этой платформой, очень много интересных методов разработки эффективных приложений в Oracle Developer, способов их оптимизации, а также оригинальных решений при построении форм вы найдете у Peter Koletzke в [6].

Нарушение логической целостности данных.

Гораздо хуже обстоит дело с отчетами, которые часто используются для разного рода сверок и "крыжений". Во многих банковских системах вы найдете с десяток отчетов, единственная цель которых - предотвращение "логического" нарушения целостности данных. Чем хуже спроектирована система, тем больше вероятность возникновения таких ошибок.

Использование таблиц промежуточных итогов без наличия механизма "компрометации" потерявших актуальность данных тоже может приводить к печальным результатам. Ведь не всегда возможно оперативно пересчитывать агрегированные данные при изменении детальных данных. Часто эти довольно ресурсоемкие операции приходится откладывать до моментов невысокой загрузки системы, либо до возникновения реальной потребности в этих данных.

С появлением в последних версиях Oracle механизма материализованных представлений (materialized view) и аналитических функций этот класс задач стало несколько легче реализовать. Эти новые возможности сервера не только упрощают приложение, но и снижают его ресурсоемкость. Обязательно обратите внимание на наличие опции QUERY REWRITE, это, пожалуй, одно из самых ценных свойств материализованных представлений.

Литература:

• [1] Десять главных рекомендаций по настройке приложений. Адам Джефферсон, Главный консультант корпорации Oracle UK. 29 апреля 2000 г. Oracle Magazine RE.
• [2] Мнение Эллисона: выбрасывайте все это и начинайте снова. 29 апреля 2000 г. Oracle Magazine
• [3] Oraclea Forms Developer. Form Builder Reference, Volume 2. Release 6i. January, 2000. Part No: A73074-01.
• [3] Network Performance with the Oracle RDBMS. Roberto Zamora and Cameron Melvin. Oracle Corporation. IOUG-A Live! 98
• [4]Джемини Беллмюлле. Настройка SQL*Net с учетом особенностей основных сетевых протоколов. Русское издание Oracle Magazine №3(5) 1997г.
• [5] Oracle8i Application Developer's Guide - Fundamentals. Release 2 (8.1.6). Part Number A76939-01.
• [6] Peter Koletzke. An "Object-Oriented" Approach to Oracle Developer Forms and Reports 6.0. IOUG-A Live! 2000.
• [7] I/O Tuning with Different RAID Configurations. Note:30286.1.
• [8] Gaja Krishna Vaidyanatha, Quest Software Inc. Implementing Raid On Oracle Systems. Русскоязычный перевод доступен на http://www.oradba.com.ru
• [9] Juan Loaiza, Oracle Corporation. Optimal storage configuration made easy.
• [10] Configuring the Oracle Database with VERITAS Software and EMC Storage for Optimal Scallability, Mangeability, And Performance.
• [11] Steven Feuerstein, Bill Pribyl. Oracle PL/SQL Programming. Second Edition.
• [12] Adaptive Tablespace Management. Alex Tsukerman & Bhaskar Himatsingka, Oracle Corporation. IOUG-A Paper #712
• [13] Пейдж Вильям Дж. и др. Использование Oracle8/8i. Специальное издание.: Пер. с англ. - М.: Издательский дом "Вильямс", 2000.
• [14] Outage Prevention, Detection, And Repair. Lawrence To. Center of Expertise Worldwide Customer Support Oracle Corporation. October 1995.
• [15] List of Database Outages. Lawrence To. Center of Expertise e-Business Support Services Oracle Corporation. October 1999.

Часть 1 - Часть 2 - Часть 3