В данной статье описывается один из многих аспектов использования хранимых шаблонов (stored outlines) при настройке производительности приложений, работающих с СУБД 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));
Таблица создана.
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, впервые представленный в 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;
строки не выбраны
На данном этапе важно отметить, что, в отличие от Oracle 8i, где сравнение SQL-запросов с их аналогами в хранимых шаблонах происходит посимвольно, в Oracle 9i оно не имеет столь жесткого критерия. Поэтому, скажем, запрос:
select /*+ rule*/ *
from t1
where itemid = :itemid;
с точки зрения использования хранимых шаблонов в Oracle 9i, аналогичен запросу:
select /*+ RULE*/ * from t1 where
ItemID = :itemid;
тогда как в Oracle 8i это было бы неверно.
Итак, продолжим. Отключим автоматическое создание хранимых шаблонов:
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 строк выбрано.
В вышеупомянутых русскоязычных источниках описывается несколько приемов "обмана" стоимостного оптимизатора (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'
Как видим ничего не изменилось, всё тот же полный просмотр таблицы. Активируем наш хранимый шаблон:
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)
Цель достигнута.
Изменив подобным образом все проблемные запросы и активировав соответствующую категорию полученных хранимых шаблонов, можно добиться прежней производительности приложения.
"А что же будет, если мы таким образом подменим план, который был сделан на основе совершенно другой таблицы, либо, скажем, удалим индекс таблицы в существующем примере?" - задаст вопрос проникшийся идеей читатель. Ответ прост - стоимостной оптимизатор выдаст план так, как будто бы хранимого шаблона и нет вовсе.
Проводя исследования в данном направлении, я заметил интересную особенность. Вот что я сделал. Я удалил индекс таблицы t1. Проверил реакцию оптимизатора, - он проигнорировал хранимый шаблон (как и было отмечено выше). Вновь создал индекс с тем же именем и на те же поля. И вот тут - самое интересное! Так как я не пересобирал статистическую информацию о таблице после создания индекса, выполнение запроса "без плюсика" привело к полному просмотру таблицы, а выполнение нашего "проблемного" запроса показало прекрасный план, взятый стоимостным оптимизатором из нашего шаблона. Вот такие дела :). Не забывайте собирать "статистику"!