Глава 11

И только потом оказывается, что у них - разный объем оперативной памяти, разное количество процессоров и параметры в файле init.ora имеют различные значения, в соответствии с разными конфигурациями оборудования. Любой из этих факторов может влиять на оптимизатор, и приводит к изменению плана. Стабилизация плана оптимизатора позволяет избежать этой проблемы.

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

Проверка использования индексов

На самом деле хранимые шаблоны создавались с другой целью; это скорее побочный эффект, но их можно так использовать! Часто задают вопрос: В базе данных - множество индексов, и некоторые из них, определенно, не используются, но непонятно - какие. Как это определить? . Один из способов - с помощью хранимых шаблонов: в них перечислены имена всех индексов, использованных в плане выполнения запроса. Если используется триггер ON LOGON для включения автоматической генерации шаблонов, поработайте некоторое время с системой, а затем отключите генерацию, - будет получен сравнительно полный список индексов, используемых в системе (и запросов, которые эти индексы используют). Как будет показано ниже, все подсказки , используемые хранимыми шаблонами, находятся в таблице словаря данных. Благодаря этому легко понять, какие индексы используются (и какими запросами), а какие - нет. Например, по результатам предыдущих двух примеров можно определить, какие запросы используют индекс ЕМР РК:

tkyte@TKYTE816> select name, hint

2 from user outline hints

3 where hint like INDEX%EMP PK%

КАМЕ HINT

MYOUTLINE INDEX (EMP EMP PK)

FIRST ROWS EMP INDEX (EMP EMP PK)

Столбец NAME этого запроса позволяет найти в представлении USER OUTLINES текст исходного SQL-запроса, использующего индекс.

Получение списка SQL-операторов, выполненных приложением

Это тоже побочный эффект, а не прямое назначение хранимых шаблонов, но тем не менее его можно использовать. Часто пользователи интересуются, какие SQL-операто-ры фактически выполняют их приложения. Изменить текст приложения нельзя, а установка SQL TRACE ON приводит к слишком большим расходам ресурсов. Используя

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

триггер ON LOGON для некоторых пользователей приложения, можно автоматически сохранить в таблицах OUTLINE все SQL-операторы, выполненные приложением. Эту информацию в дальнейшем можно использовать для настройки или анализа.

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

Как выполняется стабилизация плана оптимизатора

Стабилизация плана оптимизатора выполняется на базе механизма подсказок Oracle. Используя пред]дущий пример с таблицей ЕМР, мы сможем увидеть подсказки, сохраненные для запроса, а также их применение во время выполнения. Мы также рассмотрим пользовательскую схему OUTLN, в которой хранятся все шаблоны запросов и подсказки для них.

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

Представления OUTLINES и OUTLINE HINTS

С шаблонами запросов связаны два представления, между которыми есть отношение главное/подчиненное. Главное представление - OUTLINES (как обычно, есть три его версии: DBA , ALL и USER . Подчиненное представление - OUTLINE HINTS (оно тоже доступно в трех версиях). В следующих разделах описаны эти представления и их использование.

Представления JOUTLINES

В этих представлениях находятся хранимые шаблоны. В представлении DBA OUTLINES есть записи для всех хранимых шаблонов в системе, тогда как в представлениях ALL и USER OUTLINES присутствуют только строки, имеющие отношение к текущему пользователю (шаблоны, доступн1е или созданн1е пользователем, соответственно).

Поскольку представления DBA OUTLINES и USER OUTLINES отличаются только

одним столбцом (в представлении DBA есть столбец OWNER, содержащий имя схемы, в которой создан шаблон), мы рассмотрим представление DBA OUTLINES:

NAME. Имя шаблона, заданное в операторе CREATE OUTLINE (в представленных выше примерах использовались имена MYOUTLINE и FIRST ROWS EMP). Если для создания хранимого шаблона использовался оператор ALTER SESSION

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

620 Глава 11

именем в двух категориях или у различных пользователей. Более детально это описано в разделе Проблемы .

OWNER. Схема, в которой создан шаблон. Шаблоны не принадлежат никому, так что имя столбца - несколько неправильное. Правильно было бы назвать столбец CREATOR, создатель.

CATEGORY. Категория, к которой отнесена схема (в нашем примере - MYCATEGORY). Шаблоны запросов могут принадлежать к категории, указанной по имени, либо к общей категории DEFAULT, которая используется, если имя категории не задано. В ходе работы пользователь или приложение выполняет оператор ALTER SESSION SET USE STORED OUTLINES = <TRUEимя категории>, чтобы указать, какой набор храним1х шаблонов надо использовать. При установке значения TRUE будут использоваться шаблоны стандартной категории, DEFAULT. В каждый момент времени может использоваться только одна категория шаблонов.

USED. Этот атрибут показывает, б1л ли указанный шаблон хоть раз использован. Он будет иметь значение unused до первого использования шаблона для изменения плана выполнения запроса; при этом атрибут получает значение used.

TIMESTAMP. Дата и время создания исходного шаблона.

VERSION. Версия СУБД, в которой б]л создан исходный шаблон.

SQL TEXT. Фактический (дословный) SQL-запрос, использованный для генерации шаблона. Этот шаблон может использоваться только для запросов, текст которых полностью совпадает.

Итак, например, после выполнения запросов в представленных выше примерах, в представлении USER OUTLINES будет следующая информация:

tkyte@TKYTE816> select * from user outlines;

КАМЕ CATEGORY USED TIMESTAMP VERSION SQLTEXT

MYOUTLINE MYCATEGORY USED ll-APR-01 8.1.6.0.0 select empno, ename

from emp where empno

> 0

FIRST ROWS EMP HR APPLICATION USED 12-APR-01 8.1.6.0.0 SELECT ENAME,EMPNO

FROM EMP WHERE EMPNO

> 0

Как и ожидалось, выданы все описанные выше атрибуты.

Представления OUTLINE HINTS

В этих представлениях находятся реальные подсказки, которые надо применять на разных внутренних стадиях плана выполнения запроса. Сервер по ходу работы переписывает переданный запрос, встраивая эти подсказки в соответствующие места, что и дает необходимый план выполнения. В тексте запроса эти подсказки не появляются, - они добавляются во внутренние структуры плана выполнения запроса. Един-