Oracle собрать статистику по таблице
Стандартная проблема: «хороший» MERGE, заливающий данные из вспомогательной таблицы (периодически наполняется и truncate-ится) неожиданно начинает выполняться крайне медленно, при этом команда EXPLAIN PLAN показывает хороший, быстрый план выполнения:
С планом — понятно, EXPLAIN PLAN не в первый раз показывает неактуальный план (при этом никакие связанные переменные на процесс генерации не влияют — их просто нет), правильный запрос к V$SQL_PLAN или dbms_xplan.display_cursor показывают как в действительности выполняется запрос:
— понятно, что NESTED LOOPS с TABLE ACCESS FULL доступом к внутренней таблице соединения (SHOWS) — не самое быстрое развлечение)
Также из обоих планов хорошо видно, что в основе проблемы лежит ошибка в оценке количества строк вспомогательной таблицы TEMP_SHOWS E-Rows=1, источником которой является статистика таблицы:
— для периодически очищаемой таблицы это типичная ситуация, просто статистика собралась в неудачный момент — сразу после truncate)
Как вариант фиксации хорошего плана/статистики можно:
- собирать и блокировать правильную с точки зрения планов выполнения статистику, например, перед truncate
- установить и навсегда заблокировать реальные расчётные (или заведомо большие, приводящие к выбору правильного плана) значения в статистике:
, и получить удовлетворительный (быстрый) план с фантастическими фиксированными оценками Cardinality (Rows) и стоимости:
Кроме этого в документации даётся ещё одна, более гибкая рекомендация:
- Статистика может быть удалена. Если Oracle сталкивается с таблицей без статистики, необходимая статистика собирается динамически как часть процесса оптимизации запроса. Динамический сбор статистики контролируется параметром OPTIMIZER_DYNAMIC_SAMPLING …
Вариант с удалением статистики волатильной таблицы может оказаться предпочтительным, учитывая возможные изменения данных в будующем, для нечасто выполняющихся (чтобы парсинг & сэмплинг периодически выполнялись) и не очень быстрых (сэмплинг требуют немного времени — миллисекунды, но всё-таки) запросов
После удаления+блокирования статистики:
план, построенный с использованием dynamic sampling, становится более правдоподобным (за исключением оценки Time, близкой к бесконечности):
Интересно оценить погрешность, с которой dynamic sampling рассчитала объём вспомогательной таблички:
— вполне удовлетворительная точность
Данные для оценки (numrows) в отсутствии статистики таблицы, имхо, берутся при чтении избранных блоков таблицы во время сэмплинга (avgrlen) и из имеющейся статистики сегмента (numblks):
В базе данных Oracle оптимизатор запросов играет важнейшую роль в выполнении операторов SQL наиболее эффективным образом. Один и тот же оператор SQL можно выполнить несколькими способами, и задача оптимизатора запросов состоит в выборе наиболее быстрого и эффективного пути выполнения каждого запроса к базе данных.
Чтобы разработать наилучший план выполнения любого оператора SQL, оптимизатор сначала оценивает возможные пути доступа, порядки соединения (join orders) и т.п., и выбирает несколько подходящих планов выполнения. Затем он вычисляет стоимость альтернативных планов на основе использования ими системы ввода-вывода, центрального процессора и памяти. На этом шаге оптимизатор использует статистику, которая среди прочего включает информацию о распределении данных и характеристики хранения таблиц и индексов. И, наконец, оптимизатор сравнивает стоимости альтернативных планов и выбирает план с минимальной стоимостью.
На заметку! В Oracle рекомендуют позволить базе данных автоматически собирать статистику оптимизатора.
Автоматически собираемая статистика оптимизатора
Выпуск Oracle Database 10g представил средство сбора статистики оптимизатора. Теперь вас не должен беспокоить вопрос о частоте сбора статистики или объектах, включаемых в процесс сбора, поскольку Oracle теперь обо всем позаботиться за вас.
На заметку! Бывают ситуации, когда ручной сбор статистики оптимизатора оправдан, и мы обсудим это в ниже в разделе “Ручной сбор статистики оптимизатора”.
В Oracle Database 11g очень легко включить автоматический сбор статистики — Oracle автоматически запускает его при создании новой базы данных Oracle Database 11g или обновлении до версии Oracle Database 11g. Для автоматического сбора статистики оптимизатора используется пакет DBMS_STATS.
Совет. Удостоверьтесь, что параметр инициализации STATISTICS_LEVEL установлен в TYPICAL или ALL, чтобы обеспечить включение средства автоматического сбора статистики.
Ручной сбор статистики оптимизатора
С помощью пакета DBMS_STATS можно выполнять ручной сбор статистики оптимизатора на уровне таблицы, схемы или базы данных, а также собирать статистику системы. Начиная с версии Oracle Database 10g, рекомендованный способ сбора статистики, однако, заключается в том, чтобы позволить базе данных делать это автоматически. Тем не менее, в некоторых ситуациях, вроде описанных ниже, для сбора статистики необходимо использовать традиционный пакет DBMS_STATS вместо того, чтобы полагаться на автоматический сбор Oracle.
Для того, чтобы понять зачем нужен сбор статистики, рекомендую ознакомиться с постами «Оптимизатор. Начальные понятия» здесь , здесь и здесь.
C каждой новой версией ORACLE стоимостной оптимизатор становится все более сложным.
Поэтому для принятия правильных решений ему нужны достоверные статистические данные. Администратор должен об этом побеспокоиться, так как сбор статистики непосредственно влияет на мощность всей системы. Соответственно устаревшие статистические данные являются причиной выбора неоптимального плана выполнения запросов.
Для сбора статистики используется пакет dbms_stats и оператор analyze. Однако Oracle "настоятельно" рекомендует использовать именно пакет dbms_stats. Да и Том Кайт придерживается того же мнения. Его авторитетные утверждения:
- dbms_stats - это рекомендованный, предпочтительный метод сбора статистики
- DBMS_STATS позволяет выполнять сбор статистики параллельно
- С помощью DBMS_STATS можно выполнять мониторинг статистики и получать устаревшую статистику для измененных строк
- С помощью dbms_stats можно непосредственно импортировать/экспортировать/задавать статистику
- Пакет dbms_stats упрощает автоматизацию обновления статистики
- Пакет dbms_stats (в 9i) позволяет собирать статистику системы
- Пакет dbms_stats позволяет анализировать внешние таблицы
- с помощью dbms_stats можно собирать статистику в удаленной базе данных
Так как только dbms_stats собирает глобальную статистику (столбец global_stats в dba_tables, dba_indexes, dba_tab_cols, dba_tab_columns, dba_tab_col_statistics), значит, по значению столбца global_stats можно определить каким образом собиралась статистика - DBMS_STATS или ANALYZE.
DBMS_STATS собирает статистику только для стоимостного оптимизатора. То есть этот пакет не собирает никакую иную информацию. Например, не собирает такие данные: количество chained rows, средний объем свободного места в блоке, количество неиспользованных блоков данных. Такую статистику собирает оператор analyze.
Обратите внимание, что даже та статистика, которая собирается и DBMS_STATS, и ANALYZE несколько отличается. Это в первую очередь относится к avg_row_len и avg_col_len.
К тому же ожидается, что в будущем оператор ANALYZE не будет собирать статистику, необходимую стоимостному оптимизатору. Эта функция будет возложена исключительно на DBMS_STATS!
Управление автоматической задачей (Automatic Maintenance Task) сбора статистики:
Интересно, что если при создании бд с помощью dbca или вручную не были разрешены Automatic Maintenance Task— записи в обзоре dba_autotask_task появятся только после выполнения dbms_auto_task_admin.enable, точнее, после наступления следующего интервала выполнения (Maintenance Windows)
С 11.2.0.3 появился параметр:
Параметры автоматического сбора статистики
Мониторинг изменений данных с целью определения кандидатов для сбора статистики
USER | DBA| ALL_TABLES.MONITORING — атрибут мониторинга изменений данных таблицы для автоматического сбора статистики процедурами DBMS_STATS, начиная с с 11g признак deprecated, т.е. все таблицы автоматически мониторятся
USER | DBA| ALL_TAB_MODIFICATIONS — список таблиц, данные которых были модифицированы со времени последнего сбора статистики
Список таблиц со устаревшей (STALE) статистикой:
Частота обновления мониторингов
By default STATISTICS_LEVEL is set to TYPICAL and monitoring of tables is enabled
…
Monitoring tracks the approximate number of INSERT, UPDATE, and DELETE operations for the table since the last time statistics were gathered. This information on «changes made» is maintained in the SGA and periodically (about every 15 minutes) the SMON flushes the data into the data dictionary tables. You can manually flush the information by calling dbms_stats.FLUSH_DATABASE_MONITORING_INFO(). The data dictionary information is made visible through the views: DBA_TAB_MODIFICATIONS, ALL_TAB_MODIFICATIONS and USER_TAB_MODIFICATIONS.
Cкрытые столбцы (hidden columns) и выражения (expression), статистика по которым использовуется CBO
10g Function based indexes в статусе unusable
Элегантный способ создания «виртуальных столбцов» со статистикой и гистограммами для версий до 11g — Jonathan Lewis «Virtual Columns revisited»:
Extended statistics:
Информация о расширениях статистики/группах столбцов
Какая extended statistics рекомендуется Oracle при определённой нагрузке — на примере создания рекомендованной расширенной статистики, рекомендуемой для конкретного запроса sql_id 8ypggjw7dz6mv:
Удаление расширенной статистики для группы столбцов или выражений:
Ручное управление статистикой
Handling statistics for a column with ever increasing or decreasing values… [ID 877645.1] — процедура установки LOW_VALUE / HIGH_VALUE с помощью dbms_stats.set_column_stats для таблицы, значение столбцов в которой изменяются часто (чаще, чем может собираться статистика)
для таблицы с неравномерным распределением кол-ва строк по значениям стобца (skewed column)
Пример ручного создания height balanced histogram — Jonathan Lewis «Fake Histograms»
Как предотвратить (или, наоборот, стимулировать) создание гистограмм для конкретного столбца на версиях с использованием dbms_stats.lock_table_stats, dbms_stats.gather_table_stats( …, force => true) в 10g и dbms_stats.set_table_prefs в 11g — Optimizer development team «How do I drop an existing histogram on a column and stop the Auto Stats gathering job from creating it in the future?»
Параметры сбора статистики
Установка параметра METHOD_OPT для процедур пакета DBMS_STATS на уровне системы — на примере отключения создания гистограмм для всех таблиц:
Включение обязательного создания гистограмм для отдельного столбца на уровне таблицы (Oracle 11+):
Новое в статистике: о новом параметре TABLE_CACHED_BLOCKS для расчёта Index Clustering Factor и ограничении действия процедуры DBMS_STATS.SET_SCHEMA_PREFS только на объекты схемы, существующие на момент запуска для версий 11.2.0.2+
How does AUTO_SAMPLE_SIZE work in Oracle Database 11g[+]?:
Summary:
New AUTO_SAMPLE_SIZE algorithm does a full table scan to gather basic column statistics
…
Статистика временных таблиц / Global Temporary Table
… no statistics are gathered about them than what can be collected on the fly — т.е. автоматически статистика GTT не собирается, однако в DBA_TAB_MODIFICATIONS временные таблицы попадать могут, например, в бд OEBS:
— при этом изменений в таблицах не фиксируется INSERTS = UPDATES = DELETES = 0, плюс попали временные таблицы либо с собранной (USER_STATS=NO), либо с искусственно установленной статистикой (USER_STATS=YES)
DBMS_STATS Tracing
dbms_stats.set_global_prefs(‘trace’,:trace_flags)
Following are the possible values for the trace flags:
1 = use dbms_output.put_line instead of writing into trace file
2 = enable dbms_stat trace only at session level
4 = trace table stats
8 = trace index stats
16 = trace column stats
32 = trace auto stats – logs to sys.stats_target$_log
64 = trace scaling
128 = dump backtrace on error
256 = dubious stats detection
512 = auto stats job
1024 = parallel execution tracing
2048 = print query before execution
4096 = partition prune tracing
8192 = trace stat differences
16384 = trace extended column stats gathering
32768 = trace approximate NDV (number distinct values) gathering
Например, to dump a trace on stats job error:
— для более полного трейса
Прочее
Doug Burns: STATISTICS ON PARTITIONED OBJECTS — про сбор (параметр GRANULARITY пакета DBMS_STATS) и использование оптимизатором статистики объектов/партиций/субпартиций и партицированных инддексов
Несложный по логике запрос с использованием быстрой функции fast_function, которая сама по себе выполняется за миллисекунды и не обращается к системным объектам, выполняется неожиданно медленно:
И план выполнения (приведён выше ), и трейс 10046 (ниже, под катом) показывают, что причиной длительного выполнения является неправильный порядок соединения: полное сканирование системного обзора v$tablespace (в плане отражена подлежащая фиксированная таблица X$KCCTS) с вызовом функции fast_function для каждой строки:
3440 выполнений (Execute) и извлечений результатов запроса (Fetch) объясняются удвоенным (по кол-ву вызовов fast_function) количеством строк в v$tablespace
Причину выбора неоптимального плана можно найти в вышеприведённом плане выполнения — при сканировании таблицы X$KCCTS (v$tablespace) оптимизатор предполагает получить одну строку (столбец Rows плана, в то действительности имеем 1720 строк!), что является следствием отсутствия статистики по фиксированным таблицам:
Автоматическими процедурами (dbms_scheduler ‘GATHER_STATS_JOB’ в 10g и dbms_auto_task_admin ‘AUTO OPTIMIZER STATS COLLECTION’ в 11g) статистика по фиксированным таблицам (fixed tables | objects) не собирается ни в Oracle 10g, ни в Oracle 11g:
«Статистика по фиксированным объектам … должна собираться вручную с использованием процедуры DBMS_STATS.GATHER_FIXED_OBJECTS_STATS. В фиксированных таблицах отражена текущая активность бд; сбор статистики должен производиться во время репрезентативной активности бд»
Хороший пример про правильное время для сбора такой статистики можно найти в блоге Martin Nash: When to Gather Fixed Object Optimiser Statistics, или представить как будет отличаться содержимое таблиц типа v$session, v$sql, v$open_cursor. в зависимости от времени :)
В то же время в документе поддержки Fixed Objects Statistics Considerations [ID 798257.1] указывается, что «Начиная с версии 10.1, оптимизатор использует dynamic sampling в случае отсутствия статистики…»
В каких же случаях используется dynamic sampling для фиксированных таблиц в отсутствии статистики, и почему не был использован в запросе ?
Итак, Oracle 10g, без статистики фикс. таблиц, параметр optimizer_dynamic_sampling установлен по умолчанию (что уже предполагает применение механизма dynamic sampling ко всем неанализированным таблицам — как раз наш случай), более двухсот строк в v$tablespace:
Демо-запрос не слишком логичный, но с наглядным планом выполнения:
dynamic sampling не использован, оптимизатор предполагает получить одну строку из таблицы
Подсказка DYNAMIC_SAMPLING ничего не меняет, как и изменение параметра :(
Версия 11.2, исходные условия — аналогичные:
При значении параметра optimizer_dynamic_sampling=2 (по умолчанию) механизм по прежнему не работает, увеличение параметра до максимального значения (10)
также ничего не даёт, однако механизм начинает работать при использовании подсказки с тем же уровнем 2:
с соответствующими точной оценкой кол-ва строк в фикс.таблице и изменением порядка соединения в плане на правильный, более быстрый
При увеличении degree_of_sampling в подсказке DYNAMIC_SAMPLING «правильный» план естественно не меняется,
но не меняется и degree_of_sampling / степень dynamic sampling (level=2)
Получается, что механизм dynamic sampling для фиксированных таблиц без статистки работает только в Oracle 11g и только при форсировании с помощью подсказки
Планировщик и GATHER_STATS_JOB
Когда вы создаете новую базу данных или обновляете до версии Oracle Database 11g, Oracle автоматически создает задание базы данных по имени GATHER_STATS_JOB, а планировщик Oracle Scheduler автоматически планирует задание для запуска во время окна обслуживания. Вот как проверить, работает ли задание автоматического сбора статистики:
Oracle планирует задание GATHER_STATS_JOB для автоматического выполнения с использованием инструмента Oracle Scheduler. Начиная с Oracle Database 10g, планировщик Scheduler заменил старые средства планирования заданий, использовавшие пакет DBMS_JOB .
Планировщик Oracle Scheduler по умолчанию имеет два окна операций:
- еженедельное ночное окно между 10 вечера и 6 утра, с понедельника по пятницу;
- окно на выходные дни между полуднем субботы и полуднем понедельника.
Вместе эти два окна известны как окно обслуживания (maintenance window). Oracle автоматически планирует задание GATHER_STATS_JOB для запуска во время открытия окна обслуживания. Отключается автоматический сбор статистики следующим образом:
Задание GATHER_STATS_JOB вызывает процедуру DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC для сбора статистики оптимизатора. Задание собирает статистику только для объектов, которые попадают в один из перечисленных ниже классов.
- Объекты с отсутствующей статистикой. Любой объект без статистики является кандидатом на сбор статистики.
- Объекты с устаревшей статистикой. Oracle считает статистику объекта устаревшей, если 10 или более процентов строк объекта были модифицированы с момента последнего сбора статистики для этого объекта.
По умолчанию Oracle отслеживает модификации (изменения с помощью операторов DML) в объектах базы данных, пока параметр инициализации STATISTICS_LEVEL установлен в TYPICAL или ALL (TYPICAL — значение по умолчанию).
Процедура GATHER_DATABASE_STATS_JOB_PROC устанавливает приоритеты между объектами базы данных на основе активности DML по каждому объекту. Эта процедура первыми анализирует объекты, которые претерпели максимум изменений DML, так что даже если задание по сбору статистики не будет завершено целиком, прежде чем закроется окно обслуживания, она гарантированно соберет наиболее востребованную статистику.
Обратите внимание, что по умолчанию планировщик прервет задание GATHER_STATS_JOB, если оно еще будет выполняться на момент закрытия окна обслуживания. Объекты, для которых статистика не будет собрана до закрытия окна поддержки, будут автоматически обработаны при следующем запуске задания. Однако атрибут STOP_ON_WINDOW_CLOSE задания GATHER_STATS_JOB можно установить в FALSE. Я планирую написать статью, посвященную Oracle Scheduler, и там будет объяснятся, как это сделать.
Использование Database Control для управления расписанием GATHER_STATS_JOB
Вы всегда можете изменить окно обслуживания по умолчанию с помощью SQL*Plus. С помощью OEM Database Control можно также модифицировать текущее расписание запуска GATHER_STAT_JOB.
Ниже перечислены шаги, которые потребуется для этого сделать.
- На домашней страницы Database Control щелкните на вкладке Administration (Администрирование).
- Перейдите в раздел Scheduler Group (Группа планировщика) и щелкните на ссылке Windows (Окна).
- Щелкните на кнопке Edit (Редактировать). После этого можно отредактировать времена окон обслуживания.
Читайте также: