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

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

Необходимо, чтобы определенный запрос использовал конкретный режим оптимизации, независимо от значения параметра OPTIMIZER GOAL в ходе выполнения приложения. Можно задать соответствующий режим оптимизации с помощью оператора ALTER SESSION и выполнить оператор CREATE OUTLINE для соответствующего запроса.

Необходимо избежать использования определенных возможностей, например QUERY REWRITE ENABLED, HASH JOIN ENABLED, или перехода к прежним значениям OPTIMIZER FEATURES ENABLED. Можно создать сеанс, выполнить ряд операторов ALTER SESSION для включения/отключения соответствующих возможностей, а затем выполнить операторы CREATE OUTLINE для требуемых запросов.

Хорошо, а что делать, если установки параметров на уровне сеанса, например HASH JOIN ENABLED, не изменяют план выполнения запросов нужным образом? Когда единственный способ, дающий необходимый результат, - физическое включение подсказки в текст запроса? Можно, конечно, использовать подсказки в запросах, план выполнения которых надо стабилизировать, но это не то, что требуется. Необходимо, чтобы этот план использовал запрос, выполняющийся без подсказок. Но для того чтобы была возможность применять этот хранимый план, должны выполняться запросы, в точности с тем же текстом, который использовался для генерации шаблона. Надо сохранить шаблон для запроса без подсказок и использовать соответствующий план при выполнении этого запроса в дальнейшем. Зная, как выполняется стабилизация плана оптимизатора, это можно сделать, хотя и несколько нетривиальным способом. Вот как это делается.

Предположим, надо сохранить шаблон для следующего запроса:

scott@TKYTE816> set autotrace traceonly explain

scott@TKYTE816> select * from emp, dept where emp.deptno = dept.deptno; Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 NESTED LOOPS

2 1 TABLE ACCESS (FULL) OF EMP

3 1 TABLE ACCESS (BY INDEX ROWID) OF DEPT

4 3 INDEX (UNIQUE SCAN) OF DEPT PK (UNIQUE)

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

scott@TKYTE816> select *

2 from (select /*+ use hash(emp) */ * from emp) emp,

3 (select /*+ usehash(dept) */ * from dept) dept

4 where emp.deptno = dept.deptno

Глава 11 Execution Flan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=67 Bytes=7839)

1 0 HASH JOIN (Cost=3 Card=67 Bytes=7839)

2 1 TABLE ACCESS (FULL) OF EMP (Cost=l Card-82 Bytes=7134)

3 1 TABLE ACCESS (FULL) OF DEPT (Cost-1 Card=82 Bytes = 2460)

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

Ну, поскольку использование шаблонов планов запроса основано на сравнении строк, можно добиться требуемого результата с помощью другой схемы и представлений с подсказками. Поскольку использованные выше объекты находятся в пользовательской схеме SCOTT, мы создадим представления в пользовательской схеме TKYTE:

scott@TKYTE816> grant select on emp to tkyte; Grant succeeded.

scott@TKYTE816> grant select on dept to tkyte; Grant succeeded.

scott@TKYTE816> connect tkyte/tkyte Connected.

tkyte@TKYTE816> drop table emp; Table dropped.

tkyte@TKYTE816> drop table dept; Table dropped.

tkyte@TKYTE816> create or replace view emp as

2 select /*+ use hash(emp) */ * from acott.emp emp

3 / View created.

tkyte@TKYTE816> create or replace view dept as

2 select /*+ use hash(dept) */ * from scott.dept dept

3 / View created.

Теперь сгенерируем хранимый шаблон для запроса в приложении:

tkyte@TKYTE816> create or replace outline my outline

2 for category my category

3 on select * from emp, dept where emp.deptno = dept.deptno; Outline created.

Итак, в пользовательской схеме TKYTE имеются представления с подсказками для базовых объектов, и мы создали в этой схеме хранимый шаблон для требуемого запроса. При желании теперь можно удалить представления. У нас уже есть все необходимое: хранимый шаблон, использующий соединения хешированием. Теперь, снова зарегистрировавшись как SCOTT, мы делаем следующее:

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

scott@TKYTE816> connect scott/tiger

scott@TKYTE816> alter session set use stored outlines=my category; Session altered.

scott@TKYTE816> set autotrace traceonly explain

scott@TKYTE816> select * from emp, dept where emp.deptno = dept.deptno; Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=67 Bytes=7839)

1 0 HASH JOIN (Cost=3 Card=67 Bytes = 7 839)

2 1 TABLE ACCESS (FULL) OF EMP (Cost=l Card=82 Bytes=7134)

3 1 TABLE ACCESS (FULL) OF DEPT (Cost=l Card=82 Bytes=2460)

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

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

Управление шаблонами

Сейчас мы детально рассмотрим средства управления шаблонами: операторы ЯОД (ALTER и DROP) или подпрограммы стандартного пакета OUTLN PKG.

Операторы ЯОД

Кроме оператора CREATE, для управления шаблонами запросов можно также использовать операторы ALTER и DROP. Оператор ALTER позволяет:

переименовать (RENAME) хранимый шаблон;

пересоздать (REBUILD) план для хранимого шаблона;

изменить (CHANGE) категорию хранимого шаблона. Оператор DROP удаляет указанный по имени хранимый шаблон.

Оператор ALTER OUTLINE

Оператор ALTER имеет три версии, и мы рассмотрим их поочередно. Чтобы разобраться, как работает этот оператор, создадим хранимый шаблон, а потом будем изменять его различными способами:

tkyte@TKYTE816> create or replace outline my outline 2 for category my category