В многоуровневых средах среднее звено передает запрос клиента через несколько сеансов базы данных. Отслеживать клиента через все эти сеансы очень трудно. Аналогично, в средах с разделяемым сервером очень трудно определять сеанс пользователя, отслеживаемый в любой заданный момент времени. Из-за того, что одно и то же соединение с разделяемым сервером могут использовать многие сеансы, при прослеживании соединения нельзя быть уверенным в том, о каком точно пользователе идет речь в любой конкретный момент времени, потому что активные сеансы, использующие соединение с разделяемым сервером, постоянно меняются.
Во всех перечисленных выше случаях выполнение трассировки одного сеанса становится невозможным. В Oracle Database 10g появился механизм сквозной трассировки (end-to-end tracing), позволяющий уникальным образом идентифицировать и отслеживать одного и того же клиента среди множества сеансов. Атрибут CLIENT_IDENTIFIER уникально идентифицирует клиента и остается одинаковым во всех звеньях. Для выполнения сквозной трассировки можно использовать пакет DBMS_MONITOR. Кроме того, настраивать сквозную трассировку можно и с помощью интерфейса OEM Database Control. Оба этих подхода более подробно рассматриваются в следующих разделах.
Применение пакета DBMS_MONITOR
Как уже было сказано выше, для настройки сквозной трассировки можно применять поставляемый Oracle PL/SQL-пакет DBMS_MONITOR. Для отслеживания сеанса пользователя среди множества звеньев и генерации трассировочных файлов в случае применения этого пакета можно использовать следующие атрибуты:
- идентификатор клиента;
- имя службы;
- комбинация, состоящая из имени службы, имени модуля и названия действия.
Можно указывать комбинацию, состоящую из имени службы, имени модуля и названия действия. Но можно также задавать и только одно имя службы или комбинацию, состоящую только из имени службы и имени модуля. Указывать только лишь название действия, однако, нельзя. Для указания имени модуля и названия действия нужно, чтобы в приложении обязательно использовался пакет DBMS_APPLICATION_INFO. Что касается имени службы, то оно отражается в строке соединении, которая используется для подключения к службе. Если сеанс пользователя не ассоциирован ни с какой службой специальным образом, то им занимается служба sys$users.
Давайте попробуем воспользоваться двумя из предлагаемых в пакете DBMS_MONITOR процедурами: SERV_MOD_ACT_TRACE_ENABLE, которая позволяет задавать такие атрибуты, как имя службы, имя модуля и название действия, и CLIENT_ID_TRACE_ENABLE, которая дает возможность указывать идентификатор клиента. Например:
SQL> EXECUTE dbms_monitor.serv_mod_act_trace_enable
(service_name=>'myservice', module_name=>'batch_job');
PL/SQL procedure successfully completed.
SQL> EXECUTE dbms_monitor.client_id_trace_enable
(client_id=>'salapati');
PL/SQL procedure successfully completed.
SQL>
Для получения идентификатора сеанса клиента предназначена процедура SET_IDENTIFIER из пакета DBMS_SESSION. Ниже приведен пример использования триггера входа и процедуры SET_IDENTIFIER для захвата идентификатора сеанса пользователя сразу же после входа пользователя в систему:
SQL> CREATE OR REPLACE TRIGGER logon_trigger
AFTER LOGON
ON DATABASE
DECLARE
user_id VARCHAR2(64);
BEGIN
SELECT ora_login_user ||':'||SYS_CONTEXT('USERENV','OS_USER')
INTO user_id
FROM dual;
dbms_session.set_identifier(user_id);
END;
С помощью значения для атрибута client_id можно получать значения для столбцов SID и SERIAL# в представлении V$SESSION для любого пользователя и настраивать трассировку для этого client_id. Ниже приведен соответствующий пример:
SQL> EXECUTE dbms_monitor.session_trace_enable (session_id=>111, serial_num=>23, waits=>true, binds=>false);
Далее можно предложить пользователю выполнить проблемный SQL-код и собрать трассировочные файлы, чтобы потом иметь возможность проанализировать их с помощью утилиты TKPROF. Таких файлов может быть особенно много в средах с разделяемым сервером. С помощью утилиты командной строки trcsess информацию из множества трассировочных файлов можно легко консолидировать в один файл. Ниже приведен соответствующий пример (сначала необходимо перейти в каталог дампа соответствующего пользователя или в каталог udump):
$ trcsess output="salapati.trc" service="myservice "module="batch job" action="batch insert"
Затем можно выполнить обычную команду TKPROF в отношении консолидированного трассировочного файла, как показано ниже:
$ tkprof salapati.trc output=salapati_report SORT=(EXEELA, PRSELA, FCHELA)
На заметку! В наших блогах уже было показано, как активизировать трассировку SQL с помощью утилиты SQL Trace, а также пакетов DBMS_SESSION и DBMS_MONITOR. Для выполнения трассировки SQL-операторов следует использовать один из двух упомянутых пакетов, а не утилиту SQL Trace. Любой из этих трех методов можно применять для настройки трассировки как на уровне отдельных сеансов, так и на уровне всего экземпляра. К выполнению трассировки на уровне всего экземпляра, однако, следует относиться осторожно, поскольку она ведет к чрезмерной нагрузке на экземпляр, а также созданию слишком большого количества объемных трассировочных файлов.
Применение Database Control для выполнения сквозной трассировки
Наилучшим, а также рекомендуемым подходом для выполнения сквозной трассировки является применение интерфейса OEM Database Control. При таком подходе не нужно возиться с ручными запусками пакета DBMS_MONITOR. Вот что в случае его применения нужно делать.
- На домашней странице Database Control щелкните на ссылке Performance (Производительность).
- На странице Performance (Производительность) щелкните на ссылке Top Consumers (Главные потребители) в разделе Additional Management Links (Ссылки для дополнительного управления).
- На странице Top Consumers (Главные потребители), на которой будут отображаться вкладки Top Services (Главные службы), Top Module (Главные модули), Top Actions (Главные действия), Top Clients (Главные клиенты) и Top Sessions (Главные сеансы), как показано на рис. ниже, щелкните на вкладке Top Clients (Главные клиенты).
- Для включения механизма агрегирования для клиента выберите клиента и щелкните на кнопке Enable Aggregation (Включить агрегирование).
При желании интерфейс Database Control можно использовать и для выполнения трассировки обычного сеанса SQL вместо команды SET_TRACE и утилиты TKROF. Для выполнения трассировки того или иного пользовательского сеанса на шаге 3 в описанной выше последовательности достаточно щелкнуть не на вкладке Top Clients, а на вкладке Top Session, а затем на кнопке Enable SQL Trace (Включить трассировку SQL). Для остановки процесса трассировки сеанса потом нужно щелкнуть на кнопке Disable SQL Trace (Отключить трассировку SQL), а для просмотра вывода — на кнопке View SQL Trace File (Просмотреть файл трассировки SQL).
На заметку! Просматривать всю примечательную трассировочную информацию в экземпляре можно либо через представление DBA_ENABLED_TRACES, либо в отчете по трассировке, сгенерированном в Database Control.
Использование представления V$SQL для выявления неэффективного SQL-кода
Представление V$SQL является просто бесценным инструментом для отслеживания неэффективного SQL-кода в приложении. Помимо прочей важной информации, оно собирает из области разделяемого пула информацию по всем выполняемым каждым оператором операциям чтения с диска и из памяти. В нем содержатся все SQL-операторы, которые выполнялись с момента запуска экземпляра, но нет никакой гарантии, что они будут содержаться там до самого момента остановки экземпляра. По причинам, связанным с пространством, более старые операторы постепенно удаляются из представления V$SQL. Поэтому лучше предоставлять разработчикам права на выборку данных прямо из этого представления, если им еще не было предоставлено право выбора любого каталога. Представление V$SQL может служить для выполнения нерегламентированных запросов по использованию дисков ЦП, но не следует забывать, что отчет AWR включает в себя суммарные сведения по показателям подобного рода.
В частности, представление V$SQL содержит, помимо прочих вещей, следующие столбцы, которые помогают узнать о том, сколько ресурсов потребляет SQL-оператор.
- rows_processed. Этот столбец показывает, сколько всего строк было обработано оператором.
- sql_text. В этом столбце отображается текст SQL-оператора (а точнее — первые 1000 символов).
- sql_fulltext. Этот столбец представляет собой столбец типа CLOB и показывает, как выглядит весь текст SQL-оператора.
- buffer_gets. Этот столбец позволяет оценить общее количество произведенных операций логического чтения (что может свидетельствовать о высоком потреблении ресурсов ЦП).
- disk_reads. Этот столбец сообщает об общем количестве произведенных операций чтения с диска (и тем самым свидетельствовать о высоком потреблении ресурсов подсистемы ввода-вывода).
- sorts. Этот столбец отражает количество произведенных для выполнения оператора операций сортировки (что может свидетельствовать о высоком коэффициенте операций сортировки).
- cpu_time. Этот столбец показывает, сколько всего времени ЦП было потрачено на синтаксический анализ и выполнение SQL-оператора.
- elapsed_time. Этот столбец сообщает, сколько в общем времени ушло на синтаксический анализ и выполнение.
- parse_calls. В этом столбце отображается совместное количество произведенных для оператора вызов soft parse и hard parse, т.е. вызовов на выполнение частичного и полного синтаксического анализа.
- executions. Этот столбец показывает, сколько раз выполнялся оператор.
- loads. Этот столбец показывает, сколько раз оператор повторного загружался в разделяемый пул после сбрасывания.
- sharable_memory. Этот столбец отражает общий объем разделяемой памяти, использованной курсором.
- persistent_memory. Этот столбец отражает общий объем постоянной памяти, использованной курсором.
- runtime_memory. Этот столбец показывает, сколько памяти времени выполнения использовалось курсором.
На заметку! В предыдущих версиях администраторы баз данных для сбора всей перечисленной выше информации применяли представление V$SQLAREA. Представление V$SQL, однако, дополняет V$SQLAREA, поскольку предоставляет всю информацию, что и это представление, плюс также важную информацию касательно настройки.
Выявление SQL-кода, который потребляет больше всего ресурсов
Выявлять SQL-код, потребляющий много ресурсов, можно путем выдачи запросов к представлению V$SQL. В этом представлении определение интенсивно использующего ресурсы SQL-кода производится как по количеству операций логического чтения (logical reads) или чтения буферов (buffer gets), так и по большому количеству операций чтения с диска (disk reads), вызовов синтаксического анализа (parse calls) и выполнений (executions) или того и другого вместе. Очевидно, что высокий показатель по количеству операций disk reads является признаком неэффективности, поскольку большое количеств физических операций ввода-вывода всегда замедляет скорость выполнения запроса. Высокий показатель по количеству операций чтения из памяти (buffer gets), однако, тоже служит признаком высокой стоимости, поскольку эти операции потребляют ресурсы ЦП. Обычно большое количество операций buffer gets происходит из-за использования неправильного индекса, неправильной управляющей таблицы в соединении или какой-нибудь другой подобной ошибки, связанной с SQL. Одной из главных целей при настройке SQL должно быть снижение количества ненужных операций logical reads. Если показатели по количеству операций buffer gets и disk reads выглядят идентично, это может свидетельствовать об отсутствии какого-то индекса. Объясняется это следующим образом: в случае отсутствия индекса Oracle вынужденно выполняет полное сканирование таблицы. Результаты операций полного сканирования таблиц, однако, не могут удерживаться в области SGA слишком долго, поскольку могут привести к удалению оттуда множества других данных. Следовательно, целая таблица не будет оставаться в SGA слишком долго, если только она не имеет маленький размер.
Ниже приведен простой запрос, показывающий, как представление V$SQL может помочь выявить проблемные SQL-операторы. В этом запросе в качестве критериев для обнаружения неэффективных SQL-операторов используются высокие показатели как по операциям disk reads, так и по операциям logical reads. Столбец SQL_TEXT показывает, как точно выглядит SQL-оператор, ответственный за высокие показатели по количеству операций disk reads и logical reads.
SQL> SELECT sql_text, executions, buffer_gets, disk_reads, 2 FROM V$SQL 3 WHERE buffer_gets > 100000 4 OR disk_reads > 100000 5 ORDER BY buffer_gets + 100*disk_reads DESC; SQL_TEXT EXECUTIONS BUFFER_GETS DISK_READS ----------------------------- ---------- ----------- ---------- BEGIN dbms_job.run(1009133); 726216 1615283234 125828 BEGIN label_sc_pkg.launch_sc; 34665 1211625422 3680242 SELECT COUNT(*) AV_YOUTHS... 70564 152737737 7186125 SELECT UC.CHART_ID... 37849 96590083 5547319 SELECT MAX(REC_NUM) FROM... 5163242 33272842 6034715 SQL>
Ниже приведен другой запрос, немного отличающийся от предыдущего. Его цель состоит в том, чтобы выяснить, сколько строк было обработано для каждого оператора:
SQL> SELECT sql_text, rows_processed, 2 buffer_gets, disk_reads, parse_calls 3 FROM V$SQL 4 WHERE buffer_gets > 100000 5 OR disk_reads > 100000 6* ORDER BY buffer_gets + 100*disk_reads DESC; SQL_TEXT ROWS_PROCESSED BUFFER_GETS DISK_READS PARSE_CALLS ---------------------------- -------------- ----------- ---------- ----------- BEGIN dbms_job.run(1009133); 9659 1615322749 125830 2078 BEGIN label_sc_pkg.launch_sc; 3928 1214405479 3680515 4 SELECT COUNT(*) AV_YOUTHS... 70660 152737737 7186125 3863 SELECT UC.CHART_ID... 37848 96590083 5547319 5476 SELECT MAX(REC_NUM) FROM... 5163236 33272842 6034715 606 SQL>
Таким образом, представление V$SQL помогает узнать, какие из запросов имеют высокие показатели по логическим операциям ввода вывода (logical I/O — LIO) и по физическим операциям ввода-вывода (physical I/O — PIO). Предоставляя также информацию о количестве строк, обработанных каждым оператором, оно тем самым позволяет видеть, является ли оператор эффективным. Предоставляя информацию о количестве операций disk reads и числе выполнений каждого оператора, оно помогает определять, является ли число операций disk reads, приходящееся на каждое выполнение оператора, разумным. Если беспокойство вызывает уровень потребления ресурсов ЦП, следует искать операторы с высоким показателем по количеству операций buffer gets, а если уровень потребления ресурсов подсистемы ввода-вывода — операторы с наибольшим показателем по количеству операций disk reads. После обнаружения оператора, подлежащего дальнейшему изучению, необходимо проанализировать его весь и определить, можете ли вы (либо разработчик) его улучшить.
Одним из наилучших способов для выявления SQL-запросов с плохой производительностью является применение интерфейса событий ожиданий Oracle.
Ниже приведен запрос, показывающий, как использовать представление V$SQL для сортировки и получения пяти главных запросов, которые отнимают больше всего времени ЦП и выполняются дольше всех:
SQL> SELECT sql_text, executions, 2 ROUND(elapsed_time/1000000, 2) elapsed_seconds, 3 ROUND(cpu_time/1000000, 2) cpu_secs from 4 (select * from v$sql order by elapsed_time desc) 5* WHERE rownum <6; SQL_TEXT EXECUTIONS ELAPSED_SECONDS CPU_SECS ---------------------------- ---------- --------------- --------- DELETE MS_DASH_TRANLOGS... 2283 44.57 43.04 UPDATE PERSONS SET... 14132 19.74 20.52 SELECT /*+ INDEX(ud)... 9132 9.95 9 SELECT PROG_ID FROM UNITS ... 14132 5.26 5.81 SELECT NVL(SUM(RECHART),0)... 2284 4.13 4.43 SQL>
Использование других представлений словаря данных для настройки SQL
Представления V$SQL_PLAN и V$SQL_PLAN_STATISTICS очень полезны при отслеживании эффективности планов выполнения. Следует осторожно относиться к внесению быстрых изменений в код для исправления даже самых плохо выполняющихся запросов в системе. Предположим, что для исправления такого проблемного запроса принимается решение создать дополнительный индекс или изменить порядок столбцов в составном ключе. Как узнать, не скажется ли это негативно на других запросах в приложении? Подобные промахи случаются гораздо чаще, чем кажется, и потому следует проявить должную старательность и исключить все возможные случайные последствия вносимых исправлений.
