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

В шаблонах используется простое сравнение текста

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

При работе с шаблонами сервер Oracle ограничивается сопоставлением текста оператора. Не делается попытка проверить, что базовые объекты - одни и те же. Этим мы воспользовались в предыдущем разделе: создали схему, в ней - представления с подсказками, имена которых совпадают с именами базовтх таблиц в другой схеме. Затем мы сгенерировали шаблоны для запросов к этим представлениям. На эти шаблоны существенно повлияли заданные подсказки. При выполнении точно такого же запроса в исходной схеме с таблицами (а не представлениями), сервер Oracle использовал хранимый шаблон, хотя базовые таблицы б1ли другими. Именно так и было задумано. Намеренно было сделано так, чтобы для запросов с одинаковым текстом использовались одни и те же наборы подсказок.

Следует учитывать, что при сравнении текста требуется полное совпадение строк. Пробелы, символы табуляции, символы новой строки, регистр символов, - все имеет значение. Эти два запроса:

select * from dual; SELECT * FROM DUAL;

различны с точки зрения хранимых шаблонов.

Шаблоны по умолчанию хранятся в табличном пространстве SYSTEM

По умолчанию шаблоны хранятся в табличном пространстве SYSTEM. Если планируется интенсивное использование хранимых шаблонов, стоит перенести их в другое табличное пространство. Как это сделать, было описано в разделе, посвященном пользователю OUTLN. Таблица подсказок может очень быстро разрастаться (в нашем примере для простого запроса select * irom all objects было сгенерировано более 100 строк в таблицу подсказок). Если разрастание табличного пространства SYSTEM нежелательно, рекомендуется перенести объекты пользователя OUTLN в другое табличное пространство.

Раскрытие условий OR

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

select * from T where x = 5 or x = 6;

Глава 11

переписывается в виде:

select * from T where x = 5 Union All

select * from T where x = 6;

Механизм шаблонов не позволяет распространять подсказки на этот переписанный сервером план. Все хранимые подсказки будут применены к первой части запроса UNION ALL, но не к последующим частям. В файле readme, поставляемом с сервером Oracle ([ORACLE HOME]/rdbms/doc/README.txt), сказано:

7.4.2. Раскрытие условий OR

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

Решение проблемы:

Хранимые шаблоны, требующие раскрытия условий OR, можно выявить путем поиска в представлении USER OUTLINE HINTS подсказки с текстом, содержащим конструкцию USE CONCAT. Выполните следующий запрос:

SELECT NAME, HINT FROM USER OUTLINE HINTS WHERE HINT LIKE USE CONCAT%;

Любой шаблон, содержащий эту подсказку, надо либо удалить с помощью оператора DROP OUTLINE, либо перенести в неиспользуемую категорию с помощью следующей команды:

ALTER OUTLINE <имя шаблона> CHANGE CATEGORY TO <имя неиспользуемой категории>;

Производительность

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

Чтобы проверить это, я создал небольшой блок PL/SQL, который разбирает, выполняет и извлекает строки результатов для х простых запросов (select * from T1, где Tl -

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

64 1

таблица из одной строки и одного столбца). При этом основное время уходит на разбор запросов. Для этого я выполнил следующий блок, создающий 100 таблиц:

tkyte@TKYTE816> begin

2 3 4

10 11 12

immediate drop table tjji;

for i in 1 .. 100 loop begin

execute exception

when others end;

execute immediate create table execute immediate insert into t end loop;

then null;

tjjijj (dummy jjijj values (

char(l)) ; x ) ;

end;

PL/SQL procedure successfully completed.

Итак, после создания 100 таблиц с именами от Т1 до Т100 я выполнил блок кода, по сути только разбирающий SQL-оператор и помещающий его в разделяемый пул. Мы хотим оценить влияние шаблонов, а не разбора запроса:

tkyte@TKYTE816> declare l tmp char(l);

2 select * into l tmp from tl;

3 select * into l tmp from t2;

4 select * into l tmp from t3;

l start number : =

dbms utility.get time; begin

99 select * into l tmp from t98;

100 select * into l tmp from t99;

101 select * into l tmp from t100;

102 dbms output.put line(round((dbms utility.get time-l start)/100,

2 ) jj seconds);

103 end;

104 /

.89 seconds

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

tkyte@TKYTE816> declare l tmp char(1); l start number :=

dbms utility.get time; begin

2 select * into l tmp from tl;

3 select * into l tmp from t2;

4 select * into l tmp from t3;

into l tmp from t98; into l tnp from t99; into l tmp from t100;

99 select

100 select

101 select