Стабилизация плана оптимизатора в Oracle 8i/9i

2004 г.

Стабилизация плана оптимизатора в Oracle 8i/9i

Джонатан Льюис, www.jlcomp.demon.co.uk
Перевод Валерия Кравчука

Узнайте, как с помощью "хранимых шаблонов" (stored outlines) можно повысить производительность приложения, даже если нельзя менять его исходный код, систему индексации и параметры конфигурации системы...

Инструментальные средства: Для упрощения экспериментов, в статье рассматривается только простой SQL- и PL/SQL-код, выполняемый в сеансе SQL*Plus. Читателю необходимы будут привилегии, которые типичным конечным пользователям обычно не предоставляют, но, в остальном, понадобится только знание основ языка SQL. Статья начинается с описания возможностей версии Oracle 8i, но затем автор переходит к Oracle 9i, в котором появилось ряд дополнительных возможностей генерации хранимых шаблонов и работы с ними.

Черный ход в черный ящик

Если вы - АБД, отвечающий за работу приложения стороннего производителя на базе СУБД Oracle, то, наверняка, испытывали разочарование, обнаружив в библиотечном кэше пару крайне медленно работающих и пожирающих массу ресурсов SQL-операторов, которые очень легко можно настроить, - если бы только можно было добавить пару подсказок оптимизатору в исходный код.

Начиная с Oracle 8.1, вам больше не надо переписывать SQL-операторы, чтобы добавить подсказки - можно передать оптимизатору подсказки, не меняя код. Эта возможность известна как использование хранимых шаблонов (Stored Outlines) или стабилизация плана оптимизатора (Plan Stability), причем, сонвная ее идея весьма проста: вы сохраняете в базе данных информацию типа: "если встречается SQL-оператор типа XXX, то перед его выполнением надо вставить вот такие подсказки в следующих местах..."

Эта возможность дает вам три потенциальных преимущества. Прежде всего, можно оптимизировать ту самую пару пожирающих ресурсы операторов. Далее, если есть друшгие операторы, которые сервер Oracle дольше оптимизирует, чем выполняет, можно сэкономить время и уменьшить количество конфликтов на стадии оптимизации. Наконец, можно использовать новый параметр конфигурации cursor_sharing, не рискуя потерять оптимальные пути выполнения операторов.

Есть несколько проблем в Oracle 8i, которые при этом придется как-то обходить (они практически решены в Oracle 9i), но, в общем случае, использовать хранимые шаблоны очень легко. В этой статье описаны некоторые возможности, которые при этом открываются.

Обзор основных возможностей

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

Мы увидим, как перехватить выполняемые SQL-операторы и особенности текущего пути их выполнения в базе данных, подобрать подсказки, повышающие производительность этих SQL-операторов, а затем заставить сервер Oracle использовать эти подсказки при дальнейшем выполнении этих SQL-операторов.

По ходу демонстрации мы создадим пользователя, таблицу в схеме этого пользователя и процедуру, обращающуюся к этой таблице, - но, ради развлечения, мы применим к процедуре утилиту wrap, чтобы ее код нельзя было увидеть. Затем мы поставим себе задачу настройки производительности SQL-операторов, выполняемых этой процедурой.

При демонстрации мы предполагаем, что все необходимое для использования хранимых шаблонов было установлено автоматически при создании базы данных.

Предварительные действия

Создаем пользователя с привилегиями: create session, create table, create procedure, create any outline и alter session. Подключаемся от имени этого пользователя и выполняем следующие операторы для создания таблицы:

create table so_demo (
	n1	number,
	n2	number,
	v1	varchar2(10)
);

insert into so_demo values (1,1,'One');

create index sd_i1 on so_demo(n1);
create index sd_i2 on so_demo(n2);

analyze table so_demo compute statistics;

Теперь необходимо создать процедуру, обращающуюся к этой таблице. Создаем сценарий c_proc.sql, содержащий следующий код:

create or replace procedure get_value (
	i_n1	in	number,
	i_n2	in	number,
	io_v1	out	varchar2
)
as
begin
	select	v1
	into	io_v1
	from	so_demo
	where	n1 = i_n1
	and	n2 = i_n2;
end;
/

Можно, конечно, просто выполнить этот сценарий для построения процедуры, но, для большей эффектности, давайте выполним следующую команду в окне командной строки операционной системы:

wrap iname=c_proc.sql

В ответ вы должны получить:

Processing c_proc.sql to c_proc.plb

Вместо выполнения сценария c_proc.sql для генерации процедуры, выполните неочевидный сценарий c_proc.plb, и вы обнаружите, что никаких следов использованного SQL-оператора в представлении user_source нет.

Что хочет сделать приложение?

Теперь, когда исследуемое приложение готово, можно запустить его, включив, пожалуй, предварительно sql_trace, чтобы понять, что происходит. Неудивительно, если окажется, что SQL-оператор выполняет полный просмотр таблицы для получения необходимых данных.

В этом небольшом тесте полный просмотр, скорее всего, является самым эффективным способом выполнения запроса, но, предположим, мы доказали, что более высокая производительность достигается, когда сервер Oracle использует план на базе одностолбцовых индексов и опции and-equal. Как заставить сервер выполнять запрос именно так, не добавляя в код подсказки оптимизатору?

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

Что, по вашему, должно делать приложение?

Чтобы заставить сервер Oracle делать то, что нужно нам, необходимо пройти три этапа:

Начинаем новый сеанс и повторно выполняем процедуру, потребовав предварительно от сервера Oracle перехватывать каждый поступающий SQL-оператор вместе с информацией о плане его выполнения. Эти "планы" станут нашим первым примером хранимых шаблонов.
Создаем более подходящие шаблоны для всех проблемных SQL-операторов и заменяем "плохие" хранимые шаблоны хорошими.
Начинаем новый сеанс и требуем от сервера Oracle начать использовать новые хранимые шаблоны вместо обычных методов оптимизации при обработке соответствующих SQL-ператоров. Затем снова выполняем процедуру.

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

Итак, начнем сеанс и выполним следующую команду:

alter session set create_stored_outlines = demo;

Затем выполним небольшой анонимный блок, вызывающий процедуру, например:

declare
	m_value	varchar2(10);
begin
	get_value(1, 1, m_value);
end;
/

Теперь прекращаем сбор планов выполнения (иначе несколько следующих SQL-операторов тоже окажутся в таблицах хранимых шаблонов, что усложнит выполнение дальнейших действий).

alter session set create_stored_outlines = false;

Для просмотра результатов выполненных действий можно сделать запросы к представлениям, позволяющим получить детальную информацию о шаблонах, автоматически созданных и сохраненных сервером Oracle:

select name, category, used, sql_text
from user_outines
where category = 'DEMO';

NAME                           CATEGORY                       USED
------------------------------ ------------------------------ -------
SQL_TEXT
---------------------------------------------------------------------
SYS_OUTLINE_020503165427311    DEMO                           UNUSED
SELECT V1   FROM SO_DEMO  WHERE N1 = :b1  AND N2 = :b2

select	name, stage, hint
from	user_outline_hints
where	name = ' SYS_OUTLINE_020503165427311';

NAME                                STAGE HINT
------------------------------ ---------- ------------------------------
SYS_OUTLINE_020503165427311             3 NO_EXPAND
SYS_OUTLINE_020503165427311             3 ORDERED
SYS_OUTLINE_020503165427311             3 NO_FACT(SO_DEMO)
SYS_OUTLINE_020503165427311             3 FULL(SO_DEMO)
SYS_OUTLINE_020503165427311             2 NOREWRITE
SYS_OUTLINE_020503165427311             1 NOREWRITE

Как видите, есть категория, demo, включающая всего лишь один хранимый шаблон, а посмотрев на sql_text для этого шаблона можно увидеть нечто похожее на SQL-оператор в исходном PL/SQL-коде, но не точно совпадающее с ним. Это существенно, поскольку сервер Oracle будет рассматривать возможность использования хранимого шаблона только если сохраненное значение sql_text очень близко к тексту SQL-оператора, который требуется выполнить. Фактически, в Oracle 8i тексты должны совпадать буквально, и первоначально это было большой проблемой при экспериментах с хранимыми шаблонами.

По листингу видно, что хранимые шаблоны представляют собой набор подсказок, описывающих действия сервера Oracle, который он выполнил (или должен выполнить) при выполнении соответствующего SQL-оператора. Данный план использует полный просмотр таблицы. Не правда ли, сервер Oracle используем немало подсказок, чтобы гарантировать выполнение настолько простого действия, как полный просмотр таблицы?..

Обратите внимание, что хранимый шаблон всегда относится к определенной категории, в данном случае, к категории demo, которую мы задали в исходной команде alter session. Если в исходной команде просто указать true вместо demo, хранимые шаблоны окажутся в категории по имени default.

Хранимые шаблоны тоже имеют имена, и эти имена должны быть уникальными во всей базе данных. Имя шаблона не может совпадать с именем другого шаблона, даже сгенерированного другим пользователем. Фактически, у шаблонов нет владельцев, - есть только создатели. Если кто-то создал хранимый шаблон, соответствующий выполняемому мной в дальнейшем SQL-оператору, сервер Oracle применит соответствующий набор подсказок к моему тексту, даже если эти подсказки лишены смысла в контексте моей схемы. (Это дает нам несколько абсолютно новых возможностей для формирования хранимых шаблонов, и заслуживает отдельной статьи). Можно заметить, что когда сервер Oracle автоматически генерирует хранимые шаблоны, имена имеют простой формат и включают временную отметку (время создания) с точностью до миллисекунды.

Продолжая процесс "настройки" нашего проблематичного SQL-оператора, мы решаем, что если добавить подстказку /*+ and_equal(so_demo, sd_i1, sd_i2) */, сервер Oracle будет использовать необходимый нам план выполнения, так что, теперь мы явно создает хранимый шаблон следующим образом:

create or replace outline so_fix
for category demo on
select /*+ and_equal(so_demo, sd_i1, sd_i2) */ v1
from	so_demo
where	n1 = 1 
and	    n2 = 2;

Этот оператор создает хоранимый шаблон с явно заданным именем so_fix в категории demo. Вид хранимого шаблона можно получить, повторив запросы к user_outlines и user_outline_hints с добавлением условия name = 'SO_FIX'.

NAME                           CATEGORY                 USED
------------------------------ --------------------- ---------
SQL_TEXT
---------------------------------------------------------------
SO_FIX                         DEMO                   UNUSED
select /*+ and_equal(so_demo, sd_i1, sd_i2) */ v1
from so_demo
where n1 = 1
and n2 = 2

NAME                                STAGE HINT
------------------------------ ---------- --------------------------------
SO_FIX                                  3 NO_EXPAND
SO_FIX                                  3 ORDERED
SO_FIX                                  3 NO_FACT(SO_DEMO)
SO_FIX                                  3 AND_EQUAL(SO_DEMO SD_I1 SD_I2)
SO_FIX                                  2 NOREWRITE
SO_FIX                                  1 NOREWRITE

Обратите внимание, в частности, что строка FULL(SO_DEMO) заменена строкой AND_EQUAL(SO_DEMO SD_I1 SD_I2), что и требовалось.

Теперь надо "поменять местами" эти два хранимых шаблона. Мы хотим, чтобы сервер Oracle использовал наш новый список подсказок при выполнении оператора с исходным текстом; и для этого придется прибегнуть к трюку. Представления user_outlines и user_outline_hints созданы на основе двух таблиц (ol$ и ol$hints, соответственно), принадлежащих схеме outln, и мы собираемся изменять эти таблицы непосредственно; для этого подключаемся от имени пользователя outln или пользователя, имеющего привилегию изменения этих таблиц.

К счастью, таблицы outln не имеют включенных декларативных ограничений целостности ссылок. Нам на руку то, что взаимосвязь между таблицами ol$ (шаблоны) и ol$hints (подсказки) задается по имени шаблона (которое хранится в столбце ol_name). Поэтому, особо внимательно проверяя имена, мы можем поменять подсказки в хранимых шаблонах, меняя местами имена в таблице ol$hints следующим образом:

update outln.ol$hints
set ol_name = 
	decode(
		ol_name,
			'SO_FIX','SYS_OUTLINE_020503165427311',
			'SYS_OUTLINE_020503165427311','SO_FIX'
	)
where ol_name in ('SYS_OUTLINE_020503165427311','SO_FIX');

Вас может смущать непосредственное изменение данных, настолько близких к ядру сервера Oracle, особенно с учетом комментариев в руководствах -- но такое изменение, фактически, санкционировано документом Metalink Note: 92202.1 от 5 июня 2000 года. Однако в этом документе не сказано, что может понадобиться и другой оператор update, гарантирующий согласованность количества подсказок в каждом из хранимых шаблонов с самими подсказками. Если его не обеспечить, может оказаться, что некоторые из хранимых шаблонов повреждены или уничтожены в ходе экспорта/импорта.

 update outln.ol$ ol1
set hintcount = (
	select	hintcount 
	from	ol$ ol2
	where	ol2.ol_name in
 ('SYS_OUTLINE_020503165427311',' SO_FIX')
	and	ol2.ol_name != ol1.ol_name
	)
where
	ol1.ol_name in 
('SYS_OUTLINE_020503165427311','SO_FIX');

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

alter session set use_stored_outline = demo;

Просмотрев файл трассировки, вы должны обнаружить, что для выполнения SQL-оператора теперь используется план с and_equal. (Если вы используете утилиту tkprof для обработки и изучения файла трассировки, то можете обнаружить в результатах два противоречащих друг другу плана. Первый, правильный, план должен показывать, что используется and_equal, а второй, скорее всего, будет показывать полный просмотр таблицы, поскольку хранимый шаблон мог и не использоваться когда утилита tkprof выполняла explain plan для протрассированного SQL-оператора).

От разработки - к внедрению

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

alter outline SYS_OUTLINE_020503165427311 
rename to AND_EQUAL_SAMPLE;
alter outline AND_EQUAL_SAMPLE 
change category to PROD_CAT;

Для экспортирования шаблона из среды разработки в производственную систему можно воспользоваться возможностью добавлять конструкцию where в файле параметров экспорта, что позволяет создать следующий файл:

userid=outln/outln
tables=(ol$, ol$hints, ol$nodes)	# ol$nodes 
существует только в версии 9
file=so.dmp
consistent=y			    # очень важно
rows=yes
query='where ol_name  = ''AND_EQUAL_SAMPLE'''

Дополнительные возможности Oracle 9

Есть множество других деталей, которые необходимо учитывать при работе с хранимыми шаблонами. Хранимые шаблоны в версии Oracle 8i имеют ряд неприятных и ограничивающих возможности особенностей. К счастью, многие проблемы решены в Oracle 9.

Самой тривиальной и очевидной проблемой при использовании хранимых шаблонов в Oracle 8 было то, что они могли использоваться только если сохраненный текст в точности совпадал с поступившим. В Oracle 9 применяется "нормализация", ослабляющая это требование совпадения; тексты операторов перед сравнением преобразуются в верхний регистр и все лишние пробелы убираются. Это повышает вероятность, что незначительно отличающиеся SQL-операторы смогут использовать тот же хранимый шаблон.

Есть также ряд проблем с более сложными планами выполнения, включающих несколько блоков запросов -- корпорация Oracle решила их в Oracle 9, добавив третью таблицу в схему outln, ol$nodes. Она помогает серверу Oracle разбивать на части список подсказок в таблице ol$hints и учитывать их в соответствующих фрагментах обрабатываемого SQL-оператора. Это, конечно, хорошо, но может повлиять на стратегию замены подсказок одного хранимого шаблона на подсказки другого, поскольку в таблице ol$hints появились дополнительные детали о длине и смещении фрагментов текста. При переходе на Oracle 9 придется использовать другие методы создания хранимых шаблонов, такие как отдельные схемы со специально подобранными наборами данных или отсутствующими индексами, или представлениями со встроенными подсказками, имена которых совпадают с именами таблиц в тексте настраиваемых операторов.

Также в Oracle 9 появились дополнительные возможности создания требуемых хранимых шаблонов, в том числе, предварительная версия пакета, позволяющего непосредственно редактировать хранимые шаблоны. Важнее, однако, что появилась возможность более безопасной работы с планами, хранящимися в производственной системе. Хотя в производственной среде экспериментировать не любит никто, иногда производственная система - единственное место, где можно получить реальные распределения и объемы данных, позволяющие найти оптимальный план выполнения проблемного SQL-оператора. В Oracle 9 можно создать собственную копию таблиц outln и выбрать в них "общедоступные" хранимые шаблоны для "частных" экспериментов, не рискуя сделать один из экспериментальных хранимых шаблонов видимым для кода конечного пользователя. Лично я выполнял бы такую настройку только в крайнем случае, но вполне могу представить себе ситуацию, когда она может понадобиться. Менее опасно будет экспериментировать в отдельной тестовой системе или в системе разработчика, а там эта возможность позволит выполнять независимое тестирование.

Проблемы

Эта статья предоставляет достаточно информации для начала экспериментов с хранимыми шаблонами, но есть ряд нюансов, о которых надо знать, прежде чем начать применять шаблоны в производственной системе.

Во-первых, в Oracle 8i стандартный пароль для outln (схемы, которой принадлежат таблицы, используемые для размещения хранимых шаблонов) широко известен, а сама учетная запись имеет очень опасные привилегии. Обязательно поменяйте пароль этой учетной записи. В Oracle 9i вы обнаружите, что эта учетная запись заблокирована.

Во-вторых, таблицы, используемые для размещения хранимых шаблонов, создаются в табличном пространстве system. В производственной системе окажется, что при создании хранимых шаблонов вы используете очень много пространства в табличном пространстве system. Имеет смысл перенести эти таблицы в другое табличное пространство, предпочтительно, - специально для них созданное. К сожалению, одна из таблиц содержит столбец типа long, поэтому для переноса в новое табличное пространство, вероятно, придется использовать exp/imp.

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

Заключение

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

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

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

Джонатан Льюис (Jonathan Lewis) - независимый консультант с более чем 17-летним опытом проектирования и настройки баз данных Oracle. Он - автор книги "Optimizing Oracle 8i", опубликованной издательством Addison-Wesley, разработчик и ведущий семинара "Optimizing Oracle - Performance by Design", а также составитель списка ЧаВО The Co-operative Oracle Users' FAQ, который можно найти на сайте www.jlcomp.demon.co.uk.

Эта статья первоначально была опубликована на сайте DBAzine.com, сетевом портале, посвященном проблемам различных СУБД и их решениям. Перевод публикуется с разрешения автора.