Методы удаления старых данных в базах данных Oracle

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

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

В течение многих лет мы рассматривали лучшие методы. В прошлом я слышал, что люди использовали секционированные таблицы для управления сбором старых данных — например, один месяц на секцию и ежемесячно удаляли самую старую секцию. Главный недостаток этого подхода заключается в том, что наши правила сбора урожая выходят за рамки «удалить месяц X». Пользователи могут указать, как долго данные должны оставаться в системе, на основе ключевых значений (например, в таблице счетов учетная запись foo может быть удалена через 3 месяца, а учетная запись bar может потребоваться в течение 2 лет).

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

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

Застряли ли мы на удалении в обозримом будущем, или есть другие, более умные способы справиться с жатвой?


oracle partitioning purge
person Steve Broberg    schedule 11.06.2009    source источник
comment
+1 хороший вопрос, жаль, что у меня не было умного решения, потому что я мог бы использовать его сам ;-)   -  person DCookie    schedule 11.06.2009
comment
Сохранение данных не вариант? то есть вы можете фильтровать старые записи в своих запросах (например, используя предикаты VPD) и просто не возвращать старые записи. Просто говорю, что если удаление строк вызывает проблемы с производительностью, я бы, по крайней мере, допускал возможность того, что их сохранение не обязательно может быть худшей ситуацией.   -  person Jeffrey Kemp    schedule 19.06.2013

Ответы (4)


arrow_upward
4
arrow_downward

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

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

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

person David Aldridge    schedule 11.06.2009

arrow_upward
0
arrow_downward

Удаление не так уж плохо, при условии, что вы перестроите свои индексы. Oracle восстановит страницы, которые больше не содержат данных.

Однако начиная с версии 8i (и, вполне вероятно, до сих пор) он не будет правильно восстанавливать страницы индекса, которые больше не содержат действительных ссылок. Хуже того, поскольку листья индекса были связаны цепочкой, вы могли попасть в ситуацию, когда он начал бы ходить по листовым узлам, чтобы найти строку. Это привело бы к довольно значительному падению производительности: запросы, которые обычно выполнялись бы за секунды, могут выполняться за минуты. Падение тоже было очень внезапным: сегодня все будет хорошо, а завтра уже нет.

Я обнаружил это поведение (для этого была ошибка Oracle, так что и другие люди тоже) с приложением, которое использовало возрастающие ключи и регулярно удаляло данные. Наше решение состояло в том, чтобы инвертировать части ключа, но это не поможет вам с датами.

person kdgregory    schedule 11.06.2009
comment
Если все записи удалены из листового блока, он убирается нормально. Если вы оставите один или два, это может быть неуклюжим. Индексы с обратным ключом были бы так же эффективны для дат, как и для любого другого типа данных, но они делают сканирование диапазона непрактичным. Большие удаления могут сделать перестроение НЕКОТОРЫХ индексов целесообразным, но измерьте и проверьте, чтобы убедиться, что оно того стоит. - person Gary Myers; 12.06.2009
comment
Я предполагаю, что вы сотрудник Oracle и имеете в виду выпуски после 8i; как я уже сказал, начиная с 8i это была известная ошибка, которую не планировалось исправлять. Переворачивая ключи, я полагаю, мне следовало более четко объяснить, почему это вам не поможет. - person kdgregory; 12.06.2009

arrow_upward
0
arrow_downward

Что, если вы временно деактивируете индексы, выполняете их удаление, а затем перестраиваете? Улучшит ли это производительность ваших удалений? Конечно, в этом случае вы должны убедиться, что сценарии корректны и обеспечить правильный порядок удаления и ссылочную целостность.

person Cătălin Pitiș    schedule 12.06.2009

arrow_upward
0
arrow_downward

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

Таблицы должны разрешать перемещение строк (например, для ретроспективного кадра): alter table TTT разрешает перемещение строк; изменить таблицу TTT сжать пространство; а затем перестроить все индексы.

Я не знаю, как у вас с окнами обслуживания, если приложение должно быть в рабочем состоянии все время, это сложнее, если нет, вы можете сделать некоторую «перепаковку», когда оно отключено. «alter table TTT move tablespace SSSS» проделывает большую работу по устранению беспорядка по мере перезаписи таблицы. Вы также можете указать новые параметры хранилища, такие как управление экстентом, размеры и т. д.

Я использую такой скрипт, чтобы создать скрипт для всей базы данных:

SET SQLPROMPT "-- "
SET ECHO OFF
SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
SET TERMOUT OFF
SET VERIFY OFF
SET TAB OFF
spool doit.sql
select 'prompt Enabling row movement in '||table_name||'...'||CHR (10)||'alter table '||table_name||' enable row movement;' from user_tables where table_name not like '%$%' and table_name not like '%QTAB' and table_name not like 'SYS_%';
select 'prompt Setting initial ext for '||table_name||'...'||CHR (10)||'alter table '||table_name||' move storage (initial 1m);' from user_tables where table_name not like '%$%' and table_name not like '%QTAB' and table_name not like 'SYS_%';
select 'prompt Shrinking space for '||table_name||'...'||CHR (10)||'alter table '||table_name||' shrink space;' from user_tables where table_name not like '%$%' and table_name not like '%QTAB' and table_name not like 'SYS_%';
select 'prompt Rebuilding index '||index_name||'...'||CHR (10)||'alter index '||index_name||' rebuild;' from user_indexes where status = 'UNUSABLE';
spool off
prompt now check and then run @doit.sql
exit
person slovon    schedule 15.06.2009