«Технологии баз данных (СУБД Oracle)» (стр. 10 )

Одно выполнение для всех или почти всех операторов SQL. Если в отчете TKPROF указано, что все операторы SQL выполняются только один раз, скорее всего, не используются связываемые переменные (выполняются похожие запросы, которые отличаются лишь используемыми константами). В трассировочных файлах реальных приложений уникальных операторов SQL обычно немного; одни и те же SQL-операторы выполняются многократно. Слишком большое количество уникальных SQL-операторов обычно означает, что недостаточно используются связываемые переменные.

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

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

Большая величина отношения (FETCH COUNT)/(количество извлеченных строк). Для ее вычисления берем количество действий типа FETCH и делим на количество извлеченных строк. Если полученный результат близок к одному и извлечено более одной строки, приложение не выполняет множественные извлечения. Любой язык или функциональный интерфейс позволяет это делать - извлекать несколько строк одним вызовом. Если возможность множественного извлечения не используется, на пересылки информации с клиента на сервер и обратно уйдет намного больше времени. Этот постоянный обмен информацией, помимо того, что чрезвычайно загружает сеть, выполняется намного медленнее, чем получение нескольких строк одним вызовом. Как организовать множественное извлечение данных в приложении, зависит от используемого языка и/или функционального интерфейса.

Слишком большое количество физических обращений к диску. Простое правило для этого параметра придумать сложнее, но если DISK COUNT = QUERY + CURRENT MODE BLOCK COUNT, значит, все блоки читались с диска. Будем надеяться, что при следующем выполнении этого запроса часть блока будет найдена в области SGA. Большое количество обращений к диску - предупреждающий сигнал о том, что необходимо провести дополнительное исследование. Возможно, надо увеличить размер буферного кэша в SGA или придумать другой запрос, требующий чтения меньшего количества блоков.

Слишком большое количество обработанных блоков (QUERY или CURRENT). Это показывает, что запрос обрабатывает большой объем информации. Проблема это или нет - судить вам. Некоторым запросам действительно необходимо обработать много данных, как в представленном ранее примере. Часто выполняемый запрос, однако, должен обрабатывать сравнительно немного блоков. Если сложить значения QUERY и CURRENT количества обработанных блоков и поделить на значение столбца count в строке EXECUTE, должно получаться небольшое число.

Следующая часть отчета:

Misses in library cache during parse: 0

Optimizer goal: CHOOSE

Parsing user id: 69

Из этого можно сделать вывод, что выполненный запрос был найден в разделяемом пуле (количество не найденных в библиотечном кэше запросов равно 0). То есть, выполнялся мягкий разбор запроса. При самом первом выполнении запроса в этой строке будет значение 1. Если практически у всех выполнявшихся запросов будет значение 1, значит, не используются связываемые переменные (это надо исправить). Операторы SQL не используются повторно.

Вторая строка показывает режим работы оптимизатора при выполнении запроса. Эта информация - для справки; выбранный и использованный план выполнения запроса зависит от этого режима.

Наконец, представлен идентификатор пользователя, разобравшего запрос. По этому идентификатору можно получить имя пользователя:

SELECT * FROM all_users WHERE user_id = 69;

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

Утилита TKPROF имеет много опций командной строки, и если ввести команду

TKPROF

все они будут выданы:

Usage: tkprof tracefile outputfile [explains ] [tables ][prints ] [insert= ] [sys= ] [sorts ]

table=имя_схемы. имя таблицы Используйте "имя_схемы. имя_таблицы" внесте с опцией "explains".

explain=пользователь/пароль Подключиться к ORACLE и выполнить EXPLAIN PLAIN.

print=количество Выдать только указанное "количество" операторов SQL.

aggregate=yes|no

iпsеrt=имя_файла Выдать в этот файл операторы SQL и данные в операторах INSERT.

sys=no He выдавать информацию об операторах SQL, выполненных от имени пользователя SYS.

record=имя_файла - Выдать сюда нерекурсивные операторы, имеющиеся в трассировочном файле.

sort=опции - Набор из нуля или более следующих опций:

prscnt - сколько раз выполнялся разбор,

prscpu - процессорное время раэбора,

prsela - реальное время раэбора,

prsdsk - количество чтений с диска в ходе разбора,

prsqry - количество буферов, прочитанных в режиме согласованного чтения в ходе разбора,

prscu - количество буферов, непосредственно прочитанных в ходе разбора,

prsmis - количество непопаданий в библиотечный кэше в ходе разбора,

execnt - сколько раз выполнялся оператор,

ехесри - процессорное время выполнения,

exeela - реальное время выполнения,

exedsk - количество чтений с диска при выполнении,

exeqry - количество буферов, прочитанных в режиме согласованного чтения в ходе выполнения,

execu - количество буферов, непосредственно прочитанных при выполнении,

exerow - количество обработанных при выполнении строк,

exemis - количество непопаданий в библиотечный кэш в ходе выполнения,

fchcnt - сколько раз выполнялось извлечение данных,

fchcpu - процессорное время извлечения данных,

fchela - реальное время извлечения данных,

fchdsk - количество обращений к диску при извлечении данных,

fchqry - количество буферов, прочитанных в режиме согласованного чтения при

извлечении данных,

fchcu - количество буферов, непосредственно прочитанных при извлечении данных,

fchrow - количество извлеченных строк,

userid - идентификатор пользователя, разобравшего оператор,

Наиболее полезной, является опция sort=. Можно сортировать результаты по разным показателям процессорного и реального времени выполнения, чтобы "наихудшие" запросы оказывались в начале трассировочного файла. Сортировку можно также использовать для поиска запросов, выполняющих слишком много физического ввода/вывода и т. д. Назначение остальных опций очевидно. В 99,9 процентах случаев используется:

TKPROF имя_трассировочного_файла имя_файла_отчета

и ничего более.

При этом операторы SQL выдаются примерно в том порядке, как они посылались серверу в ходе выполнения.

Связываемые переменные

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

SELECT * FROM employees WHERE employee_id = 123;

Но можно задать и другой запрос:

SELECT * FROM employees WHERE employee_id =:employee_id;

B обычной системе информацию о сотруднике с номером 123 могут запрашивать всего один раз. В дальнейшем будут запрашивать информацию о сотрудниках с номерами 456, 789 и т. д. При использовании в запросе литералов (констант) каждый запрос является для СУБД абсолютно новым, никогда ранее не выполнявшимся. Его надо разбирать, уточнять (определять объекты, соответствующие именам), проверять права доступа, оптимизировать и т. д. - короче, каждый выполняемый уникальный оператор придется разбирать при каждом выполнении.

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

Таким образом, разбор оператора с явными, жестко заданными константами (так называемый жесткий разбор) выполняется дольше и требует намного больше ресурсов, чем повторное использование уже сгенерированного плана запроса (его называют мягким разбором). Менее очевидным может оказаться, насколько постоянный жесткий разбор сокращает количество пользователей, поддерживаемых системой. Отчасти это связано с повышенным потреблением ресурсов, но в гораздо большей степени - с механизмом защелок, используемых в библиотечном кэше. При жестком разборе запроса СУБД будет дольше удерживать определенные низкоуровневые средства обеспечения последовательного доступа, которые называются защелками. Защелки защищают структуры данных в разделяемой памяти сервера Oracle от одновременного изменения двумя сеансами (иначе эти структуры данных Oracle в конечном итоге были бы повреждены) и от чтения этой структуры данных по ходу изменения другим сеансом. Чем чаще и на более продолжительное время на эти структуры данных устанавливаются защелки, тем длиннее становится очередь для установки этих защелок. Точно так же происходит при использовании длинных транзакций в среде MTS, - монополизируются критические ресурсы.

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

При использовании связываемых переменных любой сеанс, выдающий тот же самый запрос, будет использовать уже разобранный план выполнения из библиотечного кэша. Это очень эффективно, и именно такую работу пользователей предполагает СУБД. При этом не только используется меньше ресурсов (мягкий разбор требует намного меньше ресурсов), но и защелки удерживаются значительно меньше времени, и нужны гораздо реже.

Это повышает производительность и масштабируемость.

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

ALTER SYSTEM FLUSH SHARED_POOL;

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

DECLARE

TYPE rc IS REF CURSOR;

l_rc rc;

l_dummy all_objects. object_name%TYPE;

BEGIN

FOR i IN

LOOP

OPEN l_rc FOR 'SELECT object_name FROM all_objects WHERE object_id = ' || i;

FETCH l_rc into l_dummy;

CLOSE l_rc;

END LOOP;

END;

В этом коде используется динамический SQL для запроса одной строки из таблицы ALL_OBJECTS. Он генерирует 1000 уникальных запросов со значениями 1, 2, 3, ... и так далее, жестко заданными в конструкции WHERE. Запомним время выполнения.

Теперь сделаем то же самое с использованием связываемых переменных:

DECLARE

TYPE rc IS REF CURSOR;

l_rc rc;

l_dummy all_objects. object_name%TYPE;

BEGIN

FOR i IN

LOOP

OPEN l_rc FOR 'SELECT object_name FROM all_objects WHERE object_id = :x' USING i;

FETCH l_rc INTO l_dummy;

CLOSE l_re;

END LOOP;

END;

Время выполнения уменьшилось в 10 раз. В этом коде использован точно такой же алгоритм. Единственное изменение - вместо жестко заданных значений 1, 2, 3... и так далее в запросе используется связываемая переменная. Результаты весьма впечатляющи. Код не только выполняется намного быстрее (разбор запросов требовал больше времени, чем их реальное выполнение!), но и позволяет большему количеству пользователей одновременно работать с системой. Выполнение операторов SQL без связываемых переменных во многом подобно перекомпиляции подпрограммы перед каждым вызовом.

Существует ряд установок, включая которые на уровне СУБД, можно снизить влияние грубых ошибок программирования. Например, параметр –

CURSOR_SHARING=FORCE.

Он позволяет включить автоматическое использование связываемых переменных. В результате запрос

SELECT * FROM employees WHERE employees_id = 1234

автоматически переписывается в виде

SELECT * FROM employees WHERE employees_id =: x.

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

В таблице может быть столбец с весьма неравномерным распределением значений (например, 90 процентов значений в столбце - больше 100, а 10 процентов - меньше 100). Причем лишь 1 процент значений меньше 50. Хотелось бы, чтобы при выполнении запроса:

SELECT * FROM t WHERE x < 50;

индекс использовался, а при выполнении запроса:

SELECT * FROM t WHERE x > 100;

не использовался. Если установить параметр CURSOR_SHARING=FORCE, оптимизатор не сможет учесть значения 50 или 100, поэтому будет выбирать план для общего случая, когда индекс, скорее всего не будет использоваться (даже если 99,9 процентов запросов будут содержать конструкцию WHERE x < 50).

Кроме того, хотя установка CURSOR_SHARING = FORCE обеспечивает большую скорость работы, чем повторный анализ и оптимизация множества одинаковых запросов как уникальных, это все равно медленнее, чем выполнение запросов, где связываемые переменные используются изначально. Это происходит не из-за неэффективности механизма совместного использования кода курсора, а из-за неэффективности самой программы. Во многих случаях приложение, не использующее связываемые переменные, также не обеспечивает эффективного анализа и повторного использования курсоров. Поскольку в приложении предполагается уникальность каждого запроса (так как для каждого из них создается уникальный оператор), то и курсор в нем не будет использоваться более одного раза. Факт в том, что если программист использует связываемые переменные, то он зачастую также разбирает запрос один раз и затем использует многократно. Именно затраты ресурсов на повторный разбор приводят к наблюдаемому снижению производительности.

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

Даже если есть соответствующие параметры, которые можно установить на уровне базы данных, а их пока немного, проблемы одновременного доступа и неэффективных запросов (неудачно сформулированных или вызванных неудачной организацией данных) нельзя решить только установкой параметров сервера. Для решения этих проблем необходимо переписать приложение (а зачастую и изменить его архитектуру). Перенос файлов данных с одного диска на другой, изменение количества блоков, читаемых подряд одной операцией ввода, и другие настройки "на уровне базы данных" часто мало влияют на общую производительность приложения. Они никак не дадут ускорения в 2, 3, ... N раз, необходимого для достижения приемлемой скорости работы приложения.

Как часто требуется ускорить работу приложения на 10 процентов? Если надо ускорить работу на 10 процентов, обычно никто вообще не поднимает вопрос об этом. Пользователи начинают жаловаться, когда, по их мнению, скорость надо увеличить раз в пять. Вы не увеличите скорость работы в пять раз за счет переноса файлов данных на другие диски. Это можно сделать только путем изменения приложения, например, сократив объем вода/вывода.

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

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

Для следующего теста будут использовать следующие таблицы. Для выполнения этого примера необходим доступ к представлению V$SESSION_EVENT, т. е. наличие привилегии SELECT для представления V$SESSION_EVENT. Кроме того, необходимо установить параметр системы (SYSTEM) или сеанса (SESSION) TIMED_STATISTICS, чтобы получать осмысленные результаты (иначе время выполнения каждого оператора будет равно нулю). Это можно сделать с помощью оператора

ALTER SESSION SET TIMED_STATISTICS=TRUE.

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

CREATE GLOBAL TEMPORARY TABLE sess_event ON COMMIT PRESERVE ROWS

AS SELECT * FROM v$session_event WHERE 1=0;

Теперь создадим "прикладную" таблицу для тестирования:

CREATE TABLE t

(c1 INT, c2 INT, c3 INT, c4 INT)

STORAGE (FREELIST 10);

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

TRUNCATE TABLE sess_event;

INSERT INTO sess_event SELECT * FROM v$session_event WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1) ;

DECLARE

l_number NUMBER;

BEGIN

FOR i IN 1

LOOP

l_number := dbms_random. random;

EXECUTE IMMEDIATE ' INSERT INTO t VALUES (' || l_number || ',' || l_number || ',' || l_number || ',' || l_number || ')' ;

END LOOP;

COMMIT;

END;

SELECT a. event, (a. total_waits-nvl (b. total_waits,0)) total_waits, (a. time_waited-nvl(b. time_waited,0)) time_waited

FROM (SELECT * FROM v$session_event WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1)) a, sess_event b

WHERE a. event = b. event (+)

AND (a. total waits-nvl (b. total_waits,0)) > 0

Результат:

EVENT TOTAL_WAITS TIME_WAITED

SQL*Net message from client 4 14

SQL*Net message to client 5 0

log file sync 5 2

Cоздаем уникальный оператор INSERT, который будет выглядеть примерно так:

insert into t values (12323, 12323, 12323, 12323);

insert into t values ( 632425);

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

EVENT TOTAL_WAITS TIME_WAITED

SQL*Net message from client 4 18

SQL*Net message to client 5 0

Enqueue 2 0

latch free

log file sync 2 2

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

·  SQL*Net message from client. Сервер ждал, пока клиент пошлет ему сообщение. В данном случае клиент - SQL*Plus. В большинстве случаев ожидание этого события можно игнорировать, но если при выполнении приложения предполагаются длительные раздумья пользователя, это число неизбежно будет большим. В нашем случае сеанс SQL*Plus постоянно выдавал операторы на сервер, поэтому время ожидания должно быть небольшим. Если бы оно было большим, проблема была бы связана с клиентом (узким местом был бы клиент, неспособный достаточно часто обращаться к базе данных).

·  SQL*Net message to client. Сколько времени потребовалось на передачу сообщений с сервера клиенту (SQL*Plus).

·  Enqueue. Ожидание той или иной блокировки.

·  Log file sync. Время ожидания сброса буфера журнала повторного выполнения на диск процессом LGWR при фиксации.

·  latch free. Ожидание события освобождения защелки. Эта защелка предотвращает одновременный доступ к разделяемой области SQL.

Стабилизация плана оптимизатора

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

• разработать приложение;

• протестировать и настроить его запросы;

• сохранить соответствующие хорошо настроенные планы выполнения в базе данных для использования оптимизатором в дальнейшем.

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

• повторный анализ таблицы после изменения количества данных;

• повторный анализ таблицы после изменения распределения данных;

• повторный анализ таблицы с помощью других методов или параметров;

• изменение различных параметров в файле init. ora, влияющих на поведение оптимизатора;

• добавление индексов;

• обновление версии ПО Oracle.

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

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

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

Продемонстрируем возможности стабилизации плана оптимизатора на примере:

Создадим тестовую таблицу:

CREATE TABLE emp AS SELECT ename, empno FROM scott. emp GROUP BY ename, empno;

ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (empno);

Зададим режим оптимизации CHOOSE.

ALTER SESSION SET OPTIMIZER_GOAL = CHOOSE;

Получим план выполнения запроса:

SET AUTRACE ON EXPLAIN

SELECT empno, ename FROM emp WHERE empno > 0;

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

CREATE OR REPLACE OUTLINE myoutline FOR CATEGORY mycategory ON SELECT empno, ename FROM emp WHERE empno > 0;

Оператор CREATE OR REPLACE OUTLINE создал шаблон запроса и сохранил его в базе данных. Поскольку мы явно создали шаблон, можно задать ему имя (myoutline). Кроме того, мы отнесли этот шаблон запроса к определенной категории (mycategory). Следует отметить, что при выполнении оператора CREATE OUTLINE можно получить сообщение об ошибке:

ORA-18005: create any outline privilege is required for this operation

Если выдается такое сообщение, необходимо, чтобы администратор базы данных предоставил соответствующему пользователю привилегию CREATE ANY OUTLINE. Давайте теперь изменим базу данных - просто проанализируем таблицу:

ANALYZE TABLE emp COMPUTE STATISTICS;

Посмотрим, каким теперь будет план выполнения запроса:

SET AUTRACE ON EXPLAIN

SELECT empno, ename FROM emp WHERE empno > 0;

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

ALTER SESSION SET use_stored_outlines = mycategory

Это обеспечивает применение хранимых шаблонов категории mycategory. Если теперь посмотреть план выполнения запроса:

SET AUTRACE ON EXPLAIN

SELECT empno, ename FROM emp WHERE empno > 0;

Оказывается, что снова используется исходный план с доступом но индексу. В этом цель стабилизации плана оптимизатора: "заморозить" планы выполнения запросов для хорошо настроенного приложения. Приложение изолируется от изменений планов оптимизатора, происходящих на уровне базы данных (в результате анализа таблиц, выполненного администратором базы данных, изменения параметров инициализации или обновления версии сервера). Как и большинство средств, стабилизация плана оптимизатора - палка о двух концах. То, что внешние изменения не сказываются на приложении, может оказаться как положительным, так и отрицательным. Хорошо это потому, что позволяет добиться предсказуемой производительности в долгосрочной перспективе (поскольку план никогда не изменяется). Однако так можно пропустить новый план, ускоряющий выполнение запроса, и это плохо.

С шаблонами запросов связаны два представления, между которыми есть отношение главное/подчиненное. Главное представление - OUTLINES (как обычно, есть три его версии: DBA_, ALL_ и USER_. Подчиненное представление - OUTLINE_HINTS (оно тоже доступно в трех версиях).

В представлениях _OUTLINES находятся хранимые шаблоны. В представлении DBA_OUTLINES есть записи для всех хранимых шаблонов в системе, тогда как в представлениях ALL_ и USER_OUTLINES присутствуют только строки, имеющие отношение к текущему пользователю (шаблоны, доступные или созданные пользователем, соответственно). Поскольку представления DBA_OUTLINES и USER_OUTLINES отличаются только одним столбцом (в представлении DBA есть столбец OWNER, содержащий имя схемы, в которой создан шаблон), рассмотрим представление DBA_OUTLINES:

• NAME. Имя шаблона, заданное в операторе CREATE OUTLINE (в представленном выше примере использовалось имя MYOUTLINE).

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

• OWNER. Схема, в которой создан шаблон. Шаблоны не "принадлежат" никому, так что имя столбца - несколько неправильное. Правильно было бы назвать столбец CREATOR, создатель.

• CATEGORY. Категория, к которой отнесена схема (в примере - MYCATEGORY). Шаблоны запросов могут принадлежать к категории, указанной по имени, либо к общей категории DEFAULT, которая используется, если имя категории не задано. В ходе работы пользователь или приложение выполняет оператор

ALTER SESSION SET USE_STORED_OUTLINES = <TRUE|имя_категории>,

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

• USED. Этот атрибут показывает, был ли указанный шаблон хоть раз использован. Он будет иметь значение unused до первого использования шаблона для изменения плана выполнения запроса; при этом атрибут получает значение used.

• TIMESTAMP. Дата и время создания исходного шаблона.

• VERSION. Версия СУБД, в которой был создан исходный шаблон.

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

В представлении _OUTLINE_HINTS находятся реальные подсказки, которые надо применять на разных внутренних стадиях плана выполнения запроса. Сервер по ходу работы переписывает переданный запрос, встраивая эти подсказки в соответствующие места, что и дает необходимый план выполнения. В тексте запроса эти подсказки не появляются, - они добавляются во внутренние структуры плана выполнения запроса. Единственное структурное отличие между представлением DBA_OUTLINE_HINTS, USER_OUTLINE_HINTS и ALL_OUTLINE_HINTS - добавление столбца OWNER, идентифицирующего пользователя, создавшего шаблон.

• NAME. Имя хранимого шаблона. Если шаблон создан с помощью оператора CREATE OUTLINE, это будет имя, заданное в операторе.

• OWNER. Имя пользователя, создавшего шаблон запроса.

• NODE. Запрос или подзапрос, к которому применяется подсказка. Запрос верхнего уровня получает значение 1 в столбце NODE, а последующие подзапросы, встроенные в основной запрос, получают последовательно увеличивающиеся значения.

• STAGE. Стадия выполнения, на которой применяются подсказки в ходе обработки запроса. Это число представляет стадию, на которой подсказка будет "вписана" в запрос. Речь идет о внутренних стадиях обработки, выполняемых оптимизатором Oracle, которые обычно пользователям недоступны.

• JOIN_POS. Задает таблицу, к которой будет применяться эта подсказка. Для всех подсказок, не задающих метод доступа, в этом столбце будет значение ноль. Для подсказок, задающих метод доступа (например, доступ к таблице по индексу), столбец JOIN_POS задает таблицу.

• HINT. Подсказка, которая должна быть встроена в запрос.

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

• CREATE ANY OUTLINE. Позволяет создавать шаблоны в базе данных. При отсутствии этой привилегии будет выдаваться сообщение об ошибке

ORA-18005:create any outline privilege is required for this operation.

• ALTER ANY OUTLINE. Позволяет изменять (переименовывать, изменять категорию или пересоздавать план) шаблон запроса.

• DROP ANY OUTLINE. Позволяет удалять существующий шаблон с указанным именем.

• EXECUTE ON OUTLN_PKG. Позволяет выполнять подпрограммы пакета OUTLINE.

Обратите внимание, что это привилегии класса ANY. Это означает, что при наличии привилегии CREATE OR REPLACE ANY OUTLINE можно переписать шаблон другого пользователя, не спрашивая у него разрешения. Шаблоны, в отличие от большинства других объектов базы данных не принадлежат никому. У шаблона есть создатель, но нет владельца в обычном смысле. Если можно удалять собственные шаблоны, то можно (ненамеренно) удалить и шаблон любого другого пользователя, поэтому при использовании этих привилегий надо быть внимательным.

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

CREATE [OR REPLACE] OUTLINE имя_шаблона [FOR CATEGORY имя_категории]

ON оператор_для_которого_сохраняется_шаблон

В этом операторе:

• Имя_шаблона - имя, присвоенное шаблону. Оно должно иметь смысл для создателя и разработчика приложения. При этом на имя налагаются те же ограничения, что и для любого объекта базы данных (не более 30 символов, начинается с буквы и т. д.). Кроме того, имя_шаблона должно быть уникальным для базы данных, а не для пользователя или категории, как можно было бы подумать, поэтому будьте особенно внимательны при использовании конструкции OR REPLACE, поскольку оператор перезапишет любой существующий шаблон с таким именем.

• Имя_категории - имя, используемое для группировки шаблонов. Эта часть оператора CREATE - не обязательная, и если категория не задана, шаблон будет отнесен к категории DEFAULT. Рекомендуется явно указывать имя категории и не использовать категорию DEFAULT. Поскольку сеанс в каждый момент времени может использовать только одну категорию шаблонов, в ней надо сохранить шаблоны планов для всех существенных запросов.

• Оператор_для_которого_сохраняется_шаблон - любой допустимый SQL-оператор.

Генерация шаблонов с помощью операторов больше всего подходит для приложений, в которых все SQL-операторы хранятся вне приложения. Другими словами, есть файл ресурсов, в котором записаны все потенциально выполняемые SQL-операторы. В этом случае по такому файлу очень легко сгенерировать сценарий с операторами CREATE OUTLINE и выполнить его на сервере. Это гарантирует создание шаблонов для всех запросов (если запросы указаны в этом файле ресурсов). Кроме того, такой подход предохраняет от случайной генерации шаблонов для лишних запросов. Например, если используется триггер ON LOGON, после регистрации в SQL*Plus окажется, что для автоматически выполняемых утилитой SQL*Plus запросов тоже сохранены шаблоны.

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

Использование оператора ALTER SESSION более универсальный метод генерации шаблонов запросов. Он применяется аналогично установке SQL_TRACE при трассировке программ. С момента выполнения соответствующего оператора ALTER SESSION и до отключения создания хранимых шаблонов для каждого выполняемого запроса будет сохраняться шаблон. Этот метод можно применять для любого приложения, если требуется стабилизировать все планы. Другими словами, когда необходимо точно знать, какими будут планы выполнения SQL-операторов, независимо от версии сервера, на котором будет установлено приложение, независимо от значений параметров инициализации экземпляра и т. п. Чтобы добиться этого для приложения, можно использовать триггер ON LOGON, а затем полностью протестировать приложение, выполнив все возможные запросы. Это надо сделать на тестовом сервере в процессе окончательного тестирования перед поставкой приложения клиентам. После сбора всех планов необходимо извлечь их с помощью утилиты ЕХР, а затем устанавливать с помощью утилиты IMP в процессе инсталляции приложения. Этот метод также используется, если на уровне сервера включена автоматическая подстановка связываемых переменных (auto binding).

Синтаксис соответствующих версий оператора ALTER SESSION несложен:

ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE;

ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;

ALTER SESSION SET CREATE_STORED_OUTLINES = категория_шаблонов;

Если параметр CREATE_STORED_OUTLINES получает значение TRUE, сервер Oracle будет генерировать хранимые шаблоны для категории DEFAULT. Категория DEFAULT - самая обычная категория с соответствующим именем; ее использование надо включать так же, как и любой другой категории. После установки параметру CREATE_STORED_OUTLINES значения FALSE сервер Oracle перестанет генерировать хранимые шаблоны для соответствующего сеанса.

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

Кроме оператора CREATE, для управления шаблонами запросов можно также использовать операторы ALTER и DROP. Оператор ALTER позволяет:

• переименовать (RENAME) хранимый шаблон;

• пересоздать (REBUILD) план для хранимого шаблона;

• изменить (CHANGE) категорию хранимого шаблона;

Оператор DROP удаляет указанный по имени хранимый шаблон.

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

CREATE OR REPLACE outline my_outline FOR CATEGORY my_category

ON SELECT * FROM all_objects;

SELECT name, category, sql_text FROM user_outlines;

SELECT count (*) FROM user_outline_hints;

Итак, мы работаем с шаблоном MY_OUTLINE в категории MY_CATEGORY, с которым сейчас связано 138 подсказок (результат может быть другим, в зависимости от установок оптимизатора). Прежде всего, оператор ALTER OUTLINE позволяет переименовать хранимый шаблон. Эта версия оператора имеет следующий синтаксис:

ALTER OUTLINE имя_шаблона RENAME TO новое_имя

Итак, применим этот оператор для переименования шаблона с MY_OUTLINE в PLAN_FOR_ALL_OBJECTS следующим образом:

ALTER OUTLINE my_outline RENAME TO plan_for_all_objects;

Простой запрос позволяет проверить, сработало ли все, как предполагалось:

SELECT name, category, sql text FROM user_outlines;

Следующий шаг - изменить с помощью оператора ALTER OUTLINE категорию, в которой хранится шаблон. Эта версия оператора имеет следующий синтаксис:

ALTER OUTLINE имя_штаблона CHANGE CATEGORY TO новое_имя_категории;

Переведем наш хранимый шаблон из категории MY_CATEGORY в категорию DICTIONARY_PLANS:

ALTER OUTLINE plan_for_all_objects CHANGE CATEGORY TO dictionary_plans;

SELECT name, category, sql_text FROM user_outlines;

Оператор ALTER OUTLINE просто изменяет имя категории в пользовательской схеме OUTLN. Чтобы продемонстрировать последний вариант использования оператора ALTER OUTLINE, пересоздадим план выполнения запроса в текущей среде. Синтаксис оператора в этом случае:

ALTER OUTLINE имя_таблона REBUILD;

В настоящий момент в используемом сеансе SQL*Plus параметр OPTIMIZER_GOAL имеет значение CHOOSE. Поскольку объекты словаря не проанализированы, для запроса используется оптимизатор, основанный на правилах (если режим оптимизации CHOOSE и объекты в запросе не проанализированы, используется оптимизатор, основанный на правилах). Установим цель оптимизации ALL_ROWS, что требует использовать оптимизатор, основанный на стоимости, и перестроим план.

ALTER SESSION SET optimizer_goal = all_rows;

ALTER OUTLINE plan_for_all_objects REBUILD;

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

SELECT count (*) FROM user_ourline_hints WHERE name = 'PLAN_FOR_ALL_OBJECTS'

План, несомненно, отличается: теперь подсказок 139 и запрос оптимизирован в режиме ALL_ROWS, а не CHOOSE.

Оператор удаления шаблона имеет следующий синтаксис:

DROP OUTLINE имя_игаблона

Используем этот оператор для удаления существующего хранимого шаблона:

DROP OUTLINE plan_for_all_objects;

SELECT * FROM user_outlines;

Для работы с шаблонами существует пакет OUTLN_PKG. Этот пакет создавался:

• Для поддержки множественных операций с шаблонами, таких как удаление неиспользуемых хранимых шаблонов, удаление шаблонов определенной категории и т. д. Это можно сделать и с помощью операторов ALTER и DROP, но только по одному шаблону. Пакет OUTLN_PKG предлагает набор процедур для работы с несколькими шаблонами одним оператором.

• Чтобы предоставить набор процедур для утилит ЕХР и IMP, обеспечивающих экспорт и импорт хранимых шаблонов.

Продемонстрируем использование некоторых процедур пакета OUTLN_PKG для множественных операций.

Пакет OUTLN_PKG создается сценариями dbmsol. sql и prvtol. plb, которые находятся в каталоге [ORACLE_HOME]/rdbms/admin. Эти сценарии вызываются сценарием catproc. sql (который находится в том же каталоге) и создают пакет в базе данных по умолчанию. Помимо создания пакета OUTLN_PKG, эти сценарии вставляют необходимые строки в соответствующие таблицы словаря данных, чтобы зарегистрировать его функции для использования утилитами EXP/IMP. Пакет должен устанавливаться пользователем SYS. Поскольку пакет автоматически устанавливается при обновлении или установке сервера, выполнять сценарий установки вручную не понадобится.

В пакете OUTLN_PKG есть процедуры:

• DROP_UNUSED. Удаляет все шаблоны, в столбце USED которых находится значение UNUSED. Это хранимые шаблоны, сгенерированные, но ни разу не использовавшиеся для переписывания запроса.

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

• UPDATE_BY_CAT. Переименовывает категорию глобально, изменяя все входящие в нее шаблоны.

Процедура OUTLN_PKG. DROP_UNUSED, не имеющая параметров, удаляет все не использованные шаблоны из всех категорий. Она находит шаблоны, в столбце USED для которых хранится значение UNUSED, и применяет к ним аналог оператора DROP OUTLINE имя_шаблона.

Пример использования этой процедуры:

EXEC outln_pkg. drop_unused;

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

Процедура DROP_BY_CAT удаляет все хранимые шаблоны указанной категории. Ее можно использовать, например, при тестировании для удаления категорий шаблонов, не оправдавших ожидания. Можно также использовать эту процедуру для удаления категории шаблонов по ходу работы. Это позволяет приложению использовать планы, генерируемые оптимизатором, вместо планов, сохраненных в шаблонах. Пример использования этой процедуры:

SELECT category FROM user_outlines;

EXEC outln_pkg. drop_by_cat ('DICTIONARY_PLANS');

SELECT category FROM user_outlines;

Процедура OUTLN_PKG. UPDATE_BY_CAT позволяет переименовать существующую категорию или объединить категории. Синтаксис вызова этой процедуры:

outln_pkg. update_by_cat (старое_имя_категории, новое_имя_категории);

Эта процедура работает следующим образом:

• Если категории новое_имя_категории в базе данных еще нет, все существующие шаблоны из категории старое_имя_категории переводятся в категорию новое_имя_категории.

• Если категория новое_имя_категории существует, все хранимые шаблоны из категории старое_имя_категории переносятся в категорию новое_имя_категории.

• Если в столбце SQL_TEXT хранимого шаблона в категории старое_имя_категории хранится текст, совпадающий с текстом одного из шаблонов в категории новое_имя_категории, то шаблон в новую категорию не переносится.

Рассмотрим пример, демонстрирующий эту возможность:

CREATE outline outline_l FOR CATEGORY cat_1 ON SELECT * FROM dual;

CREATE outline outline_2 FOR CATEGORY cat_2 ON SELECT * FROM dual;

CREATE outline outline_3 FOR CATEGORY cat_2 ON SELECT * FROM dual A;

Итак, имеется три хранимых шаблона в двух категориях. Для запроса SELECT * FROM DUAL есть два хранимых шаблона, а для запроса SELECT * FROM DUAL A - один. Посмотрим, что имеется сейчас:

SELECT category, name, sql_text FROM user_outlines ORDER BY category, name:

В категории САТ_1 - 1 шаблон, а в категории САТ_2 - 2 шаблона. Более того в категории САТ_2 есть шаблон с таким же значением в столбце SQL_TEXT, что и в шаблоне в категории САТ_1. Теперь объединим категории:

EXEC outln_pkg. update_by_cat('CAT_2', 'САТ_1');

SELECT category, name, sql_text FROM user_outlines ORDER BY category, name:

Шаблон из категории САТ_2 для запроса, не входящего в категорию САТ_1, был перенесен. Хранимый шаблон для дублирующегося запроса, однако, не перенесен. Дело в том, что все шаблоны должны быть уникальны по столбцу NAME и паре столбцов (CATEGORY, SIGNATURE). В пределах категории значения в столбце SQL_TEXT должны быть уникальны. Это обеспечивается путем генерации уникальной сигнатуры для значения SQL_TEXT. Если необходимо перенести шаблон OUTLINE_2 из категории САТ_2 в категорию САТ_1, придется удалить шаблон OUTLINE_1 из категории САТ_1 перед выполнением процедуры UPDATE_BY_CAT.

DROP OUTLINE outline_l;

EXEC outln_pkg. update_by_cat (‘CAT_2', 'CAT_1’);

SELECT category, name, sql_text FROM user_outlines ORDER BY category, name;

Список литературы

1.  Томас Кайт. Oracle для профессионалов. Книга 1-2. Архитектура и основные особенности. - К: DiaSoft, 2005. - С- ISBN -5

2.  Алапати. Oracle 11g: руководство администратора баз данных. - М.: Вильямс, 2009. С. 1341. - ISBN 1592-4

3.  Рик Гринвальд, Роберт Стаковьяк, Гэри Додж, Дэвид Кляйн, Бен Шапиро, Кристофер Дж. Челья. Программирование баз данных Oracle для профессионалов. - М.: Диалектика, 2007. - С. 784. - ISBN -5