Инструменты и методы Oracle

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

Историю развития методики можно прочитать в презентации 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)¹

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

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

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

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

Параметры

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

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

Обзоры

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

DBA_HIST_*

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

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

Пример AWR report

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

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

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

select dbms_advisor.get_task_report('SCOTT_ADDM', 'TEXT', 'ALL') from sys.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.

Oracle mechanics