2004 г.

Формирование хранимых шаблонов в Oracle 9

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

В предыдущей статье я рассматривал хранимые шаблоны и описал один механизм "обмана" системы для получения необходимого хранимого шаблона. Я также подчеркнул, что использование этого метода в Oracle 9 сопряжено с определенным риском, поскольку детальность представления информации существенно возросла. В данной статье, продолжающей ту же тему, я представлю законный способ манипулирования ххранимыми шаблонами, который можно использовать как в Oracle 8, так и в Oracle 9. Фактически эта статья основана на экспериментах, проводившихся в стандартно установленных версиях Oracle 8.1.7.0 и Oracle 9.2.0.1.

Обзор

Что делать, если известно, как существенно ускорить работу оператора ЯМД, добавив несколько подсказок, но нет доступа к исходному коду, в котором можно было бы вставить эти подсказки?

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

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

Можно просмотреть список хранимых SQL-операторов м подсказок, которые с ними связаны, с помощью пары запросов, представленных на рис. 1.

select name, used, sql_text
from user_outlines
where category = 'DEFAULT';

select stage, node, hint
from user_outline_hints
where name = '{одно из имен}';

Рис. 1. Просмотр хранимых шаблонов.

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

Там же я указал, что этот метод был вполне безопасен в Oracle 8, но может привести к проблемам в Oracle 9 в связи с изменениями в новой версии.

В данной статье рассматриваются эти изменения и описывается законный способ регистрации необходимого набора подсказок для проблемных запросов в таблицах outln.

Изменения

Если подключиться к схеме outln (которая по умолчанию в Oracle 9 заблокирована) и посмотреть список имеющихся таблиц, окажется, что в Oracle 9 добавлена одна таблица. В схему входят следующие таблицы:

ol$ - SQL-операторы  
ol$hints - подсказки 
ol$nodes - блоки запроса 

Третья таблица - новая; она используется для привязки списка подсказок к различным блокам во (внутренне переписанной) версии SQL-запроса. Также можно обнаружить, что список подсказок (ol$hints) дополнен подпробностями о длине и смещении фрагментов текста.

Столбцы всех трех таблиц представлены на рис. 2, причем, новые столбцы, появившиеся в версии Oracle 9, помечены звездочками.

ol$

OL_NAME          VARCHAR2(30)
SQL_TEXT         LONG
TEXTLEN          NUMBER
SIGNATURE        RAW(16)
HASH_VALUE       NUMBER
HASH_VALUE2      NUMBER           ***
CATEGORY         VARCHAR2(30)
VERSION          VARCHAR2(64)
CREATOR          VARCHAR2(30)
TIMESTAMP        DATE
FLAGS            NUMBER
HINTCOUNT        NUMBER
SPARE1           NUMBER           ***
SPARE2           VARCHAR2(1000)   ***

ol$hints

OL_NAME          VARCHAR2(30)
HINT#            NUMBER
CATEGORY         VARCHAR2(30)
HINT_TYPE        NUMBER
HINT_TEXT        VARCHAR2(512)
STAGE#           NUMBER
NODE#            NUMBER
TABLE_NAME       VARCHAR2(30)
TABLE_TIN        NUMBER
TABLE_POS        NUMBER
REF_ID           NUMBER           ***
USER_TABLE_NAME  VARCHAR2(64)     ***
COST             FLOAT(126)       ***
CARDINALITY      FLOAT(126)       ***
BYTES            FLOAT(126)       ***
HINT_TEXTOFF     NUMBER           ***
HINT_TEXTLEN     NUMBER           ***
JOIN_PRED        VARCHAR2(2000)   ***
SPARE1           NUMBER           ***
SPARE2           NUMBER           ***

ol$nodes  (новая таблица в версии 9)

OL_NAME          VARCHAR2(30)
CATEGORY         VARCHAR2(30)
NODE_ID          NUMBER
PARENT_ID        NUMBER
NODE_TYPE        NUMBER
NODE_TEXTLEN     NUMBER
NODE_TEXTOFF     NUMBER

Рис. 2. Таблицы в схеме outln.

Пара нюансов бросается в глаза сразу -- представления, созданные на базе этих таблиц, не включают массу полезной информации. Хотя в таблице ol$hints и появилось 10 новых столбцов, определение представления user_outline_hints не изменилось. Фактически, это представление и в Oracle 8 было слишком урезанным, не включая, в частности, весьма информативный столбец hint#.

Обратите также внимание, что в Oracle 9 теперь есть два столбца hash_value. Если задать два одинаковых оператора на серверах Oracle 8 и Oracle 9, окажется, что значения в столбце hash_value для них совпадают, но вот добавленный в Oracle 9 столбец hash_value2, скорее всего, имеет совсем другое значение.

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

select * from t1 where id = 5;

SELECT  *
FROM	T1
WHERE   ID = 5;

Это стратегическое изменение привело к изменению сигнатуры для SQL-оператора, для которого первоначально генерируется план. Поэтому при переводе базы данных с сервера Oracle 8 на Oracle 9 придется перегенерировать хранимые шаблоны, - иначе может оказаться, что они более не используются. (На самом деле, пакет outln_pkg с псевдонимом dbms_outln включает специальную процедуру update_signatures для решения этой проблемы).

Самое же существенное изменение в таблицах версии 9, однако, состоит в намного более детальном описании текста запроса и объектов, которые он затрагивает. Прежде чем читать дальше, выполните операторы в примере, представленном на рис. 3, и просмотрите содержимое таблицы ol$hints.

drop table t1;

create table t1 
nologging
as
select
	rownum		id,
	rownum		n1,
	object_name, 
	rpad('x',500)	padding
from
	all_objects
where 
	rownum <= 100;

alter table t1
add constraint t1_pk primary key (id);

create index t1_i1 on t1(n1);

analyze table t1 compute statistics;

create or replace outline demo_1 on
select	* from t1
where	id = 5
and	n1 = 10;

Рис. 3. Код примера.

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

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

 	STAGE  NODE  HINT
	    3     1  NO_EXPAND
	    3     1  ORDERED
	    3     1  NO_FACT(T1)
	    3     1  INDEX(T1 T1_PK)
	    2     1  NOREWRITE
	    1     1  NOREWRITE

Как и ожидалось, четвертая строка показывает, что для доступа к таблице используется индекс по первичному ключу (T1_Pk). Но что нам делать с этим хранимым шаблоном, если на самом деле необходимо, чтобы сервер Oracle использовал не уникальный индекс T1_I1? В идеале хотелось бы изменить этот хранимый шаблон так, чтобы строка:

3 1 INDEX(T1 T1_PK)

была заменена строкой:

3 1 INDEX(T1 T1_I1)

Новые возможности

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

Однако при просмотре описания пакета и чтении руководств оказывается, что в пакет входят только следующие процедуры, связанные с "редактированием":

CREATE_EDIT_TABLES
DROP_EDIT_TABLES
CHANGE_JOIN_POS

Первые две процедуры позволяют создавать и удалять локальные копии таблиц, обычно находящихся в схеме outln. Третья позволяет изменять порядок соединения таблиц в сохраненном плане. Нет ни одной процедуры, позволяющей просто изменить одну подсказку. В настоящее время этот пакет кажется практически бесполезным, но он со временем, несомненно, станет более "продвинутым".

Запасной вариант, конечно же, связан с непосредственным изменением таблиц! Если подключиться от имени пользователя outln и изучить содержимое таблицы ol$hints (на базе которой построено представление user_outline_hints), можно попытаться выполнить следующее изменение:

update ol$hintsset 	hint_text = 'INDEX(T1 T1_I1)'
where	ol_name = 'demo_1'
and hint# = 4;

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

connect test_user/test
alter system 
	flush shared_pool;
alter session 
	set use_stored_outline=true;

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

Старые методы (1)

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

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

Первый способ (первоначально описанный, насколько я знаю, Томом Кайтом в его книге "Expert One on One: Oracle") ("Oracle для профессионалов" в моем переводе на русский - прим. переводчика) связан с заменой таблиц представлениями, содержащими необходимые подсказки.

Подключаемся к другой схеме, имеющей доступ к таблице T1, и создаем представление с подсказками с тем же именем, что и исходная таблица:

Create or replace view t1 as
Select /*+ index(t1,t1_i1) */ * 
from test_user.t1;

После создания этого представления, используем эту схему для "перекомпиляции" существующего шаблона с помощью команды:

alter outline demo_1 rebuild;

Учтите, что для успешного выполнения этой команды необходима привилегия alter any outline.

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

Почему этот способ работает? Потому что хранимые шаблоны не принадлежат никакой схеме. При пересоздании шаблона по имени demo_1 в новой схеме, имя T1 обозначает локальное представление, содержащее подсказку, поэтому сервер Oracle учитывает эту подсказку в реальном плане выполнения, и, следовательно, в шаблоне. Если обратиться к представлению user_outline_hints, можно обнаружить, что критическая строка действительно имеет вид:

	 3      1  INDEX(T1 T1_I1)

К сожалению, можно также заметить, что теперь в представлении есть три строки вида:

     2      1  NOREWRITE
	    1      2  NOREWRITE
	    1      1  NOREWRITE

Первоначально таких строк было только две:

     2     1  NOREWRITE
	    1     1  NOREWRITE

Мы также добавили подсказку, применяющуюся для 'Stage 1, Node 2' ("Стадия 1, Пункт 2"). Я не берусь утверждать, что точно знаю, что это означает, но это должно быть связано с тем, что при анализе и оптимизации запроса из другой схемы сервер Oracle выполнил дополнительный шаг, преобразуя ссылку на представление в ссылку на базовую таблицу.

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

Старые методы (2)

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

1. Создадим новую схему.
2. Создадим таблицу T1 в этой схеме.
3. Создадим ТОЛЬКО индекс T1_I1.
4. Перестроим шаблон в этой схеме.

Если сравнить содержимое представления user_outline_hints для нашего шаблона до и после перестройки (для этого необходимо будет снова подключиться к исходной схеме), окажется, что они идентичны за исключением той единственной строки, которую мы хотели изменить. Снова подключившись к исходной схеме и, как обычно, сбросив разделяемый пул и включив использование шаблонов, мы увидим, что измененный шаблон успешно используется.

Однако в этом методе есть и скрытая проблема, на этот раз, немного более тонкая. Возвращаясь к рис. 2, на котором представлены определения новых столбцов, появившихся в Oracle 9, - как вы думаете, какая информация содержится в столбце user_table_name? Там хранится уточненное имя таблицы; т.е.:

{имя_пользователя}.{имя_таблицы}

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

Опять-таки, сейчас этот метод работает, но зачем эта информация вообще сохраняется -- видимо, в связи с планируемыми изменениями в будущих версиях.

Безопасный способ

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

В нашем случае, надо удалить индекс по первичному ключу, сгенерировать план, а затем заменить первичный ключ!

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

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

Например: в запасной базе данных вполне можно будет удалить первичный ключ, чтобы избежать сканирования (unique scan) соответствующего индекса. Если после этого сервер Oracle не начнет использовать другой индекс автоматически, можно всеми возможными способами обманывать его, например:

• Изменить режим оптимизации на first_rows.
• Создать данные, уникальные по столбцу N1. (Не создавайте, однако, по нему уникальный индекс, иначе в сгенерированном шаблоне будет указано действие unique scan вместо range scan).
• Используйте средства пакета dbms_stats чтобы сказать, что индекс имеет фантакстическую степень кластеризации (clustering_factor).
• Используйте параметр optimiser_index_caching чтобы сказать, что индекс на 100% кэширован.
• Используйте параметр optimiser_index_cost_adj чтобы сказать, что чтение по несколько блоков (multiblock read) в 100 раз медленнее, чем чтение по одному блоку (single block read).
• Используйте средства пакета dbms_stats чтобы задать те же утверждения через таблицу aux_stats$, и добавте также утверждение, что обычно при чтении нескольких блоков читается только два блока.
• Пересоздайте индекс так, чтобы он включал оба столбца, заданные в конструкции where.

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

Выводы

Информация, записываемая в хранимый шаблон в Oracle 9, намного более "уязвима", чем в Oracle 8. Раньше "изменять" шаблоны было сравнительно просто и безопасно. Прежние методы работают, но большой объем дополнительной информации, собираемой в Oracle 9, позволяет предположить, что в будущем их использование сопряжено с риском.

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

Ссылки

Oracle 9i Release 2: Database Performance Tuning Guide and Reference -- Глава 7.

Oracle 9i Release 2: Supplied PL/SQL Packages and Types Reference -- Главы 41 - 42.

--

Джонатан Льюис (Jonathan Lewis) - независимый консультант с более чем 17-летним опытом использования Oracle. Он специализируется на физическом проектировании баз данных и стратегии использования сервера Ortacle. Джонатан - автор книги "Practical Oracle 8i - Designing Efficient Databases", опубликованной издательством Addison-Wesley и один из наиболее известных лекторов среди специалистов по Oracle в Великобритании. Подробнее о его публикациях, презентациях и семинарах можно узнать на сайте www.jlcomp.demon.co.uk, где также находится список ЧаВО The Co-operative Oracle Users' FAQ по дискуссионным группам Usenet, связанным с СУБД Oracle.

---

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