Oracle собирает статистику по таблице

Обновлено: 02.07.2024

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

Как собрать статистику в Oracle?

Для сбора статистики в oracle нам потребуется пакет DBMS_STATS. Он будет собирать статистику параллельно со сбором глобальной статистики для секционированных объектов. Пакет DBMS_STATS специально используется только для статистики оптимизатора. Как я объяснил в первом абзаце, dbms_stats очень важен для хорошей производительности SQL. Нам необходимо собрать статистику перед настройкой или настройкой любых параметров оптимизатора в оракуле.

Оптимизация – это процесс, при котором SQL может выполняться в эффективное время.

Использование пакета DBMS_STATS:

1.Чтобы изменить статистику

3.Чтобы удалить статистику

4.Чтобы экспортировать или импортировать статистику

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

Синтаксис для сбора статистики на уровне схемы:

exec DBMS_STATS.GATHER_SCHEMA_STATS(собственное имя,estimate_percent, block_sample, method_opt,степень,зернистость,каскад,stattab,statid,options,statown,no_invalidate, collect_temp,gather_fixed);

Сценарий 1. Сбор статистики для Schema

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

Сценарий 2. Сбор статистики в процентах по схеме:

exec dbms_stats.gather_schema_stats(ownname=>’Amit_Schema’, Assessment_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);

exec dbms_stats.gather_schema_stats(собственное имя => ‘Amit_Schema’, оценка_percent => 50);

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

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

Сбор статистики для таблицы:

Мы можем собирать статистику на уровне таблицы. Если после этого пользователь создает индексы или использует какой-либо метод секционирования, нам требуется собрать статистику. Мы можем собирать статистику с помощью процедуры Gather_table_stats пакета dbms_stats.

exec dbms_stats.gather_table_stats(‘Schema_name’, ‘Table_name’);

Пример 1:

exec dbms_stats.gather_table_stats('Amit_schema', 'Сотрудник');

Он соберет таблицу статистики сотрудников в схеме Amit_Schema.

Пример 2:

Запрос:

exec dbms_stats.gather_table_stats (
ownname => 'Amit_Schema',
tabname => 'Employee',
estimate_percent => dbms_stats.auto_sample_size,
method_opt = > 'для всех столбцов размер авто',
cascade => true,
степень => 5
)
/
Процедура PL/SQL успешно завершена.

собственное имяЭто не что иное, как имя схемы
имя вкладкиИмя таблицы для сбора статистики
estimate_percentОценка процента строк (NULL означает вычисление). Используйте константу DBMS_STATS.AUTO_SAMPLE_SIZE, чтобы Oracle определил подходящий размер выборки для достоверной статистики. Это параметр по умолчанию.
method_optЭто также параметр по умолчанию, который указывает ДЛЯ ВСЕХ КОЛОНН РАЗМЕР АВТО.
cascadeЭтот оператор используется для сбора статистики по индексам для этой таблицы.
степеньЭто указывает степень параллелизма. По умолчанию степень равна NONE.

Сбор статистики для индекса:

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

Синтаксис:

exec dbms_stats.gather_index_stats(‘table_name’, ‘Index name’);

Удаление статистики схемы:

Мы можем удалить статистику схемы, используя следующую инструкцию:

Приведенное выше выражение удалит статистику Amit_Schema.

Сбор статистики, другие примеры:

Это разные процедуры пакета DBMS_STATS.

Как собрать статистику для объекта секционированной схемы:

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

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

8 ответов на вопрос «Сбор статистики в Oracle: схема, таблицы и индексы»

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

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

На какой версии оракула это работает?

Для любой версии оракула этот синтаксис использовался для сбора статистики. Я тестировал его на 11G и 12c.

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

спасибо за информацию
действительно хорошо

Спасибо, Луис, за добрые слова!

Отличная публикация 👌🏻👍🏻большое спасибо за подробное объяснение, независимо от того, сколько лет опыта у нас есть, стоит обновить концепции, и это также помогает лучше подготовиться к интервью.

Сейчас в Google есть тонны контента, но всегда сложно выбрать лучший контент, так что это лучший полезный контент, который очень полезен и многому учит

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

При выполнении оператора SQL база данных должна преобразовать запрос в план выполнения и выбрать наилучший способ извлечения данных. Для Oracle каждый SQL-запрос имеет множество вариантов планов выполнения, в том числе, какой индекс использовать для извлечения строки таблицы, в каком порядке объединять несколько таблиц и какие внутренние методы соединения использовать (в Oracle есть соединения с вложенными циклами, хеш-соединения, звездообразные соединения и сортировка методов объединения слиянием). Эти планы выполнения вычисляются оптимизатором SQL на основе затрат Oracle, широко известным как CBO.

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

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

Это довольно простой план. В нем оптимизатор говорит Oracle сначала получить все строки отдела EMP, а затем отсортировать эти строки (Чтение плана выполнения — это своего рода искусство, так что поверьте нам, полное сканирование таблицы EMP — это первый).

Вы заметили, что в плане есть столбец СТРОКИ? Это количество строк, которые будет обрабатывать запрос. Как Oracle узнал, что он собирается обработать 100 строк. Эта штука с Oracle умная, не так ли.

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

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

Старомодные методы analysis table и dbms_utility для создания статистики CBO устарели и несколько опасны для производительности SQL, поскольку они не всегда собирают высококачественную информацию о таблицах и индексах. CBO использует статистику объектов для выбора наилучшего плана выполнения для всех операторов SQL.

Утилита dbms_stats намного лучше оценивает статистику, особенно для больших секционированных таблиц, а чем выше статистика, тем быстрее выполняются планы выполнения SQL. Вот пример выполнения dbms_stats с опцией options.

Как создать статистику Oracle

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

Для создания статистики мы используем сохраненный пакет dbms_stats. В пакете dbms_stats есть две процедуры, которые вас больше всего заинтересуют: dbms_stats.gather_schma_stats и dbms_stats.collect_table_stats. Кроме того, в базе данных Oracle 10g и более поздних версиях у вас есть возможность собирать системную статистику и статистику фиксированных представлений. Далее рассмотрим каждую из этих операций более подробно.

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

Использование dbms_stats.gather_schema_stats

Процедура dbms_stats.gather_schema_stats позволяет собирать статистику для всех объектов в заданной схеме. Это самый простой способ генерировать статистику для большого количества объектов. Вот пример использования процедуры dbms_stats.gather_schema_stats для сбора статистики по схеме SCOTT базы данных:

EXEC dbms_stats.gather_schema_stats('SCOTT', cascade=>TRUE);

Эта команда создаст статистику по всем таблицам в схеме SCOTT. Поскольку мы включили команду cascade, для индексов также будет сгенерирована статистика. Это важно, нужна статистика как по индексам, так и по таблицам в Oracle!

Конечно, это всего лишь базовый способ запуска этой команды. Доступно несколько вариантов, но на данный момент для нового администратора баз данных этого вполне достаточно. Фактически, Oracle 10g автоматически собирает статистику базы данных каждую ночь из коробки. Позже вы захотите изучить некоторые параметры сбора статистики Oracle Database 10g, такие как гистограммы и степень детализации.

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

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

Получите полную
информацию о настройке Oracle SQL

Знаменитая книга "Расширенная настройка Oracle SQL. Полное руководство" содержит ценную информацию о настройке Oracle SQL. Эта книга включает сценарии и инструменты для повышения производительности Oracle 11g, и вы можете купить ее со скидкой 30 % непосредственно у издателя.


Бурлесон — американская команда


Примечание. Эта документация по Oracle была создана в качестве справочника по поддержке и обучению Oracle для использования нашими специалистами-консультантами по настройке производительности администраторов баз данных. Не стесняйтесь задавать вопросы на нашем форуме Oracle.

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

Ошибки? Технология Oracle меняется, и мы стараемся обновлять нашу информацию о поддержке BC Oracle. Если вы обнаружите ошибку или у вас есть предложение по улучшению нашего контента, мы будем признательны за ваш отзыв. Просто электронная почта:


и укажите URL-адрес страницы.


Burleson Consulting

Оракул поддержки баз данных

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

  • Оптимизатор, основанный на правилах (RBO). Это был первоначальный метод оптимизации, и, как следует из названия, он представлял собой список правил, которым Oracle должен следовать для создания плана выполнения. Даже после того, как был введен оптимизатор на основе затрат, этот метод использовался, если у сервера не было внутренней статистики, относящейся к объектам, на которые ссылается оператор, или если явно запрашивался подсказкой или параметром экземпляра/сеанса. Этот оптимизатор устарел, а затем объявлен устаревшим в более поздних версиях базы данных.
  • Оптимизатор на основе затрат (CBO). CBO использует статистику базы данных для создания нескольких планов выполнения, выбирая план с наименьшей стоимостью, где стоимость связана с системными ресурсами, необходимыми для выполнения операции.

В более новых версиях базы данных оптимизатор на основе затрат является единственным доступным вариантом. Если создаются новые объекты, изменяется объем данных или разброс данных в базе данных, статистика больше не будет отражать реальное состояние базы данных, поэтому процесс принятия решений по CBO может быть серьезно нарушен.В этой статье основное внимание будет уделено управлению статистикой с помощью пакета DBMS_STATS, хотя будут упомянуты и устаревшие методы.

Введение

"Вам не обязательно нужна актуальная статистика. Вам нужна статистика, которая репрезентативна для ваших данных". - Грэм Вуд.

Это означает, что возраст статистики в вашей системе не является проблемой, если она по-прежнему репрезентативна для ваших данных. Таким образом, просто просмотр столбца LAST_ANALYZED в представлении DBA_TABLES не является признаком действительной статистики в вашей системе.

"Вы хотите, чтобы оптимизатор давал вам наилучшую производительность или постоянную производительность?" - Аньо Колк

Это означает, что регулярное изменение статистики может привести к изменениям. Изменения — это не всегда хорошо.

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

Какой должна быть наша стратегия в отношении статистики? Вот несколько предложений.

  • Автоматический сбор статистики оптимизатора. Начиная с версии 10g, база данных автоматически ежедневно собирает статистику. Задание статистики по умолчанию на протяжении многих лет подвергалось большой критике, но его значение зависит от типа систем, которыми вы управляете. Большая часть этой критики исходит от людей, обсуждающих пограничные случаи, такие как большие хранилища данных. Если вы управляете большим количеством небольших баз данных с относительно скромными требованиями к производительности, вы можете позволить Oracle заниматься статистикой самостоятельно. Если у вас есть какие-то конкретные проблемы, решайте их в каждом конкретном случае.
  • Смешанный подход: вы полагаетесь на автоматическое задание для сбора большей части статистики, но у вас есть определенные таблицы или схемы, которые предъявляют очень специфические требования к статистике. В этих случаях вы можете либо установить настройки для рассматриваемых объектов, либо заблокировать статистику для определенных таблиц/схем, чтобы предотвратить их изменение заданием, а затем разработать собственное решение для этих таблиц/схем.
  • Вручную: вы полностью отключаете автоматический сбор статистики и разрабатываете индивидуальное решение для всей базы данных.

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

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

СТАТИСТИКА_СУБД

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

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

Статистика таблиц и индексов

Табличная статистика может быть собрана для базы данных, схемы, таблицы или раздела.

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

В Oracle 11g константа AUTO_SAMPLE_SIZE является предпочтительным (и используемым по умолчанию) размером выборки, поскольку механизм определения фактического размера выборки был улучшен. Кроме того, оценка статистики, основанная на автоматической выборке, почти на 100 % точна и собирается намного быстрее, чем в предыдущих версиях, как описано здесь.

Параметр CASCADE определяет, следует ли собирать статистику для всех индексов анализируемой таблицы. До Oracle 10g значение по умолчанию было FALSE, но в 10g и выше по умолчанию используется AUTO_CASCADE , что означает, что Oracle определяет, нужна ли статистика индекса.

В результате этих изменений поведения при сборе статистики в Oracle 11g и выше базовые значения по умолчанию для сбора статистики таблиц являются удовлетворительными для большинства таблиц.

Статистика индекса может быть собрана явным образом с помощью процедуры GATHER_INDEX_STATS.

Текущая статистическая информация доступна из представлений словаря данных для конкретных объектов (представления DBA, ALL и USER). Некоторые из этих представлений были добавлены в более поздних версиях.

  • DBA_TABLES
  • DBA_TAB_STATISTICS
  • DBA_TAB_PARTITIONS
  • DBA_TAB_SUB_PARTITIONS
  • DBA_TAB_COLUMNS
  • DBA_TAB_COL_STATISTICS
  • DBA_PART_COL_STATISTICS
  • DBA_SUBPART_COL_STATISTICS
  • DBA_INDEXES
  • DBA_IND_STATISTICS
  • DBA_IND_PARTITIONS
  • DBA_IND_SUBPARTIONS

Информация о гистограмме доступна в следующих представлениях.

  • DBA_TAB_HISTOGRAMS
  • DBA_PART_HISTOGRAMS
  • DBA_SUBPART_HISTOGRAMS

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

Статистика системы

Появившаяся в Oracle 9iR1 процедура GATHER_SYSTEM_STATS собирает статистику, относящуюся к производительности системного ввода-вывода и ЦП. Предоставление этой информации оптимизатору делает его выбор плана выполнения более точным, поскольку он может взвесить относительную стоимость операций с использованием как ЦП, так и профилей ввода-вывода системы.

Существует два возможных типа системной статистики:

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

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

Текущая системная статистика может быть отображена путем запроса таблицы AUX_STATS$.

Если вы используете версию 11.2.0.1 или 11.2.0.2, ознакомьтесь с примечанием MOS: 9842771.8.

Процедура DELETE_SYSTEM_STATS удалит всю статистику рабочей нагрузки и заменит ранее собранную статистику без рабочей нагрузки значениями по умолчанию.

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

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

Имена доступных параметров можно найти здесь.

Если сомневаетесь, используйте значения по умолчанию.

Статистика фиксированного объекта

Появившаяся в Oracle 10gR1 процедура GATHER_FIXED_OBJECTS_STATS собирает статистику по таблицам X$, которые находятся под динамическими представлениями производительности V$. Таблицы X$ на самом деле вовсе не таблицы, а окно в структуры памяти в ядре Oracle. Статистические данные по фиксированным объектам не собираются автоматически, поэтому их необходимо собирать вручную, когда база данных находится на репрезентативном уровне активности.

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

Статистика удаляется с помощью процедуры DELETE_FIXED_OBJECTS_STATS.

Блокировка статистики

Чтобы предотвратить перезапись статистики, вы можете заблокировать статистику на уровне схемы, таблицы или раздела.

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

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

Перенос статистики

Возможна передача статистики между серверами, что позволяет согласовать планы выполнения между серверами с различными объемами данных. Сначала необходимо собрать статистику в таблицу статистики. В следующих примерах статистика для пользователя APPSCHEMA собирается в новую таблицу STATS_TABLE, которая принадлежит DBASCHEMA.

Затем эту таблицу можно перенести на другой сервер с помощью предпочитаемого вами метода (экспорт/импорт, SQL*Plus COPY и т. д.), а статистику импортировать в словарь данных следующим образом.

Настройка параметров

Начиная с Oracle 10g, многие значения параметров по умолчанию для процедур DBMS_STATS были изменены с жестко заданных на использование предпочтений. В Oracle 10g эти настройки можно было изменить с помощью процедуры SET_PARAM.

В версии 11g процедура SET_PARAM устарела в пользу многоуровневого подхода к настройкам. Четыре уровня предпочтений изменяются с помощью следующих процедур.

  • SET_GLOBAL_PREFS: используется для установки глобальных настроек, включая некоторые особенности задания автоматического сбора статистики.
  • SET_DATABASE_PREFS: устанавливает настройки для всей базы данных.
  • SET_SCHEMA_PREFS: задает настройки для определенной схемы.
  • SET_TABLE_PREFS: устанавливает настройки для определенной таблицы.
< /tr> < /tr> < /tr> < tr>
Предпочтение Описание По умолчанию (11gR2) Область действия Версия
CASCADE Определяет, следует ли собирать статистику индекса для текущей таблицы (TRUE, FALSE, AUTO_CASCADE). DBMS_STATS.AUTO_CASCADE G, D, S, T 10gR1+
DEGREE Степень параллелизма (целое число или DEFAULT_DEGREE).< /td> DBMS_STATS.DEFAULT_DEGREE G, D, S, T 10gR1+
ESTIMATE_PERCENT Процент строк для выборки при сборе статистики (0,000001-100 или AUTO_SAMPLE_SIZE). DBMS_STATS.AUTO_SAMPLE_SIZE G, D, S, T 10gR1+
METHOD_OPT Управляет сбором статистики столбца и созданием гистограммы. ДЛЯ ВСЕХ СТОЛБЦОВ РАЗМЕР АВТО G, D, S, T 10gR1+
NO_INVALIDATE Определяет, должны ли зависимые курсоры аннулироваться в результате новой статистики на объектах (TRUE, FALSE или AUTO_INVALID ATE). DBMS_STATS.AUTO_INVALIDATE G, D, S, T 10gR1+
AUTOSTATS_TARGET Определяет, какие объекты имеют собранную статистику (ALL, ORACLE, AUTO). AUTO G 10gR2+
ГРАНУЛЯРНОСТЬ Гранулярность статистики, которая будет собираться для разделенных объектов (ВСЕ, АВТО, ПО УМОЛЧАНИЮ, ГЛОБАЛЬНО, «ГЛОБАЛЬНО И РАЗДЕЛ», РАЗДЕЛ, ПОДРАЗДЕЛ). AUTO G, D, S, T 10gR2+
ПУБЛИКАЦИЯ Определяет если собранную статистику следует опубликовать немедленно или оставить в состоянии ожидания (ИСТИНА, ЛОЖЬ). TRUE G, D, S, T 11gR2+< /td>
INCREMENTAL Определяет, будет ли добавочная статистика использоваться для глобальной статистики по разделенным объектам, а не генерироваться с помощью сканирования таблицы (TRUE, FALSE). FALSE G, D, S, T 11gR2+
CONCURRENT Должен статистика объектов собирается по нескольким объектам одновременно, или по одному (РУЧНОЙ, АВТОМАТИЧЕСКИЙ, ВСЕ, ВЫКЛ). ВЫКЛ G 12cR1+
GLOBAL_TEMP_TABLE_STATS Должна ли статистика в глобальных временных таблицах быть специфичной для сеанса или использоваться совместно между сеансами (SHARED, SESSION). SESSION G, D, S 12cR1+
INCREMENTAL_LEVEL Какой уровень сводок следует собирать для добавочной секционированной статистики (TABLE, PARTITION). РАЗДЕЛ G, D, S, T 12cR1+
INCREMENTAL_STALENESS Как определяется устаревание статистики разделов (USE_STALE_PERCENT, USE_LOCKED_STATS, NULL). NULL G, D, S, T 12cR1+
TABLE_CACHED_BLOCKS Количество блоков, закэшированных в буферном кеше при вычислении коэффициента кластеризации индекса. Джонатан Льюис рекомендует "16" как разумное значение. 1 G, D, S, T 12cR1+
OPTIONS Используется для параметра OPTIONS процедуры GATHER_TABLE_STATS (GATHER, GATHER AUTO). GATHER G, D, S, Т 12cR1+

Ниже показано их основное использование.

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

Настройка статистики вручную

Пакет DBMS_STATS предоставляет несколько процедур для ручной настройки статистики.

  • SET_SYSTEM_STATS
  • SET_TABLE_STATS
  • SET_COLUMN_STATS
  • SET_INDEX_STATS

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

  • GET_SYSTEM_STATS
  • GET_TABLE_STATS
  • GET_COLUMN_STATS
  • GET_INDEX_STATS

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

Проблемы

  • Исключите таблицы загрузки данных из регулярного сбора статистики, если вы не знаете, что они будут заполнены во время сбора статистики.
  • До версии 10g сбор статистики для схемы SYS мог замедлить, а не ускорить работу системы.
  • Сбор статистики может потребовать больших ресурсов сервера, поэтому избегайте периодов пиковой нагрузки или собирайте только устаревшую статистику.
  • Даже если это запланировано, может потребоваться сбор свежей статистики после обслуживания базы данных или больших объемов данных.

Устаревшие методы сбора статистики базы данных

Информация в этом разделе представлена ​​исключительно по историческим причинам. Все управление статистикой теперь должно осуществляться с помощью пакета DBMS_STATS.

Анализ отчета

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

СУБД_УТИЛИТИ

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

Обновление устаревшей статистики

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

Статистика планирования

До Oracle 10g планирование сбора статистики с помощью пакета DBMS_JOB было самым простым способом убедиться, что она всегда актуальна.

Приведенный выше код настраивает задание на сбор статистики для SCOTT за текущее время каждый день. Вы можете перечислить текущие задания на сервере, используя представления DBA_JOBS и DBA_JOBS_RUNNING.

Oracle Database 19c Automatic Indexing: Invisible Indexes Oddity (Wild Eyed Boy From Freecloud) 3 февраля 2021 г.


Было несколько «странностей» в отношении как автономных баз данных Oracle, так и поведения автоматического индексирования, которые я видел достаточно часто (в Oracle 19.5.0.0.0), чтобы о них стоило написать короткую статью в блоге.< /p>

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

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

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

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

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

Поэтому наличие этих гистограмм очень странно.

Если мы запустим следующий запрос с предикатом фильтрации на основе столбца CODE:

В настоящее время у CBO нет другого выбора, кроме как использовать FTS без индекса. Но что сделает с вещами автоматическое индексирование? Если мы посмотрим на следующий отчет об автоматическом индексировании:

Мы заметили, что Oracle создал автоматический индекс, но это НЕВИДИМЫЙ индекс!!

Если мы посмотрим на детали этого автоматического индекса:

Индекс находится в состоянии INVISIBLE/VALID, а не в обычном состоянии INVISIBLE/UNUSABLE для индекса, для которого автоматическое индексирование решает, что индекс недостаточно эффективен для реализации.

Это НЕ ожидаемое поведение.

Обычно индексы INVISIBLE/VALID создаются, когда автоматическое индексирование находится в режиме «ТОЛЬКО ОТЧЕТ», хотя я сталкивался с этим сценарием, когда статистика устарела или отсутствует. Но в этом случае автоматическое индексирование находится в режиме «РЕАЛИЗАЦИЯ», и таблица недавно собрала статистику, хотя и с присутствием странных гистограмм (поэтому я думаю, что эти проблемы связаны).

Если мы снова запустим тот же запрос:

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

Если вы полагаетесь на автоматическое индексирование и используете его в режиме реализации, я бы порекомендовал проверить все индексы в этом состоянии INVISIBLE/VALID, поскольку они указывают на то, что, скорее всего, что-то пошло не так…

Автоматическое индексирование Oracle 19c: искажение данных, часть 2 (все в порядке) 14 сентября 2020 г.


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

В этом посте я отвечу, КАК этого удалось добиться CBO.

Получите некоторое представление об ответе, взглянув на детали столбца:

Мы видим, что теперь в столбце действительно есть гистограмма. Когда и как были собраны эти гистограммы?

Ответ кроется в новой функции Oracle Database 19c под названием «Высокочастотный автоматический сбор статистики», которая доступна в средах Exadata. Поскольку я запускаю все эти демонстрации в облачной среде Oracle Autonomous Transaction Processing Cloud, которая работает на платформе Exadata, эта функция включена по умолчанию.

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

По таблице BOWIE1 не собрана статистика.

Таблица BOWIE2 содержит новые строки, добавленные после сбора статистики, а также "устаревшую" устаревшую статистику.

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

Если мы посмотрим на текущую статистику по этим таблицам:

Мы видим, что у BOWIE1 действительно нет статистики.

BOWIE2 помечен как имеющий статистику состояния, хотя благодаря другой функции Oracle Database 19c под названием «Сбор статистики в реальном времени» в нем собрана дополнительная статистика (например, NUM_ROWS), когда дополнительные строки были вставлены. Я расскажу об этой функции более подробно в следующей статье в блоге.

BOWIE3 считается нормальным, поскольку его статистика НЕ ​​устарела, НО…

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

Если мы подождем примерно 15 минут (по умолчанию), пока запустится процесс высокочастотного автоматического сбора статистики, и еще раз просмотрим статистику по этим столбцам:

Теперь мы замечаем, что:

BOWIE1 теперь собирает статистику, так как процесс высокочастотного автоматического сбора статистики ищет таблицы с отсутствующей статистикой.

BOWIE2 теперь имеет полностью обновленную статистику, так как процесс высокочастотного автоматического сбора статистики ищет таблицы с устаревшей статистикой.

BOWIE3 теперь имеет гистограммы в столбцах CODE, так как процесс высокочастотного автоматического сбора статистики ищет отсутствующие гистограммы, если впоследствии были выполнены запросы с плохой оценкой кардинальности.

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

Вы можете настроить высокочастотный автоматический сбор статистики следующим образом:

Это включает/выключает функцию. По умолчанию она отключена в стандартных средах Exadata, но включена по умолчанию в среде автономной базы данных.

Указывает, как долго может выполняться процесс (по умолчанию 3600 секунд/60 минут).

Это настраивает интервал между запуском процесса (по умолчанию каждые 900 секунд/15 минут).

В следующем посте я рассмотрю немного более сложный пример перекоса данных с автоматическим индексированием, где вызываются как селективные, так и неселективные предикаты SQL…

12c Index Like Table Statistics Collection (Wearing The Inside Out) 9 июля 2014 г.

Это изменение, введенное в версии 12c, несколько раз привлекало меня внимание.

Если бы вы создали новую таблицу:

А затем заполните его обычной вставкой:

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

Но если бы мы сейчас создали индекс для этой таблицы:

Мы обнаружили, что теперь у нас действительно есть индексная статистика, собираемая по умолчанию (во всяком случае, с 9i дней):

Хорошо, если теперь мы создадим другую таблицу и индекс:

И заполните его параллельной массовой загрузкой, добавив вставку:

Если мы теперь посмотрим на статистику таблицы:

Мы заметили, что в 12c они автоматически заполняются и точны. В столбце ПРИМЕЧАНИЯ в dba_tab_col_statistics указано, что статистика была собрана с помощью STATS_ON_LOAD.

Если же мы посмотрим на текущее состояние статистики индекса:

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

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

Такой же автоматический сбор статистики таблицы происходит, когда мы создаем таблицу с помощью предложения sub-select:

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

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