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

2002 г

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

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

Oracle Magazine RE - Апрель 2002

Когда представление хуже таблицы?

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

Один из разработчиков, с кем мне как-то довелось работать, однажды познакомился с механизмом представлений и безмерно его полюбил. Сказать, что он их использовал везде, значит, ничего не сказать. Разбираясь с какой-либо проблемой в работе системы, я находил целые "грозди" нанизанных друг на друга представлений. Вполне нормальным считалось в самом нижнем использовать START WITH с CONNECT BY, на следующем уровне - WHERE с кучей EXISTS и IN,.. ну уж на четвертом или пятом появлялась весомая фраза GROUP BY. Надо заметить, все это было в версии Oracle 7.3.3. Скорость работы отчетов, построенных на этих представлениях, была просто потрясающая.

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

CREATE VIEW  num_v(dat)
   AS
SELECT '01' FROM dual
 UNION ALL
SELECT '02' FROM dual
 UNION ALL
:
SELECT '30' FROM dual

Полчаса я рассказывал ему о том, как оптимизатор Oracle работает с представлениями, что не всегда он может "раскрывать скобки" и тогда приходится сначала вычислять результат, а уж потом подавать его на вход SQL-предложения, обращающегося к VIEW. Тем более что параметр COMPLEX_VIEW_MERGING, позволяющий управлять политикой работы оптимизатора при обработке запросов, содержащих представления, появился только в Oracle8 и недоступен в 7 версии. Попытался привести несколько примеров сравнения запросов, обращающихся к табличке с 30-ю строками и к представлению.

Следующие полчаса были посвящены механизмам кэширования в Oracle:

Затем последовали мои жалкие попытки сослаться на "авторитетов". Но цитирование Стива Адамса (Чего стоит одна его фраза - "I dislike any unnecessary overhead, so I would avoid the views anyway") и примеры из Стивена Ферстайна, который лишний раз к dual не обратится, ни к чему не привели. Однако на мое предложение, сделать еще пару - тройку подобных представлений (на квартал, полгода и год) он почему-то обиделся. J

Опять о повторных действиях и стойких заблуждениях.

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

С подачи производителей и торговцев аппаратных средств сложилось мнение о том, что RAID - чуть ли не панацея от всех мыслимых и немыслимых бед. В последнее время активно продвигаются на рынок дешевые одно- и двухканальные устройства либо устройства c интерфейсом EIDE [что само по себе на сервере является нонсенсом]. При ограниченных бюджетах служб автоматизации все часто заканчивается приобретением сервера, оснащенного RAID-контроллером с минимальным возможным количеством дисков. На котором, в лучшем случае, строится единственный том 5 уровня. В результате на один логический том "сваливаются" все файлы базы данных, а иногда туда же попадают файлы журнала повтора. Для активно нагруженной системы класса OLTP это можно считать началом ее медленной и мучительной смерти. Физическое чередование(striping) и кэшированный контроллер еще как-то спасают операции чтения, :

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

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

Стоит прислушаться к совету самой Oracle - для обеспечения высокой надежности и приемлемой производительности использовать RAID 0+1 для файлов базы данных, RAID 1 - для redo log и archive redo log. А также к рекомендациям по выбору stripe для различных файлов базы и на их распределение между различными контроллерами и каналами. [7]

По настоящему высокой производительности баз данных Oracle на RAID вы сможете добиться при "достаточно большом количестве" дисков и каналов и после многих бессонных ночей, потраченных на конфигурирование дисковой подсистемы под потребности вашей базы. Обратите внимание на конфигурации, которые тестировались в статье Gaja Krishna Vaidyanatha из Quest Software Inc., или Juan Loaiza из Oracle Corporation, или, наконец, на манускрипт сотрудников корпорации VERITAS Software.[8, 9, 10] Самые "скромные" из описанных конфигураций содержали 36 дисков.

Так что Oracle на RAID это не для бедных, хотя и просто Oracle - не для бедных.

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

Объяснялось это довольно просто - база данных переводилась на RAID, но толком никто не задумывался о перестройке существовавшей конфигурации. А зря, так как, если у вас два члена (журнала), то время отклика записи повтора должно увеличиться ровно в два раза. LGWR пишет буфера журнала последовательно, сначала в один активный член, затем во второй [если журнал в файловой системе]. Так может не стоит использовать избыточное резервирование, тем более что на уровне RAID это резервирование будет выполнено параллельно?

Не менее избита тема сырых (RAW) разделов для файлов БД и журналов повтора. Так же, как RAID не является панацеей от всех бед, также этим не являются сырые партиции.

Ваша система перегружена операциями ввода/вывода? Прежде, чем переходить к использованию raw, желательно удостовериться, что все основные параметры базы данных разумно сконфигурированы. Нагрузка ввода/вывода распределена между различными физическими дисками, выбраны оптимальные размеры буферного кэша, буфера журнала, словарного кэша, правильно настроены сегменты отката и области сортировки. Как правило, на этом этапе уже поздно (или слишком дорого) менять логику приложений и методы доступа к данным. Если это действительно так, можно рассмотреть возможность перехода на сырые разделы. Давайте попытаемся рассмотреть ее вместе с использованием асинхронного ввода/вывода, так как эти вопросы довольно-таки сильно связаны между собой.

Обычно, когда говорят о raw, перечисляют примерно такой список их достоинств:

возможен незначительный прирост производительности, т.к. при записи обходится буферный кэш UNIX;
обходятся все накладные расходы, связанные с инодами и списками свободных блоков;
нет негативных последствий от некорректных соотношений размеров блоков Oracle и ОС;
возможность использования полноценного асинхронного ввода/вывода, даже если он не поддерживается на уровне операционной системы;
отсутствие задержек при записи в мультиплексированные члены файла журнала;
отсутствие блокировок чтения/записи файлов данных на уровне ОС;
отсутствие фрагментации файлов базы данных на уровне операционной системы;
лучшая утилизация памяти.

В качестве главного и, пожалуй, единственного недостатка системы, построенной на сырых разделах, приводится аргумент: "Они сложны в администрировании".

Разве в UNIX отменили команду dd? Как-то у Стива Адамса я нашел еще более "убийственный" аргумент относительно журналов повтора на сырых разделах: "The standard cavil against raw, namely that it complicates backups, does not apply to online log files because they should not be backed up. Thus the case for using raw log files is very compelling".

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

Я не раз встречался с подобными публикациями результатов экспериментов по сравнению производительности записи на сырые разделы и в файлы файловой системы. Хотя какое отношение к Oracle имели эти эксперименты, непонятно. С небольшими сокращениями процитирую одну заметку из relcom.comp.dbms.oracle:

"На разных дисках выделил по одному разделу, на одном создал файловую систему, на другом оставил raw. Геометрия разбивки одинакова. Попробовал по очереди проверить скорость записи на диски через fs и raw. Вот результат.

для raw:
#timex
dd=/dev/zero of=/dev/rdsk/c1t1d0s4 bs=512b count=200000
время = 11.83с

для fs:
#timex
dd=/dev/zero of=/test/test.1 bs=512b count=200000
время = 10.06с

т.е. через fs пишется быстрее. Отключил буферизацию при монтировании fs. Результат записи на диск - 10.34с. Неужели "соль" только в двойной буферизации. Но отключение ее это не подтверждает".

Позвольте, разве перед нами стояла задача выяснения производительности команды dd? Меня больше интересует, как будет работать активно нагруженная база данных, если ее журналы повтора и/или файлы данных разместить на сырых разделах.

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

Я только хочу напомнить о том, что полностью оценить достоинства асинхронного ввода/вывода можно только на сырых разделах при работе на многопроцессорных системах имеющих очень активную нагрузку. Реального прироста производительности вы не получите, если ввод/вывод не является узким местом системы. Никто не говорит, что в других случаях не стоит связываться с raw. Как минимум, они обеспечат большую масштабируемость системы.

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

В заключение приведу реальный пример конфигурирования небольшой базы данных. Количество активных пользователей - около 100, нагрузка - днем в основном OLTP, вечером ближе к OLAP. Дисковая подсистема: четырехканальный RAID с девятью дисками (из них 1 Hot Spare) на SCSI, на двух каналах EIDE - три диска, один из которых используется под ОС, СУБД и прикладное программное обеспечение.

Для начала собираем статистику ввода вывода на ранее существовавшей конфигурации.

File_name                          Reads %   Writes %
/u02/oradata/ABS/usr/users02.dbf   92.58     50.34
/u02/oradata/ABS/usr/users01.dbf    5.62     11.84
/u02/oradata/ABS/ind/indx01.dbf     1.55     14.07
/u02/oradata/ABS/tmp/temp01.dbf      .05     10.58
/u02/oradata/ABS/prv/usersprv01.dbf  .09       .01
/u02/oradata/ABS/sys/system01.dbf    .11       .3
/u02/oradata/ABS/rbs/rbs01.dbf        0      12.85
/u02/oradata/ABS/emp/tools01.dbf      0         0
/u02/oradata/ABS/prv/indxprv01.dbf    0        .01

Примерное соотношение суммарного объема операций Read/Writes = 130.

Получается, что на один из файлов (users02.dbf) приходится почти 93% чтений и около 50% операций записи. Все остальные файлы базы данных "отдыхают".

Пользовательская информация хранится в трех файлах БД. Два первых (users01.dbf, users02.dbf) относятся к одному табличному пространству. Третий (usersprv01.dbf) создан для отдельной прикладной подсистемы.

Попробуем разделить всю пользовательскую информацию по трем группам, чтобы несколько распараллелить нагрузку:

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

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

Что мы имеем в результате? При OLTP нагрузке читаются и остаются в кэше небольшие таблицы из первой группы, параллельно читаются и обновляются таблицы из второй и часть таблиц из третей групп. При OLAP активно читается вторая и немного первая группа, обновляется аналитическая часть третей группы, которая потом только читается.

Вроде бы все логично. Теперь займемся индексами. Анализ ввода/вывода по файлам данных показывает, что по записи индексы занимают второе место (14.07%). Благодаря кэшированию, физическое чтение незначительно - 1.55%. Распределим индексы по трем табличным пространствам, как распределили таблицы.

Осталось разложить все наше "хозяйство" по физическим дискам. А точнее по логическим, т.к. это будущие тома еще не сконфигурированного нами RAID'а. Реально нам доступны три канала и 8 дисков. Что можно из этого получить:

RAID 0+1 на 4-х дисках (1том);
RAID 1 на 2-х дисках (2том);
RAID 1 на 2-х дисках (3том).

Теперь распределим диски по трем доступным каналам RAID котроллера:

1 канал: 1-ый диск 1-го тома,
       зеркало 1-го диска 3-го тома;
2 канал: 2-ой диск 1-го тома,
       зеркало 1-го диска 1-го тома,
               1-ый диск 2-го тома;
3 канал: 1-ый диск 3-го тома,
       зеркало 2-го диска 1-го тома,
               зеркало 1-го диска 2-го тома.

Первый том (RAID 0+1) "отдаем" под третью группу (аналитика и внутрисистемные таблицы). Физический страйпинг очень поможет при чтении больших активно используемых таблиц. Там же размещаем индексы для второй и первой группы.

Второй том (RAID 1) подойдет под вторую группу (OLTP таблицы) и под индексы третей группы.

На оставшемся третьем томе (RAID 1) разместим SYSTEM, RBS, TOOLS, TEMP и первую "условно-постоянное" табличное пространство.

Ах, да, мы совсем забыли про REDO LOG'и. К сожалению, ни на одном из наших трех томов они не смогут "прожить" без конкуренции с "соседями". Остается одно: создать несколько групп по два члена в каждой и разместить их в сырых разделах на IDE дисках на разных каналах. Это позволит хоть как-то распараллелить процесс записи в них.

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

redo log space request колеблется в пределах от 5 до 20.

Это говорит о том, что пользовательские процессы ждут освобождения места в буферах журнала повтора [в идеале это значение должно быть около нуля]. Поэтому нам следует немного увеличить существовавший до этого размер LOG BUFFER (65536) и через некоторое время поглядеть, какое влияние это изменение окажет на redo log space request.

Что касается архивированных файлов журнала, положить их можно куда угодно, где останется достаточно места, за исключением нашего первого тома c RAID 0+1.

Маленькие "приколы нашего городка". Или к чему может привести увлечение "инкапсуляцией".

Как-то раз в стареньком приложении, написанном на SQL*Menu 5.0, я встретил такой истинный шедевр жанра.

PROCEDURE v1 is
begin
previous_menu;
end;
или еще такой:
PROCEDURE exe_7 is
-- снятие зависшего (и не только) терминала
begin
  OS_COMMAND('(TYPE=3) cltty');
end;

По-моему комментарии излишни:

RTFM.

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

create or replace function quartal
  (dat date default sysdate)
    return number is
    resnumber;
begin
    select
trunc((to_number(to_char(dat, 'mm')) + 2) / 3)
    into
res
    from dual;
    return res;
end quartal;

Как вы думаете, во сколько раз быстрее будет работать простая TO_CHAR(dat, 'Q')? Правильно, как минимум в два раза быстрее.

Запрограммировать можно все, за исключением, пожалуй, человеческой глупости.

SQL vs. PL/SQL.

Не так давно мне довелось пообщаться с одним из заслуженных мэтров Oracle'вского направления в России. Он проводил что-то вроде небольшого собеседования со мной и задал вопрос: "А что вы больше любите: SQL или PL/SQL?" Я с превеликим трудом удержался от своего любимого ответа: "Девчонок". А если серьезно, то : я даже не знаю что и ответить на этот вопрос. Во всяком случае, так, чтобы ответ уместился в этом номере журнала.: Всему свое место и время.

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

SELECT a.vatr
  FROM atrelst a
 WHERE a.idobj = p_idobj
   AND EXISTS ( SELECT *
         FROM atrelst d
         WHERE d.idobj = a.idobj
         AND d.vatr = a.vatr
         AND d.elst = p_elst
         AND d.kelst = p_kelst)
   AND NOT EXISTS ( SELECT *
              FROM atrelst b
              WHERE b.vatr = p_vatr
              AND b.elst != p_elst
              AND b.idobj = a.idobj
              AND NOT EXISTS ( SELECT *
                         FROM atrelst c
                         WHERE c.idobj = a.idobj
                         AND c.vatr = a.vatr
                         AND c.elst = b.elst
                         AND c.kelst = b.kelst));

Несмотря на громоздкий вид, план выполнения этого запроса оказался довольно хорошим:

SELECT STATEMENT, GOAL = CHOOSE
 FILTER
  INDEX RANGE SCANPK_ATRELST
  TABLE ACCESS BY INDEX ROWIDATRELST
   INDEX UNIQUE SCANPK_ATRELST
  FILTER
   TABLE ACCESS BY INDEX ROWIDATRELST
    INDEX RANGE SCANPK_ATRELST
   TABLE ACCESS BY INDEX ROWIDATRELST
    INDEX UNIQUE SCANPK_ATRELST

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

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

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

И снова курсоры. Explicit vs. implicit.

В отличие от предыдущего пункта, где мы беседовали об использовании в PL/SQL элементов SQL и процедурных конструкций, тут речь пойдет о чистом PL/SQL.

Практически в любой книге по PL/SQL вы найдете не одну главу о способах работы с курсорами и об их эффективном использовании. Выходят новые версии СУБД Oracle, расширяются ее возможности, а вместе с ними развиваются языки SQL и PL/SQL. У одного только Стивена Ферстайна не меньше, чем в пяти книгах затрагивались эти темы [11]:

В качестве примера приведу небольшую функцию.

CREATE OR REPLACE FUNCTION fu_check_ogrdog (
  p_idn       IN   INTEGER, -- Проверяемый договор
  p_trazdog   IN   INTEGER -- Тип разрешения
)
  RETURN INTEGER
IS
BEGIN
-- Функция проверки  полномочий пользователя
-- 0-не найдено 1-найдено
  DECLARE
    num               INTEGER;
    p_vdog            INTEGER;
    p_tdog            INTEGER;
    trols             INTEGER;
    trolv             INTEGER;
    -- Пользователь создавший договор
    usrs              VARCHAR2 (30);
    -- Пользователь ведущий договор
    usrv              VARCHAR2 (30);
-- Проверка  полномочий пользователя на изменение ЛС
   -- Красным цветом выделены мои комментарии
  BEGIN
  -- count  только для того, чтобы не обрабатывать no_data_found
    SELECT COUNT (*)
          INTO num
   --    idn в таблице dogovor является primary key
      FROM dogovor
    --    т.е. INDEX UNIQUE SCAN   ADMBNK  PK_DOGOVOR
     WHERE idn = p_idn;
      IF num = 0
    THEN
      RETURN (num);
    END IF;
-- Определяем вид и тип проверяемого договора
--   я конечно понимаю, что и требуемый блок индекса и
-- таблицы после первого запроса вероятно
-- находятся в буферном кэше,
-- но зачем второй раз перечитывать кэш?
    SELECT vdog, tdog
           INTO p_vdog, p_tdog
      FROM dogovor
     WHERE idn = p_idn;
-- Определяем полномочия пользователя данного типа
-- на данный договор
  -- опять count только для того, чтобы
-- не обрабатывать no_data_found J
-- если count > 1, зачем "ворошить" лишние данные
    SELECT COUNT (usrgr)
          INTO num
      FROM rdogusr r
-- нас ведь интересует > 0
     WHERE r.trazdog = p_trazdog
       AND (   r.usrgr = USER
            OR (EXISTS ( SELECT     LEVEL, usrgrr
                       FROM usr_grp
                      WHERE trazdog = p_trazdog AND usrgrp = USER
                 START WITH usrgrr = r.usrgr
                 CONNECT BY PRIOR usrgrp = usrgrr)
               )
           )
       AND NVL (r.idn, p_idn) = p_idn
       AND NVL (r.vdog, p_vdog) = p_vdog
       AND NVL (r.tdog, p_tdog) = p_tdog
       AND (   r.trol IS NULL
            OR (    r.trol IS NOT NULL
                AND EXISTS ( SELECT NULL
                               FROM roldog
                              WHERE idn = p_idn
                                AND trol = r.trol
                                AND usr = r.usr)
               )
           );
    IF num > 0
    THEN
      RETURN (1);
    ELSE
      RETURN (0);
    END IF;
  END;
END fu_check_ogrdog;

В данном случае имело смысл написать два явных курсора с обработкой no_data_found. Зачем считать точное количество, если нас интересует факт наличия хотя бы одной строки?


%ROWTYPE и %TYPE, перекомпиляции и размер PGA.

Для начала небольшая цитата классиков PL/SQL.

Always fetch from an explicit cursor into a record declared with %ROWTYPE, as opposed to individual variables. [11]

Без всякого сомнения, это очень хорошая практика. Но только во всем надо иметь чувство меры. Некоторые разработчики, с которыми я работал, восприняли подобные советы слишком буквально. Разглядев %ROWTYPE, они не разглядели за ним все остальное. В своих программах они часто без всякой надобности использовали курсоры вида SELECT * FROM : и переменные типа cursor%ROWTYPE, забыв, что кроме символа '*' существует список выражений, которые необходимо получить. Даже если на самом деле в этом курсоре требовалось выбрать один, два столбца вместо этого выбиралась вся строка. Вроде бы мелочь, но попробуйте сравнить работу двух курсоров, обращающихся к одной большой таблице, только один из них будет выбирать только то, что на самом деле необходимо выбрать, а второй - '*'.

Проанализируйте следующие статистики: physical reads, consistent gets, db block gets и session pga memory для двух тестовых сессий с такими курсорами по всем записям таблицы. Я думаю, результат будет очень наглядным.

Совместное использование кода.

Один из наших программистов обладал очень своеобразным стилем написания SQL-предложений. Если вы встретите в какой-либо программе что-то подобное (текст приведен с точность до количества пробелов)

select
  sum(nvl(svdnv,0)+nvl(fu_poblstek(ls,datod_),0))
  into result
from v_ls_lico
  where 0 = 0
    and s2p = s2p_
and val = nvl(val_, val)
and tiplic = nvl(tiplic_, tiplic)
and vidlic = nvl(vidlic_, vidlic)
and vidls = nvl(vidls_, vidls);

или непредсказуемое количество пробелов и строк с пустыми комментариями, значит это его творение. Объяснял он это "легкостью" выполнения последующих модификаций, правда "работал" его способ только когда все условия в WHERE объединялись через AND, да и честно говоря, из его невразумительных объяснений никто ничего не понял. А поняли, когда проанализировали содержимое V$SQLAREA и V$SESSION. Даже совсем примитивное предложение подобное:

SELECT *
  FROM dual;

превращалось во что-то громоздко-непотребное. Рядом с ним сидело несколько человек, которые "вооружившись" PL/SQL Developer с PL Formatter "выдавали" абсолютно однотипные по стилю и форматированию предложения.

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

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

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

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

Индексы - как много в этом слове...

Если собрать и распечатать все упоминания о пользе индексов, ...

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

Наличие избыточных индексов на "активных" таблицах.

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

Bitmap индексы в OLTP приложениях на таблицах с большим количеством конкурирующих INSERT, UPDATE и DELETE не принесут ничего, кроме вреда. Обновление одной строки таблицы приведет к блокированию целого диапазона строк и ожиданиям его освобождения в других сессиях. Это происходит потому, что одна битовая карта может соответствовать большому диапазону ROWID и минимальной единицей блокировки в этом случае является не одна строка, а весь диапазон строк.

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

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

Также не имеет смысла создавать bitmap индексы на столбцах с большим количеством возможных значений. Под "небольшим" количеством обычно понимают несколько десятков, а под большим - 100 и более уникальных значений на миллионы строк. Иногда "границу" определяют как 1 - 2% от общего количества записей в таблице.

Когда "не хочется" создавать индекс?

Довольно часто встречаются ситуации, когда на таблице, которая содержит буквально несколько строк (или несколько десятков строк), создается индекс для обеспечения первичного ключа. С одной стороны, вроде бы, и ключ нужен, а с другой - вряд ли этот индекс добавит быстродействия приложению. Однако в том случае, если на эту таблицу ссылаются другие таблицы, без создания индекса уже никак не обойтись.

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

Редкий анализ и редкая перестройка индексов.

Со временем объекты базы данных (хранящие данные) подвергаются некоторым изменениям, которые не добавляют производительности при работе с ними. В результате многочисленных операций с таблицей структура ее индексов сильно меняется. Наличие старых значений и новые вставки приводят к "разбалансировке" дерева индекса и увеличению его глубины. В таком индексе строки располагаются хаотическими группами, приводящими к более частому обращению к одним частям индекса. При интенсивных обращениях это может стать узким местом.

Поэтому периодически (хотя бы раз в год) необходимо выявлять кандидатов на перестройку. Для этого проанализируйте (при помощи команды ANALYZE INDEX) ваши индексы (что желательно делать регулярно) и выполните подобный запрос:

SELECT index_name, blevel,
  FROM dba_indexes
 WHERE owner = '&index_owner'
   AND blevel > 4;

Он поможет вам найти индексы с высоким branch level. Эта величина показывает количество переходов в дереве индекса, которое требуется Oracle для поиска конкретной строки. Строки с высоким значением - хорошие кандидаты для перестройки.

Теперь более детально проанализируйте индексы с опцией VALIDATE STRUCTURE и выполните еще один запрос для каждого индекса:

SELECT
 del_lf_rows/DECODE(lf_rows,0,1,lf_rows)*100
        pct_deleted,
 del_lf_rows_len/DECODE(lf_rows_len,0,1,lf_rows_len)*100
        pct_length,
 (lf_rows - distinct_keys)/DECODE(lf_rows,0,1,lf_rows)*100
             distinctiveness
  FROM index_stats
 WHERE name = '&index_name';

Первый столбец показывает процент удаленных элементов индекса, но остающихся в нем. Индексы с большим количеством удаленных строк (более 10-25%) требуется перестроить.

Второй - средний процент соотношений длин удаленных элементов. Если полученный результат окажется в диапазоне 20 - 25%, индекс нуждается в перестройке.

Третий - это средний процент повторений значений индекса. Если это значение велико, следует подумать о необходимости этого индекса. Вполне возможно, что более целесообразным в этом случае будет bitmap индекс.

Если запустить этот select для "свежеиспеченного" индекса, он покажет 0 0 0. Но, к сожалению, при активной работе с таблицей, для которой создан этот индекс, картина сильно меняется.

Таблица index_stats обладает одной "замечательной" особенностью, поэтому опрашивайте ее в той же сессии, в которой выполняли команду ANALYZE INDEX ### VALIDATE STRUCTURE.

Начиная с версии 7.3, Oracle предоставляет нам очень полезное средство для перестройки индексов - команду ALTER INDEX ### REBUILD, которая избавляет претендентов на перестройку от фрагментации листовых блоков и от многих других свойств "престарелых" индексов. Хотя в Oracle8i это возможно, пожалуйста, не пытайтесь запустить эту команду во время активной работы пользователей с системой.

"Все выше, выше и выше:"

Этими словами из известной советской песни можно описать "жизненное кредо" такого неживого существа Oracle как High Water Mark. И хотя в этом разделе мы говорили об индексах, мне показалось уместным вспомнить о причинах и следствиях "старения" не только индексов, но и таблиц. По пути вскользь коснемся вопросов фрагментации.

High Water Mark указывает на наибольший блок таблицы в котором когда-либо сохранялись строки. Во время вставки новых строк HWM увеличивается, но он никогда не уменьшается при удалении строк с помощью DELETE. Существует только один способ "опустить" его - это использовать команду TRUNCATE (либо пересоздать таблицу).

Oracle использует HWM для определения количества блоков, которое необходимо прочесть при полном просмотре таблицы. Для таблиц, в которые данные в основном вставляются, это позволяет сэкономить на излишнем чтении пустых блоков, однако если из таблицы удаляются большие объемы данных, можно по несколько минут "опрашивать пустую таблицу".

Еще одно следствие "активной жизни" таблиц - фрагментация строк (row fragmentation) или сцепленные строки (row chaining). Это происходит, когда при UPDATE новое значение строки не умещается в блоке данных. В этом случае вся строка перемещается в новый блок данных, а в старом остается ссылка на новое местоположение строки, что приводит к дополнительной нагрузке подсистемы ввода/вывода при обращениях к этим строкам.

Фрагментация блоков данных (data block fragmentation) является следствием частого удаления строк и на больших таблицах может привести к падению производительности.

Многих проблем, связанных с ухудшением эксплуатационных характеристик описанных объектов, можно избежать, если корректно выбрать параметры хранения при создании объекта (PCTFREE, PCTUSED для таблиц и PCTFREE для индексов). В некоторых случаях может помочь изменение этих параметров для уже существующего объекта, но иногда единственным средством (как это не печально) является пересоздание объекта с последующим переносом данных (с помощью exp, imp или create as select). Информацию об этом можно найти в документации Oracle. es. Lawrence To. Center of Expertise e-Business Support Services Oracle

Литература:

[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