upd. 25.03.2025, данная статья устарела, но полезна для общего развития, т.к. здесь была положена идея(в 2021г), которая преобразовалась в kPerf for Oracle

—————————————————————————————————————

Контроль стабильности работы бд требует мониторинга и понимания метрик этого мониторинга. В связи с этим я написал свой небольшой снапер метрик бд и вложил в него ‘своё’ восприятие этих метрик для своих нужд, но возможно общая идея будит интересна ещё кому ни будь.

Мониторинг состоит из нескольких компонентов:

1. Набор таблиц
2. Задание по расписанию для заполнения таблиц из пункта 1
3. Получение данных из таблиц пункта 1 и передача этих данных.
4. Grafana
5. Prometheus
6. OracleDB exporter

Инструкция не содержит пошаговой настройки всех компонентов, а только показывает, как собирается статистика в бд и как обрабатывается.

1. Набор таблиц.

1.1

CREATE SEQUENCE system.kks$sys_wait_event_sequence
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

create table system.kks$system_wait_event(
wait_class varchar2(256),
TOTAL_WAITS_sum number,
TIME_WAITED_MICRO_sum number,
snap_time date default sysdate,
snap_id number
)
tablespace sysaux;

create index SYSTEM.snap_time_ix on SYSTEM.KKS$SYSTEM_WAIT_EVENT (snap_time) tablespace sysaux;
create index SYSTEM.wait_class_ix on SYSTEM.KKS$SYSTEM_WAIT_EVENT (wait_class) tablespace sysaux;
create index SYSTEM.snap_id_ix on SYSTEM.KKS$SYSTEM_WAIT_EVENT (snap_id) tablespace sysaux;

1.2

CREATE SEQUENCE system.kks$sys_wait_event_sequence_p1
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;
  
create table system.kks$system_wait_event_p1(
wait_event varchar2(256),
TOTAL_WAITS_sum number,
TIME_WAITED_MICRO_sum number,
snap_time date default sysdate,
snap_id number
)
tablespace sysaux;

create index SYSTEM.snap_time_p1_ix on SYSTEM.KKS$SYSTEM_WAIT_EVENT_p1 (snap_time) tablespace sysaux;
create index SYSTEM.wait_event_p1_ix on SYSTEM.KKS$SYSTEM_WAIT_EVENT_p1 (wait_event) tablespace sysaux;
create index SYSTEM.snap_p1_id_ix on SYSTEM.KKS$SYSTEM_WAIT_EVENT_p1 (snap_id) tablespace sysaux;

1.3

CREATE SEQUENCE system.kks$sys_wait_event_sequence_p2
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;
  
create table system.kks$system_wait_event_p2(
wait_event varchar2(256),
TOTAL_WAITS_sum number,
TIME_WAITED_MICRO_sum number,
snap_time date default sysdate,
snap_id number
)
tablespace sysaux;

create index SYSTEM.snap_time_p2_ix on SYSTEM.KKS$SYSTEM_WAIT_EVENT_p2 (snap_time) tablespace sysaux;
create index SYSTEM.wait_event_p2_ix on SYSTEM.KKS$SYSTEM_WAIT_EVENT_p2 (wait_event) tablespace sysaux;
create index SYSTEM.snap_p2_id_ix on SYSTEM.KKS$SYSTEM_WAIT_EVENT_p2 (snap_id) tablespace sysaux;

2. Задание по расписанию для заполнения таблиц из пункта 1

2.1

Заполняем таблицу из пункта 1.1. Делаем снапшот суммарного количества ожиданий по каждому классу ожиданий(WAIT_CLASS) и суммарного количества времени отведённого для каждого класса ожиданий(WAIT_CLASS), устаревшие данные удалям.

declare
  seqno number;
  begin
    select system.kks$sys_wait_event_sequence.nextval into seqno from dual;

  insert into system.kks$system_wait_event(wait_class,total_waits_sum,time_waited_micro_sum,snap_id)
   select w.WAIT_CLASS,sum(w.TOTAL_WAITS) totalwaits,sum(w.TIME_WAITED_MICRO) mcsec,seqno from v$system_event w
  where w.WAIT_CLASS!='Idle'
  group by w.WAIT_CLASS;
  insert into system.kks$system_wait_event(wait_class,time_waited_micro_sum,snap_id)
  select o.STAT_NAME,o.VALUE,seqno from V$SYS_TIME_MODEL o where o.STAT_NAME='DB CPU';
  commit;
  delete from system.kks$system_wait_event p1 where p1.snap_time<=sysdate-30/24/60;
  commit;
  end;

2.2

Заполняем таблицу из пункта 1.2. Делаем снапшот суммарного количества ожиданий по каждому ожиданию(EVENT) и суммарного количества времени отведённого для каждого ожидания(EVENT), устаревшие данные удалям.

declare
  seqno number;
  begin
    select system.kks$sys_wait_event_sequence_p1.nextval into seqno from dual;

  insert into system.kks$system_wait_event_p1(wait_event,total_waits_sum,time_waited_micro_sum,snap_id)
   select w.EVENT,sum(w.TOTAL_WAITS) totalwaits,sum(w.TIME_WAITED_MICRO) mcsec,seqno from v$system_event w
  where w.WAIT_CLASS!='Idle' and w.EVENT!='SQL*Net message to client'
  group by w.EVENT;
  insert into system.kks$system_wait_event_p1(wait_event,time_waited_micro_sum,snap_id)
  select o.STAT_NAME,o.VALUE,seqno from V$SYS_TIME_MODEL o where o.STAT_NAME='DB CPU';
  commit;
  delete from system.kks$system_wait_event_p1 p1 where p1.snap_time<=sysdate-30/24/60;
  commit;
  end;

2.3

Заполняем таблицу из пункта 1.3. Делаем снапшот суммарного количества ожиданий по каждому ожиданию(EVENT) и суммарного количества времени отведённого для каждого ожидания(EVENT), устаревшие данные удалям.

declare
  seqno number;
  begin
    select system.kks$sys_wait_event_sequence_p2.nextval into seqno from dual;
  insert into system.kks$system_wait_event_p2(wait_event,total_waits_sum,time_waited_micro_sum,snap_id)
   select w.EVENT,sum(w.TOTAL_WAITS) totalwaits,sum(w.TIME_WAITED_MICRO) mcsec,seqno from v$system_event w
  where w.WAIT_CLASS!='Idle' and w.EVENT!='SQL*Net message to client'
  group by w.EVENT;
  commit;
  delete from system.kks$system_wait_event_p2 p1 where p1.snap_time<=sysdate-30/24/60;
  commit;
  end;

Я заполняю эти таблицы каждые 5сек. Возможно 2.3 избыточен, т.к. почти такие же данные есть в 2.2. Но тут кроется ‘моя’ особая логика, 2.2 нужен для анализа ожиданий в процентном отношении с учётом DB CPU.

3. Получение данных из таблиц пункта 1 и передача этих данных.

Далее, используется https://github.com/iamseth/oracledb_exporter в качестве передачи метрик в Prometheus.

3.1

1. Получение значений ожиданий по каждому классу ожиданий, выраженное в процентах. Сумма всех классов ожиданий не превышает 100%

[[metric]]
    context = "db_wait_event"
    metricsdesc= { value="System wait event time Diff in %" }
	fieldtoappend= "wait_class"
	request = '''
    with 
    waitpcts1 as ( 
    select sum(p.time_waited_micro_sum) as timewait1, p.snap_id as snap_id1  from system.kks$system_wait_event p
    where p.snap_id=(select max(snap_id) from system.kks$system_wait_event)
    group by p.snap_id),
    waitpcts2 as ( 
    select sum(p.time_waited_micro_sum) as timewait2,p.snap_id as snap_id2  from system.kks$system_wait_event p
    where p.snap_id=(select max(snap_id)-1 from system.kks$system_wait_event)
    group by p.snap_id),
    wait1 as ( 
    select p.wait_class as wait_class1,p.time_waited_micro_sum as time_waited_micro_sum1,p.snap_id as snap_id1  from system.kks$system_wait_event p
    where p.snap_id=(select max(snap_id) from system.kks$system_wait_event)),
    wait2 as ( 
    select p.wait_class as wait_class2,p.time_waited_micro_sum as time_waited_micro_sum2,p.snap_id as snap_id12 from system.kks$system_wait_event p
    where p.snap_id=(select max(snap_id)-1 from system.kks$system_wait_event))
    select wait1.wait_class1 as "wait_class",
	round(((wait1.time_waited_micro_sum1-wait2.time_waited_micro_sum2)/decode((waitpcts1.timewait1-waitpcts2.timewait2),0,1,(waitpcts1.timewait1-waitpcts2.timewait2)))*100) as "value"
    from wait1,wait2,waitpcts1,waitpcts2
    where wait1.wait_class1=wait2.wait_class2
    '''

Как это выглядит:

3.2 Получение значения времени по каждому ожиданию(event)(сколько времени потрачено на одно ожидание) выраженное в процентах. Сумма всех ожиданий не превышает 100%.

[[metric]]
    context = "event_p1"
    metricsdesc= { value="System wait event P1 Diff in % }
	fieldtoappend= "wait_event_p1"
	request = '''
    with 
    waitpcts1 as ( 
    select sum(p.time_waited_micro_sum) as timewait1, p.snap_id as snap_id1  from system.kks$system_wait_event_p1 p
    where p.snap_id=(select max(snap_id) from system.kks$system_wait_event_p1)
    group by p.snap_id),
    waitpcts2 as ( 
    select sum(p.time_waited_micro_sum) as timewait2,p.snap_id as snap_id2  from system.kks$system_wait_event_p1 p
    where p.snap_id=(select max(snap_id)-1 from system.kks$system_wait_event_p1)
    group by p.snap_id),
    wait1 as ( 
    select p.wait_event as wait_event1,p.time_waited_micro_sum as time_waited_micro_sum1,p.snap_id as snap_id1  from system.kks$system_wait_event_p1 p
    where p.snap_id=(select max(snap_id) from system.kks$system_wait_event_p1)),
    wait2 as ( 
    select p.wait_event as wait_event2,p.time_waited_micro_sum as time_waited_micro_sum2,p.snap_id as snap_id12 from system.kks$system_wait_event_p1 p
    where p.snap_id=(select max(snap_id)-1 from system.kks$system_wait_event_p1))
    select replace(replace(replace(wait1.wait_event1,':','_'),'-','_'),'&','_')||'_p1' as "wait_event_p1",
	ceil(((wait1.time_waited_micro_sum1-wait2.time_waited_micro_sum2)/decode((waitpcts1.timewait1-waitpcts2.timewait2),0,1,(waitpcts1.timewait1-waitpcts2.timewait2)))*100) as "value"
    from wait1,wait2,waitpcts1,waitpcts2
    where wait1.wait_event1=wait2.wait_event2
    and ceil(((wait1.time_waited_micro_sum1-wait2.time_waited_micro_sum2)/decode((waitpcts1.timewait1-waitpcts2.timewait2),0,1,(waitpcts1.timewait1-waitpcts2.timewait2)))*100)>0
    '''

Как это выглядит:

3.3 Сколько времени потрачено на одно событие ожидания(event) в микросекундах.

[[metric]]
    context = "event_p2"
    metricsdesc= { value="System wait event time P2 Diff in micsec for 4sec." }
	fieldtoappend= "wait_event_p2"
	request = '''
    with 
    waitpcts1 as ( 
    select p.wait_event as waitevent1,
    p.time_waited_micro_sum as timewait1,
    p.total_waits_sum as totalwaits1,
    p.snap_id as snap_id1  
    from system.kks$system_wait_event_p2 p
    where p.snap_id=(select max(snap_id) from system.kks$system_wait_event_p2)),
    waitpcts2 as ( 
    select p.wait_event as waitevent2,
    p.time_waited_micro_sum as timewait2,
    p.total_waits_sum as totalwaits2,
    p.snap_id as snap_id2  
    from system.kks$system_wait_event_p2 p
    where p.snap_id=(select max(snap_id)-1 from system.kks$system_wait_event_p2))
    select replace(replace(replace(waitpcts1.waitevent1,':','_'),'-','_'),'&','_')||'_p2' as "wait_event_p2",
	ceil(((waitpcts1.timewait1-waitpcts2.timewait2)/decode((waitpcts1.totalwaits1-waitpcts2.totalwaits2),0,1,(waitpcts1.totalwaits1-waitpcts2.totalwaits2)))) as "value"
    from waitpcts1,waitpcts2
    where waitpcts1.waitevent1=waitpcts2.waitevent2
    and ceil(((waitpcts1.timewait1-waitpcts2.timewait2)/decode((waitpcts1.totalwaits1-waitpcts2.totalwaits2),0,1,(waitpcts1.totalwaits1-waitpcts2.totalwaits2))))>0
    '''

Как это выглядит:

3.3 Количество событий по каждому ожиданию(event) в секунду

[[metric]]
    context = "event_p3"
    metricsdesc= { value="System wait events P3 Diff for 4sec." }
	fieldtoappend= "wait_event_p3"
	request = '''
    with 
    waitpcts1 as ( 
    select p.wait_event as waitevent1,
    p.total_waits_sum as totalwaits1,
    p.snap_id as snap_id1  
    from system.kks$system_wait_event_p2 p
    where p.snap_id=(select max(snap_id) from system.kks$system_wait_event_p2)),
    waitpcts2 as ( 
    select p.wait_event as waitevent2,
    p.total_waits_sum as totalwaits2,
    p.snap_id as snap_id2  
    from system.kks$system_wait_event_p2 p
    where p.snap_id=(select max(snap_id)-1 from system.kks$system_wait_event_p2))
    select 
    replace(replace(replace(waitpcts1.waitevent1,':','_'),'-','_'),'&','_')||'_p2' as "wait_event_p3",
   ceil((waitpcts1.totalwaits1-waitpcts2.totalwaits2)/4) as "value"
    from waitpcts1,waitpcts2
    where waitpcts1.waitevent1=waitpcts2.waitevent2 and ceil(waitpcts1.totalwaits1-waitpcts2.totalwaits2)>0
'''

Как это выглядит:

upd. 25.03.2025, данная статья устарела, но полезна для общего развития, т.к. здесь была положена идея(в 2021г), которая преобразовалась в kPerf for Oracle