Два КСлова про Oracle

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

Иногда мне приходится слышать, что Oracle очень сложен в настройке, «вот ××× — совсем другое дело». Перестать обращать внимание на подобные заявления мне помог случай.

Когда я читал спецкурс по Oracle, мне пришлось сделать для студентов учебную базу данных. Один из студентов выдал запрос select * from anytable и заявил, что клиентская машина начала «притормаживать». Я спросил его, сколько записей он извлёк из таблицы. Он посмотрел и сказал «Ого!» После этого претензий к производительности Oracle не возникало. А извлечено было всего-то 20 000 записей — для Oracle количество просто смешное. Недавно я прочитал в техническом задании требование хранить 3 000 000 000 (три миллиарда) записей...

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

* * *

Итак, задача предельно простая: скопировать данные из одной таблицы в другую. Что сделает счастливый обладатель обычной базы данных? Правильно, он сделает вот так:

insert into target 
select * from source;

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

Direct-load insert

См. Oracle concepts, Chapter 25 „Direct-load INSERT“

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

Идея direct-load insert состоит в том, чтобы передавать серверу не сырые данные, а уже готовые сформированные блоки — ему останется только записать эти блоки в таблицу. Разумеется, при таком подходе использованные блоки не заполняются — либо заполняются полностью свободные блоки, либо к таблице добавляются новые.

Делать direct-load insert умеет утилита SQL*Loader, входящая в комплект клиентского ПО Oracle, но для этого необходимо, чтобы клиент и сервер работали на одной и той же платформе, то есть загрузить, например, таким методом данные из Windows в Solaris не получится. Для того же, чтобы воспользоваться этим методом внутри сервера, необходимо воспользоваться хинтом append.

Хинт (hint) — комментарий специального вида, подсказывающий серверу, как именно выполнять то или иное действие. Oracle имеет полное право проигнорировать хинт, но мне не встречалось случаев, чтобы он проигнорировал append. Подробнее о хинтах см. Oracle tuning, Chapter 7 „Optimizer Modes, Plan Stability, and Hints“

Итак, вот первый шаг в решении нашей задачи:

insert /*+ append */
  into target 
select * from source;

Индексы

Если копировать данные методом direct-load insert, то не приходится сохранять никакой информации для отката, т.е. rollback segment практически не растёт.

Для синхронизации пользователей Oracle применяет механизм сохранения нескольких поколений данных. Новые данные записываются непосредственно в таблицы, а старые данные переписываются в специально отведённые для этого места — сегменты отката (rollback segments). Пока транзакция не завершена, все другие транзакции читают старые данные из сегментов отката. В момент подтверждения транзакции сегмент отката помечается как ненужный, а вот откат транзакции — операция дорогостоящая, т.к. при этом старые данные переписываются из сегментов отката обратно в таблицы. Подробнее о сегментах отката см. Oracle concepts, Chapter 4 „Data Blocks, Extents, and Segments“

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

select text from (
select
  'create '||
  decode(index_type,'NORMAL',null,index_type||' ')||
  decode(uniqueness,'NONUNIQUE',null,uniqueness||' ')||
  'index '||owner||'.'||index_name||
  ' on '||table_owner||'.'||table_name||'(' as text,
  owner||'.'||index_name as name, 0 as part
from
  all_indexes
where
  table_owner='HARDSIGN' and table_name='TARGET'
union all
select
  decode(column_position,1,'  ',', ')||column_name as text,
  index_owner||'.'||index_name as name,
  column_position as part
from
  all_ind_columns
where
  (index_name,index_owner) in (
    select
      index_name,owner 
    from
      all_indexes
    where
      table_owner='HARDSIGN' and table_name='TARGET'
  )
union all
select
  ') pctfree '||pct_free||' initrans '||ini_trans
  ||' maxtrans '||max_trans||chr(10)||
  'tablespace '||tablespace_name||chr(10)||
  decode(logging,'YES','logging','nologging')||chr(10)||
  'storage ('||
  'initial '||initial_extent||
  ' next '||next_extent||
  ' pctincrease '||pct_increase||
  ' minextents '||min_extents||
  ' maxextents '||max_extents||
  ' freelists '||freelists||');' as text,
  owner||'.'||index_name as name, to_number(null) as part
from
  all_indexes
where
  table_owner='HARDSIGN' and table_name='TARGET'
)
order by name,part;

Новички могут упрекнуть меня в излишней сложности команд — на это я отвечу, что сложного ничего нет, проблема не в сложности запроса, а в переводе сухого языка таблиц и представлений на язык SQL, почти человеческий. Профессионалы, в свою очередь, упрекнут меня в том, что не учтены разные тонкости, например, partitioned indexes — на это отвечу цитатой из Козьмы Пруткова: «никто не обнимет необъятного*».

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

select
  'drop index '||owner||'.'||index_name||';'
from
  all_indexes
where
  table_owner='HARDSIGN' and table_name='TARGET';

Хотя, повторяю, никто не мешает написать все необходимые команды вручную.

Ключи

Очевидно, для ускорения копирования имеет смысл отключить первичные и внешние ключи. Делать это также стоит только в том случае, когда объём копируемых данных значителен по сравнению с объёмом существующих данных. Формально ключи с индексами никак не связаны (за исключением индексов, создаваемых для поддержки первичных ключей), однако фактически поддержание внешних ключей без соответствующих индексов — чересчур ресурсоёмкая задача. Поэтому если принято решение разрушать индексы, ключи также надо отключить.

Запрос, создающий скрипт, который включает/отключает ключи, весьма прост, но для полноты картины приведу его здесь:

select
  'alter table '||owner||'.'||table_name
  ||' enable constraint '||constraint_name||';'
from
  all_constraints
where
  owner='HARDSIGN' and table_name='TARGET'
  and constraint_type in ('P','R')

Параллелизм

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

Достигается это несколькими способами. Способ первый — указать при создании таблицы степень параллелизма, допустимую при операциях с этой таблицей.

См. Oracle8i SQL Reference, Chapter 7 „SQL Statements“

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

Второй путь — указать хинт parallel непосредственно в команде. Тут необходимо иметь в виду следующее: во-первых, чтение данных и их запись — разные процессы, и распараллеливать их надо по отдельности; во-вторых, запись не распараллелится, если не разрешить это явно командой enable parallel DML. С учётом вышесказанного скрипт для копирования данных приобретает следующий вид:

alter session enable parallel dml;

insert /*+ append parallel(t,4) */
  into target t
select /*+ parallel(s,4) */ * 
  from source s;

Здесь порождается четыре процесса для записи и четыре — для чтения. Если запись идёт существенно медленнее, чем чтение (например, не разрушены индексы), то можно сделать записывающих процессов больше, чем читающих.

При задании хинта parallel подразумевается append, то есть приведённый кусок кода избыточен. В отличие от просто append, при параллельной записи в таблицу игнорируются не только заполненные, но и пустые блоки — формируются новые экстенты, и таблица растёт. Поэтому, если вы очищаете таблицу перед копированием командой truncate table, то не используйте опцию reuse storage.

И ещё одно. Запись не распараллелится, если остался включённым хотя бы один внешний ключ, где целевая таблица является дочерней. А Oracle 8.0 не распараллелит запись, если чтение происходит по линку, т.е. из другого экземпляра Oracle.

Подробнее об ограничениях см. Oracle concepts, Chapter 26 „Parallel Execution“

Выделение памяти

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

Как известно, если место в таблице кончается, к ней приписывается новый экстент — совокупность подряд идущих блоков. Размер этого экстента определяется параметрами next и pctincrease, заданными в команде create table. Если экстент маленький, то он выделится быстро, но выделение будет происходить часто. Если экстент большой, то выделяться экстенты будут редко, но зато подолгу. Необходимо помнить, что словарь данных при изменении блокируется монопольно, то есть если один из параллельных процессов запросил экстент, а в это время обрабатывается запрос от другого процесса, первый встанет и будет ждать.

Если перед копированием данных таблица очищается командой truncate table, то скорость очистки зависит не от количества выделенных под таблицу блоков, а от количества экстентов, так что у маленьких экстентов есть ещё один минус.

Дать однозначные рекомендации по размеру экстентов невозможно, всё зависит от объёма данных, то есть «ширины» таблицы и количества записей. Желательно, правда, чтобы размеры экстентов во всех таблицах были кратными — это уменьшит фрагментацию табличного пространства и ускорит поиск свободного пространства.

Сегменты отката

Как вы заметили, одна из проблем больших транзакций — возможное переполнение сегментов отката. Пытливый ум может усомниться в их необходимости — в других СУБД таких проблем нет. Согласитесь, копирование больших объёмов данных — отнюдь не ежедневная операция, а вот пользователи работают каждый день. Мне лично приходилось видеть, как больше 300 пользователей работали на одном сервере без взаимных блокировок, в то время как блокировку, например, DB2 вызвать очень легко — достаточно попробовать считать запись, изменяемую в другой сессии. При подходе же к копированию данных, принятом в Interbase или PostgereSQL, более частая операция подтверждения транзакции (commit) занимает больше времени, чем операция отката (rollback).

Если вы внимательно посмотрели на картинку, иллюстрирующую использование сегментов отката, то увидели, что они используются по очереди. Пусть, например, у нас есть 4 сегмента — R01, R02, R03 И R04. То есть если транзакция использовала, например, сегмент R01, то следующей транзакции будет назначен сегмент R02.

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

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

alter session enable parallel dml;

set transaction use rollback segment R01;

insert /*+ append parallel(t,4) */
  into target1 t
select /*+ parallel(s,4) */ * 
  from source1 s;

commit;

set transaction use rollback segment R01;

insert /*+ append parallel(t,4) */
  into target2 t
select /*+ parallel(s,4) */ * 
  from source2 s;

commit;

Заключение

О, сколько нам открытий чудных...

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

Если вам захочется поделиться со мной своими трудностями и радостями, связанными с Oracle — пишите письма. Если же радости есть, а писать лень, можно распечатать на цветном принтере и приклеить на веб-страничку/монитор/лоб традиционную кнопку 88×31:

24.03.2003