Март 2005

Профессионалу администратору

Джонатан Льюис

Понимание системных статистических данных
(Understanding System Statistics, by Jonathan Lewis)

Источник: http://www.oracle.com/technology/pub/articles/lewis_cbo.html.

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

Какой компонент СУБД Oracle является самым важным? Я за то, что это – оптимизатор. Все, что сервер базы данных делает, выражается на языке SQL, и каждый фрагмент SQL-кода должен быть оттранслирован во что-то, что сможет эффективно работать. Независимо от того, что вы делаете с вашими данными, в это вовлечен оптимизатор.

Оптимизатор по стоимости (CBO, Cost-Based Optimizer) – сложный участок кода в ядре СУБД, поэтому, когда он впервые появился в СУБД Oracle7, не было ничего удивительного в наличии некоторых проблем. Однако не потребовалось много выпусков исправленных версий системы, чтобы она стала вполне жизнеспособной и позволяла строить системы исключительно с оптимизацией по стоимости. Я несколько раз переустанавливал такие исправленные версии системы в 7.1 и, по крайней мере, один раз важную исправленную версию в 7.2.

Тем не менее, в поразительном количестве установок достаточно длительное время избегали использовать CBO – причина заключается в том, что разработчики и администраторы баз данных по-прежнему мыслили в терминах оптимизатора по синтаксису (RBO, Rule-Based Optimizer), и поэтому поступали так, что это усложняло функционирование CBO. По существу они "обманывали" свой оптимизатор, а потом удивлялись, почему он принимает плохие решения.

Я полагаю, причина моего раннего успеха заключается в том, что мне посчастливилось заметить нечто, ставшее теперь хорошо понятным: тот факт, что CBO "нравится" выполнять полные просмотры таблиц, если значение параметра инициализации db_file_multiblock_read_count (максимальное количество блоков, считывающихся во время последовательного просмотра за одну операцию ввода-вывода) велико, и он "предпочитает" использовать индексы, когда это значение невелико. Как теперь мы знаем, подобные свойства имеют и некоторые другие параметры – то есть, RBO предпочитает их большие значения, а CBO – маленькие. И, конечно, было небольшое отличие, заключавшееся в "разнонаправленности" обработки списков таблиц в предложениях FROM, если в SQL-операторах явно не указывался порядок этой обработки.

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

История повторяется

CBO продолжает развиваться. В некоторых выпусках СУБД Oracle вводятся незначительные усовершенствования, в некоторых – значительные, а в некоторых выпусках изменяется парадигма.

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

Итак, есть ли в CBO какие-либо новейшие изменения, которые могли бы так же сильно влиять на наше мышление, как и при переходе от RBO? Ответ: "да"

В сервере Oracle9i Database корпорация Oracle ввела системные статистические данные (это я считаю новой парадигмой) и автоматическое управление размером рабочих областей (это я считаю значительным усовершенствованием). В сервере Oracle Database 10g появились разнообразные консультанты по настройке (tuning advisors), которые позволяют оптимизировать время ваших разработчиков, помогая им быстрее создавать более эффективный SQL- или PL/SQL-код, материализованные представления или выполнять индексирование. Мы также получаем механизм профилирования запросов (query profiles), значительное усовершенствование, которое позволяет хранить статистическую информацию в базе данных (с целью помогать оптимизатору принимать лучшие решения). Эта информация особенно полезна, когда вы имеете дело с неприкосновенным SQL-кодом сторонних производителей.

В обеих версиях сервера базы данных мы получаем механизм динамической статистической выборки (dynamic_sampling), очень полезное средство, введенное в 9i, и особенно выгодное в хранилищах данных и системах поддержки принятия решений. Но если в 9i вы могли не обращать внимание на динамическую выборку, то она может стать источником досадных помех в 10g, если у вас работает OLTP-система, поскольку в 10g динамическая выборка фактически включается по умолчанию, что может привести к ненужным затратам ресурсов.

Самым важным из всех этих изменений является введение системных статистических данных. Фактически, я пошел бы настолько далеко, чтобы сказать, что одним из ключевых шагов в переходе от Oracle8 к Oracle9i является активизация сбора системных статистических данных и отработка их воздействия на вашу систему. Точно так же и в среде 10g, системные статистические данные очень важны для оптимизатора. Следовательно, наилучшее время для ознакомления с системными статистическими данными – это время, когда вы решаете переходить от 8i к более новым версиям.

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

Системные статистические данные

До СУБД Oracle9i вычисления в CBO базировались на количестве запросов ввода-вывода, которое понадобилось бы для выполнения операторов запроса; для этого использовались различные константы для оценки полных просмотров таблиц, а также несколько правил для оценки таких вещей, как кеширование небольших индексов. (Для ознакомления с этой темой см. на моем сайте DBAzine.com статью "Why isn't Oracle using my index?"(Почему сервер Oracle не использует мой индекс?).)

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

В 9i корпорация Oracle ввела механизм cpu_costing (оценка стоимости затрат ресурсов центрального процессора), который позволяет включать в общую оценку стоимости затрат оценку стоимости затрат ресурсов центрального процессора на выполнение операций. Это средство в 9i включается только тогда, когда вы активизируете системные статистические данные; в 10g оно включается по умолчанию.

Итак, что же делает механизм cpu_costing и что такое системные статистические данные? Давайте начнем с системных статистических данных, используя для демонстрации несколько вызовов пакета dbms_stats. (В этом примере используется сервер версии 9.2.0.4 и чтобы все работало, вашей учетной записи нужно предоставить роль gather_system_statistics (сбор системных статистических данных).)

execute dbms_stats.gather_system_stats('Start');

-- некоторая временная задержка при работе сервера с типичной рабочей нагрузкой

execute dbms_stats.gather_system_stats('Stop');

Чтобы увидеть, что же вы сделали, вы можете выполнить запрос к таблице aux_stats$ (находится в схеме SYS). После сбора системных статистических данных эта таблица будет содержать несколько важных чисел, используемых новыми алгоритмами оптимизатора для оценки стоимости затрат. (Вы должны очистить разделяемый пул, чтобы сделать недействительными хотя бы существующие планы выполнения.) Следующий запрос покажет вам текущие параметры настройки: 

select	pname, pval1 
from 	sys.aux_stats$ 
where 	sname = 'SYSSTATS_MAIN';

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

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED                              564
MAXTHR                           13899776
MBRC                                    6
MREADTIM                           10.496
SLAVETHR                           182272
SREADTIM                            1.468

В 10g также появляется несколько дополнительных строк со значениями, которые устанавливаются при запуске экземпляра сервера базы данных: 

	
CPUSPEEDNW                      904.86697
IOSEEKTIM                              10
IOTFRSPEED                           4096

Я привел SQL-код для удобства; фактически принятый способ просмотра этой информации – процедура get_system_stats пакета dbms_stats. Существует также процедура set_system_stats, с помощью которой вы можете "настроить" значения, не собирая их должным образом.

Полные просмотры таблиц

Существует два существенных изменения, которые применяются оптимизатором для вычисления стоимости затрат при наличии системных статистических данных. Сначала обратите внимание, таблица sys.aux_stats$ содержит следующие данные:

  • sreadtim: среднее время выполнения операции одноблочного чтения (single-block read time), в миллисекундах;
  • mreadtim: среднее время выполнения операции многоблочного чтения (multi-block read time), в миллисекундах;
  • MBRC: среднее количество блоков, читаемых одной операцией многоблочного чтения (multi-block read count).

Используя эту информацию, сервер Oracle может оценить, сколько времени потребуется на выполнение полного просмотра таблицы (или быстрого полного просмотра индекса). Арифметика проста: это всего-навсего количество операций многоблочного чтения, требуемое для просмотра, которое умножается на среднее время выполнения операции многоблочного чтения. Игнорируя незначительные изменения, связанные с автоматическим управлением пространством сегментов, мы просто возьмем маркер максимального уровня заполнения (HWM, high-water mark) и получим следующее:

Время до завершения = mreadtim * HWM / MBRC.

Сервер Oracle не использует "время до завершения" в качестве стоимости запроса, а переформулирует его в терминах эквивалентного количества операций одноблочного чтения. Чтобы сделать это, просто разделим "время до завершения" на среднее время выполнения операции одноблочного чтения:

Стоимость = время до завершения / sreadtim

Или, объединив эти две формулы и переставив термины:

Стоимость полного просмотра таблицы = (HWM / MBRC) * (mreadtim / sreadtim)

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

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

Исторически стоимость полного просмотра таблицы вычислялась просто:

Стоимость полного просмотра таблицы = HWM / (модифицированное значение db_file_multiblock_read_count).

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

Этот недостаток в значительной степени объясняет, почему корпорация Oracle в 8.1.6 ввела параметр optimizer_index_cost_adj (настройка стоимости индексного доступа), который позволяет вам вводить коэффициент с целью, похожей на использование mreadtim, которое содержится в системных статистических данных. (Вы, возможно, заметили сходство между элементом mreadtim/sreadtim в новой формуле стоимости, и обычным способом оценки осмысленного значения параметра optimizer_index_cost_adj.) Однако использование параметра optimizer_index_cost_adj может иметь некоторые побочные эффекты, которые могут стать источниками проблем; механизмы, использующие собранные системные статистические данные, намного более устойчивы.

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

Стоимость затрат ресурсов центрального процессора

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

Обратите внимание, в число статистических данных входит условная скорость центрального процессора (CPUSPEED), выражаемая в мегагерцах. Не волнуйтесь, если она не имеет ничего похожего на реальную скорость центрального процессора в вашей системе – это число, весьма вероятно, было получено в результате внутреннего измерения базовой операции, которую сервер Oracle использует для вычисления относительной стоимости затрат ресурсов центрального процессора для других операций. На машине, работающей со скоростью 2,8 ГГц, я обычно вижу условную скорость центрального процессора, равную нескольким сотням мегагерц. (Имейте в виду, то что вы видите – это номинальная скорость одного центрального процессора, а не сумма скоростей всех процессоров в многопроцессорной системе.)

Итак, чем знание (условной) скорости вашего центрального процессора помогает оптимизатору? Рассмотрим пример с двумя вариантами выбора:

  1. Для поиска 20 строк, разбросанных по таблице, использовать индекс одного столбца данных.
  2. Использовать полный просмотр таблицы, проверяя каждую строку на попадание в указанный диапазон.

Сервер Oracle может решить, основываясь исключительно на количестве и скорости выполнения одноблочных и многоблочных чтений, что полный просмотр таблицы будет более быстрым. Но сколько ресурсов центрального процессора потребуется для этого, если во время полного просмотра нужно проверить в 10 000 строках следующее:

date_col between to_date('01-Jan-2004') and to_date('02-Jan-2004);

На выполнение операций центрального процессора также требуется время, и если количество и характер проверок, которые должны быть выполнены во время полного просмотра, требуют большого количества ресурсов центрального процессора, сервер Oracle может переключить выполнение запроса на просмотр диапазона по индексу. Oracle разлагает стоимость выполнения запроса на множители, используя для этого формулу, опубликованную в Oracle9i Database Performance Tuning Guide and Reference (A96533 p. 9-22): 

Стоимость = (
		#SRds * sreadtim +
		#MRds * mreadtim +
		#CPUCycles / cpuspeed 
	  ) / sreadtim

Значение #CPUCycles (количество циклов центрального процессора) в этой формуле берется из столбца cpu_cost (стоимость ресурсов центрального процессора) таблицы plan_table (таблица плана выполнения), используемой в новейших версиях средств построения планов выполнения (explain plan). (Это еще одна маленькая, но важная, деталь изучения усовершенствований оптимизатора: всегда проверяйте, как развиваются средства построения планов выполнения.)

На самом деле, если вы в формуле вычисления стоимости используете этот столбец, формулу нужно подправить – cpuspeed измеряется в мегагерцах, а другие временные значения даются в миллисекундах, поэтому процессорный компонент формулы нужно разделить на 1 000:

#CPUCycles / (cpuspeed * 1000)

Порядок предикатов

Кроме выбора вариантов на высоком уровне, знание требуемых ресурсов центрального процессора и сложности предикатов может позволить серверу Oracle делать то, что вы никогда не смогли бы даже рассматривать во время ручной настройки. Лучше всего это показать на работающем примере (немного надуманном). Для обеспечения повторяемости тестирования следующий код выполнялся на сервере версии 9.2.0.4, использовались локально-управляемое табличное пространство с одинаковым размером экстентов, равным 1МБ, и ручное распределение пространства сегментов. 

create table t1 as
select
		trunc(sysdate-1) + rownum/1440	d1,
		rownum				n1,
		rpad('x',100)			padding
from
		all_objects
where
		rownum <= 3000
;

alter table t1
	add constraint t1_pk primary key (d1,n1)
	using index (create index t1_pk on t1(d1,n1))
;

execute dbms_stats.gather_table_stats(user,'t1',cascade=>true)

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

select	/*+ index(t1) */
		padding
from	t1
where	n1 = 2800
and	d1 >= trunc(sysdate)
;

select	/*+ index(t1) */
		padding
from	t1
where	d1 >= trunc(sysdate)
and	n1 = 2800
;

Обратите внимание, единственное различие в двух запросах заключается в порядке предикатов. Если вы выполняете запросы в режиме автотрассировки (включается командой set autotrace on), вы обнаружите, что для обоих запросов создается один и тот же план выполнения с одинаковым количеством операций согласованного чтения (consistent gets), потребовавшихся для выполнения каждого запроса: 

Execution Plan
----------------------------------------------------------
 0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=112)
 1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=8 Card=1 Bytes=112)
 2    1     INDEX (RANGE SCAN) OF 'T1_PK' (NON-UNIQUE) (Cost=7 Card=1562)

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
9  consistent gets
0  physical reads

Итак, когда я выполняю каждый запрос 10 000 раз в PL/SQL-цикле, то почему на выполнение первого запроса потребовалось 4,34 секунд времени центрального процессора (работает со скоростью 2,28 ГГц), а второго – 13,42 секунд (в обоих случаях системные статистические данные не были активизированы)? И почему, когда я активизирую эти данные, оба запроса выполняются быстрее?

Ответ не виден в режиме автотрассировки, но для получения полного плана выполнения можно воспользоваться пакетом dbms_xplan, поставляемом с СУБД Oracle, и для более быстрого запроса вы увидите следующее (системные статистические данные по-прежнему не активизированы): 

   Id  Operation                    Name           Rows  Bytes    Cost  
-----  ---------------------------- ------------ ------ ------- -------
    0  SELECT STATEMENT                               1     110       8
    1  TABLE ACCESS BY INDEX ROWID  T1                1     110       8
 *  2  INDEX RANGE SCAN             T1_PK          1562               7

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."D1">=TRUNC(SYSDATE@!) AND "T1"."N1"=2800)
       filter("T1"."N1"=2800 AND "T1"."D1">=TRUNC(SYSDATE@!))

Замечание: механизм cpu_costing выключен

Если вы поменяете местами предикаты для проверки более медленного запроса (по-прежнему без системных статистических данных), в разделе Predicate Information (информация о предикатах) вы обнаружите небольшое изменение: 

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."D1">=TRUNC(SYSDATE@!) AND "T1"."N1"=2800)
       filter("T1"."D1">=TRUNC(SYSDATE@!) AND "T1"."N1"=2800)

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

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

Различие во времени выполнения объясняется тем, что я спроектировал набор данных и запрос так, чтобы заставить сервер Oracle проверять оба столбца (d1 и n1) для каждого элемента индекса, выбранного при выполнении операции index range scan (просмотр диапазона по индексу), и для заданных значений предикатов серверу Oracle потребовалось проверить 1 560 строк. Каждая строка из этих 1 560 строк пройдет проверку дат, но только одна пройдет проверку чисел. Так, переключая порядок предикатов вручную, я фактически выбирал между выполнением:

1 560 успешных проверок дат, за которыми следуют 1 560 проверок чисел

и

1 560 проверок чисел, из которых только одна будет успешной, и одна последующая проверка даты.

Различие во времени выполнения полностью объясняется отсутствием 1 559 проверок дат (умноженных на 10 000 итераций цикла).

Как сервер Oracle находит оптимальный порядок вычисления предикатов, когда включен механизм cpu_costing? Во-первых, в рамках своего "традиционного" механизма оптимизации оптимизатор всегда определяет селективность (доля строк, которые будут возвращены) для каждого из предикатов. Во вторых, сервер Oracle поддерживает внутренний список ссылок на основные операции с их стоимостями в виде: "операции X требуется N единиц времени центрального процессора", и этот список "калибруется" по элементу cpuspeed системных статистических данных.

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

Итак, когда включен механизм вычисления затрат ресурсов центрального процессора, начинают действовать новые ветви кода, которые могут повысить производительность выполнения некоторых ваших запросов, не оказывая никакого влияния на видимый план выполнения. И вы будете понимать, что же происходит, только проверив новый вывод средств построения планов выполнения. (Эту перестройку порядка предикатов можно остановить, воспользовавшись подсказкой оптимизатору /*+ ordered_predicates */, но ее не рекомендуется использовать в 10g.)

Заключение

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

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

Если вы все еще работаете в среде 8i и планируете перейти на сервер Oracle9i, вы должны в плане перехода запланировать активизацию системных статистических данных. Если вы работаете в среде 9i, не используя системные статистические данные, вы должны активизировать их как можно скорее. Если вы переходите к версии 10g, а до этого в 9i системные статистические данные не использовались, то в своих планах проведения испытаний учтите необходимость проведения исследования этих данных. Если вы не сделаете этого, вы можете потратить много времени, пытаясь понять, почему в тех местах, в которых вы вставили "заплатку" для решения возникшей проблемы, случаются странные вещи. Этой проблемы не существовало бы, если вы с самого начала все делали бы правильно.

Джонатан Льюис (Jonathan Lewis) – (http://www.jlcomp.demon.co.uk/) – независимый консультант с более чем 18-летним опытом проектирования физических баз данных и использования СУБД Oracle. Он – автор книги "Practical Oracle 8i: Designing Efficient Databases", опубликованной издательством Addison-Wesley.

E-mail this page