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

Итак, теперь текст запроса имеется, и все готово для генерации хранимого шаблона с планом, который мы хотим использовать для запроса. Интересно отметить, что текст отличается от использовавшегося в PL/SQL-коде: все символы переведены в верхний регистр. Он отличается и от теста запроса в отчете TKPROF: там использовались символы новой строки. Поскольку использование хранимого шаблона зависит от точного совпадения запросов, я собираюсь продемонстрировать, как проще всего изменить набор подсказок, связанных с перехваченным шаблоном. Обратите внимание, как в представленных выше результатах запроса к представлению USER OUTLINES мы выбрали столбцы NAME и SQL TEXT, чтобы можно б1ло определить интересующий нас запрос и найти имя соответствующего хранимого шаблона, SYS OUTLINE 0104120951400008.

Итак, можно задать цель оптимизации FIRST ROWS, пересоздать шаблон с соответствующим именем, и все:

tkyte@TKYTE816> alter session set optimizer goal=first rows

2 /

Session altered.

tkyte@TKYTE816> alter outline SYS OUTLINE 0104120951400008 rebuild

2 /

Outline altered.

tkyte@TKYTE816> alter session set optimizer goal=choose; Session altered.

Мы начали с установки режима оптимизации FIRST ROWS, вместо CHOOSE. Известно, что если выполнять запрос в режиме оптимизации FIRST ROWS, будет выбран необходимый план (именно такой сценарий мы подготовили для демонстрации, в реальной же ситуации это надо определить с помощью нескольких итераций тестирования и настройки). Вместо повторного выполнения запроса мы просто перестроим (REBUILD) шаблон - при перестройке будет сгенерирован план, соответствующий текущей среде.

Теперь, чтобы убедиться, что шаблоны работают, надо включить использование соответствующей категории шаблонов. В целях демонстрации мы будем использовать оператор ALTER SESSION в интерактивном сеансе, но чтобы делать это автоматически и без изменения кода приложения, можно использовать триггер ON LOGON для включения поддержки шаблонов при регистрации пользователя. Чтобы проверить, используется ли шаблон, придется повторно запустить приложение:

tkyte@TKYTE816> alter session set optimizer goal=choose; Session altered.

tkyte@TKYTE816> alter session set USE STORED OUTLINES = hr application; Session altered.

tkyte@TKYTE816> alter session set sql trace=true; Session altered.

Глава 11

tkyte@TKYTE816> exec show emps 7369,SMITH

7934,MILLER

PL/SQL procedure successfully completed.

Mi восстановили стандарттЕй режим оптимизации, заставили сервер Oracle использовать храним1е шаблоне! из категории HR APPLICATION и еще раз в1полнили приложение. Теперь в отчете утилите! TKPROF можно обнаружить следующее:

SELECT ENAME,EMPNO FROM

EMP WHERE EMPNO > 0

Rows Row Source Operation

14 TABLE ACCESS BY INDEX ROWID EMP

15 INDEX RANGE SCAN (object id 28094)

Это доказывает, что использовался нужн1й план. При выполнении приложения оператор ALTER SESSION SET USE STORED OUTLINE подключил соответствующие хранимые шаблон!. Интересующий нас запрос будет оптимизирован с использованием запомненн1х подсказок, сгенерированн1х в режиме оптимизации FIRST ROWS. Остальные запросы приложения будут оптимизироваться так же, как раньше.

Средство разработки

Предположим, создается новое приложение, которое необходимо передать большому количеству пользователей. Контролировать среду базы данных у пользователей практически невозможно - приложение может оказаться единственным или одним из десятка других работающих приложений. У используем1х серверов могут б1ть разные значения параметров инициализации, влияющих на работу оптимизатора, например DB BLOCK BUFFERS, DB FILE MULTIBLOCK READ COUNT, HASH MULTIBLOCK IO COUNT, OPTIMIZER GOAL, HASH JOIN ENABLED и т.п. Может включена или отключена возможность параллельного в1полнения запросов. Сервер на большой машине может иметь как большую, так и маленькую область SGA. Приложение может работать на сервере версии 8.1.6.1, 8.1.7 или 8.1.6.2. И так да-

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

лее. На генерируемый оптимизатором план выполнения запроса может влиять множество факторов.

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

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

sys@TKYTE816> create or replace trigger tkyte logon

2 after logon on database

3 begin

4 if (user = TKYTE) then

5 execute immediate

6 alter session set create stored outlines = KillerApp;

7 end if;

8 end;

Trigger created.

Триггер был создан от имени пользователя SYS, который стандартно имеет все необходимые для создания такого триггера привилегии.

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

alter session set use stored outlines = KillerApp;

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