В большинстве случаев SQL-запрос можно выполнять несколькими способами. Одинаковые результаты можно получать как за счет полного сканирования таблицы, так и за счет использования индекса. Данные также можно извлекать за счет доступа к таблицам и индексам в разном порядке. Задача оптимизатора состоит в поиске оптимального или наилучшего плана выполнения предоставляемых пользователем DML- операторов вроде SELECT, INSERT, UPDATE и DELETE. Для оказания помощи в определении эффективных методов выполнения запросов в Oracle применятся оптимизатор по стоимости (Cost-Based Optimizer — CBO).
Оптимизатор CBO, основываясь на статистических данных по таблицам и индексам, порядке таблиц и столбцов в SQL-операторах, доступных индексах и любых предоставляемых пользователем подсказках касательно методов доступа, выбирает наиболее эффективный способ для получения к ним доступа. Самым эффективным, по его мнению, является метод доступа, имеющий наименьшую стоимость в плане ресурсов подсистемы ввода-вывода и ЦП, которые он требует затратить на извлечение строк. Получение доступа к необходимым строкам означает выполнение Oracle считывания блоков базы данных из файловой системы в буферный пул. Связанное с этим потребление ресурсов подсистемы ввода-вывода является наиболее дорогостоящим этапом выполнения SQL- оператора, поскольку предусматривает чтение с диска. Изучать все эти пути доступа можно с помощью инструментов, подобных EXPLAIN PLAN. В следующих разделах описываются задачи, которые необходимо выполнять для получения уверенности в том, что оптимизатор функционирует эффективным образом.
Выбор режима оптимизации
В прежних версиях Oracle можно было выбирать между режимом оптимизации по синтаксису (rule-based) и режимом оптимизации по стоимости (cost-based). В режиме оптимизации по синтаксису применяется эвристический метод для осуществления выбора среди нескольких альтернативных путей доступа с помощью определенных правил. Всем путям доступа присваивался ранг, после чего из них выбирался тот, ранг у которого оказывался самым низким. Операции с более низким рангом обычно выполнялись быстрее операций с высоким рангом. Например, стоимость запроса, в котором для поиска строки используется идентификатор ROWID, будет равна 1. Этого следует ожидать, потому что выявление строки с помощью идентификатора ROWID, т.е. подобного указателям механизма Oracle, является самым быстрым способом для обнаружения строки. Стоимость запроса, в котором используется операция полного сканирования таблицы, с другой стороны, будет составлять 19, что является самым высоким из возможных значений стоимости при оптимизации по синтаксису. Режим оптимизации по стоимости практически всегда работает лучше прежнего режима оптимизации по синтаксису, поскольку, помимо всего прочего, предусматривает принятие во внимания и самых недавних статистически данных по объектам базы данных.
Предоставление статистических данных оптимизатору
По умолчанию необходимые оптимизатору статистические данные автоматически собираются самой базой данных. Каждую ночь во время указанного в планировщике Oracle (Oracle Scheduler) окна обслуживания выполняется задание по сбору статистики. Это окно по умолчанию занимает период с десяти вечера до шести утра в рабочие дни и весь день по выходным, а это задание называется GATHER_STATS_JOB и по умолчанию запускается в каждой базе данных Oracle Database 11g. Есть возможность отключать задание GATHER_STATS_JOB и получать детальные сведения о нем путем выполнения запроса к представлению DBA_SCHEDULER_JOBS.
В частности, во время задания GATHER_STATS_JOB собираются статистические данные для всех таблиц, по которым у оптимизатора либо вообще нет никаких статистических данных, либо есть, но только устаревшие. В Oracle статистические данные объекта начинают считаться устаревшими, если с момента последнего сбора более 10% его данных изменилось. По умолчанию в Oracle ведется мониторинг всех DML-изменений (вставок, обновлений и удалений), которые вносятся во все объекты базы данных. Информацию обо всех этих изменения можно просматривать с помощью представления DBA_TAB_MODIFICATIONS. На основании этого проводимого по умолчанию мониторинга за объектами в Oracle и принимается решение о том, следует собирать для того или иного объекта новые статистические данные либо нет.
Для проверки того, действительно ли GATHER_STATS_JOB регулярно собирает статистические данные, можно использовать следующий запрос:
SQL> SELECT last_analyzed, table_name, owner, num_rows, sample_size 2 FROM dba_tables 3* ORDER by last_analyzed; TABLE_NAME LAST_ANALYZED OWNER NUM_ROWS SAMPLE_SIZE ----------- ----------------------- -------- -------- ----------- iR_LICENSE 22/JUN/2008 12:38:56 AM APSOWNER 142 142 ROLL_AUDIT 06/JUN/2008 11:34:29 PM APSOWNER 8179264 5444 HISTORY_TAB 04/JUN/2008 07:28:40 AM APSOWNER 388757 88066 YTDM_200505 04/JUN/2008 07:23:21 AM APSSOWNER 113582 6142 REGS163X_200505 04/JUN/2008 07:23:08 AM APSSOWNER 115631 5375 UNITS 07/JUN/2008 01:18:48 AM APSOWNER 33633262 5144703 CAMPAIGN 16/JUN/2008 02:01:45 AM APSOWNER 29157889 29157889 FET$ 30/JUN/2008 12:03:50 AM SYS 5692 5692 . . . SQL>
В этом выводе важно обратить внимание на следующие моменты.
- GATHER_STATS_JOB собирает статистические данные во время окна обслуживания базы данных, под которое по умолчанию выделяется период с десяти вечера до шести утра в рабочие дни и весь день в выходные.
- GATHER_STATS_JOB запускается каждую ночь планировщиком Oracle.
- Если таблица была создана в этот день, при первом сборе для нее статистических данных GATHER_STATS_JOB использует все ее строки.
- Процент выборки варьируется от менее чем 1% до 100%.
- Размер таблиц и процент выборки никак не соотносятся.
- GATHER_STATS_JOB не собирает статистические данные для всех таблиц каждый день.
- Если данные таблицы не изменились с момента ее создания, GATHER_STATS_JOB никогда не собирает для нее статистические данные во второй раз.
В Oracle объем выборки (sample size) определяется для каждого объекта на основании внутренних алгоритмов; никакого стандартного объема выборки для всех объектов не существует. После получения уверенности в том, что сбор статистических данных действительно происходит регулярно, можно по большей части предоставлять базе данных самостоятельно заниматься этим делом и фокусировать внимание на других более важных областях, что потенциально позволяет работать с огромными базами данных годами и практически никогда не запускать задание по сбору статистики вручную с помощью пакета DBMS_STATS. Конечно, в случае загрузки данных в течение дня или после начала выполнения задания GATHER_STATS_JOB возможность будет утрачиваться, и объект будет оставаться без собранных статистических данных. Поэтому все-таки лучше приглядывать за теми объектами, которые могут подвергаться серьезными изменениям в течение дня и, возможно даже, планировать выполнение задания по сбору статистики сразу же после внесения изменений в данные.
Кроме того, необходимые статистические данные оптимизатору еще также можно предоставлять и самостоятельно с помощью пакета DBMS_STATS (в автоматическом процессе сбора статистики, выполняемом GATHER_STATS_JOB, внутренне используется именно этот пакет); о том, как конкретно это делать, более подробно речь пойдет позже в этой статье. В целом оптимизатору необходимо предоставлять следующие статистические данные:
- количество строк в таблице;
- количество строк в каждом блоке базы данных;
- средняя длина строк;
- общее количество блоков базы данных в таблице;
- количество уровней в каждом индексе;
- количество листовых блоков в каждом индексе;
- количество различающихся значений в каждом столбце таблицы;
- гистограммы распределения данных;
- количество различающихся ключей индекса;
- кардинальность (количество столбцов с похожими значениями в каждом);
- минимальные и максимальные значения для каждого столбца;
- статистические показатели системы, в том числе характеристики средств ввода-вывода в системе и статистические показатели ЦП, наподобие скорости ЦП и другие похожие показатели.
Главную роль в выборе наилучшего возможного плана запроса играет способность CBO правильно оценивать количество затрат, связанных с каждой операцией в этом плане. Оценка этих затрат производиться на основании того, сколько ресурсов ввода-вывода, ЦП и памяти требуется для выполнения каждой операции согласно статистическим данным по таблицам и индексам, а также на основании статистических показателей системы, которые перечислялись выше, и дополнительной информации, касающейся ее производительности.
Статистические данные, которые база данных собирает для оптимизатора, сохраняются в словаре данные. Просматривать статистические данные по всем таблицам в базе данных можно с помощью таблицы DBA_TAB_STATISTICS, а по столбцам — выполняя запрос к представлению DBA_TAB_COL_STATISTICS, как показано ниже:
SQL> SELECT column_name, num_distinct FROM dba_tab_col_statistics WHERE table_name='PERSONNEL'; COLUMN_NAME NUM_DISTINCT ------------------------------ ------------ PERSON_ID 22058066 UPDATED_DATE 1200586 DATE_OF_BIRTH 32185 LAST_NAME 7281 FIRST_NAME 1729 GENDER 2 HANDICAP_FLAG 1 CREATED_DATE 2480278 MIDDLE_NAME 44477 SQL>
Здесь видно, что в таблице PERSONNEL находится более 22 миллионов номеров PERSON_ID, но только 7281 различающихся фамилий (LAST_NAME) и 1729 различающихся имен (FIRST_NAME), а также, естественно, только два различающихся значения в столбце GENDER. Информацию о данных таблицы именно такого рода оптимизатор будет принимать во внимание перед принятием решения о том, какой план выполнения является наилучшим для SQL-оператора, предусматривающего обработку столбцов таблицы.
Совет. К числу необходимых оптимизатору статистических данных относятся как статистические данные по объектам (таблицам и индексам), так и статистические данные по системе. Без последних оптимизатор не сможет правильно вычислить количество затрат и, следовательно, выводить альтернативные планы выполнения.
Установка режима оптимизатора
Oracle оптимизирует производительность запросов по умолчанию. Под оптимизацией производительности подразумевается использование как можно меньшего количества ресурсов для обработки всего SQL-оператора. Еще можно просить Oracle оптимизировать время отклика, что обычно подразумевает использование как можно меньшего количества ресурсов для получения первой или первых n строк. В случае пакетных заданий время отклика отдельных SQL-операторов является не таким важным, как общее время, затрачиваемое на выполнение всех операции. В случае интерактивных приложений время отклика играет более критичную роль.
Для оптимизатора по стоимости (CBO) можно использовать любой из трех описанных ниже режимов. Значение, устанавливаемое для параметра инициализации OPTIMIZER_MODE, становится принятым по умолчанию режимом для оптимизатора Oracle. Оптимизатор, работающий по синтаксису (правилам), является устаревшим продуктом и потому даже не упоминается здесь.
- ALL_ROWS. Этот режим применяется для оптимизатора по умолчанию и заставляет Oracle использовать CBO независимо от того, имеются статистические данные по любой из указанных в запросе таблиц (полученные вручную с помощью пакета DBMS_STATS или автоматически самой базой данных Oracle) или нет, тем самым выражая необходимость максимизировать производительность.
Совет. При описании всех трех значений режима оптимизации говорится о применении оптимизации по стоимости независимо от того, имеются ли какие-то статистические данные по тем объектам, к которым получается доступ в запросе. Это означает, что при отсутствии статических данных, собранных с помощью пакета DBMS_STATS, Oracle будет применять динамические приемы выборки для сбора необходимой оптимизатору статистики во время выполнения. Для некоторых типов объектов, наподобие внешних и удаленных таблиц, Oracle будет использовать просто принятые по умолчанию значения вместо выбираемых динамически. Например, для длины строки Oracle будет применять принятое по умолчанию значение, составляющее 100 байт, а для количества строк — значение, выводимое приблизительно из количества занимаемых таблицей блоков хранения и средней длины строк. Однако ни выбираемые динамически, ни принятые по умолчанию значения не будут давать таких же хороших результатов, как подробные статистические данные, собираемые с помощью пакета DBMS_STATS. Как при ручном методе, так и при использовании функции Automatic Optimizer Statistics Collection (которая на внутреннем уровне тоже пользуется пакетом DMBS_STATS), статистические данные для оптимизатора так или иначе собираются с помощью пакета DBMS_STATS.
- FIRST_ROWS_n. Этот режим оптимизации подразумевает применение оптимизации по стоимости независимо от доступности статистических данных. Его целью является достижение максимально быстрого времени отклика по возврату n-го количества первых строк вывода, где на месте n может указываться значение 10, 100 или 1000.
- FIRST_ROWS. Этот режим предусматривает применение оптимизации по стоимости и определенной эвристики (т.е. главных правил) независимо от того, доступны статистические данные или нет. Он применяется тогда, когда требуется, чтобы первые строки возвращались быстро, и время отклика тем самым сводилось к минимуму. Обратите внимание на то, что режим FIRST_ROWS был оставлен только для целей обеспечения обратной совместимости и что режим FIRST_ROWS_n является последней версией этой модели.
Установка уровня оптимизатора
Режим оптимизатора можно устанавливать на уровне экземпляра, сеанса или оператора. На уровне экземпляра режим оптимизатора устанавливается за счет установки для параметра OPTIMIZER_MODE значения ALL_ROWS, FIRST_ROWS_n или FIRST_ROWS, как объяснялось в предыдущем разделе. Например, делать целью оптимизатора запросов весь экземпляр можно добавлением в файл параметров инициализации следующей строки:
OPTIMIZER_MODE = ALL_ROWS
Установка для параметра инициализации OPTIMIZER_MODE значения ALL_ROWS гарантирует получение всего результирующего набора запроса сразу же, как только это станет возможным.
Еще режим оптимизатора можно устанавливать и только для одного сеанса за счет использования следующего оператора ALTER SESSION:
SQL> ALTER SESSION SET optimizer_mode = first_rows_10; Session altered. SQL>
Показанный выше оператор указывает оптимизатору основывать свои решения на наилучшем времени отклика для получения первых десяти строк вывода каждого из выполняемых SQL-операторов.
На заметку! Обратите внимание на то, что действие выбираемого режима оптимизатора распространяется только на те SQL-операторы, которые выполняются напрямую. В случае применения оператора ALTER SESSION для изменения режима оптимизатора на уровне какого-нибудь SQL-оператора, который является частью блока кода PL/SQL, он будет игнорироваться. Для установки режима оптимизатора на уровне любого SQL-оператора, который является частью блока PL/SQL, нужно применять специально предназначенные для оптимизатора подсказки.
Узнать, как в текущий момент выглядит режим оптимизатора в базе данных, можно с помощью следующего запроса:
SQL> SELECT name, value FROM V$PARAMETER 2 WHERE name = 'optimizer_mode'; NAME VALUE --------------- -------- optimizer_mode ALL_ROWS SQL>
Любой SQL-оператор может переопределять установленные на уровне экземпляра или сеанса настройки с помощью так называемых подсказок оптимизатора (optimizer hints), которые представляют собой предназначенные для оптимизатора директивы касательно выбора оптимального метода доступа. За счет применения таких подсказок можно легко переопределить действующее на уровне экземпляра значение параметра инициализации OPTIMIZER_MODE.
Что делает оптимизатор
Оптимизатор (CBO) выполняет несколько хитроумных шагов для получения оптимального плана выполнения для запроса пользователя. Чаще всего он преобразует исходный SQL-оператор и вычисляет альтернативные пути доступа (вроде сканирования всей таблицы или только отдельных индексов). При наличии необходимости в соединении таблиц, он еще также вычисляет и все возможные методы и порядки соединений. То есть оптимизатор вычисляет все возможные варианты и приходит к такому плану выполнения, который является наиболее дешевым в точки зрения общей стоимости, т.е. стоимости по использованию ресурсов как подсистемы ввода-вывода, так и ЦП.
Преобразование SQL-операторов
Запросы редко когда выполняются в Oracle в своем исходном виде. В случае обнаружения, что другая формулировка SQL-запроса позволит достичь тех же самых результатов, но более эффективно, CBO преобразовывает оператор перед его выполнением. Хорошим примером может служить предоставление запроса с условием OR, который CBO всегда преобразует в оператор с конструкцией UNION или UNION ALL, или оператора с подсказкой, указывающей осуществлять сканирование конкретных индексов, который CBO может преобразовать в оператор, предусматривающий выполнение сканирования всей таблицы, что в некоторых случаях может оказаться более эффективным вариантом. Как бы там ни было, не помешает запомнить, что запрос, который желает выполнить пользователь, может и не выполнятся в Oracle в том же самом виде, но его результаты все равно будут выглядеть так же. Ниже перечислены некоторые наиболее распространенные виды выполняемых Oracle CBO преобразований.
- Преобразование операций IN в операции OR.
- Преобразование операций OR в операции UNION или UNION ALL.
- Преобразование несвязанных вложенных операторов SELECT в более эффективные операторы соединения.
- Преобразование операторов внешнего соединения в более эффективные операторы внутреннего соединения.
- Преобразование сложных подзапросов в операторы соединения, полусоединения и антисоединения.
- Выполнение для таблиц в хранилище данных преобразования типа звезда на основании схемы типа “звезда”.
- Преобразование операторов BETWEEN в операторы GREATER THAN OR EQUAL TO (больше чем или равно) или LESS THAN OR EQUAL TO (меньше чем или равно).
Выбор пути доступа
Oracle часто может получать доступ к одним и тем же данным различными путями. Для каждого запроса оптимизатор вычисляет все возможные пути и выбирает из них тот, который является наименее дорогостоящим в плане потребления ресурсов. В следующих разделах дается краткий обзор наиболее типичных методов доступа, которые может использовать оптимизатор. Если применяются операции соединения, тогда для получения наилучшего плана выполнения еще и вычисляется порядок и метод соединения. Также будут вкратце рассмотрены и все этапы, через которые доводится проходить оптимизатору перед принятием окончательного решения касательно выбираемого плана выполнения.
Полное сканирование таблиц
При выполнении полного сканирования таблиц Oracle сканирует таблицу целиком. Oracle считывает каждый блок в таблице последовательно, благодаря чему операция полного сканирования таблицы может оказываться эффективной, если в базе данных на внутреннем уровне по умолчанию используется высокое значение для параметра инициализации DB_FILE_MULTIBLOCK_READ_COUNT. Этот параметр определяет, сколько максимум блоков может считываться при выполнении последовательного сканирования. В случае больших таблиц, однако, операции полного сканирования, как правило, являются неэффективными.
Получение доступа к таблицам по идентификатору ROWID
Получение доступа к таблице по ROWID подразумевает извлечение строк с использованием уникальных идентификаторов ROWID. В Oracle идентификаторы ROWID указывают, где точно в файле данных и в каком именно блоке данных находится строка, поэтому получение доступа с помощью этих идентификаторов является самым быстрым способом извлечения строк в Oracle. Зачастую Oracle получает ROWID за счет сканирования индексов таблицы. В случае использования идентификаторов ROWID выборка строк в Oracle происходит очень быстро.
Сканирование индексов
В каждом индексе хранится две вещи: значение столбца, на котором основан индекс, и ROWID-идентификатор строк в таблице, в которой содержится это значение столбца. При сканировании индекса производится извлечение данных из индекса с использованием значений его столбцов. В случае запрашивания в запросе значений только индексированных столбцов, Oracle будет возвращать эти значения, а в случае запрашивания в нем значений еще и других неиндексированных столбцов, Oracle будет использовать идентификаторы ROWID и извлекать строки из таблицы.
Выбор метода соединения
При необходимости получения доступа к данным, находящимся в двух или более таблицах, Oracle соединяет эти таблицы на основании общего столбца. Однако существует несколько способов для соединения наборов строк, возвращаемых на различных этапах плана выполнения. Для каждого оператора Oracle вычисляет наилучший метод соединения на основе статистических данных и типа уникальных или первичных ключей таблиц. После вычисления методов соединения Oracle оптимизатор CBO выбирает из них тот, который является наименьшим по стоимости.
Ниже перечислены наиболее типичные методы соединения, которые может использовать CBO.
- Соединение с вложенным циклом (nested loop join). Такое соединение подразумевает назначение одной таблицы в цикле соединения управляющей таблицей (driving table), также называемой внешней таблицей (outer table). Другая таблица в соединении называется внутренней таблицей (inner table). Oracle извлекает все строки внутренней таблицы для каждой строки в управляющей таблице.
- Хеш-соединение (hash join). При соединении двух таблиц Oracle использует ту, которая имеет меньший размер, для построения хеш-таблицы по ключу соединения, а затем выполняет поиск по той, которая имеет больший размер, и возвращает соединенные строки из хеш-таблицы.
- Соединение типа сортировка-слияние (sort-merge join). Операция сортировки при таком соединении предусматривает выполнение сортировки входных данных по ключу соединения, а операция слияния — выполнение слияния отсортированных списков. Если входные данных уже отсортированы по столбцу соединения, выполнять операцию сортировки для данного источника строк нет никакой необходимости.
Выбор порядка соединения
После выбора метода соединения оптимизатор определяет порядок, в котором должны соединяться таблицы. Целью оптимизатора является соединением таблиц таким образом, чтобы управляющая таблица исключала как можно большее количество строк. Запрос с четырьмя таблицами, например, будет иметь максимум 4!, или 24, возможных способа соединения этих таблиц. Каждый из этих способов или порядков соединения будет приводить к получению других планов выполнения на основании доступных индексов и методов доступа. Поиск оптимальной стратегии соединения может занимать много времени в запросе с большим количеством таблиц, поэтому в Oracle применяется стратегия адаптивного поиска (adaptive search strategy) для ограничения количества времени, затрачиваемого на поиск наилучшего плана выполнения. Эта стратегия гарантирует, что время, затрачиваемое на оптимизацию, всегда будет составлять лишь небольшой процент от общего времени, затрачиваемого на выполнение самого запроса.
Недостатки CBO
Оптимизатор CBO работает систематизированным образом, но не следует постоянно одному и тому же плану в похожих ситуациях. Его действия, однако, не всегда являются идеальными, и потому необходимо помнить о перечисленных ниже моментах.
- CBO не работает фиксированным образом во всех версиях Oracle. Планы выполнения могут со временем меняться и потому выглядеть при смене версий по-разному. Чуть позже в этой гстатье будет показано, как создавать хранимые планы выполнения, чтобы оптимизатор всегда использовал известный план выполнения, и тем самым обеспечивал стабильность.
- Разработчики приложений могут знать больше, чем CBO, о том, какой путь доступа является наилучшим. Разработчикам приложений известно о потребностях пользователей, а CBO нет. Это чревато тем, что CBO может оптимизировать производительность тогда, когда пользователи лучше бы предпочли быстро видеть набор результатов на своем экране. За счет применения подсказок наподобие FIRST_ROWS_n этот недостаток CBO можно легко преодолеть.
- Работа CBO очень сильно зависит от сбора правильных статистических данных. Если статистические данные отсутствует или устарели, оптимизатор может принимать очень неэффективные решения.
Предоставление статистических данных оптимизатору CBO
Хотя позволение базе данных автоматически собирать статистические данные для оптимизатора и является рекомендуемым подходом, собирать эти данные можно и вручную с помощью пакета DBMS_STATS.
Совет. В случае больших таблиц Oracle рекомендует производить выборку (sampling) отдельных данных, а не всех. Oracle позволяет производить выборку как отдельных строк, так и отдельных блоков, и порой рекомендует, чтобы объем выборки составлял всего лишь 5%. По умолчанию объем выборки для проведения оценки тоже является низким. Вдобавок Oracle рекомендует использовать автоматическую процедуру выборки из пакета DBMS_STATS. Статистические данные, собираемые посредством выборки отдельных данных, однако, не являются надежными. Разница между сбором статистических данных с оценкой в 30% и 50% порой просто поражает в плане производительности. Поэтому лучше всегда собирать полностью все статистические данные по всем объектам пусть даже не так часто, как можно было бы в случае лишь выборки данных.
Как уже упоминалось в блогах, обязательно собирать статистические данные для оптимизатора вручную нужно в следующих случаях:
- в случае использования внешних таблиц;
- при необходимости сбора статистических данных по системе;
- при необходимости сбора статистических данных по фиксированным объектам, наподобие динамических таблиц производительности (для сбора статистических данных по динамическим таблицам необходимо использовать процедуру GATHER_FIXED_OBJECTS_STATS);
- сразу же после выполнения задания по массовой загрузке, поскольку это делает статистические данные, собранные автоматическим образом, нерепрезентативными.
В следующих разделах показано, как использовать пакет DBMS_STATS для сбора статистических данных.
На заметку! Oracle рекомендует не пользоваться прежним оператором ANALYZE для сбора статистических данных для оптимизатора, а лучше применять вместо него пакет DBMS_STATS. Команда ANALYZE поддерживается только для обеспечения обратной совместимости и потому должна применяться для заданий по сбору статистических данных не для оптимизатора, а, например, для верификации действительности объекта (с использованием конструкции VALIDATE) или для выявления перемещенных и расщепленных строк в таблице (с использованием конструкции LIST CHAINED ROWS).
Сохранение статистических данных для оптимизатора
Для сбора статистических данных для оптимизатора применяются различные процедуры из пакета DBMS_STATS. Почти все эти процедуры обладают тремя общими атрибутами — STATOWN, STATTAB и STATID, — которые позволяют сохранять собираемые статистические данные в принадлежащей пользователю таблице базы данных. По умолчанию все эти атрибуты имеют нулевое значение, и предоставлять для любого из них другое значение в случае сбора статистических данных для оптимизатора не требуется. При игнорировании этих атрибутов, собираемые для оптимизатора статистические данные по умолчанию сохраняются в таблицах словаря данных, где к ним может получать доступ оптимизатор Oracle.
Сбор статистических данных
В пакете DBMS_STATS есть несколько процедур, которые позволяют собирать статистические данные на различных уровнях. Наиболее важные процедуры для сбора данных на уровне таблиц и индексов базы данных в этом пакете перечислены ниже.
- GATHER_DATABASE_STATISTICS. Позволяет собирать статистические данные по всем объектам в базе данных.
- GATHER_SCHEMA_STATISTICS. Позволяет собирать статистические данные по всей схеме.
- GATHER_TABLE_STATISTICS. Позволяет собирать статические данные по таблице и ее индексам.
- GATHER_INDEX_STATISTICS. Позволяет собирать статистические данные по индексу.
Ниже приведено два примера, первый из которых показывает, как применять пакет DBMS_STATS для сбора статистических данных по схеме, а второй — как его применять для сбора статистических данных по отдельной таблице.
- Сбор статистических данных на уровне схемы:
SQL> EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS (ownname => 'hr');
PL/SQL procedure successfully completed.
SQL>
- Сбор статистических данных на уровне отдельной таблицы:
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('hr','employees');
PL/SQL procedure successfully completed.
SQL>
Процедура GATHER_DATABASE_STATISTICS позволяет собирать необходимые оптимизатору статистические данные на уровне всей базы данных. Она является, пожалуй, самым распространенным способом применения пакета DBMS_STATS, поскольку предоставляет возможность собирать статистические данные по всем объектам базы с помощью единственного оператора. Ниже приведен пример:
SQL> EXECUTE dbms_stats.gather_database_stats (- > ESTIMATE_PERCENT => NULL, - > METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', - > GRANULARITY => 'ALL', - > CASCADE => 'TRUE', - > OPTIONS => 'GATHER AUTO'); PL/SQL procedure successfully completed. SQL>
Совет. Хотя и можно собирать необходимые оптимизатору статистические данные за счет указания с помощью атрибута ESTIMATE_PERCENT конкретного количества подлежащих выборке строк, которое может варьироваться от 0,000001 до 100 процентов, следует стараться собирать статистические данные по всем строкам (за счет установки для этого атрибута значения NULL). Выборочный сбор статистических данных таит много опасностей. Если только таблицы не являются настолько огромными, что на сбор всех статистических данных по ним не хватает выделенного окна обслуживания, лучше стараться собирать полные статистические данные по всем объектам, особенно по тем, которые часто подвергаются DML-изменениям.
Давайте вкратце рассмотрим приведенную выше процедуру GATHER_DATABASE_STATS.
- В этом примере присутствует лишь некоторые из тех различных атрибутов или параметров, которые можно задавать в процедуре GATHER_DATABASE_STATS. Увидеть перечень всех доступных атрибутов можно с помощью такой команды:
SQL> DESCRIBE DBMS_STATS.GATHER_DATABASE_STATS
- Если не задан ни один из атрибутов, Oracle использует значения, принятые для них по умолчанию. В этом примере даже там, где используются принятые по умолчанию значения, атрибуты все равно перечисляются в целях демонстрации.
- Атрибут ESTIMATE_PERCENT указывает, сколько (в процентах) строк должно использоваться для подсчета статистики. В данном примере для него было выбрано значение NULL. Это значение здесь, вопреки логике, означает, что Oracle следует собирать статистические данные на основании всех строк в таблице. Применение атрибута ESTIMATE_PERCENT в процедуре GATHER_DATABASE_STATS, по сути, равнозначно применению параметра COMPUTE STATISTICS в традиционной команде ANALYZE. По умолчанию этот атрибут указывает Oracle вычислять объем выборки для каждого объекта с помощью процедуры DBMS_STATS.AUTO_SAMPLE_SIZE.
- Атрибут METHOD_OPT может служить для указания нескольких вещей, в том числе и того, должны ли собираться гистограммы. В этом примере для него было выбрано значение FOR ALL COLUMNS SIZE AUTO, являющееся значением по умолчанию.
- Действие атрибута GRANULARITY распространяется только на таблицы. Значение ALL указывает, что Oracle следует собирать статистические данные по всем таблицам как на уровне отдельных секций и подсекций, так и на общем глобальном уровне.
- Атрибут CASCADE => 'TRUE' указывает, что вместе со статическими данными по таблицам также должны собираться и статистические данные по всем их индексам.
- Атрибут OPTIONS играет критически важную роль. Ниже перечислены наиболее важные значения, которые могут устанавливаться для него.
- GATHER. Указывает, что Oracle следует собирать статистические данные по всем объектам, независимо от того, имеются ли по ним старые и новые статистические данные.
- GATHER AUTO. Указывает, что Oracle следует собирать статистические данные только по тем объектам, для которых это необходимо.
- GATHER EMPTY. Указывает, что Oracle следует собирать статистические данные только по тем объектам, по которым еще нет статистических данных.
- GATHER STALE. Указывает, что Oracle следует собирать статистические данные только по тем объектам, статистические данные по которым уже устарели, и определять, устарели они или нет, посредством представления DBA_TAB_MODIFICATIONS.
Обратите внимание, что предыдущую процедуру GATHER_DATABASE_STATS можно также было бы выполнить и в следующем виде (и получить при этом эквивалентные результаты):
SQL> BEGIN dbms_stats.gather_database_stats (ESTIMATE_PERCENT => NULL, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', GRANULARITY => 'ALL', CASCADE => 'TRUE', OPTIONS => 'GATHER AUTO'); END; PL/SQL procedure successfully completed. SQL>
Проверить, когда таблица анализировалась в последний раз, можно с помощью такого запроса:
SQL> SELECT table_name, last_analyzed FROM dba_tables; TABLE_NAME LAST_ANALYZED ----------- -------------- TEST1 07/08/2008 TEST2 07/08/2008 TEST3 07/08/2008 . . . SQL>
Похожий запрос можно использовать и для индексов, но только, соответственно, вместо DBA_TABLES указывать представление DBA_INDEXES.
Совет. Не следует забывать устанавливать для параметра инициализации JOB_QUEUE_PROCESSES в качестве значения положительное число. В случае если значение для этого параметра не задано, оy будет установлен в 0, и процедура DBMS_STATS.GATHER_SYSTEM_STATS работать не будет. Делать это можно и динамически, например, выполнив такую команду: ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20
Отсрочка публикации статистических данных
По умолчанию база данных публикует собираемые статические данные немедленно, так чтобы оптимизатор мог сразу же их использовать. Однако иногда бывает нужно, чтобы этого не происходило. Например, взамен может потребоваться сначала протестировать статистические данные и сделать их доступными для общего использования только в том случае, если они устраивают. Для этого Oracle позволяет сохранять собираемые новые статистические данные как ожидаемые (pending) и принимать решение о том, публиковать их или нет, позже на основании результатов их тестирования. Текущие или публикуемые статистические данные немедленно делаются доступными для использования оптимизатором, а ожидаемые или задерживаемые статистические данные носят частный характер и остаются для оптимизатора недоступными.
Определение и изменение режима публикации статистических данных
Определять, какой режим публикации статистических данных используется в базе данных в текущий момент, можно с помощью процедуры DBMS_STATS.GET_PREFS:
SQL> select dbms_stats.get_prefs('PUBLISH') publish from dual;
PUBLISH
--------
TRUE
SQL>
Значение TRUE указывает на то, что в текущий момент база данных автоматически публикует все статистические данные после их сбора. Подобным образом база данных ведет себя по умолчанию. Возврат после выполнения такого запроса значения FALSE будет свидетельствовать о том, что в текущий момент база данных оставляет все собираемые новые статистические данные в состоянии ожидания до тех пор, пока не будет принято официальное решение опубликовать их. Также можно выяснять, какой режим публикации используется для конкретной таблицы, выполнив функции GET_PREFS:
SQL> SELECT dbms_stats.get_prefs('PUBLISH','stats','test_table')
FROM dual;
Изменять режим публикации для объектов на уровне базы данных или отдельных объектов (таблицы) можно с помощью функции SET_TABLE_PREFS. Например, запретить базе данных автоматически публиковать статистические данные, которые она собирает по таблице EMPLOYEES, с помощью этой функции можно так:
SQL> exec dbms_stats.set_table_prefs ('HR','EMPLOYEES',
'PUBLISH','FALSE');
Статистические данные, публикация которых откладывается, сохраняются в представлении DBA_TAB_PENDING_STATS, а статистические данные, публикация которых выполняется немедленно — в представлении DBA_TAB_STATS.
Публикация отложенных статистических данных
Любые отложенные статистические данные можно тестировать в базе данных для выяснения того, каким образом они повлияют на ее производительность. Если результаты тестирования покажут, что эти статистические данные действительно способны улучшить производительность базы данных, их можно опубликовать, чтобы их мог использовать оптимизатор, а в противном случае просто удалить. Публиковать отложенные статистические данные, делая их доступными для оптимизатора, можно установкой соответствующего значения для параметра инициализации OPTIMIZER_USE_PENDING_STATISTICS. По умолчанию упомянутый параметр устанавливается в FALSE; это означает, что оптимизатор будет обходить отложенные статистические данные, как показано ниже:
SQL> show parameter optimizer_use_pending_statistics NAME TYPE VALUE -------------------------------- ------- ------ optimizer_use_pending_statistics boolean FALSE SQL>
Заставить оптимизатор принимать отложенные статистические данные во внимание можно, выбрав для параметра OPTIMIZER_USE_PENDING_STATISTICS значение TRUE:
SQL> ALTER SESSION SET optimizer_use_pending_statistics=TRUE ;
После выполнения показанного выше оператора оптимизатор будет сразу же начинать использовать отложенные статистические данные. После получения с помощью тестирования подтверждения в том, что новые статистические данные действительно являются полезными, их еще также можно делать общедоступными, выполнив процедуру PUBLISH_PENDING_STATS:
SQL> EXEC dbms_stats.publish_pending_stats (NULL,NULL);
При желании опубликовать статистические данные только по одной таблице, это тоже можно делать следующим образом:
SQL> EXEC dbms_stats.publish_pending_stats('HR','EMPLOYEES'); В случае вывода о том, что отложенные статистические данные являются абсолютно бесполезными, их можно просто удалить с помощью процедуры DELETE_PENDING_STATS:
SQL> EXEC dbms_stats.delete_pending_stats ('HR','EMPLOYEES');
Отложенные статистические данные, собранные в одной базе данных, можно протестировать в другой базе данных за счет применения процедуры EXPORT_PENDING_STATS:
SQL> EXEC dbms_stats.export_pending_stats ('HR', 'EMPLOYEES');
Расширенные статистические данные
Статистические данные, которые собирает база, порой являются нерепрезентативными. Поэтому в Oracle предлагается возможность осуществлять при некоторых обстоятельствах сбор расширенных статистических данных для уменьшения проблем, связанных со сбором статистики. К таким расширенным статистическим данным относятся статистические данные по группам столбцов и статистические данные по выражениям, подразумевающие сбор статистики по столбцам на основе функций. Оба этих вида расширенных статистических данных для оптимизатора более подробно рассматриваются в следующих разделах.
Статистические данные по группам столбцов
При сборе статистических данных по таблице Oracle оценивает степень избирательности каждого столбца по отдельности, даже когда два или более столбца тесно связаны между собой. Oracle предполагает, что оценочные значения по степени избирательности отдельных столбцов не зависят друг от друга, и просто перемножает значения избирательности независимых предикатов для вычисления степени избирательности всей группы предикатов. Такой подход ведет к недооценке настоящей степени избирательности группы столбцов. Во избежание этой недооценки и можно собирать статистические данные по группам столбцов.
Давайте рассмотрим простой пример, чтобы увидеть, почему сбор статистических данных по группам столбцов вместо отдельных столбцов является хорошей идеей, когда столбцы взаимосвязаны. Возьмем таблицу SH.CUSTOMERS, столбцы CUST_STATE_PROVINCE и COUNTRY_ID в которой связаны между собой, причем первый определяет значение второго. Ниже приведен запрос, показывающий, как выглядят отношения между этими двумя столбцами:
SQL> SELECT count(*) FROM sh.customers WHERE cust_state_province = 'CA'; COUNT(*) ---------- 3341 SQL>
В этом запросе используется только один столбец CUST_STATE_PROVINCE для получения информации о количестве заказчиков из региона CA. В следующем запросе применяется еще и столбец COUNTRY_ID, но возвращается то же самое значение 3341:
SQL> SELECT count(*) FROM customers WHERE cust_state_province = 'CA' AND country_id=52790; COUNT(*) ---------- 3341 SQL>
Очевидно, что точно такой же запрос с другим значением для столбца COUNTRY_ID вернет другое значение (каковым, скорее всего, будет 0, поскольку CA расшифровывается как California и вряд ли в других странах имеется регион с таким же названием). Собирать статистические данные по ряду взаимосвязанных столбцов вроде CUST_STATE_PROVINCE и COUNTRY_ID можно за счет оценивания совместной степени избирательности обоих столбцов. База данных может собирать статистические данные по группам столбцов на основании данных по рабочей нагрузке, но создаваться группы столбцов должны вручную за счет применения функции DBMS_STATS.CREATE_EXTENDED_STATS, как объясняется далее.
Создание групп столбцов
Создается группа столбцов за счет выполнения функции CREATE_EXTENDED_STATS, как показано в следующем примере:
declare cg_name varchar2(30); begin cg_name := dbms_stats.create_extended_stats(null,'customers', '(cust_state_province,country_id)'); end; /
После создания группы столбцов подобным образом база данных будет автоматически собирать статистические данные по этой группе столбцов, а не двум столбцам как отдельным сущностям.
Удостовериться в успешном создании новой группы столбцов можно посредством следующего запроса:
SQL> SELECT extension_name, extension
FROM dba_stat_extensions
WHERE table_name='CUSTOMERS';
EXTENSION_NAME EXTENSION
----------------------------- -----------------------------------
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_ ("CUST_STATE_PROVINCE","COUNTRY-ID")
SQL>
Удаляется группа столбцов с помощью функции DROP_EXTENDED_STATS:
SQL> exec dbms_stats.drop_extended_stats('sh','customers','
(cust_state_province, country_id)');
Сбор статистических данных по группам столбцов
Для сбора статистических данных по группам столбцов можно выполнять процедуру GATHER_TABLE_STATS вместе с установкой для аргумента METHOD_OPT значения for all columns.... За счет добавления конструкции FOR COLUMNS можно заставлять базу данных создавать новую группу столбцов и собирать для нее статистические данные за один шаг, как показано ниже:
SQL> exec dbms_Stats.gather_table_stats( ownname=>null,- tabname=>'customers',- method_opt=>'for all columns size skewonly,- for columns (cust_state_province,country_id) size skewonly'); PL/SQL procedure successfully completed. SQL>
Статистические данные по выражениям
В случае применения к столбцу какой-нибудь функции, значение столбца изменяется. Например, функция LOWER, показанная в приведенном ниже примере, возвращает строку в нижнем регистре:
SQL> SELECT count(*) FROM customers WHERE LOWER(cust_state_province)='ca';
Хотя функция LOWER и преобразует значения столбца CUST_STATE_PROVINCE, переводя их в нижний регистр, оптимизатору достаются статистические данные только по исходным, но не измененным значения. Следовательно, оптимизатор на самом деле не имеет четкого представления о том, как по-настоящему выглядят выбранные преобразованные значения столбца. По некоторым видам применяемых в отношении столбцов выражений можно собирать статистические данные в тех случаях, когда функция сохраняет исходные характеристики по распределению данных в исходном столбце. Это особенно верно в случае применения к столбцу функции, подобной TO_NUMBER. Для определяемых пользователем функций могут использоваться выражения, а также индексы на основе функций.
Действие механизма сбора статистических данных по выражениям основано на поддерживаемых Oracle возможностях виртуальных столбцов. Для создания статистических данных по выражениям столбцов служит функция CREATE_EXTENDED_STATS:
SQL> SELECT dbms_stats.create_extended_stats(null,'customers', '(lower(cust_state_province))') FROM dual;
В качестве альтернативного варианта для создания статистики по выражениям еще также можно выполнять и функцию GATHER_TABLE_STATS:
SQL> exec dbms_stats.gather_table_stats(null,'customers', method_opt=>'for all columns size skewonly, for columns (lower(cust_state_province)) size skewonly');
Как и в случае статистических данных по группам столбцов, получать информацию о статистических данных по выражениям можно с помощью запроса к представлению DBA_STAT_EXTENSIONS.
Используемая оптимизатором Oracle модель оценки стоимости
В используемой оптимизатором модели оценки стоимости учитывается расход как ресурсов подсистемы ввода-вывода, так и ресурсов ЦП, исчисляющийся и в том и в другом случае в единицах времени. CBO вычисляет стоимость альтернативного запроса за счет сравнения общего времени, необходимого для выполнения всех операций ввода-вывода, а также количества циклов ЦП, необходимых для выполнения самого запроса CBO, берет общее количество операций ввода-вывода и циклов ЦП, которые будут необходимы согласно его оценкам, и преобразует их в показатель времени выполнения, а затем просто сравнивает его с аналогичным показателем всех альтернативных планов выполнения и выбирает наилучшего кандидата на выполнение.
Чтобы CBO мог точно вычислять стоимость альтернативных путей, у него должен быть доступ к точным статистическим данным по показателям системы. Эти статистические данные, которые включают в себя детали наподобие времени поиска дорожки во время проведения операции ввода-вывода, времени передачи ввода-вывода и скорости ЦП, сообщают оптимизатору, насколько быстро работает подсистема ввода-вывода и ЦП. Предоставление таких статистических данных оптимизатору является обязанностью администратора баз данных. Сбору статистических данных по системt посвящен следующий раздел.
Сбор статистических данных по системе
Если необходимые оптимизатору статистические данные по таблицам и индексам Oracle может собирать автоматически, то по операционной системе статистические данные необходимо собирать вручную с помощью процедуры GATHER_SYSTEM_STATS. При выполнении этой процедуры Oracle заполняет таблицу SYS.AUX_STATS$ данными о различных статистических показателях системы, наподобие показателей по производительности ЦП и устройств ввода-вывода. Очень важно собирать такие статистические данные регулярно, поскольку оптимизатор CBO использует их в качестве основы для вычисления стоимости различных запросов. Эти статистические данные позволяют оптимизатору более точно сравнивать количество затрат в плане ресурсов ЦП и подсистемы ввода-вывода при исходном и альтернативном варианте выполнения, а также более точно определять время выполнения запроса.
Процедуру GATHER_SYSTEM_STATS можно выполнять в разных режимах, передавая соответствующее значение ее параметру GATHERING_MODE. В частности, в этом параметре можно задавать как режим без сбора статистических данных по рабочей нагрузке (позволяющий быстро перехватывать статистические показатели только подсистемы ввода-вывода) с помощью значения NOWORKLOAD, так и режим со сбором статистических данных по рабочей нагрузке, специфицируя значения INTERVAL или START и STOP. Ниже приведено краткое объяснение всех этих различных значений, которые можно использовать для параметра GATHERING_MODE.
- Режим без сбора статистических данных по рабочей нагрузке. За счет использования ключевого слова NOWORKLOAD можно собирать статистические данные лишь по определенным показателям системы, по большей части охватывающим общие показатели подсистемы ввода-вывода, наподобие показателя скорости поиска (IOSEEKTIM) и скорости передачи ввода-вывода (IOTFRSPEED). В идеале запускать процедуру GATHER_SYSTEM_STATS в режиме без сбора статистических данных по рабочей нагрузке следует сразу же после создания новой базы данных. В таком режиме эта процедура выполняется лишь несколько минут и подходит для всех видов данных о рабочей нагрузке.
На заметку! В случае сбора статистических данных с и без статистических данных по рабочей нагрузке, оптимизатор будет использовать те, в которых присутствуют статистические данные по рабочей нагрузке.
Режим со сбором статистических данных по рабочей нагрузке (workload mode). Для сбора репрезентативных статистических данных, вроде данных по производительности ЦП и подсистемы ввода-вывода, их сбор нужно проводить на протяжении определенного интервала времени, отражающего типичную картину рабочей нагрузки на экземпляр. Для указания того, на протяжении какого интервала времени должен осуществляться сбор статистических данных, можно использовать ключевое слово INTERVAL. В качестве альтернативного варианта можно применять ключевые слова START и STOP и с их помощью указывать, насколько долго должны собираться статистические данные. И в том и в другом случае база данных будет собирать статистические данные по следующим показателям: MAXTHR, SLAVETHR, CPUSPEED, SREADTIM, MREADTIM и MBRC.
Ниже описано, что означает каждый из этих перечисленных показателей.
- IOTFRSPEED — скорость передачи ввода-вывода (в байтах на миллисекунду).
- IOSEEKTIM — время поиска + время ожидания (latency time) + время, затрачиваемое на обработку накладных расходов операционной системы (в миллисекундах).
- SREADTIM — среднее время, затрачиваемое на (произвольное) считывание одного блока (в миллисекундах).
- MREADTIM — среднее время, затрачиваемое на (последовательное) считывание всего блока MBRC сразу (в миллисекундах).
- CPUSPEED — среднее количество циклов ЦП, охватываемых в режиме со сбором статистических данных по рабочей нагрузке (т.е. в случае сбора статистики с использование опции INTERVAL или START и STOP).
- CPUSPEEDNW — среднее количество циклов ЦП, охватываемых в режиме без сбора статистических данных по рабочей нагрузке (т.е. в случае сбора статистики с использованием опции NOWORKLOAD).
- MBR — среднее число считываемых блоков при последовательном считывании (в блоках).
- MAXTHR — максимальная пропускная способность подсистемы ввода-вывода (в байтах за секунду).
- SLAVETHR — средняя пропускная способность подчиненной подсистемы ввода-вывода (в байтах за секунду).
В общем, структура процедуры GATHER_SYSTEM_STATS выглядит так:
DBMS_STATS.GATHER_SYSTEM_STATS ( gathering_mode VARCHAR2 DEFAULT 'NOWORKLOAD', interval INTEGER DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
Ниже приведен пример, показывающий как можно использовать эту процедуру для сбора статистических данных по системе:
SQL> EXECUTE dbms_stats.gather_system_stats('start');
PL/SQL procedure successfully completed.
SQL>
SQL> EXECUTE dbms_stats.gather_system_stats('stop');
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT * FROM sys.aux_stats$;
SNAME PNAME PVAL1 PVAL2
------------- ------------- ---------- ----------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 04-25-2008 10:44
SYSSTATS_INFO DSTOP 04-26-2008 10:17
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 67.014
SYSSTATS_MAIN IOSEEKTIM 10.266
SYSSTATS_MAIN IOTFRSPEED 10052.575
SYSSTATS_MAIN SREADTIM 5.969
SYSSTATS_MAIN MREADTIM 5.711
SYSSTATS_MAIN CPUSPEED 141
SYSSTATS_MAIN MBRC 18
SYSSTATS_MAIN MAXTHR 17442816
SYSSTATS_MAIN SLAVETHR
13 rows selected.
SQL> На заметку! Просматривать статистические данные по системе можно за счет применения такой поставляемой в пакете DBMX_STATS процедуры, как GET_SYSTEM_STATISTICS.
Сбор статистических данных по объектам словаря данных
Для максимизации производительности следует собирать для оптимизатора статистические данные и по таблицам словаря данных. Таблицы словаря данных бывают фиксированными (fixed) и реальными (real). Динамические таблицы производительности нельзя не изменять, ни удалять, а это значит, что они являются фиксированными. К числу реальных таблиц словаря данных относятся схемы, подобные sys и system.
Сбор статистических данных по фиксированным объектам
В Oracle рекомендуют собирать статистические данные по динамическим таблицам производительности (которые представляют собой фиксированные объекты) только по одному разу для каждой рабочей нагрузки базы данных, которая для большинства баз данных OLTP обычно составляет неделю. Осуществлять сбор статистических данных по фиксированным объектам можно несколькими способами.
- С применением процедуры DBMS_STATS_GATHER_DATABASE_STATS и установкой аргумента GATHER_SYS в значение TRUE (по умолчанию он равен FALSE).
- С помощью процедуры GATHER_FIXED_OBJECTS_STATS из пакета DBMS_STATS, как показано ниже:
SQL> SHO USER
USER is "SYS"
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
Совет. Для выполнения анализа любых объектов словаря данных, фиксированных объектов в том числе, необходимо обладать системными привилегиями SYSDBA или ANALYZE ANY DICTIONARY.
В пакете DBMS_STATS доступны процедуры, которые позволяют собирать статистические данные на уровне отдельных таблиц. С их помощью можно собирать статистику только по конкретным фиксированным таблицам.
Сбор статистических данных по реальным таблицам словаря данных
Для сбора статистических данных по реальным таблицам словаря данных можно пользоваться следующими способами.
- Установить для аргумента GATHER_SYS процедуры DBMS_STATS.GATHER_DATABASE_STATS значение TRUE. Также можно воспользоваться опцией GATHER_SCHEMA_STATS('SYS').
- Применить процедуру DBMS_STATS.GATHER_DICTIONARY_STATS, как показано ниже:
SQL> SHO user
USER is "SYS"
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
Процедура GATHER_DICTIONARY_STATS помогает собирать статистические данные по таблицам, которыми владеют пользователи SYS и SYSTEM, а также пользователи, владеющие всеми компонентами базы данных.
На заметку! Пакет DBMS_STATS также служит для удаления, импорта, восстановления и установки предназначенных для оптимизатора статистических данных, которые были собраны ранее.
Частота сбора статистических данных
С теоретической точки зрения, если данные статичны, однократного сбора статистики по ним может быть достаточно. Если в базе данных выполняется лишь небольшое количество DML-операций, статистику можно собирать через относительно длинные интервалы времени, скажем, раз в неделю или раз в месяц.
Однако если объекты в базе данных ежедневно подвергаются изменениям, планировать выполнение заданий по сбору статистики нужно гораздо чаще, скажем, раз в день или даже чаще. Необходимости принимать решение о частоте сбора статистики можно избежать, если позволить базе данных самой решать, когда собирать новые статистические данные. Не следует забывать, что база данных принимает подобное решение на основании того, являются статистические данные “свежими” или “устаревшими”. Поэтому вполне можно расслабиться и разрешить базе данных самой судить, насколько часто следует выполнять сбор статистических данных.
Что будет происходить в случае отсутствия статистических данных
Ранее уже было показано, как база данных Oracle может собирать статистические данные для оптимизатора автоматически, и как эти данные собирать вручную за счет применения пакета DBMS_STATS. Но что произойдет в случае отключения автоматического процесса сбора статистики или не проведении сбора статистических данных через определенные промежутки времени? Даже при автоматическом сборе статистики, во время которого необходимые статистические данные собираются каждую ночь, может возникать ситуация с изменением данных таблицы уже после завершения сбора. В подобных ситуациях Oracle будет использовать для вычисления плана выполнения в оптимизаторе сведения наподобие того, сколько блоков занимают данные таблицы, и другую вспомогательную информацию.
Еще, однако, можно использовать параметр OPTIMIZER_DYNAMIC_SAMPLING и тем самым позволять Oracle вычислять статистику для оптимизатора на лету, когда не существует никаких статистических данных по таблице или когда они существуют, но являются слишком старыми и, следовательно, ненадежными. Конечно, проведение выборки статистических данных динамическим образом будет означать увеличение времени компиляции SQL-запроса. Поэтому при столкновении с объектами без статистических данных Oracle будет разумно проверять, стоит ли увеличение времени компиляции того. Если стоит, Oracle будет производить выборку части блоков данных объекта для получения статистики. Обратите внимание, что затрачивание дополнительного времени при компиляции является не сильно существенным, поскольку будет происходить только один раз на этапе первоначального синтаксического анализа, а не и при всех последующих выполнениях данного SQL-оператора. Для включения динамической выборки для всех не анализировавшихся таблиц нужно установить для параметра инициализации OPTIMIZER_DYNAMIC_SAMPLING значение 2 или выше. Поскольку по умолчанию для этого параметра выбирается значение 2, функция динамической выборки по умолчанию является в базе данных включенной. Следовательно, не спать по ночам, волнуясь об объектах с отсутствующими или устаревшими статистическим данными, не понадобится. В любом случае, если следовать рекомендациям Oracle и применять функцию Automatic Optimizer Statistics Collection (Автоматический сбор статистических данных для оптимизатора), задание GATHER_STATS_JOB будет автоматически собирать необходимые статистические данные в базе данных. Задание GATHER_STATS_JOB создается на этапе создания базы данных и управляется планировщиком Oracle (Oracle Scheduler), который запускает его при открытии окна обслуживания. По умолчанию окно обслуживания открывается каждую ночь с 10 вечера до 6 утра и на весь день на выходных. В случае применения функциональной возможности Automatic Optimizer Statistics Collection статистические данные будут собираться по всем объектам, которым они необходимы. Эта функция включается по умолчанию как при создании новой базы данных Oracle 11g, так и при обновлении до версии 11g базы данных предыдущего выпуска.
Использование OEM для сбора статистических данных для оптимизатора
Как и многие другие задачи по администрированию базы данных в Oracle Database 11g, сбор статистических данных для оптимизатора лучше выполнять с помощью интерфейса OEM Database Control или OEM Grid Control. Ниже описаны необходимые шаги.
- На домашней странице Database Control перейдите на вкладку Administration (Администрирование).
- На странице Administration (Администрирование) щелкните на ссылке Manage Optimizer Statistics (Управление статистическими данными для оптимизатора) в разделе Statistics Management (Управление статистическими данными).
- После попадания на страницу Manage Optimizer Statistics щелкните на ссылке Gather Statistics (Сбор статистических данных) и затем следуйте инструкциям по пяти подлежащим выполнению шагам.
На рис. 1 показана часть процесса сбора статистических данных для оптимизатора с помощью интерфейса OEM Grid Control.
На заметку! В Oracle настоятельно рекомендуют использовать только создаваемое самой Oracle задание GATHER_STATS_JOB, запускаемое планировщиком для сбора статистических данных для оптимизатора только во время запланированного окна обслуживания. Необходимость собирать статистические данные вручную может возникать только в чрезвычайных ситуациях, вроде неспособности базы данных сделать это во время запланированного окна обслуживания или при желании проанализировать созданную новую таблицу немедленно.
