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

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

• REGISTER. Зарегистрироваться на получение указанного сигнала. В сеансе можно многократно вызывать подпрограмму REGISTER c разными именами сигналов, чтобы получать уведомления при наступлении одного из нескольких событий.

• REMOVE. Снять регистрацию на получение сигнала, чтобы сервер не пытался уведомить о наступлении события.

• REMOVEALL. Снять для сеанса регистрацию на получение всех сигналов.

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

• WAITONE. Ожидать уведомления об указанном сигнале. Как и в случае WAITANY, ждать можно определенное время или вообще не ждать.

Приложение, желающие послать сигнал, или уведомить о событии, может сделать это с помощью следующей подпрограммы.

• SIGNAL. Послать сигнал о наступлении события при фиксации текущей транзакции. При откате посылка сигнала отменяется.

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

BEGIN

DBMS_ALERT. REGISTER (' MyMert') ;

END;

DECLARE

l_status NUMBER;

l_msg VARCHAR2(1800);

BEGIN

DBMS_ALERT. WAITONE (name => 'MyMert',

message => l_msg,

status => l_status,

timeout => dbms_alert. maxwait);

IF (l_status = 0)

THEN

dbms_output. put_line ('Сообщение события: ' || l_msg);

END IF;

END;

Мы зарегистрировались на получение сигнала MyAlert, а затем вызвали процедуру DBMS_ALERT. WAITONE, ожидая поступление этого сигнала. Поскольку использована константа DBMS_ALERT. MAXWAIT из пакета DBMS_ALERT, сеанс при выполнении этого вызова начнет ждать бесконечно. Сеанс блокируется в ожидании соответствующего события. Можно задать для клиентского приложения период ожидания 0 секунд, что исключит ожидание, и опрашивать сервер о наступлении события.

Чтобы послать сигнал, достаточно выполнить следующее в другом сеансе:

BEGIN

DBMS_ALERT. SIGNAL ('MyMert', 'Hello World');

END;

COMMIT;

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

Сообщение события: Hello World.

То есть сеанс больше не заблокирован. Сеансы ждут сигнала с определенным именем. Пока в пославшем сигнал сеансе транзакция не зафиксирована, уведомление о сигнале не посылается. В этом легко убедиться с помощью двух сеансов SQL*Plus.

Работа с сигналами становится более интересной, если задаться следующими вопросами.

• Что происходит, если несколько сигналов более-менее одновременно отправляются разными сеансами?

• Что происходит, если посыпать сигнал несколько раз: сколько сигналов будет сгенерировано в конечном итоге?

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

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

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

BEGIN

DBMS_ALERT. SIGNAL ('MyMert', 'Hello World');

END;

(транзакции не фиксируются). Окажется, что сеанс, пославший сигнал вторым, заблокирован. Это показывает, что если N сеансов одновременно пытаются послать один и тот же сигнал, N-1 из них будут заблокированы при вызове DBMS_ALERT. SIGNAL. Продолжит работу только один из сеансов. Сигналы должны посылаться последовательно, и следует позаботиться о предотвращении подобных проблем. Например, при получении оперативных данных из внешнего источника пакет DBMS_ALERT вполне можно использовать, если данные в таблицу вставляет только один сеанс. Если же речь идет о таблице проверки, в которую часто вставляют данные все сеансы, средства пакета DBMS_ALERT лучше не использовать.

Что произойдет, если послать одноименный сигнал в приложении несколько раз, а затем зафиксировать транзакцию? Сколько сигналов фактически будет послано? В данном случае ответ: один.

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

BEGIN

FOR i IN 1LOOP

DBMS_ALERT. SIGNAL ('MyAlert', 'Сообщение ' || i ) ;

END LOOP;

END;

COMMIT;

И в первом окне получаем результат:

Сообщение события: сообщение 10

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

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

DBMS_ALERT. SIGNAL в одном сеансе, запоминается только последний сигнал, и именно о нем получат уведомление сеансы. В этом можно убедиться, добавив команду PAUSE к используемому в примерах сценарию SQL*Plus:

BEGIN

DBMS_ALERT. REGISTER ('MyAlert');

END;

PAUSE

Затем в других сеансах вызовите процедуры DBMS_ALERT. SIGNAL с уникальными сообщениями (чтобы их можно было различать) и зафиксируйте каждое сообщение. Например, измените представленный ранее простой цикл следующим образом:

BEGIN

FOR i IN 1LOOP

DBMS_ALERT. SIGNAL ('MyMert', 'Сообщение ' || i);

COMMIT;

END LOOP;

END;

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

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

DBMS_PIPE

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

Пакет DBMS_PIPE, в отличие от DBMS_ALERT, - это пакет, работающий в режиме реального времени. При вызове функции SEND_MESSAGE немедленно посылается сообщение. Сервер не ждет выполнения оператора COMMIT; передача сообщения выполняется вне транзакции. Это позволяет использовать пакет DBMS_PIPE в тех случаях, когда DBMS_ALERT не подходит (и наоборот). С помощью пакета DBMS_PIPE можно обеспечить диалоговое взаимодействие двух сеансов (что с помощью DBMS_ALERT сделать невозможно). Один сеанс может "попросить" другой выполнить некоторое действие. Выполнив его, второй сеанс возвращает первому результат. Предположим, второй сеанс - это С-программа, которая снимает показания термометра, подключенного к последовательному порту компьютера, и возвращает значение температуры первому сеансу. Первому сеансу надо записать текущую температуру в базу данных. Он может послать сообщение "дай мне значение температуры" второму сеансу, который определяет это значение и выдает ответ первому сеансу. Первый и второй сеансы могут работать на разных компьютерах, главное, оба они подключены к одной базе данных. При использовании пакета DBMS_PIPE не нужно знать имя хоста и номер порта для подключения - достаточно имени канала базы данных, в который надо отправить запрос.

В базе данных есть два типа каналов - общедоступные и пользовательские. Общедоступный канал можно создать явно, вызвав CREATE_PIPE, либо неявно, послав в него сообщение. Основное отличие между явно и неявно созданными каналами состоит в том, что канал, созданный явным вызовом CREATE_PIPE, удаляется приложением по завершении работы, тогда как неявно созданный канал удаляется из области SGA как устаревший после определенного промежутка времени. Общедоступный канал устроен так, что любой сеанс, имеющий доступ к пакету DBMS_PIPE, может читать и записывать в него сообщения. Поэтому общедоступные каналы не подходят для передачи секретных или просто важных данных. Поскольку каналы обычно используются для диалога, а общедоступные каналы позволяют перехватывать или вмешиваться в этот диалог любому, злонамеренный пользователь может удалять сообщения из канала либо добавлять свои, "мусорные". Любое из этих действий нарушает диалог или протокол обмена данными между сеансами. Поэтому в большинстве приложений применяются пользовательские каналы.

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

Канал - это объект в области SGA экземпляра Oracle. Этот механизм вообще не связан с диском. Данные в каналах теряются при остановке и перезапуске сервера. Чаще всего каналы используют для создания специализированных служб или серверов.

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

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

Пример передачи сообщения 'ku-ku' в канал 'PIPE':

DECLARE a NUMBER;

b VARCHAR2(20);

BEGIN

a:=DBMS_PIPE. CREATE_PIPE ('PIPE');

DBMS_PIPE. PACK_MESSAGE ('ku-ku');

END;

Пример приема сообщения из канала 'PIPE':

DECLARE a NUMBER;

b VARCHAR2(20);

BEGIN

a:=DBMS_PIPE. SEND_MESSAGE('PIPE');

a:=DBMS_PIPE. RECEIVE_MESSAGE('PIPE');

DBMS_PIPE. UNPACK_MESSAGE(b);

DBMS_OUTPUT. PUT_LINE(b);

END;

DBMS_UTILITY

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

Процедура COMPILE_SCHEMA предназначена для перекомпиляции недействительных (invalid) процедур, пакетов, триггеров, представлений, типов и других объектов схемы.

Эта процедура выполняет оператор ALTER COMPILE от имени пользователя, который вызвал процедуру COMPILE_SCHEMA (т. е. она работает с правами вызывающего).

Процедура COMPILE_SCHEMA требyeт передавать имена пользователей в верхнем регистре. Если вызвать:

BEGIN

PILE_SCHEMA ('scott');

END;

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

Типичное заблуждение состоит в том, что объекты надо компилировать в строго определенном порядке. На самом деле компилировать объекты можно в произвольном порядке и получить тот же результат, что и при компиляции в порядке, определяемом зависимостями. Алгоритм следующий:

1.  Выбираем недействительный объект схемы, который мы еще не пытались перекомпилировать.

2.  Компилируем его.

3.  Возвращаемся к первому шагу, пока есть недействительные объекты, которые мы еще не пытались перекомпилировать.

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

Процедура ANALYZE_SCHEMA делает именно то, что можно предположить по ее названию, - выполняет операторы ANALYZE для сбора статистической информации об объектах в пользовательской схеме. Не рекомендуется применять ее для схем SYS или SYSTEM. В особенности не надо этого делать для схемы SYS, поскольку рекурсивные SQL-операторы, которые СУБД Oracle генерирует уже многие годы, оптимизированы для обработки оптимизатором, основанным на правилах. При наличии статистической информации о таблицах в схеме SYS сервер будет работать медленнее, чем мог бы. Эту процедуру можно использовать для анализа созданных пользователями прикладных схем.

Процедура ANALYZE_SCHEMA принимает пять аргументов.

• SCHEMA. Схема, которую необходимо проанализировать.

• METHOD. ESTIMATE, COMPUTE или DELETE. Если передано значение ESTIMATE, то одно из значений: ESTIMATE_ROWS, ESTIMATE_PERCENT должно быть ненулевым.

• ESTIMATE_ROWS. Количество оцениваемых строк.

• ESTIMATE_PERCENT. Процент оцениваемых строк. Если передано ненулевое значение параметра ESTIMATE_ROWS, этот параметр игнорируется.

• METHOD_OPT [FOR TABLE] [FOR ALL [INDEXED] COLUMNS] [SIZE n] [FORALL INDEXES]. Это те же опции, что используются в операторе ANALYZE.

Итак, например, все объекты в пользовательской схеме SCOTT можно проанализировать следующим образом. Начнем с удаления статистической информации, а затем соберем ее снова:

BEGIN

dbms_utility. analyze_schema (user, 'delete');

END

SELECT table_name, num_rows, and last_analyzed FROM user_tables;

BEGIN

dbms_utility. analyze_schema(user, 'compute');

END;

SELECT table_name, num_rows, last_analyzed FROM user_tables;

Этот простой пример показывает, что оператор ANALYZE COMPUTE выполняется - столбцы NUM_ROWS и LAST_ANALYZED получили значения. Процедура ANALYZE_SCHEMA работает в соответствии со своим названием. Если необходимо анализировать объекты с разной степенью детализации, она не поможет.

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

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

Стратегии и средства настройки приложений

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

Существует три уровня настройки:

• Настройка приложения, часть 1. Настройка изолированного приложения. Обеспечение его максимально быстрой работы в однопользовательском режиме.

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

• Настройка экземпляра/сервера.

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

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

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

EXPLAIN PLAN

До использования оператора EXPLAIN PLAN надо создать таблицу PLAN_TABLE, куда и будут записаны результаты разбора SQL – запроса. Скрипт на создание этой таблицы:

@ [ORACLE_HOME]/rdbms/admin/utlxplan. sql

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

EXPLAIN PLAN FOR SELECT COUNT (*) FROM hr. employees;

Результат разбора можно посмотреть следующим образом:

SELECT id, operation, cost FROM plan_table;

Или можно именовать ( 'join1') план выполнения:

EXPLAIN PLAN SET statement_id = 'join1'

FOR SELECT last_name, department_name FROM hr. employees JOIN

hr. departments USING (department_id)

И результат можно посмотреть двумя способами:

SELECT statement_id, id, operation, cost FROM plan_table WHERE statement_id = ‘join1’;

или

SELECT * FROM TABLE (dbms_xplan. display ('PLAN_TABLE,'join1', 'BASIC'));

Вместо вида отображения BASIC можно еще получить TYPICAL (по умолчанию), SERIAL, ALL.

Утилита SQL*Plus предлагает средство AUTOTRACE, позволяющее получать планы выполнения обрабатываемых запросов, а также информацию об используемых ресурсах, без выполнения команды EXPLAIN PLAN. Соответствующий отчет генерируется после успешного выполнения операторов SELECT, DELETE, UPDATE и INSERT. Средство AUTOTRACE можно настроить несколькими способами.

• зарегистрироваться в SQL*Plus от имени SYSTEM;

• запустить сценарий @utlxplan из каталога [ORACLE_HOME]/rdbms/admin;

• выполнить оператор CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;

• выполнить оператор GRANT ALL ON PLAN_TABLE TO PUBLIC.

Можно заменить GRANT... ТО PUBLIC оператором GRANT для конкретного пользователя. Предоставляя привилегию роли PUBLIC, вы фактически разрешаете трассировать операторы в SQL*Pius любому пользователю. Это неплохо - пользователи могут не устанавливать собственные таблицы планов.

Альтернатива этому - запуск сценария @UTLXPLAN в каждой схеме, где необходимо использовать средство AUTOTRACE.

Следующий шаг - создание и предоставление всем роли PLUSTRACE:

• зарегистрироваться в SQL*Plus от имени SYS;

• запустить сценарий @plustrce из каталога [ORACLE_HOME]/sqlplus/admin;

• выполнить оператор GRANT PLUSTRACE TO PUBLIC.

И в этом случае, можно заменить PUBLIC в операторе GRANT именем конкретного пользователя.

Управлять информацией, выдаваемой в отчете о плане выполнения, можно с помощью установки системной переменной AUTOTRACE.

SET AUTOTRACE OFF - Отчет AUTOTRACE не генерируется. Так происходит по умолчанию.

SET AUTOTRACE ON EXPLAIN - В отчете AUTOTRACE показывается только выбранный оптимизатором план.

SET AUTOTRACE ON STATISTICS - В отчете AUTOTRACE показывается только статистическая информация о выполнении оператора SQL

SET AUTOTRACE ON - В отчет AUTOTRACE включается как выбранный оптимизатором план, так и статистическая информация о выполнении оператора SQL.

SET AUTOTRACE TRACEONLY - Аналогично SET AUTOTRACE ON, но подавляет выдачу результатов выполнения запроса.

План выполнения отражает выбранный оптимизатором способ выполнения запроса. Каждая строка плана выполнения имеет порядковый номер. Утилита SQL*Plus также выдает номер строки родительской операции.

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

ID_PLUS_EXP - Показывает порядковый номер шага выполнения

PARENT_ID_PLUS_EXP - Показывает для каждого шага родительский шаг. Этот столбец полезен в больших отчетах.

PLAN_PLUS_EXP - Показывает описание шага выполнения.

OBJECT_NODE_PLUS_EXP - Показывает использованные базы данных или серверы для параллельного запроса.

SQL_TRACE, TIMED_STATISTICS и TKPROF

Параметр SQL_TRACE включает регистрацию всех операторов SQL, выполняемых приложением, информации о производительности, полученной в ходе выполнения этих операторов SQL, и фактически использованных планов выполнения операторов. AUTOTRACE иногда показывает неверный план, а вот параметр SQL_TRACE и утилита TKPROF показывают именно тот план, который реально использован. TIMED_STATISTICS - это параметр, при установке которого сервер регистрирует продолжительность выполнения каждого шага. Наконец, TKPROF - это простая программа, используемая для преобразования файла трассировки в более удобочитаемый вид. Продемонстрируем, как использовать установку SQL_TRACE и утилиту TKPROF, и разъясним значение содержимого используемых ими файлов.

Параметр TIMED_STATISTICS управляет тем, будет ли сервер Oracle собирать информацию о времени выполнения различных действий в базе данных. Он может иметь одно из двух значений: TRUE или FALSE. Обычно устанавливают значение TRUE, даже когда не занимаются настройкой - влияние этого значения на производительность СУБД, как правило, незначительно. Значение этого параметра можно устанавливать как на уровне системы, так и на уровне сеанса, а также глобально, в файле параметров инициализации экземпляра. Достаточно просто добавить в файл INIT. ORA для экземпляра строку:

TIMED_STATISTICS = TRUE

и при следующем перезапуске СУБД этот параметр будет включен. Для установки его на уровне сеанса выполните следующую команду:

ALTER SESSION SET timed_statistics=true;

А для включения учета времени во всей системе:

ALTER SYSTEM SET timed_statistics=true;

Параметр SQL_TRACE также можно устанавливать на уровне системы или сеанса. При его установке генерируется так много данных и работа системы так замедляется, что включать ею лучше избирательно (редко или вообще никогда его не устанавливают для системы в файле init. ora). Параметр SQL_TRACE тоже может иметь одно из двух значений, TRUE или FALSE. Если установлено значение TRUE, в каталоге, задаваемом параметром USER_DUMP_DEST файла init. ora при подключении к выделенному серверу или BACKGROUND_DUMP_DEST - при подключении к многопотоковому (MTS) серверу, будут генерироваться трассировочные файлы. Не рекомендуется использовать SQL_TRACE при подключении в режиме MTS, поскольку результаты запросов сеанса будут записываться в различные трассировочные файлы при переходе сеанса с одного разделяемого сервера на другой. При подключении в режиме MTS интерпретация результатов SQL_TRACE практически невозможна. Еще один важный параметр в файле init. ora - MAX_DUMP_FILE_SIZE. Он ограничивает максимальный размер генерируемого сервером трассировочного файла. Если обнаружится, что трассировочные файлы - усеченные, увеличьте значение этого параметра. Это можно сделать с помощью оператора ALTER SYSTEM или ALTER SESSION.

Параметр MAX_DUMP_FILE_SIZE можно задать тремя способами.

• Числовое значение параметра MAX_DUMP_FILE_SIZE задает максимальный размер в блоках файловой системы.

• Число, за которым следует суффикс К или М, задает размер в килобайтах или мегабайтах, соответственно.

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

Не рекомендуется устанавливать значение UNLIMITED - так можно заполнить всю файловую систему; значения в диапазоне от 50 до 100 Мбайт обычно более чем достаточно.

Существуют следующие способы включения параметра SQL_TRACE:

• ALTER SESSION SET SQL_TRACE=TRUE|FALSE. Выполнение этого оператора SQL позволит включить стандартный режим трассировки SQL_TRACE в текущем сеансе. Этот оператор наиболее полезен в интерактивной среде типа SQL*Plus или при встраивании в приложение, так чтобы из приложения можно было при необходимости включать и отключать трассировку. Возможность просто включать и отключать SQL_TRACE средствами приложения - будь-то опция командной строки, пункт меню или параметр конфигурации - полезна в любом приложении.

• SYS. DBMS_SYSTEM. SET_SQL_TRACE_IN_SESSION. Эта процедура позволяет устанавливать и сбрасывать трассировку для любого существующего сеанса. Для этого необходимо указать лишь параметры SID и SERIAL# соответствующего сеанса - их можно получить из представления динамической производительности V$SESSION.

• ALTER SESSION SET EVENTS. Можно установить событие, обеспечивающее трассировку с большим объемом регистрируемой информации, чем обычно получается при установке ALTER SESSION SET SQL_TRACE=TRUE. С помощью этого события можно не только получить всю информацию, выдаваемую при установке SQL_TRACE, но и значения связываемых переменных в SQL-операторах, а также информацию о том, какие события ожидаются (что замедляет работу) при выполнении этих SQL-операторов.

Методы установки SQL_TRACE оператором ALTER SESSION SET SQL_TRACE и вызовом SYS. DBMS_SYSTEM - очень просты и очевидны. Использование события несколько менее тривиально. При этом используется внутренний механизм событий сервера Oracle. Используются следующие команды:

ALTER SESSION SET EVENTS '10046 trace name context forever, level <N>';

ALTER SESSION SET EVENTS '10046 trace name context off;

где N может иметь одно из следующих значений:

N=1. Включает стандартные средства SQL_TRACE. Результат не отличается от установки SQL_TRACE=true.

N=4. Включает стандартные средства SQL_TRACE и добавляет в трассировочный файл значения связываемых переменных.

N=8. Включает стандартные средства SQL_TRACE и добавляет в трассировочный файл информацию об ожидании событий на уровне запросов.

N=12. Включает стандартные средства SQL_TRACE и добавляет как значения связываемых переменных, так и информацию об ожидании событий.

Как обеспечить трассировку, если приходится работать с приложением стороннего производителя или с существующим приложением, не поддерживающим включение SQL_TRACE? Используются два подхода. Один из них подходит для клиент-серверного приложения, постоянно подключенного к базе данных. Достаточно запустить приложение и подключиться к базе данных. Затем, выполнив запрос к представлению V$SESSION, можно определить параметры SID и SERIAL# соответствующего сеанса. Теперь можно вызвать SYS. DBMS_SYSTEM. SET_SQL_TRACE_IN_SESSION для включения трассировки указанного сеанса.

Второй способ трассировки - с помощью триггера базы данных на событие LOGON.

CREATE OR REPLACE TRIGGER logon_trigger

AFTER LOGON ON DATABASE

BEGIN

IF (user = 'SCOTT') THEN

EXECUTE IMMEDIATE

'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 4 ' " ;

END IF;

END;

Он обеспечивает включение трассировки при каждом подключении к базе данных.

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

Проверим включение параметра TIMED STATISTICS – должен быть = TRUE:

SHOW PARAMETER TIMED STATISTICS;

Включает трассировку:

ALTER SESSION SET sql_trace=true;

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

SELECT owner, count (*) FROM all_objects GROUP BY owner;

Запрос для получения идентификатора серверного процесса (SPID - server process ID) - он потребуется для идентификации соответствующего трассировочного файла.

SELECT a. spid FROM v$process a, v$session b WHERE a. addr = b. paddr AND b. audsid = userenv ('sessionid');

Значение SPID - часть имени файла трассировки.

Другой способ для идентификации файла трассировки – задание префикса трассировочного файла до начала трассировки:

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SCOTT';

Теперь, получив трассировочный файл, необходимо его сформатировать. Можно читать его и непосредственно. Но около 90 процентов нужной информации легко получить из хорошо сформатированного отчета. Остальные 10 процентов информации обычно не нужны, но если она потребуется, придется получать эту информацию непосредственно из трассировочного файла. Для форматирования трассировочного файла используется утилита командной строки TKPROF. В простейшем случае достаточно выполнить:

TKPROF ora01124_scott. trc report. txt

Параметрами команды TKPROF являются имена файла исходных данных и файла результатов обработки. Теперь достаточно открыть файл REPORT. TXT в текстовом редакторе. После текста исходного запроса идет общая информация о выполнении запроса:

Здесь можно увидеть три основные стадии выполнения запроса.

• Стадия PARSE. На этом этапе сервер Oracle находит запрос в разделяемом пуле (мягкий разбор) или создает новый план его выполнения (жесткий разбор).

• Стадия EXECUTE. Это действия, выполняемые сервером Oracle при открытии курсора или выполнении запроса. Для операторов SELECT соответствующие столбцы часто будут "пустыми", тогда как для операторов UPDATE именно на этой стадии все и делается.

• Стадия FETCH. Для оператора SELECT именно на этом этапе выполняется основная работа, что и будет отражено в отчете, но для операторов типа UPDATE ничего делаться не будет (при выполнении этого оператора данные не извлекаются).

Заголовки столбцов в этом разделе отчета имеют следующие значения:

• CALL. Может иметь одно из значений: PARSE, EXECUTE, FETCH или TOTAL. Показывает, о какой стадии обработки запроса идет речь.

• COUNT. Показывает, сколько раз произошло событие

• CPU. Показывает, сколько секунд процессорного времени заняла эта стадия выполнения запроса. Этот столбец заполняется, только если установлен параметр TIMED_STATISTICS.

• ELAPSED. Показывает, сколько реального времени потребовала эта стадия выполнения запроса. Этот столбец заполняется, только если установлен параметр TIMED STATISTICS.

• DISK. Показывает, сколько физических операций ввода/вывода с диска потребовалось для выполнения запроса.

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

• CURRENT. Показывает, сколько блоков было прочитано в режиме 'CURRENT'. Блоки в режиме CURRENT читаются в том виде, как они есть на момент чтения, а не в режиме согласованного чтения. Обычно блоки для запроса получаются в том виде, как они были на момент начала запроса. В текущем режиме блоки извлекаются в том виде, как они существуют на момент их чтения, а не какими они были ранее. В ходе выполнения оператора SELECT можно увидеть извлечения в режиме CURRENT, связанные с чтением словаря данных в поисках следующего экстента таблицы при полном просмотре (необходима текущая информация об этом, а не согласованное чтение). В ходе изменения мы будем также обращаться к блокам в режиме CURRENT.

• ROWS. Показывает, сколько строк было затронуто на данной стадии обработки. При выполнении оператора SELECT строки будут обрабатываться на стадии FETCH. При выполнении оператора UPDATE количество обработанных строк будет показано на стадии EXECUTE.

В этом разделе отчета надо обратить внимание на следующие особенности.

Если количество выполнений - более одного, то будет значительный процент (около 100) разборов по отношению к выполнениям. Берем количество разборов оператора и делим на количество выполнений. Если получаем в результате 1, значит, запрос разбирался при каждом выполнении, и это надо исправить. Желательно, чтобы это отношение стремилось к нулю. В идеале разбор должен быть один, а выполнений - более одного. Если наблюдается значительное количество разборов, значит, выполняется многократный мягкий разбор запроса. Это может существенно снизить масштабируемость и производительность даже единственного пользовательского сеанса. Необходимо обеспечить однократный разбор и многократное выполнение запроса в сеансе; от разбора SQL-оператора при каждом выполнении надо избавляться.