12 ответов

Вы можете использовать Oracle Enterprise Manager для мониторинга активных сеансов, выполнения запроса, его плана выполнения, блокировок, некоторых статистических данных и даже индикатора выполнения для более длительных задач.

Перейдите в Экземпляр → сеансы и просмотрите вкладку SQL каждого сеанса.

И, конечно, вы также можете использовать инструмент Explain PLAN FOR, TRACE и множество других способов инструментализации. В менеджере предприятия есть несколько отчетов для самых дорогостоящих SQL-запросов. Вы также можете искать последние запросы, хранящиеся в кеше.

Я нашел простое решение

Шаг1. подключиться к БД с помощью пользователя-администратора, используя PLSQL или sqldeveloper или любой другой интерфейс запросов

Step2. запустите script ниже; в столбце S.SQL_TEXT вы увидите выполненные запросы

SELECT S.LAST_ACTIVE_TIME, S.MODULE, S.SQL_FULLTEXT, S.SQL_PROFILE, S.EXECUTIONS, S.LAST_LOAD_TIME, S.PARSING_USER_ID, S.SERVICE FROM SYS.V_$SQL S, SYS.ALL_USERS U WHERE S.PARSING_USER_ID=U.USER_ID AND UPPER(U.USERNAME) IN ("oracle user name here") ORDER BY TO_DATE(S.LAST_LOAD_TIME, "YYYY-MM-DD/HH24:MI:SS") desc;

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

Alter system set timed_statistics=true

Alter session set timed_statistics=true --if want to trace your own session

Должно быть достаточно большим:

Select value from v$parameter p where name="max_dump_file_size"

Узнайте, сколько сид и серийный номер сессии вам интересны:

Select sid, serial# from v$session where ...your_search_params...

You может начать трассировку с событием 10046, четвертый параметр устанавливает уровень трассировки (12 - самый большой):

Begin sys.dbms_system.set_ev(sid, serial#, 10046, 12, ""); end;

Turn отключить трассировку с нулевым уровнем установки:

Begin sys.dbms_system.set_ev(sid, serial#, 10046, 0, ""); end;

/* возможные уровни: 0 - выключено 1 - минимальный уровень. Подобно набору sql_trace = true 4 - значения переменных привязки добавляются в файл трассировки 8 - добавляются ожидания 12 - добавляются значения привязки переменных и события ожидания */

Same, если вы хотите трассировать свою сессию с большим уровнем:

Alter session set events "10046 trace name context forever, level 12";

Turn выключен:

Alter session set events "10046 trace name context off";

File с необработанной информацией о трассировке:

Select value from v$parameter p where name="user_dump_dest"

Name файла (*. Trc) будет содержать spid:

Select p.spid from v$session s, v$process p where s.paddr=p.addr and ...your_search_params...

Also вы можете установить имя самостоятельно:

Alter session set tracefile_identifier="UniqueString";

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

C:\ORACLE\admin\databaseSID\udump> C:\ORACLE\admin\databaseSID\udump>tkprof my_trace_file.trc output=my_file.prf TKPROF: Release 9.2.0.1.0 - Production on Wed Sep 22 18:05:00 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. C:\ORACLE\admin\databaseSID\udump>

To посмотреть состояние использования файла трассировки:

Set serveroutput on size 30000; declare ALevel binary_integer; begin SYS.DBMS_SYSTEM.Read_Ev(10046, ALevel); if ALevel = 0 then DBMS_OUTPUT.Put_Line("sql_trace is off"); else DBMS_OUTPUT.Put_Line("sql_trace is on"); end if; end; /

Еще пара - в SQL * Plus - SET AUTOTRACE ON - предоставит план объяснения и статистику для каждого выполненного оператора.

TOAD также позволяет профилировать на стороне клиента.

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

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

Catch - это захват всех SQL-операций между двумя моментами времени. Как и SQL Server.

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

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

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

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

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

Сделайте первый снимок Запустите следующий sql для создания первого моментального снимка:

Create table sql_exec_before as select executions,hash_value from v$sqlarea /

Получить пользователя для выполнения своей задачи в приложении.

Сделайте второй снимок.

Create table sql_exec_after as select executions, hash_value from v$sqlarea /

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

Встроено множество диагностического кода. Часть его, например, sql_trace , хорошо описана в документации, а часть, например, представление x$trace , не документирована вовсе. Я люблю периодически посвящать некоторое время повторному анализу такого кода, чтобы узнать, насколько расширены его возможности, получили ли они официальное признание и описаны ли в документации. Недавно, работая с сервером Oracle 9i, я с удивлением обнаружил существенное расширение возможностей oracle_trace , которое произошло за последних пару релизов. Эта статья представляет собой краткое введение в oracle_trace и описание его возомжностей.

Как... ?

Как найти объект, являющийся источником всех событий buffer busy waits , которые можно увидеть в представлении v$waitstat ?

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

Вариант 1: выполнять непрерывный поток запросов к представлению v$session_wait и проверять значения столбцов p1 , p2 , p3 при возникновении этого события. Статистически, рано или поздно вы получите таким образом обоснованное представление о том, какой объект или объекты являются причиной проблемы. Этот вариант - достаточно болезненный, и результат его отчасти зависит от везения.

Вариант 2: включить событие 10046 на уровне 8 и получить поток информации об ожиданиях в трассировочных файлах. Весьма серьезно нагружает систему и тоже требует некоторого везения.

Вариант 3: есть событие (10240), которое должно порождать в трассировочном файле список адресов блоков, которые мы ожидаем (ура!), но мне еще не удавалось заставить это событие работать. Если вы знаете, как это сделать, сообщите мне, поскольку данное решение, безусловно, является оптимальным.

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

Что такое oracle_trace?

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

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

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

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

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

Использование средств oracle_trace

Ну, и как oracle_trace поможет ответить на исходный вопрос?

Просто: один из классов событий, которые можно трассировать, - ожидания. Надо проверить, что сервер запущен в режиме, позволяющем включить трассировку, а затем потребовать от него (либо с помощью PL/SQL, либо из командной строки) начать трассировать ожидания (waits). При этом мы ограничиваем набор событий одижания только событием 92 (это buffer busy waits в Oracle 9i, но проверьте, на всякийц случай, значения столбцов event# и name из представления v$event_name в вашей системе). Затем остается сидеть и ждать примерно час в период, когда проблема ощущается острее всего. Когда получим достаточно большой файл трассировки, прекращаем трассировку, помещаем данные из файла трассировки в базу и выполняем SQL-оператор, запрашивающий, скажем, следующее:

Для каких объектов возникали события buffer busy waits , сколько приходилось ждать, как часто возникали ожидания и кто более всего пострадал?

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

Собираем все вместе

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

Рисунок 1: Параметры инициализации, связанные с oracle_trace

Параметру oracle_trace_collection_name нужно явно задать пустое значение "" , ибо его стандартное значение - "oracle" , а если имя набора указано и трассировка включена, сервер Oracle выполняет трассироку на уровне экземпляра с момента запуска (ого!).

Параметр oracle_trace_collection_path задает каталог, в котором будут размещаться файлы. В каталоге oracle_trace_facility_path размещаются списки событий, которые можно трассировать (facility definition files - файлы определения средств, предоставляемые Oracle Corporation). Параметр oracle_trace_facility_name задает список событий, которые нас интересуют. Наконец, можно ограничить размер (в байтах) файла с трассировочной информацией, задав значение параметра oracle_trace_collection_size .

После запуска сервера можно начинать сбор трассировочной информации.

В этой статье я буду использовать только средства командной строки, хотя есть и альтернативный PL/SQL-интерфейс (пакет dbms_oracle_trace_agent - прим. переводчика), и даже графический интерфейс, если купить соответствующий модуль для Oracle Enterprise Manager. Мы будем использовать команду следующего вида:

Otrccol start 1 otrace.cfg

Команда otrccol - основной интерфейс для oracle_trace . Есть и другие команды, но большинство их возможностей были добавлены в otrccol . Очевидно, что параметр start требует начать трассировку (а параметр stop - ее остановить). Значение "1 " - произвольно выбранный идентификатор задания, а otrace.cfg - файл конфигурации. Пример файла конфигурации представлен на рис. 2.

Рисунок 2: Пример файла конфигурации oracle_trace

Этот файл требует от сервера создать файл с набором данных по имени jpl.dat , с файлом определения набора (collection definition file) по имени jpl.cdf и идентификатором набора jpl . Определение трассируемых средств находится в файле waits.fdf (этот файле предоставляется корпорацией Oracle и содержит только события ожидания). Размер файла трассировки будет ограничен 10 Мбайтами, но он будет использоваться повторно, так что, всегда будет содержать 10 Мбайт последних данных. Перед сбросом данных в этот файл сервер Oracle будет накапливать их в буфере размером 1 Мбайт.

Возможность задать regid - одна из наиболее мощных возможностей oracle_trace . "Стандартное" значение этой строки содержит "0 0" вместо моих "7 92" , и треубет, чтобы oracle_trace трассировал весь экземпляр Oracle, который задается идентификатором d901 в конце строки. Я же попросил трассировать только средство номер 7 (события ожидания) элемент 92 (ожидания buffer busy waits ).

При необходимости можно указывать в файле несколько строк regid . Для первого набора экспериментов я использовал две строки regid в файле конфигурации, задающие трассировку "7 129" и "7 130" - последовательные (sequential) и выборочные (scattered) чтения, соответственно, поскольку эти типы ожиданий легко сгенерировать.

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

После того, как система поработает некоторое время, выполним:

Otrccol stop 1 otrace.cfg otrccol format otrace.cfg

Первая команда останавливает трассировку, вторая - читает файл и сбрасывает данные в ряд таблиц Oracle.

Однако прежде чем вы сможете сформатировать набор, надо создать схему, в которой будут находиться таблицы, используемые при форматировании. В качестве имени и пароля пользователя мы используем значения, представленные ранее на рис. 2. Строка full_format=1 в файле конфигурации приводит к тому, что в таблицы будет сброшен весь файл; установка full_format=0 приведет к сбросу только новых данных. Обратите внимание также на имя службы (service ) - оно задает базу данных, в которой находится соответствующая учетная запись. Чтобы использовать команду format , надо запустить процесс прослушивания TNS (TNS listener), даже если данные сбрасываются в локальную базу.

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

Рисунок 3: Создание пользователя, в схеме которого будут находиться таблицы трассировки

При указании опции format программа автоматически (по крайней мере, в новых версиях Oracle) создаст необходимые таблицы в указанной схеме. Часть этих таблиц бцдет иметь вполне осмысленные имена, например:

EPC_COLLECTION

Имена других будут лишены всякого смысла:

V_192216243_F_5_E_9_9_0

Проблему с неудобными именами можно решить, запусив сценарий otrcsyn.sql в каталоге $ORACLE_HOME/otrace/demo .

Этот сценарий создает синонимы для таблиц, давая им осмысленные имена, например:

WAIT CONNECTION

(Имена отличаются в разных версиях Oracle.)

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

Некоторые результаты

Итак, что же мы сделали:

• Создали файл конфигурации
• Начали сбор данных
• Выполнили определенные действия в базе данных
• Остановили сбор данных
• Сформатировали набор данных

И что теперь?

Предположим, мы использовали файл конфигурации, представленный на рис. 2. Подключившись от имени учетной записи otrace , мы обнаружим строки в таблицах connection , disconnect и wait . Строки в таблице wait расскажут на все о событиях buffer busy waits , произошедших за время трассировки.

Например, мы могли выполнить SQL-оператор, представленный на рис. 4:

select p1 file_id, p2 block_id, p3 reason_code, count(*) ct, sum(time_waited)/100 secs from wait group by p1, p2, p3 order by sum(time_waited) desc ;

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

Если необходимо большая точность, можно выдать все ожидания с временными отметками, и столбцом, (довольно оптимистично) названным timestamp_nano .

Если необходимо выяснить, каким пользователям пришлось ждать дольше всего, измените запрос, и суммируйте по столбцам session_index (SID) и session_serial (serial# ). Для получения по значениям (session_index , session_serial ) имени пользователя, имени машины, времени регистрации и т.п. можно использовать таблицу (синоним) connection .

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

А если необходимо выявить конкретные SQL-операторы, при выполнении которых пришлось ждать, всегда, хотя и ценой затраты еще больших ресурсов, можно перейти на использование файла sql_waits.fdf , который приводит к заполнению трассировочной информацией еще нескольких таблиц, которые затем можно соединять по столбцам session_index , session_serial , timestamp и timestamp_nano .

Наконец, если вы думаете, что затраты на загрузку данных в таблицы и построение отчетов отрицательно скажутся на системе, всегда можно перенести файлы cdf и dat на другую машину и обрабатывать их в другой базе данных. Мне удалось даже, с небольшими исправлениями, сгенерировать набор данных на экземпляре версии 9i, а затем обработать их на экземпляре версии 8i, просто чтобы доказать эту возможность. Это, конечно, затруднит возможность по номерам блоков определять объекты.

Будущее

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

Еще одна возможность трассировки, которая многим пригодиться, представлена файлом connect.fdf . Он перехватывает подключения и отключения сеансов, во многом аналогично тому, как работает команда audit session . Однако в трассировочном файле накапливается еще полдюжины дополнительных статистических показателей (таких как записи повторного выполнения), которые в таблицу aud$ не попадают; и в процессе накопления запись в базу данных не выполняется.

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

Заключение

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

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

В конечном итоге, oracle_trace позволяет получать точные ответы на ряд сложных вопросов, беспокоящих АБД многие годы.

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

Проблема

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

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

"У меня программа стала работать МЕДЛЕННО !"

При этом совсем нет времени на детальное изучение проблемы, да еще и рабочие сервера как правило работают с параметром sql_trace = false ...
В этом случае я для себя нашел следующее "экспресс"-решение.

1. Просим пользователя "зайти" в программу

2. Находим sid и serial сессии пользователя (select sid, serial# from v$session where <условия поиска>)

3. Выполняем такой PL/SQL блок
declare
-- 0/1/null <--> false/true/null
-- Boolean parameters are translated from/to integers:
sql_trace boolean:= sys.diutil.int_to_bool(1);
begin
-- Call the procedure
dbms_system.set_sql_trace_in_session
(sid => 56,
serial# => 6656,
sql_trace => sql_trace);
end;
После его выполнения начинается трассировка указанной сессии.

4. Просим пользователя выполнить долгую операцию...

6. Отключает трассировку, выполнив еще раз этот скрипт, указывая параметр sql_trace = 0

7. Находим в каталоге UDUMP сервера файл.trc
Имя файла содержит в себе идентификатор процесса операционной системы. Найти его можно так
SQL> select p.spid from v$session s, v$process p
2 where s.paddr=p.addr
3 and ...мои_критерии_отбора...
4 /

8. Обрабатываем этот файл утилитой TKPROF (TKPROF <имя trc-файла> <имя выходного файла>)

9. В полученном файле анализируем финальные итоги, оценивая параметры CPU и ELAPSED
Например, сегодня я получил такую картину .
И итогах видно, что проблема ЕСТЬ и она сосредоточена во времени ожидания получения данных (20.85 сек)
Надо искать проблемные запросы.

10. В Window с помощью команды find "total" <имя выходного файла> >1.txt
ищем все строки, которые начинаются с "total" и сохраняем их в файл 1.txt

11. Бегло проматриваем этот файл и находим значения, которые по свей величине нас не устраивают.
Для моего сегодняшнего случая было 2 таких тотальных строки для двух запросов, которые занимали практически собой все время ожидания.
total 9 0.00 0.00 0 21 0 9
total 3 0.03 0.02 0 3 0 0
total 3 0.01 0.00 0 14 0 5
total 3 0.64 10.89 11848 17504 0 4
total 3 0.07 0.06 0 214 0 29
total 15 0.00 0.00 0 15 0 5
total 3 0.01 0.00 0 3 0 0
total 3 0.00 0.00 0 4 0 1
total 3 0.00 0.00 0 8 0 6
total 6 0.04 0.02 0 8 0 2
total 3 0.00 0.00 0 4 0 1
total 3 0.00 0.00 0 3 1 1
total 3 0.00 0.00 0 2 0 1
total 3 0.01 0.00 0 8 2 1
total 2 0.00 0.00 0 4 1 1
total 3 0.00 0.00 0 2 0 1
total 3 0.21 9.85 7760 12354 0 1
total 6 0.00 0.00 0 0 0 0
total 6 0.00 0.00 0 0 0 0
Дальше по этим значениям из этих строк в обработанном trace-файле находим проблемные запросы и разбираемся с ними.
Мне сегодня было достаточно построить два индекса...
ВСЕ...

По моему опыту с помощью этих нехитрых манипуляций решаются около 90 процентов замечаний по производительности серверов.

Вы можете спросить, а зачем выполнять 10 пункт. Ведь можно и в обработанном TRC-файле искать проблемные запросы. КОНЕЧНО МОЖНО. Не если таких запросов в одном trace несколько сотен, то проще бегло просмотреть или обработать только тотал, а потом перейти к деталям...