Инструменты Oracle

Краткие примеры использования основных инструментов Oracle для анализа и настройки производительности системы: statspack/AWR, ADDM, PL/SQL profiler, в основе которых используются базовые подходы Oracle:

• события ожидания – Wait Events Interface*
• статистики сессии/системы (V$SESSTAT / V$SYSSTAT)
• статистика использования / распределения времени на уровне сессии / системы – Time Model Statistics (V$SESS_TIME_MODEL / V$SYS_TIME_MODEL)

*) Историю развития методики можно прочитать в презентации Graham Wood, Oracle: A Brief History of DB Time. Performance Analysis from 2 to 10

Statspack

Классический иструмент, бесплатно поставляется с Oracle 8.1 – 11g.
Readme: $ORACLE_HOME/rdbms/admin/spdoc.txt
Скрипты для установки и администрирования: $ORACLE_HOME/rdbms/admin/sp*.sql

394937.1 Statistics Package (STATSPACK) Guide – для версий 9.2 – 11.1

“При использовании в Oracle 10-11 рекомендуется планировать выполнение Statspack snapshot так, чтобы не пересекаться по времени с AWR, который собирает данные каждый час в 00 минут [по умолчанию]“

Automatic Workload Repository (AWR) + Automatic Database Diagnostic Monitor (ADDM)¹

Необходимые привилегии

SQL> grant advisor to scott;
SQL> grant select_catalog_role to scott;
SQL> grant execute on dbms_workload_repository to scott;

, проверить наличие привилегий

SQL> select * from USER_ROLE_PRIVS where GRANTED_ROLE like '%\_CATALOG\_ROLE' escape '\';
SQL> select * from USER_TAB_PRIVS where TABLE_NAME = 'DBMS_WORKLOAD_REPOSITORY';
SQL> select * from user_sys_privs where PRIVILEGE = 'ADVISOR';

Параметры Oracle

“STATISTICS_LEVEL должен быть установлен в значения TYPICAL, либо ALL”

В 11g появился параметр CONTROL_MANAGEMENT_PACK_ACCESS = { NONE | DIAGNOSTIC | DIAGNOSTIC+TUNING} для контроля доступа к Diagnostic Pack and Tuning Pack. Значение по умолчанию DIAGNOSTIC+TUNING в Enterprise Edition, NONE в Standard Edition

Параметры запуска/выполнения AWR

SQL> select * from DBA_HIST_WR_CONTROL;
      DBID SNAP_INTERVAL     RETENTION         TOPNSQL
---------- ----------------- ----------------- -------
1234567890 +00000 00:15:00.0 +00014 00:00:00.0      40

и их изменение

SQL> BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200,                              -- Minutes (= 30 Days). Current value retained if NULL.
interval  =>    30,                                 -- Minutes. Current value retained if NULL.
topnsql   =>   100);                                 -- Top N SQL. Current value retained if NULL.
END;
/

Системные обзоры

V$ACTIVE_SESSION_HISTORY, DBA_HIST_ACTIVE_SESS_HISTORY – содержимое этих обзоров называется Active Session History (ASH)¹, imho.

DBA_HIST_*

Список AWR снимков (AWR snapshot list)

SQL> select
snap_id,
dbid,
instance_number,
begin_interval_time,
end_interval_time
from DBA_HIST_SNAPSHOT
order by begin_interval_time desc;

Пример AWR report

SQL> select output from table
(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT
(:dbid,:instance_number,:first_snap_id,:last_snap_id));

Пример подготовки ADDM report

SQL> DECLARE
  task_name VARCHAR2(30) := 'SCOTT_ADDM';
  task_desc VARCHAR2(30) := 'ADDM Feature Test';
  task_id   NUMBER;
BEGIN
  select count(*)
    into task_id
    from dba_advisor_tasks
   where task_name = 'SCOTT_ADDM';
  if task_id = 0 then
    dbms_advisor.create_task('ADDM', task_id, task_name, task_desc, null);
  else
    dbms_advisor.reset_task(task_name => 'SCOTT_ADDM');
  end if;
  dbms_advisor.set_task_parameter('SCOTT_ADDM', 'START_SNAPSHOT', :first_snap_id);
  dbms_advisor.set_task_parameter('SCOTT_ADDM', 'END_SNAPSHOT', :last_snap_id);
  dbms_advisor.set_task_parameter('SCOTT_ADDM', 'INSTANCE', :instance_number);
  dbms_advisor.set_task_parameter('SCOTT_ADDM', 'DB_ID', :dbid);
  dbms_advisor.execute_task('SCOTT_ADDM');
END;
/

и получения отчёта ADDM

SQL> select dbms_advisor.get_task_report('SCOTT_ADDM', 'TEXT', 'ALL') from dual;

¹) Согласно Oracle Database Licensing Information доступны ТОЛЬКО в Oracle Enterprise Edition и требуют приобретения Oracle Diagnostic Pack. Однако, в Oracle 10g Standard Edition AWR и ADDM установлены и функционируют по умолчанию, в 11g – уже нет ( CONTROL_MANAGEMENT_PACK_ACCESS=NONE). Однако в Oracle Enterprise Edition CONTROL_MANAGEMENT_PACK_ACCESS=DIAGNOSTIC+TUNING по умолчанию, т.е. нарушить лицензионую политику можно просто по незнанию – см. обсуждение темы у Lutz Hartmann

Active Session History

10g and above Active Session History (Ash) And Analysis Of Ash Online And Offline [ID 243132.1]

Профилирование PL/SQL

Стандартно, с использованием пакета DBMS_PROFILER (Oracle 8+): Using DBMS_PROFILER [ID 97270.1]

Oracle 11g+: иерархический профилировщик PL/SQL

Профилирование сессий

Дмитрий Волков, Игорь Старостин: Построение профиля сессии в СУБД Oracle на основе триггера on-logoff – простой и красивый метод автоматического сохранения статистики пользовательских сессий. Применим для любых версий. Легко дополняем, например данными v$sess_time_model и т.д.

TBD