Базы данных Oracle - статьи
Что, по вашему, должно делать приложение?
Чтобы заставить сервер Oracle делать то, что нужно нам, необходимо пройти три этапа:
Надо завершать текущий и начинать новый сеанс, чтобы гарантировать, что существующие курсоры не остались открытыми в кэше 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-оператора).
