Открыто о СУБД Oracle на русском : Сообщение об ошибке ORA-01555

Выпуск 26

Сообщение об ошибке ORA-01555

Уважаемые подписчики! Этот выпуск посвящен "знаменитому" сообщению об ошибке ORA-1555: snapshot too old. Как обычно, по мотивам Тома Кайта...

Snapshot too old

Том!

Не мог бы ты объяснить, что означает сообщение об ошибке snapshot too old. Когда возникает эта ошибка? По каким причинам? Как избавиться от этой ошибки?

Большое спасибо.

Ответ Тома Кайта

Мне кажется, документ службы поддержки <Note:40689.1> очень хорошо раскрывает эту тему:

ORA-01555 "Snapshot too old" - детальное объяснение

Обзор

В этой статье обсуждаются условия, при которых запрос может вернуть сообщение об ошибке ORA-01555 "snapshot too old (rollback segment too small)". Затем в статье будут обсуждаться действия, которые можно предпринять во избежание этой ошибки и, наконец, будет представлен ряд простых сценариев PL/SQL, иллюстрирующих рассмотренные проблемы.

Терминология

Предполагается, что читатель знаком со стандартными терминами Oracle, такими как "сегмент отката" и "SCN". В противном случае, необходимо сначала прочитать руководство Oracle Server Concepts и другую соответствующую документацию Oracle.

Кроме этого, ниже кратко рассмотрены два ключевых понятия, которые помогут понять причины возникновения ошибки ORA-01555:

1. СОГЛАСОВАННОСТЬ ПО ЧТЕНИЮ:

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

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

2. ОТЛОЖЕННАЯ ОЧИСТКА БЛОКА:

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

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

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

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

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

СТАДИЯ 1 - Нет изменений

Блок данных 500            Заголовок сегмента отката 5
+----+--------------+      +----------------------+---------+
| tx | Нет          |      | запись транзакции 01 |ACTIVE   |
+----+--------------+      | запись транзакции 02 |ACTIVE   |
| строка 1          |      | запись транзакции 03 |COMMITTED|
| строка 2          |      | запись транзакции 04 |COMMITTED|
| ... ..            |      |     ...     ...   .. |  ...    |
| строка n          |      | запись транзакции nn |COMMITTED|
+-------------------+      +--------------------------------+

СТАДИЯ 2 - Изменяется строка 2

Блок данных 500            Заголовок сегмента отката 5 
+----+--------------+      +----------------------+---------+
| tx |5.3uncommitted|-+    | запись транзакции 01 |ACTIVE   |
+----+--------------+ |    | запись транзакции 02 |ACTIVE   |
| строка 1          | +--->| запись транзакции 03 |ACTIVE   |
| строка 2 *изм.*   |      | запись транзакции 04 |COMMITTED|
| ... ..            |      |     ...     ...   .. |  ...    |
| строка n          |      | запись транзакции nn |COMMITTED|
+-------------------+      +--------------------------------+

СТАДИЯ 3 - Пользователь фиксирует транзакцию

Блок данных 500             Заголовок сегмента отката 5 
+----+--------------+       +----------------------+---------+
| tx |5.3uncommitted|--+    | запись транзакции 01 |ACTIVE   |
+----+--------------+  |    | запись транзакции 02 |ACTIVE   |
| строка 1          |  +--->| запись транзакции 03 |COMMITTED|
| строка 2 *изм.*   |       | запись транзакции 04 |COMMITTED|
| ... ..            |       |     ...     ...   .. |  ...    |
| строка n          |       | запись транзакции nn |COMMITTED|
+-------------------+       +--------------------------------+

СТАДИЯ 4 - Другой пользователь выбирает данные блока 500

Блок данных 500            Заголовок сегмента отката 5 
+----+--------------+      +----------------------+---------+
| tx | Нет          |      | запись транзакции 01 |ACTIVE   |
+----+--------------+      | запись транзакции 02 |ACTIVE   |
| строка 1          |      | запись транзакции 03 |COMMITTED|
| строка 2          |      | запись транзакции 04 |COMMITTED|
| ... ..            |      |     ...     ...   .. |  ...    |
| строка n          |      | запись транзакции nn |COMMITTED|
+-------------------+      +--------------------------------+

Объяснение ошибки ORA-01555

Есть две основные причины возникновения ошибки ORA-01555, которые являются следствием попыток сервера Oracle получить "согласованный по чтению" образ данных:

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

Обе эти ситуации рассматриваются ниже, вместе с последовательностями шагов, вызывающих получение сообщения об ошибке ORA-01555. При описании этих шагов упоминается "QENV". "QENV" (сокращение от "Query Environment" - среда запроса) - это среда, существовавшая на момент начала запроса и по отношению к которой сервер Oracle пытается получить согласованный по чтению образ. С этой средой связано значение SCN (System Change Number - номер системного изменения) в соответствующий момент времени, так что QENV 50 - это среда запроса для значения SCN 50.

СИТУАЦИЯ 1 - ДАННЫЕ ОТКАТА ПЕРЕПИСАНЫ

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

Шаги:

1. Сеанс 1 начинает запрос в момент времени T1 при QENV 50
2. Сеанс 1 выбирает блок B1 в ходе этого запроса
3. Сеанс 1 изменяет этот блок при SCN 51
4. Сеанс 1 выполняет другие действия, генерирующие данные отката.
5. Сеанс фиксирует изменения, выполненные на шагах 3 и 4.
   (Теперь другие транзакции могут переписывать соответствующие данные отката)
6. Сеанс повторно обращается к тому же блоку B1 (возможно, в поисках другой строки).

Теперь сервер Oracle может понять по заголвоку блока, что блок был изменен, причеми, позже, чем требуемая QENV (которая соответствовала SCN 50). Поэтому необходимо получить образ блока по состоянию на эту QENV.

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

Именно в этом случае сервер Oracle может не найти необходимые данные отката, поскольку другие изменения в сеансе 1 сгенерировали данные отката, которые переписали необходимые серверу данные, и тогда сервер возвращает сообщение об ошибке ORA-1555.

СИТУАЦИЯ 2 - ПЕРЕПИСАН СЛОТ ТРАНЗАКЦИИ В СЕГМЕНТЕ ОТКАТА

1. Сеанс 1 начинает запрос в момент времени T1 при QENV 50
2. Сеанс 1 выбирает блок B1 в ходе этого запроса
3. Сеанс 1 изменяет этот блок при SCN 51
4. Сеанс 1 фиксирует изменения.
   (Теперь другие транзакции могут переписывать соответствующие данные отката)
5. Сеанс (сеанс 1, другой сеанс или несколько других сеансов) затем использует тот же сегмент отката для выполнения ряда зафиксированных транзакций.

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

6. Запрос сеанса 1 затем обращается к блоку, который был изменен с момента установки исходной среды QENV. Поэтому серверу Oracle нгеобходимо получить образ блока на соответствующий момент времени.

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

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

Можно также обнаружить вариант слота транзакции, переписанный при очистке блока. Эта ситуация кратко описана ниже:

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

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

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

(Примечание: Обычно сервер Oracle может использовать алгоритм определения значения SCN блока в ходе очистки блока, даже если транзакции в сегменте отката был переписан. Но в этом случае сервер Oracle не может гарантировать, что версия блока не изменилась с момента начала запроса).

Решения

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

Следует отметить, что если один сеанс приводит к получению сообщения об ошибке ORA-01555, и оно не связано со специальными случаями, перечисленными в конце этой статьи, значит, сеанс заведомо использует расширение Oracle, разрешающее выбирать данные в нескольких транзакциях (fetch across commit). Это не согласуется с моделью ANSI, и в тех редких случаях, когда возвращается сообщение об ошибке ORA-01555, необходимо использовать одно из представленных ниже решений.

СИТУАЦИЯ 1 - ДАННЫЕ ОТКАТА ПЕРЕПИСАНЫ

1. Увеличьте размер сегмента отката, что снизит вероятность переписывания необходимых данных отката.
2. Сократите количество фиксаций (та же причина, что и для решения 1).
3. Выполняйте обработку данных по частям, а не всей таблицы сразу (та же причина, что и для решения 1).
4. Добавьте дополнительные сегменты отката. Это позволит распределить изменения по большему количеству сегментов отката, тем самым, сократив вероятность перезаписи требуемых данных отката.
5. При выборе данных в нескольких транзакциях можно изменить код так, чтобы этого не делать.
6. Сделайте так, чтобы внешний select не обращался к одним и тем же блокам в различные моменты времени по ходу обработки. Для этого:
   • Используйте полный просмотр таблицы вместо поиска по индексу
   • Добавьте избыточную сортировку, так, чтобы все данные извлекались, сортировались, а затем полученные блоки данных просматривались последовательно.

СИТУАЦИЯ 2 - ПЕРЕПИСАН СЛОТ ТРАНЗАКЦИИ В СЕГМЕНТЕ ОТКАТА

1. Используйте любой из представленных выше методов, кроме 6. Это позволит распределить результаты работы транзакций по сегментам отката, уменьшая тем самым вероятность использования всех слотов таблицы транзакций сегмента отката.
2. Если предполагается, что ошибка так или иначе связана с очисткой блока, принудительно вызовите очистку блока до начала транзакции, возвращающей сообщение об ошибке ORA-1555. Этого можно добиться с помощью следующих операторов в среде SQL*Plus, SQL*DBA или Server Manager:

         alter session set optimizer_goal = rule;
         select count(*) from table_name;
   

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

         select index_column from table_name where index_column > 24;
   

Примеры

Ниже представлены примеры на языке PL/SQL, иллюстрирующие описанные выше ситуации examples, приводящие к получению сообщения об ошибке ORA-1555. Чтобы данные примеры выдавали это сообщение об ошибке, сервер необходимо сконфигурировать следующим образом:

• Использовать маленький буферный кэш (db_block_buffers).
  
  ПРИЧИНА: Нужно, чтобы сеанс, выполняющий сценарий, не мог найти старые версии блока в буферном кэше, по которым можно было бы получить нужный образ блока без использования данных отката.
• Используйте один сегмент отката, не совпадающий с SYSTEM.
  
  ПРИЧИНА: Необходимо гарантировать, что при выполнении транзакции генерируются данные отката, которые будут переписывать необходимые запросу данные отката.
• Используйте небольшой сегмент отката.
  
  ПРИЧИНА: См. причину использования одного сегмента отката.

ПЕРЕЗАПИСЬ ДАННЫХ ОТКАТА

rem * 1555_a.sql - 
rem * Пример получения сообщения ora-1555 "Snapshot too old"
rem * сеансом, переписывающим данные отката, необходимые
rem * ему самому. 

  drop table bigemp;
  create table bigemp (a number, b varchar2(30), done char(1));

  drop table dummy1; 
  create table dummy1 (a varchar2(200));

  rem * Наполнение таблиц данными.
  begin
   for i in 1..4000 loop
     insert into bigemp values (mod(i,20), to_char(i), 'N');
     if mod(i,100) = 0 then
       insert into dummy1 values ('ssssssssssss');
       commit;
     end if;
   end loop;
   commit;
  end;
  /

  rem * Гарантируем "очистку" таблицы. 
  select count(*) from bigemp;

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

   -- Если другая транзакция изменяет таблицу, это условие может и не
   -- понадобиться.
   cursor c1 is select rowid, bigemp.* from bigemp where a < 20;

  begin
   for c1rec in c1 loop

     update dummy1 set a = 'aaaaaaaa';
     update dummy1 set a = 'bbbbbbbb';
     update dummy1 set a = 'cccccccc';
     update bigemp set done='Y' where c1rec.rowid = rowid;
     commit; 
   end loop;
  end;
  /

ПЕРЕЗАПИСЬ СЛОТА ТРАНЗАКЦИИ В СЕГМЕНТЕ ОТКАТА

  rem * 1555_b.sql - Пример получения сообщения ora-1555 "Snapshot too old"
  rem *              при перезаписи слота транзакции в заголовке сегмента отката.
  rem *              При этом используется всего один сеанс.

  drop table bigemp;
  create table bigemp (a number, b varchar2(30), done char(1));

  rem * Заполняем данными демонстрационную таблицу.
  begin
   for i in 1..200 loop
     insert into bigemp values (mod(i,20), to_char(i), 'N');
     if mod(i,100) = 0 then
       commit;
     end if;
   end loop;
   commit;
  end;
  /

  drop table mydual;
  create table mydual (a number);
  insert into mydual values (1);
  commit; 

  rem * Очищаем демонстрационную таблицу.
  select count(*) from bigemp;

  declare

   cursor c1 is select * from bigemp;

  begin

   -- Следующее изменение необходимо, чтобы проиллюстрировать проблему, 
   -- возникающую, если была выполнена очистка блоков таблицы bigemp. Если
   -- закомментировать (представленный выше) оператор, выполняющий очистку, 
   -- то можно будет закомментировать также операторы update и commit, и
   -- сценарий закончится с ошибкой ORA-1555 из-за очистки блока.
   update bigemp set b = 'aaaaa';
   commit;

   for c1rec in c1 loop
     for i in 1..20 loop
       update mydual set a=a;
       commit;
     end loop; 
   end loop; 
  end;
  /

Специальные случаи

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

• Сервер Trusted Oracle может вернуть его, если сконфигурирован в режиме OS MAC. Уменьшение значения LOG_CHECKPOINT_INTERVAL во вторичной базе данных, может решить эту проблему.
• Если запрос обращается к блоку данных, измененному с помощью средств дискретных транзакций Oracle, сервер вернет сообщение ORA-01555.
• Вполне возможно, что сегмент отката, созданный с опцией OPTIMAL, может привести к возврату запросом сообщения об ошибке ORA-01555, если в ходе выполнения запроса размер сегмента уменьшится, что вызове потерю данных отката, необходимых для получения согласованных по чтению версий блоков.

Резюме

В этой статье обсуждаются причины возникновения сообщения об ошибке ORA-01555 "Snapshot too old", представлен список возможных методов предотвращения этой ошибки, а также даны простые сценарии на языке PL/SQL, иллюстрирующие рассмотренные ситуации.

Оригинал обсуждения этого вопроса можно найти здесь.

Copyright © 2002 Oracle Corporation

В следующем выпуске

Результаты опроса подписчиков. Возможно, первый из серии авторских выпусков, посвященных индексам. Или перевод очередного блестящего ответа Тома Кайта... Следите за новостями на сайте проекта Open Oracle.

С наилучшими пожеланиями,

  В.К.