Oracle находит неиспользуемые индексы

Обновлено: 21.11.2024

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

Один из способов сэкономить компании — удалить ненужные таблицы, данные и индексы и освободить место на диске. Это также делает базу данных более легкой, управляемой и более быстрой. Поиск нежелательных объектов базы данных является сложной задачей, но вполне выполним, если вы сотрудничаете с разработчиком или владельцем данных. Недавно я восстановил более 15 ТБ пространства из хранилища данных, что, согласно статье, равносильно экономии более 10 миллионов долларов. В этой статье основное внимание уделяется поиску неиспользуемого индекса. Индекс может быть таким же большим, как размер таблицы или больше. Когда вы заметите, что размер больше таблицы, это тревожный сигнал, требующий немедленного внимания.

  1. Данные в столбце "Индекс" сильно изменились.
  2. Неправильный дизайн запроса.
  3. Приложение устарело.
  4. Неправильный дизайн базы данных.
  5. Изменение статистики.
  6. Изменение плана выполнения запроса.

SQL-СЕРВЕР:

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

ОРАКУЛ:

Oracle не имеет такого универсального решения, как представление SQL Server dm_db_index_usage_stats, в котором хранится статистика использования индекса. Oracle использует несколько шагов для определения использования индекса.

Шаг 1. Включите использование индекса мониторинга.

Шаг 2. Подождите несколько дней и выполните приведенный ниже запрос, чтобы проверить статус использования. Этот запрос покажет использование индекса.

Шаг 3. Удалите неиспользуемый индекс. Если индекс используется, нет необходимости удалять индекс. Мониторинг должен быть отключен.

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

Примечание. Никогда не удаляйте индексы, созданные с помощью ограничений. Это относится к любой СУБД, а не только к Oracle и SQL Server.

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

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

Заранее спасибо.

Ответы

Укажите "неиспользуемые" индексы.

а) могут быть индексы в статусе "неиспользуемые"

b) или индексы, не используемые запросами

Каково ваше определение "неиспользуемых" индексов?

Попробуйте опубликовать сообщение на форуме.

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

Затем вы можете запустить операторы SQL и после этого проверить использование объекта:

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

ВСЕ ваши индексы "не используются" до тех пор, пока кто-то не выполнит запрос, позволяющий Oracle эффективно использовать индекс.

Только ВЫ знаете, когда пользователи могут выполнять такие запросы и могут ли они использовать такой индекс.

В большинстве организаций есть запросы, которые выполняются только ежемесячно, ежеквартально или ежегодно. НЕВОЗМОЖНО узнать, есть ли у вас запрос на конец года, для эффективного выполнения которого требуется один или несколько индексов.

Начните сначала и расскажите нам, КАКУЮ ПРОБЛЕМУ вы пытаетесь решить?

Индексы используются ТОЛЬКО как часть «решения» «проблемы».

Документация вашего проекта (например,Документ с техническими требованиями) должен документировать ВСЕ индексы, которые добавляются в систему, и ПОЧЕМУ они были добавлены (т. е. для решения какой «проблемы» они использовались).

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

Если в вашей организации нет такой документации по индексам, которые она использует, значит, у вас ОГРОМНАЯ НЕДОСТАТОЧНАЯ документация. Каждая таблица (конечно, ключевые таблицы) должна иметь документацию с подробной информацией о том, какие данные содержит таблица, как эти данные используются, какие индексы/ограничения/триггеры/и т. д. используются, ПОЧЕМУ используются эти индексы/ограничения/триггеры/и т. д. и многое другое. .

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

Это по-прежнему НЕ поможет с запросами на конец года (или в будущем), которые, возможно, еще не были выполнены.

<р>1. У вас 10 пар носков в ящике комода

<р>2. Ваша вторая половинка хочет выбросить все ваши «неиспользованные» пары носков

<р>3. Как ваша вторая половинка определит, какие пары носков «неиспользованы»? Возможно, вы планируете надеть любимую пару носков на рождественскую вечеринку компании в конце года через шесть месяцев.

Отказ от ответственности. Я прекрасно понимаю, что приведенный выше пример является «надуманным». Любая «реалистичная» вторая половинка просто выбросит носки, а потом скажет, что просто («упс») совершила ошибку.

Отслеживание использования индекса в Oracle 12.2 заменяет старые функции мониторинга индексов предыдущих версий. Эта статья, по сути, представляет собой переписанную предыдущую статью о мониторинге индексов, приведенную в соответствие с Oracle Database 12c Release 2 (12.2).

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

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

Настройка

Для примеров в этой статье требуются следующие объекты схемы.

Сэмплировать или нет

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

Я погуглил и нашел сообщение Франка Пахота, в котором упоминается следующий переключатель для управления типом сбора статистики.

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

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

V$INDEX_USAGE_INFO

Использование индекса отслеживается в памяти, при этом информация об использовании верхнего уровня отображается в представлении V$INDEX_USAGE_INFO.

Столбцы представления описаны здесь полностью, но вот некоторые из них, которые выделяются.

  • INDEX_STATS_ENABLED: значение по умолчанию 1 означает, что статистика индекса включена. 0 означает отключено.
  • INDEX_STATS_COLLECTION_TYPE : значение по умолчанию 1 означает выборку статистики индекса. 0 означает, что все использование отслеживается. Выборка означает, что данные менее точны, но меньше накладных расходов.
  • ACTIVE_ELEM_COUNT : количество активных индексов с момента последней очистки.
  • LAST_FLUSH_TIME: время последнего сброса статистики на диск.

Каждые 15 минут использование индекса сбрасывается из памяти на диск, LAST_FLUSH_TIME обновляется, а ACTIVE_ELEM_COUNT устанавливается равным 0.Вы можете увидеть детали на уровне объекта только после сброса, поэтому вам нужно будет следить за значением LAST_FLUSH_TIME, когда вы отслеживаете использование индекса.

Следующий запрос проверяет представление V$INDEX_USAGE_INFO, выполняет некоторые действия, использующие индексы, и снова проверяет представление.

Мы видим, что все три индекса были активны с момента последнего сброса.

DBA_INDEX_USAGE

В представлении DBA_INDEX_USAGE отображается использование индекса на уровне объектов после его сброса на диск.

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

Помните, что вам придется дождаться сброса, прежде чем это представление будет содержать информацию.

Следующий запрос отображает информацию об использовании индекса.

Следующий запрос отображает гистограмму доступа для индексов. Сосредоточившись на индексе столбца даты, мы можем увидеть два доступа к корзине BUCKET_0_ACCESS_COUNT. Ни одна строка не была возвращена, и, следовательно, не требовалось последующего доступа к индексу. Был один случай доступа, который требовал от 11 до 100 доступов, когда мы запросили 20 строк для текущего дня.

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

На момент написания документации по этой гистограмме не было ясности. Я подтвердил с Oracle, что это ожидаемое поведение, и документация будет обновлена, чтобы отразить это, но, по моему мнению, столбцы гистограммы должны записывать количество вхождений запросов, возвращающих строки в этом диапазоне, а не сумму строк в этот диапазон. Я ожидал BUCKET_11_100_ROWS_RETURNED=1 и TOTAL_ROWS_RETURNED=20 , но это только мое мнение.

Индексы внешних ключей

Цитата из руководства Oracle Database Concepts.

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

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

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

Эта проблема несколько устранена в более поздних версиях, но ее все же стоит рассмотреть и, прежде всего, протестировать.

Механизм отслеживания использования индекса не обнаруживает индексы, используемые в этом сценарии, как продемонстрировал Франк Пашо.

Ограничения

Тот факт, что отслеживание использования индекса включено по умолчанию в Oracle 12.2, является очень полезным дополнением. Действие статистики использования индекса выборки по умолчанию означает возможность неточностей. Я буквально никогда не обнаруживал использование индекса, пока не переключился на тип коллекции «ВСЕ». Franck Pachot продемонстрировал, что индексы помечаются как используемые во время сбора статистики, а индексы, поддерживающие блокировку внешним ключом, не помечаются как используемые. Просто имейте в виду, что выборка по умолчанию не идеальна, но переключение на «ВСЕ» может привести к значительным накладным расходам.

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

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

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

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

1. Используйте собственный мониторинг использования индекса Oracle

Использование индекса выражается как состояние ДА/НЕТ, которое меняется на ДА, если индекс используется любым выполняемым SQL-оператором.
В сочетании с отметкой времени начала мониторинга вы можете определить индексы, которые не используются SQL с момента отметки времени запуска.

2. Убедитесь, что индексы не нужны для защиты внешнего ключа

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

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

Постоянный мониторинг использования индекса

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

Это можно сделать с помощью простого задания PL/SQL, которое выполняется, например. ежедневно.
Информация об использовании сбрасывается через 8 дней, если индекс был помечен как используемый.

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

Выполнение ALTER INDEX . МОНИТОРИНГ ИСПОЛЬЗОВАНИЯ приводит к аннулированию существующих планов запросов для таблицы этого индекса.
Полученный в результате жесткий синтаксический анализ при следующем выполнении может стать проблемой производительности для часто выполняемых операторов с большим количеством сеансов, выполняющих этот оператор.
Поэтому статус использования используемых индексов сбрасывается командой ALTER INDEX . МОНИТОРИНГ ИСПОЛЬЗОВАНИЯ только в том случае, если в SGA еще нет планов SQL, использующих его индекс.

Если в SGA существует план SQL, использующий этот индекс, этого достаточно для использования индекса, поэтому необходимо сбросить ALTER INDEX . MONITORING USAGE для перезапуска мониторинга в этом случае не требуется.

Скрипт для мониторинга по схеме

Скрипт для мониторинга каждой базы данных

Оценка использования индекса

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

Существует два способа сканирования неиспользуемых индексов

1. v$Object_Usage содержит состояние мониторинга для индексов вашей текущей схемы

2. sys.Object_Usage содержит состояние мониторинга для индексов всех схем

Оценка состояния использования индекса с помощью Panorama

Показать информацию об использовании в подробном представлении таблицы/индекса

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

При нажатии на ссылку "Показать" в столбце "SQL" отображаются все SQL из SGA или AWR-истории, которые используют этот индекс.

Читайте также: