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

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

Продемонстрируем возможности стабилизации плана оптимизатора на простом примере. Ниже представлен один из методов использования хранимого шаблона для запроса. После запоминания шаблона мы сделаем ряд изменений в базе данных (проанализируем таблицу), которые приведут к изменению плана. Наконец, мы увидим, как, включив стабилизацию плана оптимизатора, можно заставить сервер Oracle использовать план, сохраненный первоначально, до сделанных изменений. Сначала создадим копию таблицы SCOTT.EMP и зададим для нее первичный ключ:

tkyte@TKYTE816> create table amp

2 as

3 select ename, empno from scott.emp group by ename, empno

Table created.

tkyte@TKYTE816> alter table emp

2 add constraint emp pk

3 primary key(empno)

Table altered.

При отсутствии доступа к таблице ЕМР необходимо получить для нее привилегию SELECT. Созданный при добавлении первичного ключа индекс используется в примере; мы генерируем запрос, который его использует. Зададим режим оптимизации CHOOSE.

tkyte@TKYTE816> alter session set optimizer goal=choose

2 /

Session altered.

Это сделано исключительно для согласованности примеров. При отсутствии статистической информации, несомненно, будет вызваться оптимизатор, основанный на правилах. Однако, если установлен другой режим оптимизации, например FIRST ROWS, будет вызываться оптимизатор, основанный на стоимости, и дальнейшие изменения в базе данных могут сказаться на выбираемом плане выполнения запроса. Наконец, вот план выполнения нашего запроса:

tkyte@TKYTE816> set autotrace traceonly explain tkyte@TKYTE816> select empno, ename from amp where empno > 0

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF EMP

2 1 INDEX (RANGE SCAN) OF EMP PK (UNIQUE)

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

Глава 11

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

tkyte@TKYTE816> create or replace outline MyOutline

2 for category mycategory

3 CN

4 select empno, ename from emp where empno > 0

Outline created.

Оператор CREATE OR REPLACE OUTLINE создал шаблон запроса и сохранил его в базе данных (где и как он хранится, описано далее в этой главе). Поскольку мы явно создали шаблон, можно задать ему имя (MYOUTLINE). Кроме того, мы отнесли этот шаблон запроса к определенной категории (MYCATEGORY).

Следует отметить, что при выполнении оператора CREATE OUTLINE можно получить следующее сообщение об ошибке:

select empno, ename from emp where empno > 0

ERROR at line 4:

ORA-18005: create any outline privilege is required for this operation

Если выдается такое сообщение, необходимо, чтобы администратор базы данных предоставил соответствующему пользователю привилегию CREATE ANY OUTLINE. Все привилегии, необходимые для создания и управления шаблонами, описаны в разделе Как стабилизировать план оптимизатора .

Мы создали шаблон, задающий необходимый план выполнения запроса (просмотр по индексу). Давайте теперь изменим базу данных - просто проанализируем таблицу:

tkyte@TKYTE816> analyze table emp compute statistics 2 /

Table analyzed.

Давайте посмотрим, каким теперь будет план выполнения запроса:

tkyte@TKYTE816> set autotrace traceonly explain tkyte@TKYTE816> select empno, ename from emp where empno > 0 2 /

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=l Card=14 Bytes=112)

1 0 TABLE ACCESS (FULL) OF EMP (Cost=l Card=14 Bytes=112)

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

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

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

tkyte@TKYTE816> alter session set use stored outlines = m[ycategory 2 /

Session altered.

Это обеспечивает применение хранимых шаблонов категории MYCATEGORY. Если теперь посмотреть план выполнения запроса:

tkyte@TKYTE816> set autotrace traceonly explain tkyte0TKYTE816> select empno, ename from emp where empno > 0 2 /

Execution Flan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=14 Bytes=112)

1 0 TABLE ACCESS (BY INDEX ROWID) OF EMP (Cost=2 Card=14

2 1 INDEX (RANGE SCAN) OF EMP PK (UNIQUE) (Cost=l Card=14)

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

Использование стабилизации плана оптимизатора

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

Метод настройки

Часто спрашивают: Как задать подсказку для запроса в существующем приложении, не изменяя текста запроса? . Обычно есть доступ только к двоичному коду приложения, так что изменить запрос нельзя, но желательно изменить план его выполнения.

Проблемный запрос известен, более того, изменяя установки сеанса, можно обеспечить требуемую производительность. Если бы можно было добавить в приложение оператор ALTER SESSION (чтобы включить или отключить использование соединения хе-