2004 г.

Использование хранимых шаблонов (stored outlines) при настройке приложений с недоступным исходным кодом.

Творошенко Сергей, Oracle Certified Professional DBA,
Источник: http://sertss.narod.ru/articles/using_outlines.html
Oracle Magazine RE ,Январь/Февраль 2004

В данной статье описывается один из многих аспектов использования хранимых шаблонов при настройке производительности приложений использующих СУБД Oracle. В частности, приводится пример их использования для настройки приложений, к исходному коду которых, группа сопровождения не имеет доступа. Приводимый пример был испытан в Oracle 9i release 2. Для выполнения SQL выражений использовалась приложение SQL*Plus.

В практике сопровождения довольно часто приходится сталкиваться с задачей настройки производительности приложений, доступ к коду которых не представляется возможным. А производительность приложения сильно страдает из-за нескольких SQL выражений имеющих подсказки оптимизатору (optimizer hints) используя которые оптимизатор выбирает неоптимальный план выполнения SQL выражения. Особенно данная проблема имеет место при переходе организации на новые версии Oracle при использовании уже зарекомендовавших себя с хорошей стороны приложений, к которым все уже привыкли, однако вдруг начавших "жутко тормозить" на новой версии Oracle. Перехватив поток SQL выражений группа ИТ отдела определяет, что причина низкой производительности - подсказки, удалением которых можно добиться восстановления быстродействия. Однако сотрудники ИТ отдела не имеют доступа к исходному коду приложения и поэтому убрать "хинты" не представляется возможным.

Решить подобные проблемы можно используя хранимые шаблоны.

Русскоязычное описание использования хранимых шаблонов для стабилизации плана выполнения SQL выражений хорошо представлено в книге Тома Кайта "Oracle для профессионалов" (Thomas Kyte "Expert One on One: Oracle"), в переводе В. Кравчука, а так же на сайте (http://ln.com.ua/~openxs/projects/oracle/) автора перевода.

Итак, перейдем к примеру. Начнем с постановки задачи. Допустим мы имеем SQL выражение содержащее подсказку /*+ RULE*/. Наша задача - избавиться от этой подсказки.

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

  dict_id - идентификатор словаря
  ItemID - порядковый номер сущности
  Name - имя сущности

SQL> create table t1 (
  2    dict_id int,
  3    itemid int,
  4    name varchar2(100)
  5  );

Таблица создана.

SQL> create index indx_t1
  2  on t1(dict_id, itemid);

Индекс создан.

SQL> analyze table t1 estimate statistics;

Таблица проанализирована.


Включим показ плана выполнения SQL выражений.

SQL> set autotrace on explain

Создадим переменную привязки (bind variable), проинициализируем её и запустим наш оптимизируеммый запрос:

 
SQL> var itemid number
SQL> begin :itemid:= 0; end;
  2  /

Процедура PL/SQL успешно завершена.

SQL> select /*+ rule*/ *
  2  from t1
  3  where itemid = :itemid;

строки не выбраны

План выполнения
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (FULL) OF 'T1'

Процедура PL/SQL успешно завершена.

План выполнения показывает полное сканирование таблицы (full table scan, FTS).

Выполним тот же запрос, но "выключив" подсказку (уберем "+"):

SQL> select /* rule*/ *
  2  from t1
  3  where itemid = :itemid;

строки не выбраны

План выполнения
----------------------------------------------------------
   0    SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=78)
   1  0   TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes=78)
   2  1     INDEX (SKIP SCAN) OF 'INDX_T1' (NON-UNIQUE)


План выполнения показывает доступ к данным таблицы посредством индекса 'INDX_T1' . Доступ в пределах индекса осуществляется как  INDEX SCIP SCAN ACCESS, впервые представленный в Oracle9i. Исходим из того, что набор данных в таблице таков, что, этот план намного лучше первого. К нему и будем стремится.

Для дальнейшей работы нам потребуются следующие системные привилегии:

• create any outline
• alter any outline
• drop any outline

 Выполним следующее:

SQL> alter session set create_stored_outlines = healthy_plans;

Сеанс изменен.


В этом выражении HEALTHY_PLANS  - это имя категории, с которой будут связаны наши шаблоны.

Далее, выполним поочередно, два запроса. Первый - "проблемный", тот который мы собираемся оптимизировать(с подсказкой /*+ RULE*/). Второй, тот который мы прооптимизировали, "отключив" подсказку. Однако перед выполнением запросов нам необходимо отключить отображение планов выполнения запросов. Это нужно, чтобы Oracle перехватил, только наши два запроса (иначе будут перехвачены обращения к таблице PLAN_TABLE, содержащей планы выполнения):


SQL> set autotrace off
SQL> select /*+ rule*/ *
  2  from t1
  3  where itemid = :itemid;

строки не выбраны

SQL>
SQL> select /* rule*/ *
  2  from t1
  3  where itemid = :itemid;

строки не выбраны


На данном этапе важно отметить, что в отличие от Oracle8i, где сравнение SQL запросов с их аналогами в хранимых шаблонах происходит посимвольно, в Oracle9i оно не имеет столь жесткого критерия. Поэтому, скажем запрос:

select /*+ rule*/ *
from t1
where itemid = :itemid;

с точки зрения использования хранимых шаблонов в Oracle 9i, аналогичен запросу:

select /*+ RULE*/ * from t1 where ItemID = :itemid;

тогда, как в Oracle8i это было бы неверно.
Итак, продолжим.
Отключим автоматическое создание хранимых шаблонов:

SQL> alter session set create_stored_outlines = false;

Сеанс изменен.

Смотрим, что получилось(для удобочитаемости, установим размер буфера отображения LONG полей равным 15):

SQL> set long 15
SQL> select ol_name, sql_text from outln.ol$
  2  where category = 'HEALTHY_PLANS';

OL_NAME                        SQL_TEXT
------------------------------ ---------------
SYS_OUTLINE_031028123825493    select /*+ rule
SYS_OUTLINE_031028123825813    select /* rule*

Полученным шаблонам придадим информативные имена:

SQL> alter outline SYS_OUTLINE_031028123825493 rename to with_plus;

Вариант изменен.

SQL> alter outline SYS_OUTLINE_031028123825813 rename to without_plus;

Вариант изменен.

SQL> select ol_name, sql_text from outln.ol$
  2  where category = 'HEALTHY_PLANS';

OL_NAME                        SQL_TEXT
------------------------------ ----------------------------
WITH_PLUS                      select /*+ rule
WITHOUT_PLUS                   select /* rule*


Подсказки хранимых шаблонов находятся в таблице ol$hints схемы OUTLN:

SQL> select ol_name, hint#, hint_text from outln.ol$hints
  2  where category = 'HEALTHY_PLANS'
  3  order by ol_name desc, hint#;

OL_NAME      HINT#     HINT_TEXT
------------ -------  -----------------------
WITH_PLUS      1      NO_EXPAND
WITH_PLUS      2      ORDERED
WITH_PLUS      3      NO_FACT(T1)
WITH_PLUS      4      FULL(T1)
WITH_PLUS      5      NOREWRITE
WITH_PLUS      6      NOREWRITE
WITH_PLUS      7      RULE
WITHOUT_PLUS   1      NO_EXPAND
WITHOUT_PLUS   2      ORDERED
WITHOUT_PLUS   3      NO_FACT(T1)
WITHOUT_PLUS   4      INDEX(T1 INDX_T1)
WITHOUT_PLUS   5      NOREWRITE
WITHOUT_PLUS   6      NOREWRITE

13 строк выбрано.


В вышеупомянутых русскоязычных источниках, описываются несколько приемов "обмана" CBO (Cost Based Optimizer), прибегнув к которым мы "заставим" CBO строить нужный нам план. В этой статье я предложу вам еще один подобный прием.

Всё достаточно просто. Что нам нужно? Нам нужно чтобы при выполнении запроса с "хинтом" оптимизатор использовал подсказки из запроса с "отключенным хинтом". Итак, мы просто подменяем подсказки. Делаем это путем изменения имен хранимых шаблонов:


SQL> update outln.ol$
  2  set ol_name = 'RIGHT_PLAN'
  3  where ol_name = 'WITH_PLUS';

1 строка обновлена.

SQL> update outln.ol$hints
  2  set ol_name = 'RIGHT_PLAN'
  3  where ol_name = 'WITHOUT_PLUS';

6 строк обновлено.

SQL>
SQL> update outln.ol$hints
  2  set ol_name = 'WITHOUT_PLUS'
  3  where ol_name = 'WITH_PLUS';

7 строк обновлено.


Ставший ненужным шаблон WITHOUT_PLUS - удаляем (фиксация предыдущих изменений нам не потребуется, т.к. SQL выражение DROP относится к числу изменяющих словарь данных Oracle (Data Definition Language, DDL), что вызывает неявную фиксацию предыдущей транзакции):

SQL> drop outline WITHOUT_PLUS;

Вариант удален.


Вот собственно и всё. Теперь можно проверить наш "проблемный" запрос. Для чистоты эксперимента, посмотрим еще раз на план нашего "проблемного" запроса:

SQL> set autotrace on explain
SQL> select /*+ rule*/ *
  2  from t1
  3  where itemid = :itemid;

строки не выбраны

План выполнения
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (FULL) OF 'T1'

Как видим ничего не изменилось, всё тот же FTS. Активируем наш хранимый шаблон:

SQL> alter session set use_stored_outlines=HEALTHY_PLANS;

Сеанс изменен.

И выполним еще раз всё тот же "проблемный" запрос:
 
SQL> select /*+ rule*/ *
  2  from t1
  3  where itemid = :itemid;

строки не выбраны

План выполнения
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE (Cost=2 Card=1 Bytes=78)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes=78)
   2    1     INDEX (SKIP SCAN) OF 'INDX_T1' (NON-UNIQUE)


Цель достигнута.

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

"А что же будет, если мы таким образом подменим план, который был сделан на основе совершенно другой таблицы, либо, скажем, удалим индекс таблицы в существующем примере?" - задаст вопрос проникшийся идеей читатель. Ответ прост - CBO выдаст план, так, как будто бы хранимого шаблона и нет вовсе.

Проводя исследования в данном направлении я наблюдал интересный момент. Вот что я сделал. Я удалил индекс в таблице t1. Проверил реакцию CBO, он проигнорировал хранимый шаблон (как я уже говорил). Вновь создал индекс с тем же именем и на те же поля. И вот тут самое интерестное! Т. к. я не пересобирал статистику на таблицу после создания индекса, то выполнение запроса "без плюсика", привело к FTS, а выполнение нашего "проблемного" запроса, показало прекрасный план, взятый CBO из нашего шаблона. Вот такие дела :). Не забывайте собирать статистику!