Какие планы у Oracle?

Владимир Пржиялковский,
координатор Евро-Азиатской Группы Пользователей Oracle,
преподаватель УКЦ Interface Ltd.

Содержание

• Факторы влияния на выбор плана RBO
• Факторы влияния на выбор плана CBO
• Доверяй …
• … но проверяй

Все SQL-запросы, поступающие в СУБД Oracle , как известно, и как это устроено и в других системах, "проживают" в общем случае одинаковые стадии своего жизненного цикла:

(1) синтаксического анализа;
(2) выработки алгоритма для выполнения;
(3) собственно выполнения и
(4) предъявления результата.

Не стоит и говорить, сколь много зависит от стадии 2 выработки алгоритма выполнения запроса: если он дает правильный результат, но составлен нерационально, время реакции СУБД на запрос может возрасти против более оптимального в десятки, а то и тысячи раз.

За выработку алгоритма для выполнения запроса в Oracle отвечает часть СУБД, называемая оптимизатором. Сам алгоритм по терминологии Oracle носит название плана [обработки запроса]. Если точнее, то оптимизаторов в Oracle два: т.н. "Rule based" (RBO) и "Cost based" (CBO). Каждый запрос обрабатываться одним из этих оптимизаторов, за исключением случаев с подзапросами, для которых вид оптимизатора можно указывать индивидуально, безотносительно к оптимизатору для внешнего запроса.

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

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

Факторы влияния на выбор плана RBO

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

Логически работу RBO можно представить так: сначала он составляет полный перечень всех возможных вариантов (планов) обработки запроса, потом вычисляет для каждого варианта вес и выбирает наиболее "легкий". (Разумеется, что фактически RBO так безумно не поступает ввиду несметного числа вариантов в общем случае, но фактическая техника - это попытка приблизиться к результату, даваемому именно такой логикой).

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

1. версия СУБД, а значит и оптимизатора
2. синтаксис запроса
3. наличие или отсутствие, а также некоторые свойства, вспомогательных хранимых структур (индексов, кластеров) или основных (индексно-организованная таблица)

Небольшие замечания по поводу этих факторов.

(1) В новых версиях СУБД оптимизатор может подправляться: разработчики могут пытаться устранить некоторые замеченные ошибки или добавить новые свойства поведения. Внутренняя логика RBO относительно проста, поэтому часто разница может свестись лишь к изменению таблицы предпочтительности доступа: в версии 9.0 эта таблица состоит из 20 позиций, а в некоторых более ранних была короче.
(2) Разные по форме, но одинаковые по смыслу запросы, могут иметь разные планы. Вообще-то, Oracle к этому не стремится, особенно в CBO (полагая, что определяющим для плана является именно смысл - и правильно делает), но фирме не всегда это удается. (Вина за разные планы поровну распределяется между разработчиками Oracle и SQL). Очень простой пример - порядок выполнения соединения будет соответствовать порядку формального перечисления таблиц во фразе FROM, если только таблица предпочтительности доступа не вынудит RBO этот порядок нарушить (например, из-за наличия индекса).
(3) Наличие или отсутствие индекса или кластера - важный фактор для доступа, существенно учитываемый RBO при выборе плана. Добавленный или удаленный индекс способен радикально поменять план одного и того же запроса.

Факторы влияния на выбор плана CBO

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

CBO - программа, намного более сложная, чем RBO, и факторов влияния на выбор этой программой плана существенно больше. Вот эти факторы:

1. версия СУБД, а значит и оптимизатора
2. синтаксис запроса
3. наличие или отсутствие, а также свойства вспомогательных хранимых структур (индексов, кластеров)
4. значения некоторых INIT-параметров (SORT_AREA_SIZE, CURSOR_SHARING, DB_FILE_MULTIBLOCK_READ_COUNT и других)
5. наличие (или отсутствие) и значения (при наличии) в момент выполнения запроса предварительно собранной статистики относительно хранении объектов

Небольшие замечания по поводу этих факторов.

(1) В общем, аналогично RBO, но можно привести пару уточнений. Так, в версии СУБД 8.0.4 было устранено очень много прежних ошибок. В версии 9.0 сделано существенное улучшение работы с переменными привязки в тексте запроса: если прежде они до выработки плана не оценивались, то теперь оптимизатор сначала узнает их реальные значения, а потом предложит план. Так планы в вашей программе могли "сами поменяться".
(2) В общем, аналогично RBO.
(3) В общем, аналогично RBO.
(4) Целый ряд INIT-параметров используется CBO при выработке плана. Так, параметр COMPATIBLE переключит оптимизатор на более древнюю (или свежую) схему работы. Другие параметры повышают или понижают вес определенных узлов на дереве вариантов. Например, большее значение SORT_AREA_SIZE приводит к большей вероятности получить для соединения сортировку со слиянием. Имеется параметр специально для повышения или понижения веса "индексных" вариантов, повышающий или понижающий вероятность использования индекса в плане, предлагаемом CBO.
(5) Фактор статистики для CBO настолько важен, что нередко пользователи о существующих помимо него остаются в неведении. Нелишне напомнить, что анализироваться могут не только таблицы и индексы, но и столбцы. CBO может учитывать собранную в результате анализа гистограмму распределений значений в столбце, что существенно при неравномерном распределении этих значений в реальной таблице. Это способно дать разные планы на запросах, которые, например, различаются только приводимыми в тексте фамилией или номером изделия.

Доверяй …

В целом и эксперты, и представители Oracle рекомендуют работать с CBO, а не с RBO. В целом, опять-таки, CBO достаточно надежен и ведет себя разумно. Простая иллюстрация тому - пример отбора пяти высокооплачиваемых сотрудников (типовая задача). Вот три ("с половиной") разных способа, которые работают в версии 8.1.7:

SELECT ename, -sal sal
FROM (SELECT DISTINCT -sal sal, ename, empno FROM emp)
WHERE ROWNUM <= 5;

SELECT *
FROM (SELECT ename, sal FROM emp ORDER BY sal DESC)
WHERE ROWNUM <= 5;

SELECT * FROM
       (
       SELECT ename, sal, RANK () OVER (ORDER BY sal DESC)
       FROM emp
       )
WHERE ROWNUM <= 5;

SELECT * FROM
   (
   SELECT ename, sal FROM
       (
       SELECT ename, sal, RANK () OVER (ORDER BY sal DESC) salrank
       FROM emp
       )
   WHERE salrank <=5
   )
WHERE ROWNUM <= 5;

Читателям старых номеров журнала "Мир Oracle" напомню, что вариант А взят оттуда. Обратите внимание, что порядок сотрудников в выдаваемых списках при прогоне запросов разный; это нормально. Вариант В' - это вынужденная уловка, так как замена SELECT * на SELECT ename, sal (как хотелось бы) во внешнем SELECT варианта В неожиданно резко портит результат, что наводит на мысль об отсутствии знаний о столбовой дороге к безупречному коду у разработчиков Oracle.

А вот соответствующие планы:

0           SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=16 Bytes=320)
1        0      COUNT (STOPKEY)
2        1           VIEW (Cost=3 Card=16 Bytes=320)
3        2              SORT (UNIQUE STOPKEY) (Cost=3 Card=16 Bytes=192)
4        3                   TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=192)

0            SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=16 Bytes=320)
1         0      COUNT (STOPKEY)
2         1           VIEW (Cost=3 Card=16 Bytes=320)
3         2              SORT (ORDER BY STOPKEY) (Cost=3 Card=16 Bytes=144)
4         3                   TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=144)

      0             SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=16 Bytes=528)
      1         0       COUNT (STOPKEY)
      2         1            VIEW (Cost=3 Card=16 Bytes=528)
      3         2               WINDOW (SORT)
      4         3                   TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=144)

      0             SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=16 Bytes=528)
      1         0       COUNT (STOPKEY)
      2         1            VIEW (Cost=3 Card=16 Bytes=528)
      3         2               WINDOW (SORT PUSHED RANK)
      4         3                   TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=144)

Видно, что все планы разные ("фактор синтаксиса"), но все они имеют приятную для нас общность:

- во всех используется STOPKEY, то есть избегается полный перебор

(По поводу стоимостей запроса, указанных параметром Cost не следует быть чересчур придирчивым: во-первых, это лишь оценки оптимизатора, а не реальная статистика. За точной реальной статистикой затрат ресурсов придется обратиться к SQL Trace. Во-вторых, запросы сделаны по небольшой табличке демонстрационного примера в схеме SCOTT).

… но проверяй

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

SELECT ename, sal FROM
       (
       SELECT ename, sal, ROW_NUMBER () OVER (ORDER BY sal DESC) AS salnumber
       FROM emp
       )
WHERE salnumber <= 5;

даст такой план:

0             SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=16 Bytes=528)
1         0       VIEW (Cost=3 Card=16 Bytes=528)
2         1           WINDOW (SORT)
3         2               TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=144)

STOPKEY пропал. Пример Г дает еще один урок: похоже, что определяющую роль для этого плана играет синтаксис, и попытка "поиграться" прочими факторами ничего нам не даст. Кроме, может быть, "фактора версии СУБД": для сравнения, тот же запрос в версии 9.2 получит несколько иной план:

0             SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=14 Bytes=462)
1         0       VIEW (Cost=4 Card=14 Bytes=462)
2         1          WINDOW (SORT PUSHED RANK) (Cost=4 Card=14 Bytes=112)
3         2              TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=112)

Обратите также внимание на изменения в оценках стоимости выполняемых шагов. В первой строке Card=14, видимо, недоработка разработчиков, так как для А, Б и В/В' версия 9.2 дает более точную оценку: Card=5 (Bytes в 9.2 тоже оценивается точнее).

Post Scriptum. Новые для Oracle аналитические функции, сами по себе надежные, в некоторых технических деталях недостаточно четко описаны. А решение А привязано к побочному эффекту. Поэтому для конкретной задачи первой (последней) пятерки я бы рекомендовал следующие надежные решения: Б и Г.

Дополнительная информация

• Вы можете отправить свое мнение о материале его автору
  
• Обратиться в Interface Ltd. за дополнительной информацией/по вопросу приобретения продуктов
  
• Курсы по продуктам фирмы Oracle
  
• Другие статьи по продуктам Oracle из рубрики "Мастерская Oracle"

Обсудить на форуме Oracle
Отправить ссылку на страницу по e-mail