Стоимостной Оптимизатор: в поисках интеллекта



Скачать 336,38 Kb.
Дата19.04.2016
Размер336,38 Kb.

The Search for Intelligent Life in the Cost-Based Optimizer (v1.0, July 2001)

Стоимостной Оптимизатор: в поисках интеллекта

Tim Gorman, Evergreen Database Technologies, Inc.

rbo против cbo

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

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

В результате большинство разработчиков предпочитало по возможности по-прежнему использовать оптимизатор, основанный на анализе заданных правил3 (RBO4), который ценили за его адекватную предсказуемость и стабильность. Похоже, что единственным преимуществом CBO был тот факт, что он поддерживал все новые, модные возможности, которые Oracle включила в версию 7.1 и выше. Казалось, что риск, связанный с применением CBO, был ценой использования этих новых возможностей. Если не принимать этот момент во внимание, то "общественное мнение" был таково, что всем кроме мазохистов лучше придерживаться RBO.

Хотя постепенно ситуация с CBO стала гораздо лучше в Oracle8 версии 8.0 и на порядок лучше в Oracle8i, это широко распространенное мнение существует до сих пор. Действительно, ведь одна из новых возможностей Oracle8i stored outlines была заявлена самой корпорацией Oracle как устойчивость планов оптимизатора (optimizer plan stability). К сожалению, смысл фразы, который имела в виду компания Oracle, отличается от того, как ее понимает весь остальной мир. В данном случае имелся в виду механизм расширения возможностей CBO при тестировании и миграции данных. Но все (по крайней мере, каждый, с кем я разговаривал), похоже, интерпретируют это новое свойство как огромную “заплатку” для CBO и молчаливое признание Oracle того факта, что на CBO просто нельзя полагаться, он не работает. Забавно, но подсказки (хинты) CBO в операторе SQL воспринимаются похожим образом. В действительности, ни один из этих случаев не является молчаливым признанием какого-либо недостатка, поскольку CBO на самом деле работает.

Истина заключается в том, что хотя эти негативные ощущения были верны для Oracle7, в Oracle8 и особенно в Oracle8i CBO работает отлично. Это довольно смелое утверждение, но, пожалуйста, позвольте дать мне шанс убедить вас в том, что я не являюсь ни представителем Oracle, ни (безусловно) безумным. Любое ненормальное поведение, о котором Вы слышали или свидетелем которого Вы могли быть, скорее в большей степени связано с неправильной эксплуатацией (т.е. недостаточными входными данными или плохой настройкой), чем просто c применением CBO.



Ближе к делу…

Чтобы вам не пришлось читать всю статью целиком, я собираюсь перейти прямо к делу и дать основные рекомендации сразу в начале статьи…

Начиная с Oracle8 версии 8.0, были введены два новых недокументированных параметра инициализации. Со временем они были описаны в руководстве Oracle8i Server Reference релиза Oracle8i:


  • OPTIMIZER_INDEX_CACHING

Этот параметр представляет собой процентное отношение в диапазоне от 0 до 99. Значение по умолчанию 0 указывает CBO, что в буферном кэше SGA ожидается найти 0% блоков, полученных через индексный доступ. Это значение (также известное как нулевой коэффицент попадания в Буферный Кэш) предполагает, что любое обращение к индексу потребует операции физического чтения (physical read) с подсистемы ввода/вывода для каждой операции логического чтения (logical read) из Буферного Кэша. Этот параметр относится только к вычислению оптимизатором стоимости читаемых индексных блоков, но не блоков таблицы, связанной с этим индексом.

  • OPTIMIZER_INDEX_COST_ADJ

Этот параметр также задается процентной величиной, но в диапазоне между 1 и 10000, указывая степень сопоставления между относительной стоимостью физических запросов ввода/вывода при индексном доступе и операций полного табличного сканирования (full table-scans5). Значение по умолчание 100 указывает стоимостному оптимизатору, что индексный доступ также (100%) дорог (т.е. “стоит” столько же) как и операции полного табличного сканирования.

Оказывается, значения по умолчанию для этих параметров являются слишком неподходящими и нереалистичными. Я докажу это утверждение позже в этой статье, но пока достаточно будет сказать, что параметр OPTIMIZER_INDEX_CACHING должен быть установлен в значение 90. OPTIMIZER_INDEX_COST_ADJ должен быть выставлен в значение, которое обычно лежит в диапазоне между 10 и 50 для большинства систем оперативной обработки транзакций (OLTP6); для хранилищ данных или других систем поддержки принятия решений (DSS7) было бы разумно просто установить этот параметр равным 50. Хороший способ вычисления этого параметра с некоторой степенью точности приведен в конце статьи…

Вот и все!

Проверьте этот совет. Возьмите прежде “безнадежный” SQL оператор, удалите все хинты, которыми он был старательно залатан, установите эти два параметра, используя ALTER SESSION SET, и выполните команду EXPLAIN PLAN.

окончание истории…

Все еще здесь?

Ненавидете, когда кто-то делает дикое утверждение абсолютно без доказательств? Похоже на большинство подобных рекомендаций, не так ли? “Советы и методики” – издержки профессионального образования, поэтому важно поразмыслить над более убедительными аргументами. Хорошо, приготовьтесь размышлять…

Прежде всего, я хотел бы рассказать историю с самого начала, начиная с RBO и его принципов работы, затем перейти к обсуждению CBO и объяснить (и сопоставить) его принципы работы. Я хочу показать, какой сильный эффект производят упомянутые выше параметры при работе с CBO. Добавленные в Oracle8 версии 8.0 в качестве недокументированных, эти параметры оказывают влияние на вычисление стоимости, производимой CBO, примерно так же, как общий доход влияет на чистую прибыль за вычетом налогов.



Как работает Оптимизатор По Правилам

Однако давайте начнем с начала…

Оптимизатор по правилам (RBO) имеет только небольшой объем информации, используемый при выборе плана исполнения для оператора SQL:


  • Сам текст оператора SQL

  • Элементарная информация об объектах, расположенных в части FROM оператора SQL, таких как таблицы, кластеры и представления, и типы данных столбцов, указанных в других частях оператора

  • Элементарная информация об индексах, ассоциированных с таблицами, на которые ссылается оператор SQL

  • Информация словаря данных доступна только для локальной базы данных. Если Вы ссылаетесь на удаленную базу данных, ее словарь не доступен для RBO…

Для того чтобы определить план исполнения, RBO сначала просматривает часть оператора WHERE снизу вверх, отделяя каждый предикат от другого. Он накладывает ранг на каждый предикат, используя 15 методов доступа, упорядоченных по весьма сомнительному критерию:

  1. Извлечение одной строки с помощью ROWID

  2. Извлечение одной строки через кластерное соединение (cluster join)

  3. Извлечение одной строки через хэш-кластер с помощью уникального кластерного ключа

  4. Извлечение одной строки с помощью уникального индекса

  5. Доступ через кластерное соединение

  6. Доступ по ключу хэш-кластера

  7. Доступ по ключу индексного кластера

  8. Доступ по составному ключу

  9. Доступ по неуникальному одностолбцовому индексу

  10. Доступ через ограниченный диапазонный поиск по индексным столбцам

  11. Доступ через неограниченный диапазонный поиск по индексным столбцам

  12. Доступ через ‘sort-merge’ соединение (sort-merge join)

  13. Поиск MAX или MIN значения по индексному столбцу

  14. Операция ORDER BY по индексным столбцам

  15. Полное табличное сканирование

Любой человек, который потратил какое-то время на настройку SQL операторов, знает, что операции полного табличного сканирования не всегда являются вредными, как можно было бы предположить по последнему 15-му рангу. Существует несколько ситуаций, когда полное табличное сканирование намного превосходит индексный доступ. Простое наличие индекса не всегда означает, что это лучший метод доступа, хотя именно это закодировано в логику RBO. Таким образом, опыт подсказывает нам, что система линейного ранжирования – это ограничение, вызывающее проблемы.

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

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

Для иллюстрации того, как может быть абсурден RBO, давайте рассмотрим пример следующего SQL оператора:



SELECT COUNT(*)

FROM A, B, C

WHERE A.STATUS = B.STATUS

AND A.B_ID = B.ID

AND B.STATUS = ‘OPEN’

AND B.ID = C.B_ID

AND C.STATUS = ‘OPEN’;

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



  • В таблице B по полю ID определен уникальный (UNIQUE) индекс (первичный ключ)

  • По полю STATUS таблиц A, B определены неуникальные (NONUNIQUE) индексы. На таблице C такого индекса нет.

  • Существует неуникальный (NONUNIQUE) индекс по полю B_ID таблицы C, но нет (как кто-то мог ожидать) индекса на аналогичном поле таблицы А. Это специальное упущение, чтобы проиллюстрировать суть

  • В таблицу B (родительская сущность) была загружено 100 строк, в дочерние таблицы (A и C) – по 1000 строк (10 строк на каждую строку в таблице B)

  • Все поля STATUS были заполнены одинаковым значением ‘OPEN’

Гляда на SQL оператор с точки зрения RBO, мы видим, что часть WHERE содержит три предиката соединения (первая, вторая и четвертая строки) и два фильтрующих предиката (третья и пятая строки). Чтобы определить, как с точки зрения RBO сгенерировать план исполнения запроса, мы будем присваивать ранг каждому предикату, начиная с последней строки и продвигаясь вверх по направлению к первой.

Таким образом, используя список из 15 правил, представленных выше, RBO будет присваивать следующие ранги для каждого предиката:



1. WHERE A.STATUS = B.STATUS (FTS по таблице А или B; ранг = 15)

2. AND A.B_ID = B.ID (FTS по таблице А или B; ранг = 15)

3. AND B.STATUS = ‘OPEN’ (NONUNIQUE индекс по полю B.STATUS; ранг = 9)

4. AND B.ID = C.B_ID (FTS по таблице B или C; ранг = 15)

5. AND C.STATUS = ‘OPEN’; (индекс по полю C.STATUS не определен, значит FTS; ранг = 15)

Одному из фильтрующих предикатов (строка #5) присвоен ранг 15 – ранг операции полного табличного сканирования, поскольку по столбцу STATUS таблицы C просто нет индекса.

Трем предикатам соединения (строки #1, #2, #4) также пришлось назначить ранг FTS, несмотря на тот факт, что на столбцах этих предикатов определены индексы. Причиной печального 15-го ранга является тот факт, что ни одна из сторон равенства не является известным значением. Скорее наоборот, обе стороны являются неизвестными величинами. Поэтому, единственно возможный доступ к источникам строк8, используя данные предикаты - это полное сканирование таблицы (FTS).

Остается последний фильтрующий предикат (строка #3) с рангом 9, поскольку на столбце определен NONUNIQUE индекс. В результате, выполнение запроса начнется с диапазонного сканирования (RANGE scan) индекса по полю STATUS таблицы B.

…пока неплохо. Давайте продолжим…

Приняв начальное решение сделать B ведущей таблицой в запросе, RBO теперь знает, что он должен соединить B либо с таблицей A, либо с таблицей C. Чтобы определить, какая таблица должна быть следующей, оптимизатор снова сканирует предикаты (от последнего к первому) в части предложения WHERE.



1. WHERE A.STATUS = B.STATUS (NONUNIQUE индекс по полю A.STATUS; ранг = 9)

2. AND A.B_ID = B.ID (FTS, нет индекса; ранг = 15)

4. AND B.ID = C.B_ID (NONUNIQUE индекс C.B_ID; ранг = 9)

5. AND C.STATUS = ‘OPEN’; (нет индекса, значит FTS; ранг = 15)

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

Похоже, что теперь мы имеем неопределенность с двумя вариантами, поскольку каждый из предикатов строк #1 и #4 получает ранг 9 из-за наличия NONUNIQUE индексов. Как разрешить это противоречие?

Для принятия решений RBO имеет только элементарную (начальную) информацию. Оптимизатор знает, что каждый столбец имеет NONUNIQUE индекс, но не более. Чтобы принять решение, RBO имеет в распоряжении только минимальную информацию, хранимую в словаре данных, или те предположения, которые он может строить по внешнему виду самого SQL оператора.

В случае, когда нам необходимо сделать выбор между двумя разными источниками строк, RBO сканирует список источников строк в части FROM оператора SQL, перемещаясь справа-налево, от конца списка к его началу. В данном случае мы выбираем между таблицой A и таблицой C. При чтении части предложения FROM справа налево, таблица C встречается нам первой, поэтому она становится исходной точкой на данном шаге - будет использован NONUNIQUE индекс по полю C.B_ID. Кроме того, существует фильтрующий предикат (строка #5), поэтому во время извлечения строк из таблицы C, они также будут фильтроваться (налагается ограничение) по значению поля STATUS. Это не влияет на то, как RBO присваивает ранг, но предикат в любом случае будет использован таким образом…

Теперь мы рассмотрим оставшиеся предикаты в части WHERE и в этот раз попробуем решить, как соединить источник строк A с одной из таблиц B или C:



1. WHERE A.STATUS = B.STATUS (NONUNIQUE индекс по полю A.STATUS; ранг = 9)

2. AND A.B_ID = B.ID (FTS, нет индекса; ранг = 15)

В обеих строках присутсвует соединение таблиц B и А. Из них, только в строке #1 возможно индексное соединение (ранг = 9 для NONUNIQUE индекса). Это означает, что мы соединяем таблицу B с таблицей A, используя индекс по полю STATUS и, фильтруя строки по полю A.B_ID с учетом значений из B.ID.

Очевидно, любой разработчик сделает комментарий: по полю B_ID таблицы A ДОЛЖЕН быть определен индекс, поддерживающий внешний ключ. Но если бы он был там, что бы произошло?

Мы бы получили другую неопределенность, но в этот раз между предикатами на одном и том же источнике строк. Таким образом, мы бы не смогли разрешить неопределенность, используя порядок источников строк в части предложения FROM. Так как обоим предикатам был бы назначен одинаковый ранг из-за наличия NONUNIQUE индексов (т.е. ранг = 9), RBO пришлось бы выбрать индекс с наибольшим значением OBJECT_ID из представления DBA_OBJECTS. По сути, этот метод разрешения спорной ситуации выбирает просто более новые индексы. Не совсем иррационально, но, разумеется, существуют лучшие критерии выбора индекса.



ПРИМЕЧАНИЕ: Если Вы задумаетесь, то какой еще возможный критерий имеет RBO, чтобы сделать выбор?

ПРИМЕЧАНИЕ2: Еще более интересно поразмышлять над тем, как часто возникает подобная неопределенность? Как-никак, RBO может делать выбор только из 10 имеющихся элементарных методов доступа!

Подводя черту под сказанным, наш запрос выполняет диапазонное сканирование индекса по полю B.STATUS, выбирая строки таблицы B со значением “OPEN”. Поскольку поле в каждой строке имеет значение “OPEN”, это означает, что мы будем сканировать полную таблицу, используя данный индекс.

Затем будет происходить соединение с таблицей C, используя диапазонное сканирование индекса по полю B_ID с последующей фильтрацией строк со значением “OPEN” поля STATUS. Последней операцией запрос будет соединять таблицу B с таблицей A, используя индекс по полю STATUS и, фильтруя строки по полю A.B_ID по значениям поля B.ID. Вспомните, что все данные были загружены со значением STATUS = “OPEN”, таким образом, мы получили план, который работает не очень хорошо. Задумайтесь о том, что сначала извлекаются все строки, поскольку у всех STATUS = “OPEN”, и лишь затем отбрасываются по условию неравенства поля A.B_ID полю B.ID. Ой!

Как доказательство, данный SQL оператор отрабатывает за 21.98 секунд на базе данных версии 8.1.7, работающей под управлением Solaris 2.8 на 2х-процессорной SPARC машине. Согласно статистике утилиты AUTOTRACE в SQL*Plus запрос затрачивает 1692075 операций логического чтения (и 0 операций физического чтения) для того, чтобы подсчитать 10000 строк.

Ниже приведено несколько наблюдений:


  • RBO рассматривает только небольшое число возможных методов доступа. Если бы Oracle выбрал вариант дальнейшего развития RBO, как бы ранжировались новые методы доступа по мере их появления? Рассматривались бы масочные (bitmap) индексы в качестве желательного выбора для всех случаев? Как бы ранжировались хэш-соединения (hash joins), особенно, если учесть, что они часто выполняются с использованием полного табличного сканирования? Если Вы задумаетесь над этим, то идея одноразмерной системы ранжирования для методов доступа становится полностью абсурдной…

  • Наличие индекса – это автоматически хорошо для RBO, хотя мы знаем, что это не так…

  • RBO использует нецелесообразные приемы для разрешения противоречий. Почему порядок таблиц в части FROM оператора SQL должен быть связан с этим? Еще более неестественно, почему “возраст” индекса должен иметь какую-либо связь с чем-либо еще? Сколько администраторов баз данных периодически перестраивают индексы? Много людей выражало недовольство относительно нестабильности стоимостного оптимизатора; сколько раз RBO изменил планы исполнения после задуманной с благими намерениями перестройки индексов?

  • Заметьте, что этот запрос имел 100% коэффициент попадания в Буферный Кэш (BCHR9), то есть для операций логического чтения не было ни одной операции физического чтения. Это означает, что BCHR практически не имеет смысла в качестве критерия для измерения оптимизации производительности. Уменьшение объема выполняемой работы и адекватное снижение общего числа операций логического ввода/вывода – лучшая цель, чем скрытие слишком большого объема работы за счет удержания данных в памяти.

Перефразируя известную поговорку: “так дела не делаются”. RBO представлял собой временную меру, которой было суждено становиться все более и более абсурдной, чем дольше он продолжал развиваться. Oracle перестал развивать RBO по одной хорошей причине: им пришлось это сделать. Оптимизатор, который использует больше информации об атрибутах и организации данных – единственно возможное долгосрочное направление развития.

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



Как работает стоимостной оптимизатор

CBO – это математический процессор. Он использует формулы для вычисления стоимости оператора SQL. Стоимость, по сути, приводится к числу физических операций ввода/вывода, то есть к числу логических операций ввода/вывода, которые привели к запросам подстистемы ввода/вывода вместо полной обработки оператора SQL в буферном кэше в Oracle SGA.

Просто ради интереса, тот же самый запрос из примера выше, запущенный на той же базе данных версии 8.1.7 с использованием CBO (вместо RBO), отработал за 0.58 секунды (вместо 21.98 секунд) и произвел только 10 операций логического чтения (вместо 1.6 миллиона). Такое отличие полностью определяется тем, как работает CBO. CBO анализирует все возможные методы доступа, используя ту часть информации из словаря данных, которая доступна RBO и дополнительную информацию о данных (т.е. число строк, размер таблиц и индексов, распределение данных в столбцах и так далее). Затем он вычисляет число логических операций ввода/вывода для каждого возможного метода доступа и выбирает один с наименьшей стоимостью.

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



Играем в шахматы

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

В отличие от RBO, CBO определяет стоимость всех возможных планов исполнения. Как и RBO, он работает пошагово, начиная с анализа всех возможных вариантов доступа к таблице, затем рассчитывает последующие соединения или доступ к таблицам. Каждый шаг плана исполнения рассчитывается рекурсивно в данной манере. CBO пробует все возможные варианты (permutations10) до ограничения, налагаемого параметром инициализации OPTIMIZER_MAX_PERMUTATIONS (по умолчанию 80000). Это достаточно большое число для анализа, и CBO часто рассматривает сотни или тысячи вариантов менее чем за секунду.

ЗАМЕЧАНИЕ: Если Вы сомневаетесь, то существует trace event “CBO trace dump” (event 10053), описанный на моем web-сайте (http://www.EvDBT.com/library.htm) внизу web-страницы. Используя этот event, Вы можете получить информацию обо всех вариантах, принятых во внимание оптимизатором, в трассировочном файле “.trc” в каталоге USER_DUMP_DEST сервера базы данных.

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



Что может оказаться не так?

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

По крайней мере, два аспекта могут таить подвох для математического процессора:


  • он мог получить неверные данные на входе (старая проблема: “мусор на входе – мусор на выходе”)

  • одна или несколько формул могут не учитывать важных факторов или же быть ошибочными

На протяжении жизненного цикла Oracle7 проблема состояла и в том и в другом. В Oracle7 реализация команды ANALYZE была полна программных ошибок, которые подавали плохую статистику на вход CBO. В Oracle8 многие ошибки в команде ANALYZE были исправлены, что решило проблему с неверными входными данными. В Oracle8i пакет DBMS_STATS предоставляет еще более точную статистику, что было подтверждено несколькими “ошибками”, зарегистрированными в системе поддержки MetaLink (http://metalink.oracle.com/).

Применение DBMS_STATS в сочетании с новой в Oracle8i возможностью MONITORING позволяет просто и надежно собирать корректную, пригодную для использования CBO статистику. Таким образом, с этой проблемой покончено.

Остались тонкие исправления в формулах, которые CBO использует для вычисления стоимости. Давайте рассмотрим их…

Что означает “стоимость”?

Стоимость (cost), вычисляемая CBO, состоит главным образом из оценки физических операций ввода/вывода. Фактическая формула описана в документации как

IO + CPU/1000 + NetIO*1.5

В данной формуле составляющая “IO” обозначает физические операции ввода/вывода, “CPU” представляет логические операции ввода/вывода, а “Net I/O” – логические операции ввода/вывода в/из удаленной базы данных с через связь баз данных (database link).

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

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

Чтобы понять, насколько сложным может быть этот процесс, давайте сначала сделаем краткий обзор операций ввода/вывода в Oracle…

Обзор правил ввода/вывода в Oracle

Операции ввода/вывода в Oracle осуществляют доступ не к отдельным строкам, а к блокам базы данных, которые содержат строки. Кроме того, Oracle не пытается сразу искать блоки данных на диске, вместо этого он ожидает найти их кэшированные копии в области разделяемой памяти, точнее в Буферном Кэше, расположенном в SGA. Операции чтения буферов в Буферном Кэше известны как операции логического чтения. Говоря точнее, существует два основных типа операций логического чтения: consistent gets и db block gets (другое название current gets). Consistent gets – это операции извлечения конкретной (point-in-time11) версии блока данных, обычно запрашиваемой операторами SELECT. С другой стороны, db block gets или current gets – это операции извлечения наиболее “свежей” (most up-to-date), текущей на данный момент копии блока данных, обычно запрашиваемой с целью модификации операторами INSERT, UPDATE, DELETE. Различие между этими двумя типами логических операций чтения не столь существенно для дальнейшего обсуждения. Оно было упомянуто только к месту, однако если взглянуть на статистику в представлениях V$SYSSTAT или V$SESSTAT, Вы не найдете там статистики “logical reads”, вместо этого Вы обнаружите “consistent gets” и “db block gets”. Просто сложите их вместе.

Очевидно, что если реальная база данных находится на жестком диске, а не в разделяемой памяти, то буферы в Буферном Кэше пришлось каким-то образом заполнить! Обычно это происходит, когда серверный процесс проверяет наличие конкретного блока базы данных в Буферном Кэше (операция логического чтения). Если искомый блок находится в кэше, тогда все хорошо - процесс продолжает свою работу. Но если блок не найден ни в одном буфере Буферного Кэша, то серверный процесс, выполнивший операцию логического чтения, сделает запрос к подсистеме ввода/вывода (операция физического чтения), чтобы считать блок в буфер. Затем серверный процесс продолжает свою обычную работу.

Что произойдет, если серверный процесс выполнил логическое чтение блока, который отсутствует в Буферном Кэше, определил, что его там нет, и производит физическое чтение, а другой серверный процесс в то же время пытается выполнить операцию логического чтения до того, как первый завершит свою операцию физического чтения? Выполнят ли оба процесса операцию физического чтения? Ответ отрицательный. После того, как первый процесс определит, что требуемый блок данных отсутствует в Буферном Кэше, он резервирует буфер под этот блок до фактического выполнения операции физического чтения. Затем процесс блокирует этот буфер и запрашивает операцию физического ввода/вывода. Если второй процесс пытается выполнить операцию логического чтения в то время, когда буфер заблокирован, он отправит сигнал события-ожидания (wait-event) buffer busy wait и просто ожидает снятия блокировки. В итоге избыточные операции ввода/вывода не нужны...



Скованные одной цепью…

Буферный Кэш представляет собой кэш, состоящий из буферов, и управляемый по LRU12-алгоритму. Согласно этому алгоритму, буферы, к которым обращаются наиболее часто, перемещаются в MRU13-конец связного списка. Вновь читаемые блоки считываются в кэш и размещаются MRU-конце списка. Когда Oracle производит операции логического чтения буферов, последние перемещаются в MRU-конец списка. Если к блоку нет обращений, он постепенно вытесняется все дальше и дальше по направлению к LRU-концу списка. В конце концов, он достигает конца списка и вновь считанный блок займет его буфер. Другими словами, блок будет вытеснен из LRU-списка.



Примечание: В Oracle8i реализация LRU-алгоритма подверглась значительным изменениям, добавлен новый “mid-point insertion” алгоритм для минимизации действий над защелкой (latch) при операциях закрепления и отделения блоков от списка, особенно в случае перемещения “hot” блоков в MRU-конец списка. У меня нет более подробной информации на этот счет; оставлю детали для последующего обновления этой статьи…

Два типа операций ввода/вывода

Oracle использует два типа операций ввода/вывода при чтении файлов данных:



  • Одноблочные операции чтения со случайной природой доступа, обычно свойственные индексному доступу (во время такого запроса ввода/вывода генерируется wait-event db file sequential read)

  • Многоблочные операции чтения с последовательной природой доступа, обычно принадлежат операциям полного табличного сканирования (во время такого запроса ввода/вывода генерируется wait-event db file scattered read), но могут также иметь отношение к сортировке и параллельным запросам (генерируется wait-event direct path read)

Теперь, как говорят, давайте начнем с самого начала…

Упрощенный алгоритм кэширования ввода/вывода и эффект “очистки”

Правила для обоих типов операций ввода/вывода могут быть достаточно простыми. Когда процессу нужен блок данных, процесс, прежде всего, проверяет Буферный Кэш. Если блок находится в Буферном Кэше, то процесс использует блок и “перемещает” его в MRU-конец связного LRU-списка. Если блок отсутствует в Буферном Кэше, тогда он считывается с диска и помещается в буфер Буферного Кэша, “размещенный” в MRU-конце LRU-списка.

Но при таком подходе существует серьезная проблема. Чтобы произошло бы в соответствие с этим алгоритмом, если бы кто-то произвел сканирование каждой строки в каждом блоке данных таблицы, размер которой превышает Буферный Кэш? Другими словами, выполнит полное табличное сканирование (FTS) большой таблицы?

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



Кэшированный ввод/вывод в Oracle

В Oracle7 версии 7.0 для предотвращения эффекта очистки кэша (cache flushing effect) был введен ряд тонких, но очень важных изменений. На самом деле, описываемые изменения были впервые встроены в Oracle версии 6.0, но для удобства описания давайте начнем с Oracle7 версии 7.0…

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

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

Однако архитекторы Oracle7 осознавали, что существует несколько ситуаций, когда выгоднее удерживать блоки при операциях FTS, например, при чтении очень маленьких таблиц, данные которых извлекаются очень часто. Другая особенность очень маленьких таблиц заключается в том, что они никогда не смогли бы очистить содержимое Буферного Кэша! Таким образом, блоки, читаемые во время операций полного табличного сканирования очень маленьких таблиц (very small tables), помещались в MRU-конец связного LRU-списка в качестве исключения. Чтобы определить значение термина “очень маленькая таблица”, был создан параметр инициализации SMALL_TABLE_THRESHOLD со значением по умолчанию, равным 2% от DB_BLOCK_BUFFERS. Параметр SMALL_TABLE_THRESHOLD определялся как число блоков, любая таблица с меньшим числом блоков рассматривалась как “очень маленькая”.

ПРИМЕЧАНИЕ: Этот алгоритм также характерен для Oracle релиз 6.0, где SMALL_TABLE_THRESHOLD был жестко зашит в ядре (т.е. не был вынесен в качестве параметра) со значением, равным 4 блокам. Он стал “видимым” параметром только в Oracle7.

Таким образом, они учли все, не так ли? Какие умные люди! Тем не менее, умные архитекторы Oracle7 пренебрегли человеческим фактором…

Разработчики приложений и администраторы баз данных всегда были очарованы идеей закрепления (pinning) целых таблиц в Буферном Кэше. Мнение о том, что это хороший подход, очень популярно. Хотя, если Вы задумаетесь, это просто невозможно в большинстве случаев. Все-таки кэш по определению есть относительно небольшое подмножество гораздо большей по размеру базы данных. Даже с учетом “километров” RAM, доступных серверам в наши дни, размер ее все еще не превышает нескольких гигабайт. В большинстве приложений баз данных таблицы с большим размером встречаются также часто как червяки в саду. Но, люди – это класс оптимистов, поэтому, насвистывая модную мелодию, мы делаем все возможное, чтобы закрепить такие таблицы в памяти, полагая, что это как-нибудь улучшит производительность.

Усвоив новые правила в Oracle7 релиз 7.0, администраторы баз данных немедленно переустанавливали параметр SMALL_TABLE_THRESHOLD в абсурдно высокое значение, пытаясь разом добиться труднодостижимого эффекта закрепленной в кэше таблицы (pinned table) и избежать дискового ввода/вывода. Тем самым, они нивелировали всю тонкую работу, проделанную архитекторами Oracle7, и мы получали ту же ситуацию (сброс кэша), которой пытались избежать. Операции полного табличного сканирования постоянно “размывали” Буферный Кэш без какой-либо пользы.



Ввод/вывод в Oracle7 версии 7.1 и выше

Пришел черед Oracle7 версии 7.1…

Вновь архитекторы Oracle7 показали свою проницательность. Во-первых, они сделали проблемный параметр SMALL_TABLE_THRESHOLD “недокументированным”, переименовав его в “_SMALL_TABLE_THRESHOLD”. Это дало эффект наложения знака “опасность” на данный параметр, предупреждая: “Изменяй меня на свой страх и риск”. Хорошая мысль!

Тем не менее, они осознавали, что разработчикам нужно закреплять некоторые таблицы в Буферном Кэше. Поэтому они создали атрибуты CACHE и NOCACHE для команд CREATE TABLE и ALTER TABLE. По умолчанию использовался атрибут NOCACHE, но разработчик мог изменить его значение на CACHE. Это означало, что таблицы с атрибутом CACHE также могли быть размещены в MRU-конце связного LRU-списка (подобно случаю очень маленьких таблиц) даже при выполнении операции FTS. Блоки таблиц с атрибутом NOCACHE по-прежнему размещались в LRU-конце списка.

Однако, учитывая человеческую природу и помня популярное, но ложное представление о том, что закрепление таблиц в памяти заставит каждое приложение “летать”, как Вы думаете, что могло бы произойти? Конечно! Каждый стал бы помечать все свои таблицы атрибутом CACHE!

Набив шишки c параметром SMALL_TABLE_THRESHOLD, архитекторы Oracle7 добавили ограничитель, чтобы предотвратить эту ситуацию. Они создали еще один параметр CACHE_SIZE_THRESHOLD, который определил максимальный размер для таблиц с установленным атрибутом CACHE. Если таблица была бы помечена как CACHE, но ее размер был бы больше числа блоков, заданных параметром CACHE_SIZE_THRESHOLD, Oracle трактовал бы такую таблицу как NOCACHE, игнорируя атрибут CACHE. Значение параметра по умолчанию было определено в 10% от размера Буферного Кэша, но оно могло быть при необходимости увеличено или уменьшено администраторами баз данных (или разработчиками, которые взломали пароли DBA).

Итак, вот все правила, которые остаются в силе на протяжении последних десяти лет, начиная с Oracle7 версии 7.1. Операции индексного сканирования (использующие одноблочные операции ввода/вывода) всегда помещают блоки в MRU-конец связного LRU списка Буферного Кэша. Такие блоки имеют тенденцию сохраняться в Буферном Кэше на значительное время. Природа доступа к древообразной структуре B*-Tree индексов склонна повышать вероятность повторного использования буферов, которые будут постоянно перемещаться обратно в MRU-конец списка. Таким образом, индексы имеют тенденцию к очень хорошему кэшированию.

С другой стороны, операции FTS будут читать данные с диска в буферы кэша, которые будут сразу размещены в LRU-конце связного LRU-списка. Если же таблицы принадлежат к классу маленьких таблиц, то их данные будут считаны в буферы, размещенные в MRU-конце списка. Еще одним исключением является случай, когда таблицы помечены как CACHE и их размер меньше ограничения, накладываемого параметром CACHE_SIZE_THRESHOLD. В этом случае, считываемые блоки будут также размещены в MRU-конце списка.



Резюме характеристик ввода/вывода в Oracle

В целом, для операции FTS характерно, что почти каждая операция логического чтения приводит к операции физического чтения. Это совершенно нормально. Операции FTS обычно имеют 20% или меньший (часто до 1% или 5%) коэффициент попадания в Буферный Кэш, который означает, что практически все (за исключением небольшого числа) операции логического чтения приводят к соответствующим операциям физического чтения. Напротив, для индексного доступа обычно характерны высокие (95% или выше) значения коэффициента попадания в Буферный Кэш.



Вычисление “физического ввода/вывода” по отношению к “логическому вводу/выводу”

В обязанности стоимостного оптимизатора входит вычисление стоимости, которая примерно равна физическому вводу/выводу. Это не так просто, как звучит…

Подсчет операций логического чтения намного проще, и это как раз то, что делает CBO. Стоимостной оптимизатор учитывает информацию в словаре данных о размере таблиц и индексов, количестве строк в таблицах, количестве различных (distinct) ключей в индексах, числе уровней в B*-Tree индексе, среднем числе блоков таблицы на отдельное значение, среднем числе листьевых (leaf) узлов на отдельное значение. Он также знает, как работают четыре метода соединения таблиц, знает информацию о селективности (selectivity) данных в столбцах (или ее отсутствии) из гистограмм в словаре данных, знает различия в работе B*-Tree и Bitmap индексов. Учитывая все эти вещи, формулы, встроенные в CBO (которые Oracle не разглашает), могут быстро и аккуратно определить, как много операций логического чтения можно ожидать для каждого из нескольких, десятков, сотен или тысяч возможных планов исполнения для любого заданного оператора SQL. CBO будет подсчитывать количество логических операций чтения для всех возможных планов исполнения, пытаться преобразовать их к числу физических операций чтения (стоимости) и выбрать наименьшее результирующее значение.

Но как CBO осуществляет переход от общего числа логических операций чтения к общему числу физических операций чтения?

Для операций FTS это относительно просто! Оптимизатор берет общее число логических операций чтения (т.е. число блоков в таблице), делит его на значение параметра DB_FILE_MULTIBLOCK_READ_COUNT, и, о-о-оп ля, мы имеем число физических операций чтения. Это предельно верная формула, поскольку операции FTS характеризуются очень низким (почти отсутствующим) коэффициентом попадания в Буферный Кэш.

Для операций индексного сканирования, которые всегда кэшируются намного лучше, эта формула нуждается в корректировке…

Операции индексного сканирования реализуются с использованием одноблочных операций чтения (для UNIQUE, RANGE и полного индексного сканирования (FAST Index Scan), однако быстрое полное индексное сканирование (FAST FULL Index Scan) использует многоблочные операции чтения аналогично операциям FTS), поэтому корректировка на параметр DB_FILE_MULTIBLOCK_READ_COUNT здесь не используется. Вместо этого, начиная с Oracle8 релиз 8.0 для согласования между логическим и физическим вводом/выводом был введен новый параметр OPTIMIZER_INDEX_CACHING:

CALCULATED-LOGICAL-READS * (1 – (OPTIMIZER_INDEX_CACHING / 100)) = CALCULATED-PHYSICAL-READS = COST

Тем не менее, этот параметр имеет неудачное значение по умолчанию, установленное в ноль. Подставьте значение “0” в формулу, и число логических операции чтения будут транслироваться “один к одному” в число физических операции чтения. Значение по умолчанию приводит данную формулу к тривиальному виду. Этого просто не может быть в реальной ситуации!

Мне нравится устанавливать OPTIMIZER_INDEX_CACHING в значение 90, означающее, что оптимизатор ожидает найти в Буферном Кэше данные для 90% всех логических операций чтения при индексном доступе. Вероятнее всего для большинства приложений этот процент будет близок к 95% или 98%, или даже 100%. Но 90% - это хорошее, умеренное значение для применения. Начните с него, и Вы должны заметить впечатляющую разницу в том, как CBO будет принимать решения.

Не верьте на слово. Испытайте! Проверьте для себя эти утверждения опытным путем.

Вы можете изменить этот параметр, используя команду ALTER SESSION SET OPTIMIZER_INDEX_CACHING = 90. Значения лежат в диапазоне от 0 (по умолчанию) до 99. Начните с 90, затем попробуйте различные значения. Я склонен видеть сумасшедшее поведение оптимизатора при значении 99; испытайте это. Запустите эту команду и сделайте EXPLAIN PLAN на запросе, причиняющем особенное беспокойство, после удаления из него подсказок (hints), которые Вы ранее так усердно добавляли. Разница в том, что CBO будет выбирать правильный план. Если Вы найдете, что это происходит достаточно часто при тестовых условиях, возможно, Вы захотите установить этот параметр в файле “init.ora” и посмотреть, что будет происходить с общей производительностью системы при глобальной установке.

Похоже на новую жизнь для старого, угрюмого CBO…



Корректировка для различных типов ввода/вывода

Но постойте! Это еще не все!

CBO также необходимо знать относительную стоимость индексного доступа по сравнению с FTS. Параметр OPTIMIZER_INDEX_COST_ADJ отражает это соотношение. Значение по умолчание 100 говорит CBO, что на индексный доступ затрачивается примерно столько же времени, что и на операции доступа при FTS. CBO использует значение данного параметра для учета еще одного усовершенствования в расчете стоимости индексного сканирования:

PREVIOUS-COST * (OPTIMIZER_INDEX_COST_ADJ / 100) = FINAL-COST

Как видно, значение по умолчанию приводит данную формулу к тривиальному виду также как и в случае со значением по умолчанию для параметра OPTIMIZER_INDEX_CACHING.

К счастью, адекватное значение для параметра OPTIMIZER_INDEX_COST_ADJ может быть легко получено из самой базы данных Oracle. Ответ находится в столбце AVERAGE_WAIT представления V$SYSTEM_EVENT. Это еще один пример бесценной установки параметра TIMED_STATISTICS в значение TRUE, что позволяет наполнять ‘Session Wait’ представления (одним из которых является V$SYSTEM_EVENT) хронометрической информацией.

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



SELECT EVENT,

AVERAGE_WAIT

FROM V$SYSTEM_EVENT

WHERE EVENT LIKE ‘db file s%’;

Запрос вернет информацию о двух событиях ожидания ввода/вывода: db file scattered reads (операции FTS) и db file sequential reads (операции индексного сканирования). Столбец AVERAGE_WAIT содержит среднее время ожидания в сотых долях секунды этих событий:



EVENT AVERAGE_WAITS

========================= ==============

db file sequential reads .33178629

db file scattered reads 2.190087

В данном примере запросы ввода/вывода при индексном сканировании занимают в среднем только 15% среднего времени запросов ввода/вывода при операциях FTS. Поэтому параметр OPTIMIZER_INDEX_COST_ADJ должен быть установлен в значение 15.



Заключение

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



Блиц-вопрос: как Вы думаете, каким образом завышенное значение параметра DB_FILE_MULTIBLOCK_READ_COUNT влияет на CBO? Всегда ли высокое значение является хорошим выбором? С учетом влияния на CBO, какие могут быть потенциальные преимущества (недостатки) низкого значения для данного параметра? Настройка этого параметра в слишком высокое значение в процессе миграции с RBO вводит в заблуждение стоимостной оптимизатор и является одной из наиболее частых причин принятия им неверных решений.

Существует веское основание, что пакет DBMS_STATS (введенный в Oracle8i) гораздо лучше подсчитывает статистику по таблицам, индексам и столбцам чем команда ANALYZE, которая, по сведениям, будет вскоре выведена из употребления. Не верьте на слово, сделайте поиск на Metalink по ключевому слову “dbms_stats” с помощью опции “Расширенного Поиска”, включив показ результатов из раздела “bug database”. Несколько спорных вопросов, посланных на MetaLink относительно работы пакета DBMS_STATS, были позже исправлены как ошибки команды ANALYZE, которая использовалась в качестве основы для сравнения.

Но очень важно понимать и адекватно настроить эти два параметра. Испытайте их.

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



Благодарности

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



Jonathan Lewis (JL Computer Consultancy - UK - http://www.jlcomp.demon.co.uk/)

Jeff Maresh (Maresh Consulting, Inc., Conifer, CO - USA - http://www.EvDBT.com/)

Cary Millsap (Hotsos Enterprises, Ltd., Southlake, TX - USA - http://www.Hotsos.com/)

Mogens Norgaard (Miracle A/S, - Denmark - http://www.MiracleAS.dk/)

Craig Shallahamer (OraPub, Inc., Lake Oswego, OR - USA - http://www.OraPub.com/)

Любые ошибки или оплошности в этой статье принадлежат исключительно автору.



Об Авторе

Tim Gorman работает в информационной индустрии свыше 18 лет, главным образом, в качестве разработчика и программиста на “C”. Начиная с 1990 года, он работает в качестве разработчика приложений RDBMS Oracle и в качестве администратора баз данных Oracle c темной бурной ночи в 1993. Он является ведущим консультантом в компании Evergreen Database Technologies, Inc. (http://www.EvDBT.com), расположенной в Evergeen, Colorado и специализирующейся в оказании обучения и консалтинговых услуг в технологиях Oracle. Господин Gorman специализируется в вопросах администрирования баз данных и настройки производительности, решении сложных проблем поиска и устранения неисправностей, вопросах резервного копирования и восстановления данных, легких курьезах и сноубординге (не обязательно в данном порядке).

Перевод Эдуарда Шевцова.

Пожайлуста, все замечания относительно качества перевода высылайте по адресу edward@oradba.com.ru



1 Далее используется упрощенный перевод 'стоимостной оптимизатор’

2 Cost-Based Optimizer

3 Далее используется упрощенный перевод ‘оптимизатор по правилам’

4 Rule-Based Optimizer

5 Далее используется общеупотребимая аббревиатура ‘FTS’

6 Online Transaction Processing System

7 Decision-Support System

8 Источник строк (row source) – термин, употребляемый при пошаговом анализе плана исполнения для объектов, содержащих данные.

9 Buffer Cache Hit Ratio

10 Дословно: ‘перестановки’

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

12 Least recently used

13 Most recently used

Page of 13

Каталог: books
books -> Учебное пособие Нижний Новгород 2011 год
books -> Учебное пособие может быть использовано студентами, аспирантами, изучающими психологические, социальные, педагогические науки, а также педагогами, психологами, социальными работниками. Л. М. Шипицына, 2007 Издательство
books -> Сборник материалов III международной научно-практической конференции Екатеринбург 2011 ббк 448-951. 663. 1
books -> Учебное пособие Нижний Новгород 2011 год
books -> С. А. Беличева. Основы превентивной психологии
books -> Елена Петровна Гора учебное пособие
books -> Учебно-методический комплекс по дисциплине «Практическая полиграфия»
books -> Ливанова Е. Ю. «Роль практики в формировании профессиональных компетенций выпускника вуза»
books -> Игорь Иванович Кальной, Юрий Аскольдович Сандулов Философия для аспирантов


Поделитесь с Вашими друзьями:


База данных защищена авторским правом ©psihdocs.ru 2019
обратиться к администрации

    Главная страница