Last analyzed oracle что это

Обновлено: 06.07.2024

Мы в настоящее время управляем Oracle 11g, и я изучаю, если мы должны управлять статистикой после большого импорта. У нас есть набор statistics_level к 'ТИПИЧНОМУ'. На основе этого я думаю, что мы не должны обновлять статистику:

Начинаясь с Oracle Database 11g, КОНТРОЛЯ и NOMONITORING ключевые слова были удержаны от использования, и статистические данные собраны автоматически.

Однако после создания моей базы данных и управления моим скромным импортом (100's тысяч к миллионам отчетов в горстке столов и создании многих индексов) все таблицы, затронутые импортом, показывают пустой указатель для last_analyzed и stale_stats, используя вопрос ниже.

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

Я должен ожидать, что статистика в конечном счете будет бежать и last_analyzed, и stale_stats, который в конечном счете будет населен (документация предполагает, что эти ценности обновляются о каждых трех часах по умолчанию)?

Это был мой опыт, который для умеренно размерных баз данных (столы с миллионами отчетов и меньше, чем 10-е миллионов отчетов), что слоняние без дела со статистикой не необходимо и обычно вызывает больше проблем, чем он, решает. Этот обычно имеет место?

* * * ОТМЕЧАЕТ НА НАШЕМ РЕШЕНИИ * * *

Мы использовали это:

Мы переключились на это:

Проанализировать заявление стола заняло около 1:30 минуты в одной окружающей среде и около 15:00 - 20:00 минут во второй окружающей среде.

gather_table_stats заявление взяло около 0:30 к 1:00 минут в обоих из этих двух случаев, которые мы смогли исследовать.

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

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

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

Когда last_analyzed пустой, это означает, что статистика стола еще не была собрана.

stale_stats says whether the stats are considered fresh or stale, or if the stats will be gathered automatically next time or not. The default settings is 10 percent. If you gather table statistics and then insert/update/delete less than 10 percent of rows the statistics is considered fresh. When you reach 10 percent of modified rows they become stale.

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

Относительно STATISTICS_LEVEL , со значением по умолчанию , ТИПИЧНЫЙ , это похоже на это:

Мы не видим что-то большее чем оцененное количество рядов. Если вы устанавливаете , ИЗМЕНЯЮТСЯ, СЕССИЯ УСТАНОВИЛА statistics_level = ВСЕ тогда

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

Также знайте, что выборка статистики не сделана с каждым рядом, но по умолчанию каждые 128 рядов (может быть изменен, используя недокументированный _rowsource_statistics_sampfreq параметр),

(Husqvik полностью объяснил значение колонок и параметров, этот ответ только обращается, как собрать статистику.)

Статистика должна быть вручную собрана после того, как любые значительные * изменяются на стол. У Oracle есть большой дефолт, автоматические сборочные процессы статистики начиная с 11 г. Но даже с той новой системой есть все еще по крайней мере два серьезных основания вручную собрать статистику. Сборочная автозадача статистики по умолчанию обычно предназначается для медленно изменяющихся столов OLTP, не быстро изменяющихся столов хранилища данных.

Significant data changes can easily lead to significant performance problems. If the tables are going to be used right after they are loaded then they need good statistics immediately.

A common problem in ETL processes is when tables go from 1 row to a million rows. The optimizer thinks there is still only one row in large tables and uses lots of nested loops joins instead of hash joins. Those algorithms work well in different contexts; without good statistics Oracle does not know the correct context.

It's important to note that a NULL LAST_ANALYZED is not the worst case scenario. When there are no statistics at all, Oracle will use dynamic sampling to generate quick statistics estimates. The worst case is when the statistics job ran last night when the table is empty; Oracle thinks it has good statistics when it really doesn't.

The statistics auto-task may not be able to keep up with large changes. The statistics auto-task is a low-priority, single-threaded process. If there are too many large tables left to the automatic process it may not be able to process them during the maintenance window.

*: "Значительный" субъективное слово. Изменение обычно значительное с точки зрения относительного размера, не абсолютное. Добавление одного миллиона рядов к столу значительное, если стол в настоящее время ссорится, но не, если у стола есть миллиард рядов.

В настоящее время мы используем Oracle 11g, и я изучаю, нужно ли нам запускать статистику после большого импорта. У нас есть statistics_level, установленный на "TYPICAL". Исходя из этого, я думаю, что нам НЕ нужно обновлять статистику:

Начиная с Oracle Database 11g, ключевые слова MONITORING и NOMONITORING объявлены устаревшими, и статистика собирается автоматически.

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

Следует ли ожидать, что в этом состоянии некоторые запросы будут иметь низкую производительность?

Должен ли я ожидать, что в конечном итоге статистика будет запущена, а last_analyzed и stale_stats в конечном итоге будут заполнены (в документации предполагается, что эти значения обновляются каждые три часа по умолчанию)?

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

* * * ПРИМЕЧАНИЯ К НАШЕЙ РЕЗОЛЮЦИИ * * *

Мы использовали это:

Мы перешли на это:

Оператор таблицы анализа занял около 1:30 минут в одной среде и около 15:00 - 20:00 минут во второй среде.

Оператор gather_table_stats занял от 0:30 до 1:00 минут в обоих из двух экземпляров, которые мы смогли изучить.

Наш план в дальнейшем - переключить наши операторы анализа таблиц на вызовы gather_table_stats.

2 ответа

STATISTICS_LEVEL и сбор статистики таблицы / индекса - это совершенно разные вещи. STATISTICS_LEVEL влияет на сбор статистики источника строки во время выполнения команды. Таким образом, вы можете сравнивать оценки оптимизатора и фактические значения для каждого шага курсора дисплея.

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

Если last_analyzed равно нулю, это означает, что статистика таблицы еще не собрана.

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

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

Что касается STATISTICS_LEVEL , со значением по умолчанию TYPICAL это выглядит так:

Мы не видим ничего, кроме приблизительного количества строк. Если вы установите ALTER SESSION SET statistics_level = ALL , то

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

Также имейте в виду, что выборка статистики выполняется не для каждой строки, а по умолчанию каждые 128 строк (можно изменить с помощью недокументированного параметра _rowsource_statistics_sampfreq )

(Husqvik подробно объяснил значение столбцов и параметров, в этом ответе рассматривается только способ сбора статистики.)

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

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

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

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

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

*: «Значимый» - это субъективное слово. Изменение обычно является значительным с точки зрения относительного размера, а не абсолютного. Добавление одного миллиона строк в таблицу имеет значение, если таблица в настоящее время имеет одну строку, но не если в таблице есть миллиард строк.


В этой статье изложен многолетний опыт оптимизации SQL-запросов в процессе работы с базами данных Oracle 9i, 10g и 11g. В качестве рабочего инструмента для получения планов запросов мною используется всем известные программные продукты Toad и PLSQL Developer.

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

  • плохая статистика по таблицам и индексам запроса;
  • проблемы с индексами в запросе;
  • проблемы с хинтами в запросе;
  • неэффективно построенный запрос;
  • неправильно настроены параметры инициализации базы данных, отвечающие за производительность запросов.

Программные средства, позволяющие получить планы выполнения запросов, можно разделить на 2 группы:

  • средства, позволяющие получить предполагаемый план выполнения запроса;
  • средства, позволяющие получить реальный план выполнения запроса;

К средствам, позволяющим получить предполагаемый план выполнения запроса, относятся Toad, SQL Navigator, PL/SQL Developer и др. Это важный момент, поскольку надо учитывать, что реальный план выполнения может отличаться от того, что показывают эти программные средства. Они выдают ряд показателей ресурсоемкости запроса, среди которых основными являются:

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

полученного в Toad

Из плана видно, что наибольшие значения Cost и Cardinality содержатся во 2-й строке, в которой и надо искать основные проблемы производительности запроса.
Вместе с тем, многолетний опыт оптимизации показывает, что качественный анализ эффективности запроса требует, помимо Cost и Cardinality, рассмотрения других дополнительных показателей:

  • CPU Cost — процессорная стоимость выполнения;
  • IO Cost — стоимость ввода-вывода;
  • Temp Space – показатель использования дискового пространства.

Если дисковое пространство используется (при нехватке оперативной памяти для выполнения запроса, как правило, для проведения сортировок, группировок и т.д.), то с большой вероятностью можно говорить о неэффективности запроса. Указанные дополнительные параметры с соответствующей настройкой можно увидеть в PL/SQL Developer и Toad при их соответствующей настройке. Для PL/SQL Developer в окне с планом выполнения надо выбрать изображение гаечного ключа, войти в окно Preferensec добавить дополнительные параметры в Select Column, после чего и нажать OK. В Toad в плане выполнения по правой кнопке мыши выбирается директива Display Mode, а далее Graphic, после чего появляется дерево, в котором по каждому листу нажатием мышки можно увидеть дополнительные параметры: CPU Cost, IO Cost, Cardinality. Структура плана запроса, указанного выше, в виде дерева приведена ниже.

Предполагаемый план выполнения запроса с Cost и Cardinality можно также получить, выполнив после анализируемого запроса другой запрос, формирующий план выполнения:

Дополнительно в плане выполнения запроса выдается значение SQL_ID запроса, который можно использовать для получения реального плана выполнения запроса с набором как основных (Cost, Cardinality), так и дополнительных показателей через запрос:

Реальный план выполнения запроса и указанный выше перечень характеристик для анализа ресурсоемкого запроса дают динамические представления Oracle: V$SQL_PLAN и V$SQL_PLAN_MONITOR (последнее представление появилось в Oracle 11g).

План выполнения запроса получается из представления Oracle по запросу:

где SQL_ID – это уникальный идентификатор запроса, который может быть получен из разных источников, например, из представления V$SQL:

Трассировочный файл — это еще одно средство получение реального плана выполнения. Это довольно сильное средство диагностики и оптимизации запроса. Для получения трассировочного файла ( в Toad или PL/SQL Developer) запускается PL/SQL блок:

где первая, третья и последняя строки являются стандартными, а во второй строке пишется идентификатор (любые символы), который включается в имя трассировочного файла. Так, если в качестве идентификатора напишем M_2013, то имя трассировочного файла будет включать этот идентификатор и будет иметь вид: oraxxx_xxxxxx_ M_2013.trc. Результат пишется в соответствующую директорию сервера, которая находиться из запроса

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

Ещё одним из средств получения реального плана выполнения запроса с получением рекомендаций по его оптимизации является средство Oracle SQLTUNE.

Для анализа запроса запускается PL/SQL блок (например, в Toad или PL/SQL Developer) , в котором имеются стандартные строки и анализируемый запрос. Для рассматриваемого выше запроса блок PL/SQL примет вид:

Все строки (кроме текста запроса) являются стандартными.

Далее запуск запрос, который выдает на экран текст рекомендаций:

Для работы SQLTUNE необходимо как минимум из под SYSTEM выдать права на работу с SQLTUNE схеме, в которой запускается PL/SQL блок. Например, для выдачи прав на схему HIST выдается GRANT ADVISOR TO HIST;

В результате работы SQLTUNE выдает рекомендации (если Oracle посчитает, что есть что рекомендовать). Рекомендациями могут быть: собрать статистику, построить индекс, запустить команду создания нового эффективного плана и т.д.

Анализ плана выполнения запроса.

Анализ плана выполнения запроса имеет определенную последовательность действий. Рассмотрим на примере плана выполнения запроса из представление V$SQL_PLAN для ранее приведенного запроса

  1. При анализе план просматриваетcя снизу вверх. В процессе просмотра в первую очередь обращается внимание на строки с большими Cost, CPU Cost.
  2. Как видно из плана, резкий скачек этих значений имеется в 4-ой строке. Причиной такого скачка является 5-я строка с INDEX FULL SCAN, указывающая на наличие полного сканирование индекса X_DICTI_NAME таблицы DICTI. С этих строк и надо начинать поиск причины ресурсоемкости запроса. После нахождения строки с большим Cost и CPU Cost продолжается просмотр плана снизу вверх до следующего большого CPU Cost и т.д. При этом, если CPU Cost в строке близок к CPU Cost первой строки (максимальное значение), то найденная строка является определяющей в ресурсоемкости запроса и с ней в первую очередь надо искать причину ресурсоемкости запроса.
  3. Помимо поиска больших Cost и CPU Cost в строках плана следует просматривать первый столбец Operation плана на предмет наличия в нем HASH JOIN. Соединение по HASH JOIN приводит к соединению таблиц в памяти и, казалось бы, более эффективным, чем вложенные соединения NESTED LOOPS. Вместе с тем, HASH JOIN эффективно при наличии таблиц, хотя бы одна из которых помещаются в память БД или при наличии соединения таблиц с низкоселективными индексами. Недостатком этого соединения является то, что при нехватке памяти для таблицы (таблиц) будут задействованы диски, которые существенно затормозят работу запроса. В связи с чем, при наличии высокоселективных индексов, целесообразно посмотреть, а не улучшит ли план выполнения хинт USE_NL, приводящий к соединению по вложенным циклам NESTED LOOPS. Если план будет лучше, то оставить этот хинт. При этом в хинте USE_NL в скобках обязательно должны перечисляться все алиасы таблиц, входящих во фразу FROM, в противном случае может возникнуть дефектный план соединения. Этот хинт может быть усилен хинтами ORDERED и INDEX. Следует обратить так же внимание на MERGE JOIN. При большом CPU Cost в строке с MERGE JOIN стоит проверить действие хинта USE_NL для улучшения эффективности запроса.
  4. Особое внимание в плане следует так же уделить строкам в плане с операциями полного сканирования таблиц и индексов в столбец Operation: FULL — для таблиц и FULL SCAN, FAST FULL SCAN , SKIP SCAN — для индексов. Причинами полного сканирования могут быть проблемы с индексами: отсутствие индексов, неэффективность индексов, неправильное их применение. При небольшом количестве строк в таблице полное сканировании таблицы FULL может быть нормальным явлением и эффективнее использования индексов.
  5. Наличие в столбце Operation операции MERGE JOIN CARTESIAN говорит, что между какими-то таблицами нет полной связки. Эта операция возникает при наличии во фразе From трех и более таблиц, когда отсутствуют связи между какой-то из пар таблиц.
    Решением проблемы может быть добавление недостающей связки, иногда помогает использование хинта Ordered.

Оптимизация запроса

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

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

Неэффективная статистика.

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

  1. Устаревшая статистика. Время последнего сбора статистики определяется значением поля Last_Analyzed для таблиц и индексов, которое находиться из Oracle таблиц ALL_TABLES (DBA_TABLES) и ALL_INDEXES (DBA_INDEXES) соответственно. Oracle ежедневно в определенные часы в рабочие дни и в определенные часы в выходные сам собирает статистику по таблицам. Но для этого DML операции с таблицей должны привести к изменению не менее 10% строк таблицы. Однако, мне приходилось сталкиваться с ситуацией, когда в течение дня таблица неоднократно и существенно меняет число строк или таблица столь большая, что 10% изменений наступает через длительное время. В этом случае приходилось обновлять статистику, используя процедуры сбора статистики внутри пакетов, а ряде случае использовать JOB, запускающийся в определенные часы для анализа и обновления статистики.
    Статистика по таблице и индексу (на примере таблицы AGREEMENT и индекса X_AGREEMENT в схеме HIST) обновляется соответственно процедурами:
  2. для таблицы:
  3. для индекса:

где число 10 в процедуре указывает на процент сбора статистики. С учетом времени сбора статистики и числа строк в таблице (индексе) были выработаны рекомендации для таблиц (индексов) по проценту сбора статистики: если число строк более 100 млн. процент сбора устанавливать 2 -5, при числе строк с 10 млн. до 100 млн. процент сбора устанавливать 5-10, менее 10 млн. процент сбора устанавливать 20 -100. При этом, чем выше процент сбора, тем лучше, однако, при этом растет и может быть существенным время сбора статистики.

Для таблиц процент сбора статистики (на примере таблицы AGREEMENT в схеме HIST) вычисляется запросом:

Процент сбора статистики по индексу находиться по запросу

Необходимо пересобрать статистику по таблице или индексу с плохой статистикой.

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

Проблемы с индексами

Проблемы с индексами в плане выполнения проявляются при наличии в столбце Options значений FULL, FULL SCAN, FAST FULL SCAN и SKIP SCAN в силу следующих причин:

  1. Отсутствие нужного индекса. Требуемое действие — создать новый индекс;
  2. Индекс имеется, но он неэффективно построен. Причинами неэффективности индекса могут быть:
    — Малая селективность столбца, на котором построен индекс, т.е. в столбце много одинаковых значений, мало уникальных значений. Решение в данной ситуации — убрать индекс из таблицы или столбец, на основе которого построен индекс, ввести в составной индекс.
    — Столбец селективный, но он входит в составной индекс, в котором этом столбец не является первым (ведущим) в индексе. Решение – сделать этот столбец ведущим или создать новый индекс, где столбец будет ведущим;
  3. Построен эффективный индекс, но он работает не эффективно в силу следующих причин:
    — Индекс заблокирован от использования. Блокируют использование индекса следующие операции над столбцом, по которому используется индекс: SUBSTR, NVL, DECODE, TO_CHAR,TRUNC,TRIM, ||конкатенация, + цифра к цифровому полю и т.д.
    Решение – модифицировать запрос, освободиться от блокирующих операций или создать индекс по функции, блокирующей индекс.
    — Не собрана или неактуальная статистика по индексу. Решение – собрать статистику по индексу запуском процедуры, указанной выше.
    — Имеется хинт, блокирующий работу индекса, например NO_INDEX.
    — Неэффективно настроены параметры инициализации базы данных БД (особенно отвечающие за эффективную работу индексов, например, optimizer_index_caching и optimizer_index_cost_adj). По моему опыту использования Oracle 10g и 11g эффективность работы индексов повышалась, если optimizer_index_caching=95 и optimizer_index_cost_adj=1.
  4. Имеются сильные индексы, но они соперничают между собой.
    Это происходит тогда, когда в условии where имеется строка, в которой столбец одной таблицы равен столбцу другой таблицы. При этом на обоих столбцах построены сильные или уникальные индексы. Например, в условии Where имеется строка AND A.ISN=B.ISN. При этом оба столбца ISN разных таблиц имеют уникальные индексы. Однако, эффективно может работать индекс только одного столбца (левого или правого в равенстве). Индекс другого столбца, в лучшем случае, даст FAST FULL SCAN. В этой ситуации, чтобы эффективно заработали оба индекса, потребуется вести дополнительное условие для одного из столбцов.
  5. Индекс имеет большой фактор кластеризации CLUSTERING_FACTOR.
    По каждому индексу Oracle вычисляет фактор кластеризации (ФК), определяющий число перемещений от одного блока к другому в таблице при выборе индексом строк из таблицы. Минимальное значение ФК равно числу блоков таблицы, максимальное — числу строк в таблице. CLUSTERING_FACTOR определяется по запросу:

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

Проблемы с хинтами в запросе

Проблемы с хинтами могут быть следующие:

  1. Неэффективный хинт. Он может привести к существенному снижению производительности. Причины возникновения не эффективности хинтов:
    — хинт был написан, когда БД работала на 9-ом Oracle, при переходе на Oracle 10g и выше хинт стал тормозом (это могут быть хинты Rule, Leading и др.). Leading –мощный хинт, но при переходе на другую версию Oracle в некоторых случаях приводит в резкому снижению производительности и перед применение этих хинтов необходимо учитывать вероятность изменения со временем статистики системы и ее объектов (таблиц и индексов), используемых в запросе;
    — в хинте USE_NL содержится не полный перечень алиасов;
    — в составном хинте используется неправильный порядок следования хинтов, в результате чего хинты блокирую эффективную работу друг. Например, хинт Leading полностью игнорируются при использовании двух или более конфликтующих подсказок Leading или при указании в нем более одной таблицы.
    — хинт написан давно, после чего была модификация запроса (например, отсутствует или изменился индекс, указанный в хинте).
  2. В запросе отсутствует хинт, который бы повысил эффективность работы запроса. В ряде случаем наличие хинта повышает эффективность запроса и обеспечивает стабилизацию планов выполнения (например, при изменении статистики).
  3. При создании хинта в запросе есть ряд рекомендаций:
    — В хинте INDEX могут быть перечислены несколько индексов. Оптимизатор сам выберет соответствующий индекс. Можно поставить хинт NO_INDEX, если надо заблокировать использование какого-то индекса.
    — При наличии Distinct в запросе Distinct ставиться после хинта (т.е. хинт всегда идет после Select).
    — Наиболее эффективные и часто используемыми являются хинты: Ordered, Leading, Index , No_Index, Index_FFS, Index_Join, Use_NL, Use_Hash, Use_Merge, First_Rows(n), Parallel, Use_Concat, And_Equal, Hash_Aj и другие. При этом, например, индекс Index_FFS кроме быстрого полного сканирования индекса позволяет ему выполняться параллельно, в силу чего можно получить существенный выигрыш в производительности. Пример такого использования для секционированной таблицы где T-алиас таблицы.
    — Изменение параметров инициализации базы данных в пределах запроса позволяет сделать хинт /*+ opt_param('Параметр инициализацци' N) */ , например, /*+ opt_param('optimizer_index_caching' 10) */. Данный хинт используется для проверки производительности работы запроса в случае, когда запрос разрабатывается или тестируется на базе с одним значением параметров инициализации, а работает на базе с другими значениями.

Замечание. В некоторых случаях, когда хинт неэффективный, но заменить его оперативно в запросе не представляется возможным (например, чужая разработка), имеется возможность, не меняя рабочий запрос в программном модуле, заменить хинт (хинты) в запросе, а также в его подзапросах, на эффективный хинт (хинты). Это прием — подмена хинтов (который известен, как использование хранимых шаблонов Stored Outlines). Но такая подмена должна быть временным решением до момента корректировки запроса, поскольку постоянная подмена хинта может привести к некоторому снижению производительности запроса.

По вашему опыту, как часто следует запускать статистику базы данных Oracle? Наша команда разработчиков недавно обнаружила, что статистика не запускалась на нашем производственном сервере более 2 с половиной месяцев. Для меня это звучит очень долго, но я не администратор.

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

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

Всякий раз, когда данные изменяются «значительно».

Если таблица идет от 1 строки до 200 строк, это существенное изменение. Когда таблица переходит от 100 000 строк к 150 000 строк, это не очень значительное изменение. Когда таблица переходит от 1000 строк с одинаковыми значениями в обычно запрашиваемом столбце X к 1000 строкам с почти уникальными значениями в столбце X, это значительное изменение.

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

С Oracle 11g статистика собирается автоматически по умолчанию.

Два окна планировщика предопределены при установке базы данных Oracle:

  • WEEKNIGHT_WINDOW начинается в 10 вечера. и заканчивается в 6 часов утра каждый понедельник .__ по пятницу.
  • WEEKEND_WINDOW охватывает все дни субботы и воскресенья.

Когда статистика в последний раз собиралась?

Состояние автоматизированного сбора статистики?

Соберите статистику базы данных в этой схеме вручную:

Собирать статистику базы данных во всех схемах вручную!

Какую версию Oracle вы используете? Проверьте эту страницу, которая относится к Oracle 10:

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

В Oracle 10g и более поздних версиях оптимизатор должен обновлять статистику по таблицам и индексам для принятия «правильного» решения о плане выполнения. Как часто вы собираете статистику - это сложный вызов. Это зависит от вашего приложения, схемы, скорости передачи данных и деловой практики. Некоторые сторонние приложения, которые написаны для обратной совместимости со старой версией Oracle, плохо работают с новым оптимизатором. Эти приложения требуют, чтобы таблицы не имели статистики, чтобы база данных возвращалась к плану выполнения базы правил. Но в среднем Oracle рекомендует собирать статистику по таблицам с устаревшей статистикой. Вы можете настроить мониторинг таблиц, проверить их состояние и проанализировать, устарели ли они/когда. Часто этого достаточно, иногда это не так. Это действительно зависит от вашей базы данных. Для моей базы данных у нас есть набор таблиц OLTP, которые нуждаются в ночном сборе статистики для поддержания производительности. Другие таблицы анализируются раз в неделю. В нашей большой базе данных dw мы анализируем по мере необходимости, поскольку таблицы слишком велики для регулярного анализа, не влияя на общую нагрузку и производительность дБ. Таким образом, правильный ответ - это зависит от приложения, изменения данных и потребностей бизнеса.

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

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

Представьте, что у вас есть база данных ошибок с таблицей ISSUE и столбцом CREATE_DATE, где значения в столбце увеличиваются более или менее монотонно. Теперь предположим, что в этом столбце есть гистограмма, которая сообщает Oracle, что значения для этого столбца равномерно распределены между 1 января 2008 г. и 17 сентября 2008 г. Это позволяет оптимизатору разумно оценить количество строк, которые могли бы будет возвращен, если вы искали все проблемы, созданные на прошлой неделе (например, 7-13 сентября). Если приложение продолжает использоваться и статистика никогда не обновляется, эта гистограмма будет все менее и менее точной. Таким образом, оптимизатор будет ожидать, что запросы по «проблемам, созданным на прошлой неделе» будут все менее и менее точными с течением времени и могут в конечном итоге привести к тому, что Oracle отрицательно изменит план запроса.

Как правило, не рекомендуется собирать статистику по всей базе данных так часто, если только у вас нет веских оснований для этого, например, массовая вставка или большие изменения данных часто происходят в базе данных…. Сбор статистики в базе данных с такой частотой МОЖЕТ измениться план выполнения запросов для новых плохих планов выполнения, это может стоить вам много времени, пытаясь настроить каждый запрос, затронутый новыми плохими планами, поэтому вам следует проверить влияние сбора новой статистики в тестовой базе данных, или в случае, если у вас нет ни времени, ни человеческих полномочий для этого, по крайней мере вы должны сохранить запасной план, создав резервные копии исходной статистики, прежде чем собирать новые, так что в случае, если вы собираете новую статистику, а затем запросы не выполняются Как и ожидалось, вы можете легко восстановить исходную статистику.

В случае системы типа хранилища данных вы можете вообще не собирать статистику и полагаться на динамическую выборку (установив optimizer_dynamic_sampling на уровень 2 или выше).

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