Глава 11

шированием, например) или добавить в запрос простую подсказку (например, /*+ RULE */ или / * + ALL ROWS */). запрос выполнялся бы намного быстрее. Стабилизация плана оптимизатора позволяет это сделать. Можно независимо создавать и сохранять оптимальные шаблоны запросов независимо от существующего приложения. Затем с помощью триггера базы данных ON LOGON (который позволяет выполнить фрагмент кода при регистрации пользователя на сервере) или аналогичного механизма заставить существующее приложение использовать хранимый шаблон запроса.

Предположим, с помощью SQL TRACE был получен SQL-оператор, выполняемый в приложении или при генерации отчета. Затем с помощью утилиты TKPROF был проанализирован соответствующий трассировочный файл, и оказалось, что запрос выполняется крайне медленно. Изучив руководство Designing and Tuningfor Performance и поэкспериментировав с описанными в нем подсказками, удалось выяснить, что при установке режима оптимизации FIRST ROWS запрос работает отлично, но если задать этот режим для всего приложения, общая производительность резко снижается. Итак, хотелось бы в режиме FIRST ROWS оптимизировать этот единственный запрос, а остальные выполнять в стандартном режиме CHOOSE. Обычно достаточно добавить подсказку /*+ FIRST ROWS */ для этого запроса. Но мы не можем этого сделать - запрос изменить нельзя. Можно, однако, выполнить оператор CREATE OUTLINE, как было показано ранее, чтобы создать шаблон с указанным именем, а затем поместить его в стандартный (DEFAULT) набор шаблонов или в определенную категорию шаблонов. Затем среда изменяется так, чтобы применялся сгенерированный таким образом план. Например, в данном случае можно было бы выполнить оператор, а затем создать шаблон плана запроса. После этого с помощью триггера ON LOGON можно включать использование этого хранимого шаблона при каждой регистрации пользователя приложения.

Создание шаблона может вызвать определенные сложности, поскольку текст запроса, для которого надо сгенерировать хранимый шаблон, должен с точностью до байта совпадать с текстом оператора в приложении. Ниже мы шаг за шагом продемонстрируем, как проще всего создать шаблон. Мы будем и дальше использовать запрос к таблице ЕМР - именно его необходимо выполнять в режиме оптимизации FIRST ROWS. Остальные операторы приложения должны выполняться в режиме оптимизации CHOOSE. Допустим, имеется приложение со следующим кодом:

tkyte@TKYTE816> create or replace procedure showemps

2 as

3 begin

4 for x in (select ename, empno

5 from emp

6 where empno > 0)

7 loop

8 dbms output.put line(x.enpno , x.ename);

9 end loop;

10 end;

11 /

Procedure created.

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

Теперь выполним эту процедуру при установленном режиме SQL TRACE и по отчету утилиты TKPROF выясним, что для запроса используется нежелательный план (подробнее о режиме SQL TRACE и утилите TKPROF, а также об их использовании в различных средах см. в главе 10). В данном случае мы просто используем оператор ALTER SESSION, поскольку речь идет о PL/SQL-процедуре, которую можно выполнить в среде SQL*Plus:

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

Session altered.

tkyte@TKYTE816> exec show emps 7 87 6,ADAMS

7521,WARD

PL/SQL procedure successfully completed.

Затем применим утилиту TKPROF к полученному файлу трассировки:

SELECT ENAME,EMPNO FROM

EMP WHERE EMPNO > О

Rows

Row Source Operation

14 TABLE ACCESS FULL EMP

Прежде всего, обратите внимание, что формат запроса в результатах TKPROF совершенно другой, чем в приложении. Это - побочный эффект того, как в языке PL/ SQL обрабатываются SQL-операторы: все статические SQL-операторы переписываются, и полученный запрос может выглядеть совсем не так, как запрос в исходном коде. При создании хранимого шаблона необходимо убедиться, что используется именно тот запрос, который поступил в базу данных, поскольку при стабилизации плана оптимизатора выполняется буквальное сравнение строк - должен использоваться такой же запрос, как в приложении, вплоть до пробелов, символов табуляции и новой строки. Однако ни текст в PL/SQL-процедуре, ни текст в отчете TKPROF не подходит! К счастью, можно использовать сами механизмы создания хранимых шаблонов для перехвата запроса, с которым надо работать. Мы включим неявную генерацию шаблонов, при этом текст SQL-запросов, поступающих в базу данных, будет перехвачен автоматически:

Глава 11

tkyte@TKYTE816> alter session set create stored outlines = hr application; Session altered. tkyte@TKYTE816> exec show emps

7876,ADAMS 7521,WARD

PL/SQL procedure successfully completed.

tkyte@TKYTE816> alter session set create stored outlines = FALSE;

Session altered.

tkyte@TKYTE816> set long 50000

tkyte@TKYTE816> select name, sql text

2 from user outlines

3 where category = HR APPLICATION

NAME SQL TEXT

SYS OUTLINE 0104120951400008 SELECT ENAME,EMPNO FROM EMP WHERE EMPNO > 0

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

Команда SET LONG использована для того, чтобы в среде SQL*Plus б]л показан весь SQL-запрос; по умолчанию будет в]даваться только первых 80 байт.

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

tkyte@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 use stored outlines =

hr application;

7 end if ;

8 end;

Trigger created.

Для создания триггера на событие LOGONнеобходимы привилегии CREATETRIGGER и ADMINISTER DATABASE TRIGGER. Кроме того, владельцу триггера необходимо наличие привилегии ALTER SESSION, предоставленной непосредственно, а не через

роль.

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