Oracle mechanics

Инструменты 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 минут [по умолчанию]

Installing and Using Standby Statspack in 11g [ID 454848.1]

Начиная с 11gR1 можно использовать [специальный] statspack, устанавливаемый и выполняющийся на primary для сбора статистики выполняющей накат standby бд, открытой в режиме read-only.  Такой  standby statspack  устанавливается в отдельную схему на Primary базе…

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]

Минимальный размер буфера ASH определяется параметром:

SQL> @param_ _ash_size

NAME       VALUE    IS_DEF   DSC
---------- -------- -------- ---------------------------------------------------------------
_ash_size  1048618  TRUE     To set the size of the in-memory Active Session History buffers

Реальный размер буферов ASH определяется при старте системы формулой типа

Max( Min ( No. of CPU * 2 M, 5% of SHARED_POOL_SIZE, 2% of SGA_TARGET(?) ), _ash_size)

и может быть изменён динамически:

11.2.@ SQL> select total_size from v$ash_info;

TOTAL_SIZE
----------
   8388608

11.2.@ SQL> select * from v$sgastat where name = 'ASH buffers';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  ASH buffers                   8388608

11.2.@ SQL> alter system set "_ash_size" = 10000000 scope=both;

System altered.

Elapsed: 00:00:10.46                          -- перераспределение SGA небыстро даже на idle тестовой системе

11.2.@ SQL> select total_size from v$ash_info;

TOTAL_SIZE
----------
  10000000

11.2.@ SQL> select * from v$sgastat where name = 'ASH buffers';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  ASH buffers                  10000000

Начиная с 11.2 через обзор V$ASH_INFO доступна статистика ASH:

11.2.@ SQL> desc v$ash_info
Name
-------------------------
TOTAL_SIZE
FIXED_SIZE
SAMPLING_INTERVAL         -- _ash_sampling_interval
OLDEST_SAMPLE_ID
OLDEST_SAMPLE_TIME
LATEST_SAMPLE_ID
LATEST_SAMPLE_TIME
SAMPLE_COUNT
SAMPLED_BYTES
SAMPLER_ELAPSED_TIME
DISK_FILTER_RATIO
AWR_FLUSH_BYTES
AWR_FLUSH_ELAPSED_TIME
AWR_FLUSH_COUNT
AWR_FLUSH_EMERGENCY_COUNT

ASH presentation from John Beresniewicz:

Мониторинг [плана] выполнения SQL

DBMS_SQLTUNE.REPORT_SQL_MONITOR — для текущего выполнения

DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL — встроенный мониторинг исторических выполнений, доступен с 12c, в 12.1 может иметь проблемы High CPU Usage and/or Frequent Occurances of ORA-12850 For Monitor Queries by MMON From 12.1 (Doc ID 2102131.1), отключается «_report_capture_cycle_time»=0

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

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

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

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

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

SQL*Plus

В дополнение к документации SQL*Plus 10.1 Substitution Variables

TBD

TrackBack URI

Создайте бесплатный сайт или блог на WordPress.com.

%d такие блоггеры, как: