Real-Time Database Operation Monitoring в Oracle Database 12c

Технология мониторинга SQL-запросов в режиме реального времени (Real-Time SQL Monitoring ), которая появилась в Oracle Database 11g, позволяет администраторам и разработчикам взглянуть внутрь каждого долго выполняющего запроса.
Для каждого долго выполняющегося запроса можно увидеть его реальный план выполнения, количество обработанных строк, расхождение ожидаемой и фактической кардинальности (количество ожидаемых и количество фактических строк) на каждом шаге плана запроса. Также можно увидеть различные метрики SQL-запроса: CPU, I/O requests, throughput, PGA, temp space.
В том числе можно производить мониторинг статистики ввода-вывода для каждого шшага запроса.

В свое время технология Real-Time SQL Monitoring была большим шагом вперед для ответа на вопрос "Что реально происходит в базе данных?".

Рис 1. Мониторинг выполнения запроса с помощью Real-Time SQL Monitoring

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

Рассмотрим такой типичный случай.
При возникновении проблемы с производительностью расчета зарплаты, конечный пользователь обращается к администратору СУБД. DBA должен среды сотен (возможно и тысяч!) выполняющихся запросов, выделить запросы относящиеся к расчету зарплаты, выяснить какие из них сейчас выполняются и, затем, наконец, разобраться в причинах медленной работы этой бизнес-операции.
Для идентификации запросов часто приходится обращаться к разработчикам приложения и анализировать его исходный код. Все это часто приводит к взаимным обвинениям администраторов и разработчиков.
Не правда-ли: знакомая картина?

СУБД Oracle Database 12c предлагает решение и этой проблемы, позволяя производить мониторинг на уровне составных бизнес-операций.
Эта технология получила название Real-Time Database Operation Monitoring, и позволяет осуществлять мониторинг в режиме реального времени в терминах бизнес-процессов (бизнес-операций) выполняющихся в данный момент в СУБД.
Очевидно, что СУБД ничего "не знает" про бизнес-процессы, и для этого приложению необходимо каким-либо образом сообщить об этом базе данных: сгруппировать обращения к СУБД в бизнес-операции.

Для того, чтобы выделить в потоке обращений к СУБД бизнес-операцию, предлагается специальный API (Application Program Interface), реализованный в виде PL/SQL-пакета DBMS_SQL_MONITOR.
Пакет DBMS_SQL_MONITOR стандартно поставляется в составе СУБД и устанавливается по умолчанию - то есть входит в состав стандартных системных пакетов Oracle Database 12с.
Для выделения начала бизнес-операции предназначена процедура BEGIN_OPERATION данного пакета, для окончания бизнес-операции - END_OPERATION.

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

 CREATE PROCEDURE calc_salary(v_pDep IN NUMBER) IS
   v_xDBOpId          NUMBER;
 BEGIN
   v_xDBOpId := dbms_sql_monitor.begin_operation('Расчет зарплаты',
                                                 forced_tracking => 'Y');
   prepare_calc();
   main_calc();
   finish_calc();

   dbms_sql_monitor.end_operation('Расчет зарплаты', v_xDBOpId); 
 END;

Параметр forced_tracking процедуры BEGIN_OPERATION аналогичен хинту MONITOR в запросе: при его установке (forced_tracking => 'Y'), мониторинг бизнес-операции производится в любом случае. В противном случае - мониторинг производится только в случае, если бизнес-операции по продолжительности выполнения занимает 5 и более секунд ЦПУ или ввода-вывода. Запустим наш модифицированный бизнес-процесс расчета зарплаты и посмотрим, как он теперь отображается на экране SQL Monitoring консоли администрирования Enterprise Manager 12c. В списке выполняющихся запросов появился запрос c "говорящим" именем "Расчет зарплаты":

Рис 2. "Расчет зарплаты" в списке запросов на экране SQL Monitoring
Перейдя внутрь выполняющейся бизнес-операции - для этого нужно кликнуть мышью на SQL_ID операции (в нашем случае: "Расчет зарплаты"), можно увидеть страницу детальной информации: какие запросы из состава бизнес-операции уже выполнились, и какой запрос является текущим - выполняется в данный момент:

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

После завершения бизнес-операции входящие в него запросы точно также доступы для анализа, как это и было раньше (в СУБД Oracle Database 11g) для уже завершившихся SQL-запросов.

Рис 4. Бизнес-операция "Расчет зарплаты" завершена

Для того, чтобы сгруппировать запросы в бизнес-операции из приложений Java и С++/C-приложений, соответствующий клиентский API доступа к БД (JDBC и OCI, соответственно) также был расширен.
Вот так, например, выглядит выделение бизнес-операции в Java-приложении:

Connection conn = DriverManager.getConnection(myUrl, myUsername, myPassword);
conn.setClientInfo("E2E_CONTEXT.DBOP", “Расчет зарплаты");
Statement stmt = conn.createStatement();
stmt.execute(v_xSQLQuery1);
... ... ... ...
... ... ... ...

conn.setClientInfo("E2E_CONTEXT.DBOP", null);

Если Вы разрабатываете приложение сразу для разных версий СУБД Oracle Database, то можете воспользоваться условной компиляцией PL/SQL, чтобы иметь один и тотже код в независимости от версии БД:

 create procedure calc_salary(v_pDepartmentId in number) is
    v_xDBOpId          NUMBER;
  begin
    $if dbms_db_version.ver_le_11_2 $then

    $else
      v_xDBOpId := dbms_sql_monitor.begin_operation('Расчет зарплаты',
                                                    forced_tracking => 'Y');
    $end
  
    v_gCurrentDepartmentId := v_pDepartmentId;
  
    prepare_calc();
    main_calc();
    finish_calc();
    
    $if dbms_db_version.ver_le_11_2 $then
    $else 
      dbms_sql_monitor.end_operation('Расчет зарплаты', v_xDBOpId);  
    $end
  end;

Объявление переменной v_xDBOpId нет смысла обрамлять в символ условной компиляции,поскольку при компиляции в СУБД Oracle Database 11g, PL/SQL-оптимизатор удалит её из исходного кода, поскольку она является "мертвой" - нигде не используется.

Продолжение следует ...