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

Процедура OUTLN PKG.DROP UNUSED

Эта процедура, не имеющая параметров, удаляет все не использованные шаблоны из всех категорий. Она находит шаблоны, в столбце USED для которых хранится значение UNUSED, и применяет к ним аналог оператора DROP OUTLINE имя шаблона. Вот пример использования этой процедуры:

tkyte@TKYTE816> exec outln pkg.drop unused;

PL/SQL procedure successfully completed.

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

Процедура OUTLN PKG.DROP BY CAT

Процедура DROP BY CAT удаляет все хранимые шаблоны указанной категории. Ее можно использовать, например, при тестировании для удаления категорий шаблонов, не оправдавших ожидания. Можно также использовать эту процедуру для удаления категории шаблонов по ходу работы. Это позволяет приложению использовать планы, генерируемые оптимизатором, вместо планов, сохраненных в шаблонах. Вот простой пример использования этой процедуры:

tkyte@TKYTE816> select category from user outlines;

CATEGORY

DICTIONARY PLANS

tkyte@TKYTE816> exec outln pkg.drop by cat(DICTIONARY PLANS); PL/SQL procedure successfully completed. tkyte@TKYTE816> select category from user outlines; no rows selected

Процедура OUTLN PKG.UPDATE BY CAT

Эта процедура позволяет переименовать существующую категорию или объединить категории. Синтаксис вызова этой процедуры простой:

outln pkg.update by cat(старое имя категории, новое имя категории) ;

Эта процедура работает следующим образом:

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

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

Глава 11

Если в столбце SQL TEXT хранимого шаблона в категории старое имя катего-рии хранится текст, совпадающий с текстом одного из шаблонов в категории но-вое имя категории, то шаблон в новую категорию не переносится.

Рассмотрим пример, демонстрирующий эту возможность:

tkyte@TKYTE816> create outline outline l

2 for category CAT 1

3 on select * from dual

4 / Outline created.

tkyte@TKYTE816> create outline outline 2

2 for category CAT 2

3 on select * from dual

4 / Outline created.

tkyte@TKYTE816> create outline outline 3

2 for category CAT 2

3 on select * from dual A

4 / Outline created.

Итак, имеется три хранимых шаблона в двух категориях. Для запроса SELECT * FROM DUAL есть два хранимых шаблона, а для запроса SELECT * FROM DUAL A - один. Посмотрим, что имеется сейчас:

tkyte@TKYTE816> select category, name, sqltext

2 from user outlines

3 order by category, name

CATEGORY NAME SQL TEXT

CAT 1 OUTLINE 1 select * from dual

CAT 2 OUTLINE 2 select * from dual

CAT 2 OUTLINE 3 select * from dual A

Как видите, в категории САТ 1 - 1 шаблон, а в категории САТ 2 - 2 шаблона. Более того, четко видно, что в категории САТ 2 есть шаблон с таким же значением в столбце SQL TEXT, что и в шаблоне в категории САТ 1. Теперь объединим категории:

tkyte@TKYTE816> exec outln pkg.update by cat(CAT 2, САТ 1) ; PL/SQL procedure successfully completed.

tkyte@TKYTE816> select category, name, sql text

2 from user outlines

3 order by category, name

CATEGORY NAME SQL TEXT

CAT 1 OUTLINE 1 select * from dual

CAT 1 OUTLINE 3 select * from dual A

CAT 2 OUTLINE 2 select * from dual

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

Как видите, шаблон из категории САТ 2 для запроса, не входящего в категорию САТ 1, б]л перенесен. Хранимый шаблон для дублирующегося запроса, однако, не перенесен. Дело в том, что все шаблоны должны быть уникальны по столбцу NAME и паре столбцов (CATEGORY, SIGNATURE). В пределах категории значения в столбце SQLTEXT должны быть уникальны. Это обеспечивается путем генерации уникальной сигнатуры для значения SQLTEXT. Если необходимо перенести шаблон OUTLINE 2 из категории САТ 2 в категорию САТ 1, придется удалить шаблон OUTLINE 1 из категории САТ 1 перед выполнением процедуры UPDATE BY CAT.

tkyte@TKYTE816> drop outline outline l;

Outline dropped.

tkyte@TKYTE816> exec outln pkg.update by cat( PL/SQL procedure successfully completed.

tkyte@TKYTE816> select category, name, sql text

2 from user outlines

3 order by category, name

CAT 2\ CAT 1

CATEGORY

NAME

SQL TEXT

CAT 1 CAT 1

00TLINE 2 OUTLINE 3

select select

from dual from dual

Проблемы

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

Имена шаблонов и регистр символов

В пакете OUTLN PKG есть две процедуры, получающие имя категории шаблонов или имя шаблона. Поскольку параметр передается как строка, надо учитывать регистр передаваемых символов. Сервер Oracle по умолчанию хранит имена объектов в верхнем регистре, но если применяются идентификаторы в кавычках, можно использовать смешанный регистр. Необходимо убедиться, что регистр символов в имени категории, передаваемом процедуре DROP BY CAT, например, соответствует регистру символов имени категории, хранящегося в словаре данных. Следующий пример демонстрирует потенциальную проблему:

tkyte@TKYTE816> create or replace outline my outline

2 for category my category

3 on select * from dual

Outline created.