|
2004 г.
Использование хранимых шаблонов (stored outlines) при настройке приложений с недоступным исходным кодом.
Творошенко Сергей,
Oracle Certified Professional DBA,
Источник:
http://sertss.narod.ru/articles/using_outlines.html
Oracle Magazine RE ,Январь/Февраль 2004
В данной статье описывается один из многих аспектов использования
хранимых шаблонов при настройке производительности приложений
использующих СУБД Oracle. В частности, приводится пример Пример рассматривается в риторике чаще всего в контексте доказательств и аргументов. Для Квинтилиана пример является одним из дополняющих, наглядных доводов к высказыванию, либо упоминанием полезного, настоящего или якобы существующего образца убеждения того, что определено тобой одним. Правда, в отличие от доказательств, связь с предметом обсуждения должна быть установлена прежде автором или оратором. их
использования для настройки приложений, к исходному коду которых,
группа сопровождения не имеет доступа. Приводимый пример был испытан в
Oracle 9i release«Release» (англ. Выпуск)— восьмой студийный альбом британской поп-группы Pet Shop Boys. По настроению он напоминает другой альбом группы— «Behaviour». В записи альбома принимал участие Джонни Марр, который сотрудничал с Pet Shop Boys в 1990 году при записи вышеупомянутого «Behaviour». В Великобритании альбом занял 7-е место. 2. Для выполнения SQL выражений использовалась
приложение SQL*Plus.
В практике сопровождения довольно часто приходится сталкиваться с
задачей настройки производительности приложений, доступ к коду которых
не представляется возможным. А производительность приложения сильно
страдает из-за нескольких SQL выражений имеющих подсказки оптимизатору
(optimizer hints) используя которые оптимизатор выбирает неоптимальный
план выполнения SQL выражения. Особенно данная проблема имеет место при
переходе организации на новые версии Oracle при использовании уже
зарекомендовавших себя с хорошей стороны приложений, к которым все уже
привыкли, однако вдруг начавших "жутко тормозить" на новой версии
OracleOracle (Oracle Corporation)— американская корпорация, крупнейший в мире разработчик программного обеспечения для организаций, крупный поставщик серверного оборудования.. Перехватив поток SQL выражений группа ИТ отдела определяет, что
причина низкой производительности - подсказки, удалением которых можно
добиться восстановления быстродействия. Однако сотрудники ИТ отдела не
имеют доступа к исходному коду приложения и поэтому убрать "хинты" не
представляется возможным.
Решить подобные проблемы можно используя хранимые шаблоныШаблон в технике— пластина (лекало, трафарет ит.п.) с вырезами, по контуру которых изготовляются чертежи или изделия либо инструмент для измерения размеров..
Русскоязычное описание использования хранимых шаблонШаблон в технике— пластина (лекало, трафарет ит.п.) с вырезами, по контуру которых изготовляются чертежи или изделия либо инструмент для измерения размеров.ов для стабилизации
плана выполнения SQL выражений хорошо представлено в книге Тома Кайта
"Oracle для профессионалов" (ThomasTHOMAS— немецкая компания, производитель электробытовой техники, один из ведущих OEM-поставщиков. Компания основана в 1900 году инженером Робертом Томасом. В настоящее время богатое традициями семейное предприятие управляется уже четвёртым поколением. Производство находится только на территории Германии. В основном THOMAS производит пылесосы и центрифуги для отжима белья. Kyte "Expert One on One: Oracle"),
в переводе В. Кравчука, а так же на сайте (http://ln.com.ua/~openxs/projects/oracle/)
автора перевода.
Итак, перейдем к примеру. Начнем с постановки задачи. Допустим мы имеем
SQL выражение содержащее подсказку /*+ RULE*/. Наша задачаЗадача— проблемная ситуация с явно заданной целью, которую необходимо достичь; в более узком смысле задачей также называют саму эту цель, данную в рамках проблемной ситуации, то есть то, что требуется сделать. В первом значении задачей можно назвать, например, ситуацию, когда нужно достать предмет, находящийся очень высоко; второе значение слышно в указании: «Ваша задача — достать этот предмет». Несколько более жёсткое понимание «задачи» предполагает явными и определёнными не только цель, но и условия задачи, которая в этом случае определяется как осознанная проблемная ситуация с выделенными условиями (данным) и требованием (целью). Ещё более узкое определение называет задачей ситуацию с известным начальным состоянием системы и конечным состоянием системы, причём алгоритм достижения конечного состояния от начального известен (в отличие от проблемы, в случае которой алгоритм достижения конечного состояния системы не известен). - избавиться
от этой подсказки.
Подготовим окружениеОкружение— изоляция определённой группировки противника от остальных его войск в целях уничтожения или пленения.. Создадим таблицу, составной индекс и соберем
статистику. Таблица представляет собой некий словарьСловарь— книга, информация в которой упорядочена c помощью разбивки на небольшие статьи, отсортированные по названию или тематике. Различают энциклопедические и лингвистические словари. и содержит список
словарей, каждый из которых, содержит некий список сущностей:
dict_id - идентификатор словаря
ItemID - порядковый номер сущности
Name - имя сущности
SQL> create table t1 (
2 dict_id int,
3 itemid int,
4 name varchar2(100)
5 );
ТаблицаТаблица (из лат.tabula «доска»)— способ передачи содержания, заключающийся в организации структуры данных, в которой отдельные элементы помещены в ячейки, каждой из которых сопоставлена пара значений— номер строки и номер колонки. Таким образом, устанавливается смысловая связь между элементами, принадлежащими одному столбцу или одной строке. создана.
SQL> create index indx_t1
2 on t1(dict_id, itemid);
ИндексИндекс (лат.index — список, реестр, указатель) — число, буквы или другая комбинация символов, указывающая место элемента в совокупности или характеризующая состояние некоторой системы, например показатель активности, производительности, развития, изменения чего-либо. создан.
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 из нашего
шаблона. Вот такие дела :). Не забывайте собирать статистику!
|
