Стабилизация плана оптимизатора

ственное структурное отличие между представлением DBA OUTLINE HINTS, USER OUTLINE HINTS и ALL OUTLINE HINTS - добавление столбца OWNER, идентифицирующего пользователя, создавшего шаблон.

NAME. Имя хранимого шаблона. Если шаблон создан с помощью оператора

CREATE OUTLINE, это будет имя, заданное в операторе. Если шаблон сгенерирован автоматически с помощью ALTER SESSION, идентификатор присваивается системой, и будет иметь вид SYS OUTLINE 0104120957410010, как у шаблона в нашем примере.

OWNER. Имя пользователя, создавшего шаблон запроса.

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

STAGE. Стадия выполнения, на которой применяются подсказки в ходе обработки запроса. Это число представляет стадию, на которой подсказка будет вписана в запрос. Речь идет о внутренних стадиях обработки, выполняемых оптимизатором Oracle, которые обычно пользователям недоступны.

JOIN POS. Задает таблицу, к которой будет применяться эта подсказка. Для всех

подсказок, не задающих метод доступа, в этом столбце будет значение ноль. Для подсказок, задающих метод доступа (например, доступ к таблице по индексу), столбец JOIN POS задает таблицу.

HINT. Подсказка, которая должна быть встроена в запрос. Посмотрим на результаты выполнения исходного примера:

tkyte@TKYTE816> break on stage skip 1

tkyte@TKYTE816> select stage, name, node, join pos, bint

2 from user outline hints

3 where name = MYOUTLINE

4 order by stage

STAGE NAME

NODE

JOIN POS HINT

MYOUTLINE 1

MYOUTLINE 1

2 MYOUTLINE 1

3 MYOUTLINE 1 MYOUTLINE 1 MYOUTLINE 1 MYOUTLINE 1

7 rows selected.

0 NOREWRITE

0 RULE

0 NOREWRITE

0 NO EXPAND

0 ORDERED

0 NO FACT(EMP)

1 INDEX (BMP EMP PK)

Запрос показывает, что на стадии 1 сервер применяет подсказки NOREWRITE и RULE. Подсказка NOREWRITE предотвращает перезапись запроса по ходу выполнения (если кто-то в дальнейшем добавит соответствующие средства или включит пара-

Глава 11

метр сеанса/системы, обуславливающий вызов метода QUERY REWRITE). Подсказка RULE требует использовать оптимизатор, основанный на правилах, независимо от текущего значения OPTIMIZER GOAL и наличия (или отсутствия) статистической информации о таблице.

На стадии 2 снова предотвращается перезапись запроса. На стадии 3 вставляются подсказки, действительно определяющие требуемый план выполнения запроса. Применяется подсказка ORDERED, которая требует учитывать порядок следования таблиц в конструкции FROM при соединении (поскольку в нашем примере используется одна таблица, эта подсказка излишня). Далее применяется подсказка NO EXPAND (она применяется для условий, связанных с объектами, а, поскольку объекты в запросе не задействованы, эта подсказка не нужна). Затем, применяется внутренняя, не описанная подсказка NO FACT. Наконец, к таблице 1 (столбец JOIN POS) применяется подсказка INDEX(), задающая метод доступа, - для доступа к таблице ЕМР используется индекс ЕМР РК, первичный ключ.

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

Создание хранимых шаблонов

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

Привилегии, необходимые для создания хранимых шаблонов

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

CREATE ANY OUTLINE. Позволяет создавать шаблоны в базе данных. При отсутствии этой привилегии будет выдаваться сообщение об ошибке ORA-18005: create any outline privilege is required for this operation.

ALTER ANY OUTLINE. Позволяет изменять (переименовывать, изменять категорию или пересоздавать план) шаблон запроса.

DROP ANY OUTLINE. Позволяет удалять существующий шаблон с указанным именем.

EXECUTE ON OUTLN PKG. Позволяет выполнять подпрограммы пакета OUTLINE (подробнее о его возможностях см. далее).

Стабилизация плана оптимизатора 623

Обратите внимание, что это привилегии класса ANY. Это означает, что при наличии привилегии CREATE OR REPLACE ANY OUTLINE можно переписать шаблон другого пользователя, не спрашивая у него разрешения. Шаблоны, в отличие от большинства других объектов базы данных не принадлежат никому. У шаблона есть создатель, но нет владельца в обычном смысле. Если можно удалять собственные шаблоны, то можно (ненамеренно) удалить и шаблон любого другого пользователя, поэтому при использовании этих привилегий надо быть внимательным. Подробнее об этом см. в подразделе Пространство имен для шаблонов - глобальное раздела Проблемы .

Использование операторов ЯОД

Для создания хранимых шаблонов можно использовать оператор ЯОД следующей структуры:

CREATE [OR REPLACE] OUTLINE имя шаблона [FOR CATEGORY имя категории]

ON оператор для которого сохраняется шаблон

В этом операторе:

Имя шаблона - имя, присвоенное шаблону. Оно должно иметь смысл для создателя и разработчика приложения. При этом на имя налагаются те же ограничения, что и для любого объекта базы данных (не более 30 символов, начинается с буквы и т.д.). Кроме того, имя шаблона должно быть уникальным для базы данных, а не для пользователя или категории, как можно было бы подумать, поэтому будьте особенно внимательны при использовании конструкции OR REPLACE, поскольку оператор перезапишет любой существующий шаблон с таким именем.

Имя категории - имя, используемое для группировки шаблонов. Эта часть оператора CREATE - не обязательная, и если категория не задана, шаблон будет отнесен к категории DEFAULT. Рекомендуется явно указывать имя категории и не использовать категорию DEFAULT. Поскольку сеанс в каждый момент времени может использовать только одну категорию шаблонов, в ней надо сохранить шаблоны планов для всех существенных запросов.

Оператор для которого сохраняется шаблон - любой допустимый SQL-оператор ЯМД.

Генерация шаблонов с помощью операторов ЯОД больше всего подходит для приложений, в которых все SQL-операторы хранятся вне приложения. Другими словами, есть файл ресурсов, в котором записаны все потенциально выполняемые SQL-операторы. В этом случае по такому файлу очень легко сгенерировать сценарий с операторами CREATE OUTLINE и выполнить его на сервере. Это гарантирует создание шаблонов для всех запросов (если запросы указаны в этом файле ресурсов). Кроме того, такой подход предохраняет от случайной генерации шаблонов для лишних запросов. Например, если используется триггер ON LOGON, после регистрации в SQL*Plus окажется, что для автоматически выполняемых утилитой SQL*Plus запросов тоже сохранены шаблоны.