 |  | |
| IT сфера | ||
 |  |
| | |
ОБ АВТОРЕ
- Наши орбиты. Корень - Новости - Введение - Резюме IT - Контакты |
||
| |
| | |
МАТЕРИАЛЫ
- Разработка - Статьи - Ссылки |
||
| |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Авторская площадка "Наши орбиты" состоит из ряда тематических подразделов, являющихся моими лабораторными дневниками, содержащими записи за разное, иногда продолжительно отличающееся, время. Эти материалы призваны рассказать о прошедшем опыте, они никого ни к чему не призывают и совершенно не обязательно могут быть применимы кем-то ещё. Это только лишь истории о прошлом Начальные слова С учётом появления в 10 версии ряда новых инструментов - AWR/ASH/ADDM, метрики и SQL tuning advisor ситуация с анализом функционирования экземпляра поменялась кардинально, предоставив администратору БД удобный способ для получения быстрой аналитики и рекомендаций. Однако как всегда есть "но". Использование всех этих инструментов требует лицензирования дополнительных паков - Diagnostic Pack и Tuning Pack, даже если вы обращаетесь к табличкам AWR черех sqlplus. Как показывает практика об этом предпочитают не вспоминать, используя соответствующие инструмены без лицензирования. К слову сказать $5000 + $5000 = $10000 на процессор за оба пака, что совсем не дёшево Вместе с тем все эти инструменты основаны на всё тех же технологиях, которые применялись и в 8, и в 9 версии, и которыми при знании внутренней кухни можно пользоваться без лицензирования дополнительных паков. Эти технологии не являются недоступной "наукой о ракетах" и вполне детально изложены в руководстве по тюнингу для 9 версии СУБД Oracle. Конечно есть нюансы. Анализ и рекомендации вам придётся отрабатывать самим, кроме того у вас не будет данных ASH (ежесекундного малонагружающего сбора данных об активных сессиях) и инструментов по оптимизации SQL запросов, однако если вам важно соответствие лицензионной политике вендора, а ресурсов на паки нет, это может быть выходом Примечательно, что статистическая информация всё равно будет собираться в БД 10 и последующих версий любой из основных редакций для механизмов самоадминистрирования БД, сбор этих данных крайне не рекомендуется отключать вендором, и вы можете воспользоваться её технически, ибо технических ограничений нет. Но юридически использовать эти данные без лицензии вы права не имеете. Эта доступность ответственна за то, что функционал паков используется без лицензирования, а более трудоёмкие методы отбрасываются в сторону. В частности в моём авторском продукте ОрСиМОН БЕССТ реализован подход получения и аналитики данных, не требующий задействования лицензируемых отдельно инструментов, но интереса у публики он не вызвал В настоящей статье планируется обзорно рассмотреть анализ функционирования экземпляра как без использования лицензируемых отдельно паков, так и с помощью входящих в состав этих паков инструментов. Более ранняя версия статьи, включавшая в основном обзор методики анализа, основанной на руководстве по тюнингу и оптимизации для версии 9i, также доступна на сайте Для решения этих задач в разных версиях СУБД использовались различные инструменты. В 8 и 9 версиях доступным штатным инструментом были только административные представления и StatsPack, не отображающих трэдов по большому количеству срезов. Частично эту задачу решал с помощью разработанного мной инструмента ОрСиМОН БЕССТ. С 10 версии появились инструменты AWR, в том числе метрики, ADDM и ASH, предоставляющие не только возможности сбора аналоичных Statspack статистик, но и детальных статистик активных сессий, и возможности расширенной аналитики Обзор механизмов для анализа функционирования экземпляра про счётчики, метрики и AWR Административные представления Основным источником являются счётчики в структурах памяти, доступ к которым может быть получен через администриативные представления V$xxx. При остановке экземпляра данные этих счётчиков обнуляются. Часть этих счётчиков являются кумулятивными, нарастая с момента старта экземпляра, другие представляют текущие значения. Для использования кумулятивных счётчиков требуется собрать несколько срезов, расчитать дельты и соотнести их с промежутком времени, что может быть сделано вручную для формирования последующих аналитических показателей. Периодический сбор статистических срезов, их аналитика и формирование отчётов в 8 и 9 версиях решались с помощью пакета statspack, периодически собиравший данные и имеющего ряд процедур для расчёта и анализа по двум произвольным точкам (начало и конец) сбора статистических срезов. С 10 версии этот механизм расширен механизмами AWR (автоматического репозитория нагрузки), ADDM (аналитического модуля) и ASH (истории активных сессий), и механизмы SQL Plan Management, а с 11 версии появился SQL Monitoring. Пример доступа к административным представлениям ниже:
-- сколько уникальных запросов в библиотечном кэше
select count(distinct sql_id) from v$sqlarea ;
-- сколько запросов выполнялось только 1 раз
select count(*) from v$sqlarea where executions = 1;
-- сводные данные в разрезе SQL_ID с момента старта экземпляра
select sql_id, elapsed_time, cpu_time, executions, round(elapsed_time/executions,2) time_per_exec,
round(cpu_time/executions,2) cpu_time_per_exec
from v$sqlarea where executions >=1 order by 5 desc ;
Метрики C 10 версии для ряда аналитических показателей, состоящие из значений дэльт за период и формул над базовыми показателями, доступными в административных представлениях, вычисляются инфраструктурой AWR автоматически по сконфигурированному алгоритму (периодичность сбора) и отображается в виде так называемых метрик, хранящихся в базе данных с глубиной хранения равной глубине хранения AWR. Метрики можно создавать свои, но уже существующие отражают основные показатели БД, рекомендуемые для ручного анализа в руководстве по тюнингу экземпляра. Ключевой особенностью метрик является период сбора, настраиваемый индивидуально и не зависящий от периодического сбора срезов статистик AWR. Метрика может собираться раз в пять минут, а статистический срез AWR раз в час. В БД доступны через представления V$METRIC, V$METRIC_HISTORY, DBA_HIST_SYSMETRIC_HISTORY, DBA_HIST_SYSMETRIC_SUMMARY, DBA_HIST_FILEMETRIC_HISTORY. Второй набор метрик собирается коллекторами Grid/Cloud Control, о нём сказано ниже
select * from DBA_HIST_SYSMETRIC_HISTORY where rownum < 100;
select * from V$METRIC_HISTORY where rownum < 100;
select * from V$METRIC where rownum < 100;
-- в DBA_HIST_ нет полного объёма метрик
select METRIC_NAME from sys.DBA_HIST_SYSMETRIC_HISTORY group by metric_name order by 1;
-- а в V$_ есть - текущие и история
select METRIC_NAME from V$METRIC group by metric_name order by 1;
select METRIC_NAME from V$METRIC_HISTORY group by metric_name order by 1;
-- а здесь группы
select * from v$metricname where metric_name like '%Hit%' ;
-- выбрать сырые данные
select * from SYS.V$METRIC_HISTORY mh, SYS.V$METRICNAME mn
WHERE mh.METRIC_ID = mn.METRIC_ID and mh.GROUP_ID = mn.GROUP_ID
order by mh.BEGIN_TIME desc, mh.METRIC_ID ;
-- выбрать значения истории с раскадровкой по дополнительным полям (CASE из документации по 11 версии)
select mh.BEGIN_TIME, mh.END_TIME, mh.INTSIZE_CSEC, mh.METRIC_ID, mh.METRIC_NAME, mh.VALUE, mh.METRIC_UNIT, mn.GROUP_NAME,
CASE WHEN mh.GROUP_ID = 0 THEN 'Event#' WHEN mh.GROUP_ID = 1 THEN 'Wait Class ID' WHEN mh.GROUP_ID = 2 THEN 'N/A'
WHEN mh.GROUP_ID = 3 THEN 'N/A' WHEN mh.GROUP_ID = 4 THEN 'Session ID' WHEN mh.GROUP_ID = 5 THEN 'Session ID'
WHEN mh.GROUP_ID = 6 THEN 'N/A' WHEN mh.GROUP_ID = 7 THEN 'File#' WHEN mh.GROUP_ID = 9 THEN 'Tablespace#'
WHEN mh.GROUP_ID = 10 THEN 'N/A' ELSE 'not in docs ' END ENTITY_ID_DESC,
mh.ENTITY_ID,
CASE WHEN mh.GROUP_ID = 0 THEN 'N/A' WHEN mh.GROUP_ID = 1 THEN 'N/A' WHEN mh.GROUP_ID = 2 THEN 'N/A'
WHEN mh.GROUP_ID = 3 THEN 'N/A' WHEN mh.GROUP_ID = 4 THEN 'Seria#' WHEN mh.GROUP_ID = 5 THEN 'Serial#'
WHEN mh.GROUP_ID = 6 THEN 'Service Hash' WHEN mh.GROUP_ID = 7 THEN 'Creation Change#' WHEN mh.GROUP_ID = 9 THEN 'N/A'
WHEN mh.GROUP_ID = 10 THEN 'Service Hash' ELSE 'not in docs ' END ENTITY_SEQ_DESC,
mh.ENTITY_SEQUENCE
from SYS.V$METRIC_HISTORY mh, SYS.V$METRICNAME mn
WHERE mh.METRIC_ID = mn.METRIC_ID and mh.GROUP_ID = mn.GROUP_ID
order by mh.BEGIN_TIME desc, mh.METRIC_ID ;
-- выбрать - только Event Class Metrics
---------------------------------------
-- классы ожиданий
select * from V$SYSTEM_WAIT_CLASS wc ;
-- из общей вьюхи без истории
select mh.BEGIN_TIME, mh.END_TIME, mh.INTSIZE_CSEC, mh.METRIC_ID, mh.METRIC_NAME, mh.VALUE, mh.METRIC_UNIT, mn.GROUP_NAME,
'Wait Class ID' ENTITY_ID_DESC, mh.ENTITY_ID, wc.WAIT_CLASS
from SYS.V$METRIC mh, SYS.V$METRICNAME mn, V$SYSTEM_WAIT_CLASS wc
WHERE mh.METRIC_ID = mn.METRIC_ID and mh.GROUP_ID = mn.GROUP_ID and mn.GROUP_NAME = 'Event Class Metrics'
and mh.ENTITY_ID = wc.WAIT_CLASS#
order by mh.BEGIN_TIME desc, mh.METRIC_ID ;
-- из общей вьюхи с историей
select mh.BEGIN_TIME, mh.END_TIME, mh.INTSIZE_CSEC, mh.METRIC_ID, mh.METRIC_NAME, mh.VALUE, mh.METRIC_UNIT, mn.GROUP_NAME,
'Wait Class ID' ENTITY_ID_DESC, mh.ENTITY_ID, wc.WAIT_CLASS
from SYS.V$METRIC_HISTORY mh, SYS.V$METRICNAME mn, V$SYSTEM_WAIT_CLASS wc
WHERE mh.METRIC_ID = mn.METRIC_ID and mh.GROUP_ID = mn.GROUP_ID and mn.GROUP_NAME = 'Event Class Metrics'
and mh.ENTITY_ID = wc.WAIT_CLASS#
order by mh.BEGIN_TIME desc, mh.METRIC_ID ;
-- из специализированной вьюхи без истории
select wc.WAIT_CLASS, cm.*
from V$SYSTEM_WAIT_CLASS wc, v$WAITCLASSMETRIC cm
where cm.WAIT_CLASS# = wc.WAIT_CLASS# and cm.WAIT_CLASS_ID = wc.WAIT_CLASS_ID
order by cm.begin_time desc ;
-- из специализированной вьюхи с историей
select wc.WAIT_CLASS, cm.*
from V$SYSTEM_WAIT_CLASS wc, v$WAITCLASSMETRIC_HISTORY cm
where cm.WAIT_CLASS# = wc.WAIT_CLASS# and cm.WAIT_CLASS_ID = wc.WAIT_CLASS_ID
order by cm.begin_time desc ;
-- выбрать - только Event Metrics
---------------------------------
-- из общей вьюхи с исторей
select mh.BEGIN_TIME, mh.END_TIME, mh.INTSIZE_CSEC, mh.METRIC_ID, mh.METRIC_NAME, mh.VALUE, mh.METRIC_UNIT, mn.GROUP_NAME,
'Event#' ENTITY_ID_DESC, mh.ENTITY_ID, en.NAME
from SYS.V$METRIC_HISTORY mh, SYS.V$METRICNAME mn, V$EVENT_NAME en
WHERE mh.METRIC_ID = mn.METRIC_ID and mh.GROUP_ID = mn.GROUP_ID and mn.GROUP_NAME = 'Event Metrics'
and mh.ENTITY_ID = en.EVENT#
order by mh.BEGIN_TIME desc, mh.METRIC_ID, mh.ENTITY_ID ;
-- из специализированной вьюхи без истории
select en.NAME, em.*
from V$EVENTMETRIC em, V$EVENT_NAME en
where em.EVENT# = en.EVENT# and em.EVENT_ID = en.EVENT_ID
order by em.begin_time desc ;
Снапшоты (статистические срезы) AWR подразумевает периодический сбор данных встроенных счётчиков аналогично statspack, после чего они становятся доступны не только в отображаемых на память представлениях V$xxx, но и в представлениях DBA_HIST_xxx как сохраняемых даже при рестарте экземпляра периодических срезах, и в виде структурированных отчётов. Также они используются ADDM для автоматической аналитики и выдачи отчётов - рекомендаций Для задач анализа функционирования экземпляров можно использовать формируемые AWR отчёты или самостоятельно рассчитывать требуемые данные, но AWR можно использовать и для анализа запросов. Ниже приведён блок исследований, демонстрирующий возможности по получению данных о запросах из AWR:
-- вытащить данные о попавших в AWR запросах за период - sql_id, количество выполнений,
-- длительность выполнения, среднее время выполнения
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS' ;
-- сформировать базовый запрос по данным для последующей аналитики
select sn.snap_id, sn.BEGIN_INTERVAL_TIME, sn.END_INTERVAL_TIME, sq.sql_id, sq.EXECUTIONS_DELTA,
sq.CPU_TIME_DELTA, sq.ELAPSED_TIME_DELTA
from DBA_HIST_SNAPSHOT sn, DBA_HIST_SQLSTAT sq where sn.snap_id = sq.snap_id order by 1 desc
-- получить данные о периодах и количество уникальных SQL_ID
select MIN(sn.BEGIN_INTERVAL_TIME), MAX(sn.BEGIN_INTERVAL_TIME),count(distinct sql_id),count(*)
from DBA_HIST_SNAPSHOT sn, DBA_HIST_SQLSTAT sq where sn.snap_id = sq.snap_id ;
-- получить количество уникальных SQL_ID
select count(distinct sql_id) from DBA_HIST_SQLSTAT ;
-- результат 6932 строки (а также 56 тыс. неуникальных за 18 дней),
-- что существенно меньше данных об уникальных sql_id
-- в библиотечном кэше (>150 тыс. уникальных sql_id). Вывод - для формирования статистики среднего
-- времени выполнения и последующего отлова проблемных запросов данные из AWR врядли подойдут, правильнее
-- организовывать коллектор непосредственно из библиотечного кэша
-- ещё вывод - используется порочная практика запросов с литеральными переменными
-- получить базовую аналитику для каждого SQL_ID в разрезах периодов - количеству их исполнений,
-- затраченному времени всего и CPU, и на запрос
select sn.snap_id, sn.BEGIN_INTERVAL_TIME, sn.END_INTERVAL_TIME, sq.sql_id, count(sql_id),
sum(sq.EXECUTIONS_DELTA) SUM_EXEC, sum(sq.CPU_TIME_DELTA) SUM_TIME_CPU,
sum(sq.ELAPSED_TIME_DELTA) SUM_TIME_ELAPSED
from DBA_HIST_SNAPSHOT sn JOIN DBA_HIST_SQLSTAT sq ON sn.snap_id = sq.snap_id
group by sn.snap_id, sn.BEGIN_INTERVAL_TIME, sn.END_INTERVAL_TIME, sq.sql_id
order by 5 desc ;
-- по результату мы видим, что количество строк для одного SQL_ID в одном срезе может быть больше одного
-- смотрим на примере одиночного запроса - почему строк больше одной
select sn.snap_id, sn.BEGIN_INTERVAL_TIME, sn.END_INTERVAL_TIME, sq.sql_id,
sq.EXECUTIONS_DELTA SUM_EXEC, sq.CPU_TIME_DELTA SUM_TIME_CPU,
sq.ELAPSED_TIME_DELTA SUM_TIME_ELAPSED, sq.*
from DBA_HIST_SNAPSHOT sn JOIN DBA_HIST_SQLSTAT sq ON sn.snap_id = sq.snap_id
where sn.snap_id = 33452 and sq.sql_id = '7rds574g8u345'
order by 5 desc ;
-- вывод - строк больше одной ибо в один срез зафиксировано шесть разных планов выполнения. То есть суммировать показатели под
-- нашу задачу аналитики SQL_ID безотносительно планов - правильно
--
-- таки выберем аналитические данные за каждый период в разрезе по SQL_ID
select sn.snap_id, sn.BEGIN_INTERVAL_TIME, sn.END_INTERVAL_TIME, sq.sql_id, count(sql_id),
sum(sq.EXECUTIONS_DELTA) SUM_EXEC, sum(sq.CPU_TIME_DELTA) SUM_TIME_CPU,
sum(sq.ELAPSED_TIME_DELTA) SUM_TIME_ELAPSED,
round(sum(sq.ELAPSED_TIME_DELTA)/sum(sq.EXECUTIONS_DELTA),2) ELAPSED_TIME_PER_EXEC,
round(sum(sq.CPU_TIME_DELTA)/sum(sq.EXECUTIONS_DELTA),2) CPU_TIME_PER_EXEC
from DBA_HIST_SNAPSHOT sn, DBA_HIST_SQLSTAT sq where sn.snap_id = sq.snap_id and sq.EXECUTIONS_DELTA <> 0
group by sn.snap_id, sn.BEGIN_INTERVAL_TIME, sn.END_INTERVAL_TIME, sq.sql_id
order by 1 desc,4
-- сформировать аналитику в разрезе по SQL_ID за все периоды - количество исполнений, время всего и CPU, время на одно исполнение
-- в разрезах минимально, в среднем и максимально
select sql_id,round(min(ELAPSED_TIME_PER_EXEC),2) MIN_TM_PER_EXEC, round(avg(ELAPSED_TIME_PER_EXEC),2) AVG_TM_PER_EXEC,
round(max(ELAPSED_TIME_PER_EXEC),2) MAX_TM_PER_EXEC, round(STDDEV(ELAPSED_TIME_PER_EXEC),2) DEV_TM_PER_EXEC,
round(max(ELAPSED_TIME_PER_EXEC)/min(ELAPSED_TIME_PER_EXEC),2) FLAG1
from ( select sn.snap_id, sn.BEGIN_INTERVAL_TIME, sn.END_INTERVAL_TIME, sq.sql_id, count(sql_id),
sum(sq.EXECUTIONS_DELTA) SUM_EXEC, sum(sq.CPU_TIME_DELTA) SUM_TIME_CPU, sum(sq.ELAPSED_TIME_DELTA) SUM_TIME_ELAPSED,
round(sum(sq.ELAPSED_TIME_DELTA)/sum(sq.EXECUTIONS_DELTA),2) ELAPSED_TIME_PER_EXEC,
round(sum(sq.CPU_TIME_DELTA)/sum(sq.EXECUTIONS_DELTA),2) CPU_TIME_PER_EXEC
from DBA_HIST_SNAPSHOT sn, DBA_HIST_SQLSTAT sq where sn.snap_id = sq.snap_id and sq.EXECUTIONS_DELTA <> 0
group by sn.snap_id, sn.BEGIN_INTERVAL_TIME, sn.END_INTERVAL_TIME, sq.sql_id
order by 1 desc,4 )
group by sql_id ;
-- КОНЕЦ блока моих исследований под задачу контроля долгоиграев
-- временная аналитика AWR
-- смотрим количество запросов с большой разничей между максимальным и минимальным значением времени на один запрос
with bs1 as ( select sn.snap_id, sn.BEGIN_INTERVAL_TIME, sn.END_INTERVAL_TIME, sq.sql_id, count(sql_id),
sum(sq.EXECUTIONS_DELTA) SUM_EXEC, sum(sq.CPU_TIME_DELTA) SUM_TIME_CPU, sum(sq.ELAPSED_TIME_DELTA) SUM_TIME_ELAPSED,
round(sum(sq.ELAPSED_TIME_DELTA)/sum(sq.EXECUTIONS_DELTA),2) ELAPSED_TIME_PER_EXEC,
round(sum(sq.CPU_TIME_DELTA)/sum(sq.EXECUTIONS_DELTA),2) CPU_TIME_PER_EXEC
from DBA_HIST_SNAPSHOT sn, DBA_HIST_SQLSTAT sq where sn.snap_id = sq.snap_id and sq.EXECUTIONS_DELTA <> 0
group by sn.snap_id, sn.BEGIN_INTERVAL_TIME, sn.END_INTERVAL_TIME, sq.sql_id
order by 1 desc,4 )
select count(*) from
(select sql_id,round(min(ELAPSED_TIME_PER_EXEC),2) MIN_TM_PER_EXEC, round(avg(ELAPSED_TIME_PER_EXEC),2) AVG_TM_PER_EXEC,
round(max(ELAPSED_TIME_PER_EXEC),2) MAX_TM_PER_EXEC, round(STDDEV(ELAPSED_TIME_PER_EXEC),2) DEV_TM_PER_EXEC,
round(max(ELAPSED_TIME_PER_EXEC)/min(ELAPSED_TIME_PER_EXEC),2) FLAG1
from bs1 where SUM_EXEC > 1
group by sql_id) where FLAG1 <= 20
UNION ALL
select count(*) from
(select sql_id,round(min(ELAPSED_TIME_PER_EXEC),2) MIN_TM_PER_EXEC, round(avg(ELAPSED_TIME_PER_EXEC),2) AVG_TM_PER_EXEC,
round(max(ELAPSED_TIME_PER_EXEC),2) MAX_TM_PER_EXEC, round(STDDEV(ELAPSED_TIME_PER_EXEC),2) DEV_TM_PER_EXEC,
round(max(ELAPSED_TIME_PER_EXEC)/min(ELAPSED_TIME_PER_EXEC),2) FLAG1
from bs1 where SUM_EXEC > 1
group by sql_id) where FLAG1 > 20 ;
Механизм ASH ежесекундно собирает данные об активных сессиях встроеными сенсорами, что доступно через представления V$xxx и сбрасывает их в выделенные AWR таблицы DBA_HIST_ACTIVE_SESSION..., сохраняя не только значения на момент среза, но и промежуточные значения между точками среза, что в дальнейшем позволяет агрегировать и анализировть данные активных сессий более детально. В составе Grid Control предоставляется графический модуль ASH аналитики, позволяющий выбирать начало и ширину временного периода, а сами административные представления предоставляют информацию для анализа происходившего в системе в разрезе по времени, запросам и сессиям. Ниже - небольшие примеры использования ASH представлений:
select sample_id,count(sample_id),sample_time,session_state,wait_class,sum(time_waited)/1000000,
sum(TM_DELTA_TIME)/1000000,sum(TM_DELTA_CPU_TIME)/1000000,sum(TM_DELTA_DB_TIME)/1000000,
sum(DELTA_TIME)/1000000
from V$ACTIVE_SESSION_HISTORY
group by sample_id,sample_time,session_state,wait_class
order by sample_time desc,session_state,wait_class ;
-- записи за период
select * from DBA_HIST_ACTIVE_SESS_HISTORY
where sample_time > to_date('2017-01-07 21:00:00', 'YYYY-MM-DD HH24:MI:SS') and
sample_time < to_date('2017-01-07 22:00:00', 'YYYY-MM-DD HH24:MI:SS') ;
-- о работавших сессиях за период
select SESSION_ID, PROGRAM, MODULE, ACTION from DBA_HIST_ACTIVE_SESS_HISTORY
where sample_time > to_date('2017-01-07 21:00:00', 'YYYY-MM-DD HH24:MI:SS') and
sample_time < to_date('2017-01-07 22:00:00', 'YYYY-MM-DD HH24:MI:SS')
group by SESSION_ID, PROGRAM, MODULE, ACTION order by 1 ;
-- смотрим события активности сессии за период
select SESSION_ID, SAMPLE_TIME,SQL_ID, TOP_LEVEL_SQL_ID, SQL_PLAN_OPERATION, EVENT, P1TEXT,
P2TEXT, P3TEXT, WAIT_CLASS, WAIT_TIME, SESSION_STATE, TIME_WAITED, BLOCKING_SESSION,
PROGRAM, MODULE, ACTION, CLIENT_ID
from DBA_HIST_ACTIVE_SESS_HISTORY
where sample_time > to_date('2017-01-07 21:00:00', 'YYYY-MM-DD HH24:MI:SS') and
sample_time < to_date('2017-01-07 23:28:00', 'YYYY-MM-DD HH24:MI:SS')
and SESSION_ID = 991
order by sample_time ;
db_time и классы ожидания С этой же 10 версии введено понятие db_time, которое отражает суммарную параллельную активность в БД. Например, за час значение db_time может составить 500 часов, с которыми можно сравнивать суммарное значение того или иного действия или события ожидания в БД за тот же промежуток времени. Введение этого понятия позволяет соотносить длятельность всех производительных и непроизводительных событий ожидания с db_time, что удобно делать с групптровкой по классам ожидания. Прямое обращение к v$waitclassmetric_history позволяет, например, построить график, аналогичный EM TopActivity и увидеть распределение нагрузки по классам событий ожидания за относительно произвольный промежуток времени. Ниже приведены запросы, предоставляющие данные о распределении нагрузки по классам событий ожидания
-- базовый запрос причёсаный и с группировкой для раскрутки
SELECT end_time endTime,
ROUND (SUM (CASE WHEN wait_class = 'CPU Usage Per Sec' THEN aas ELSE 0 END), 2) cpu,
ROUND (SUM (CASE WHEN wait_class = 'User I/O' THEN aas ELSE 0 END), 2) uio,
ROUND (SUM (CASE WHEN wait_class = 'Cluster' THEN aas ELSE 0 END), 2) clu,
ROUND (SUM (CASE WHEN wait_class = 'Wait' THEN aas ELSE 0 END), 2) wt,
ROUND (SUM (CASE WHEN wait_class = 'Average Active Sessions' THEN aas ELSE 0 END), 2) aas
FROM (SELECT h.end_time,
CASE WHEN c.wait_class = 'User I/O' THEN c.wait_class
WHEN c.wait_class = 'Cluster' THEN c.wait_class ELSE 'Wait' END wait_class,
h.time_waited / (h.intsize_csec / 100) aas
FROM v$waitclassmetric h, v$system_wait_class c WHERE h.wait_class# = c.wait_class#
AND c.wait_class != 'Idle'
UNION ALL
SELECT end_time, metric_name,
CASE WHEN metric_name = 'CPU Usage Per Sec' THEN VALUE
WHEN metric_name = 'Average Active Sessions' THEN VALUE END aas
FROM v$sysmetric WHERE metric_name IN ('CPU Usage Per Sec', 'Average Active Sessions')
AND GROUP_ID = 2)
GROUP BY end_time ;
-- базовый запрос причёсаный и с группировкой - но уже из исторических вьюх
SELECT end_time endTime,
ROUND (SUM (CASE WHEN wait_class = 'CPU Usage Per Sec' THEN aas ELSE 0 END), 2) cpu,
ROUND (SUM (CASE WHEN wait_class = 'User I/O' THEN aas ELSE 0 END), 2) uio,
ROUND (SUM (CASE WHEN wait_class = 'Cluster' THEN aas ELSE 0 END), 2) clu,
ROUND (SUM (CASE WHEN wait_class = 'Wait' THEN aas ELSE 0 END), 2) wt,
ROUND (SUM (CASE WHEN wait_class = 'Average Active Sessions' THEN aas ELSE 0 END), 2) aas
FROM (SELECT h.end_time,
CASE WHEN c.wait_class = 'User I/O' THEN c.wait_class
WHEN c.wait_class = 'Cluster' THEN c.wait_class ELSE 'Wait' END wait_class,
h.time_waited / (h.intsize_csec / 100) aas
FROM v$waitclassmetric_history h, v$system_wait_class c WHERE h.wait_class# = c.wait_class#
AND c.wait_class != 'Idle'
UNION ALL
SELECT end_time, metric_name,
CASE WHEN metric_name = 'CPU Usage Per Sec' THEN VALUE
WHEN metric_name = 'Average Active Sessions' THEN VALUE END aas
FROM v$sysmetric_history WHERE metric_name IN ('CPU Usage Per Sec', 'Average Active Sessions')
AND GROUP_ID = 2)
GROUP BY end_time ORDER BY end_time ;
-- внутренний запрос без группировки классов ожидания
SELECT h.end_time, c.wait_class, h.time_waited / (h.intsize_csec / 100) aas
FROM v$waitclassmetric h, v$system_wait_class c WHERE h.wait_class# = c.wait_class# AND c.wait_class != 'Idle'
UNION ALL
SELECT end_time, metric_name,
CASE WHEN metric_name = 'CPU Usage Per Sec' THEN VALUE WHEN metric_name = 'Average Active Sessions' THEN VALUE END aas
FROM v$sysmetric WHERE metric_name IN ('CPU Usage Per Sec', 'Average Active Sessions') AND GROUP_ID = 2 ;
-- что это за группа 2
SELECT end_time, metric_name, VALUE FROM v$sysmetric order by 2 ;
select * from sys.v_$metric where GROUP_ID = 2 ;
-- внутренний запрос -усложнённый- без группировки классов ожидания
SELECT h.begin_time,h.end_time,c.wait_class,intsize_csec,h.time_waited,h.dbtime_in_wait, h.time_waited / (h.intsize_csec / 100) aas
FROM v$waitclassmetric h, v$system_wait_class c WHERE h.wait_class# = c.wait_class# AND c.wait_class != 'Idle'
UNION ALL
SELECT begin_time,end_time,metric_name,intsize_csec,value,0,
CASE WHEN metric_name = 'CPU Usage Per Sec' THEN VALUE WHEN metric_name = 'Average Active Sessions' THEN VALUE END aas
FROM v$sysmetric WHERE metric_name IN ('CPU Usage Per Sec', 'Average Active Sessions') AND GROUP_ID = 2 ;
-- внутренний запрос -усложнённый- без группировки классов ожидания
SELECT h.begin_time,h.end_time,c.wait_class,
-- это есть в базовом запросе - время ожидания делить на сотую от длины периода - это процент
-- от периода (используется), но не от DBTime но по сути это означает среднее значение на секунду
h.time_waited / (h.intsize_csec / 100) aas
FROM v$waitclassmetric_history h, v$system_wait_class c
WHERE h.wait_class# = c.wait_class# AND c.wait_class != 'Idle'
UNION ALL
SELECT begin_time,end_time,metric_name,
CASE WHEN metric_name = 'CPU Usage Per Sec' THEN VALUE
WHEN metric_name = 'Average Active Sessions' THEN VALUE END aas
FROM v$sysmetric_history
WHERE metric_name IN ('CPU Usage Per Sec', 'Average Active Sessions') AND GROUP_ID = 2 ;
-- представление с историей
select * from sys.V_$WAITCLASSMETRIC_history ;
про SQL Monitoring Компонент TuningPack. Ещё одним механизмом является SQL Monitoring, отслеживающий статусы отдельных SQL запросов, выполняющихся более 5 секунд или всегда, если параллельных. Однако из опыта знаю - запросы бывают ситуации, при которых данные долго выполняющегося запроса в SQL Monitoring не попадают (например больше часа висящий update, ожидающий снятия строчной блокировки другой сессии в 11 версии при тестировании не попал в выборку. Основными представлениями являются V$SQL_MONITOR и V$SQL_PLAN_MONITOR). В документации к 12 версии - Database SQL Tuning Guide - 16 Monitoring Database Operations
-- данные о текущих SQL
select * from v$sql_monitor ;
-- хранит данные за 5 дней, для наших целей вытаскивания текущих данных для мониторинга подходит
-- отсюда же можно вытащить долгоиграющие запросы для формирования статистики. Запросы исследования ниже:
--
-- за сколько дней хранится информация
select min(SQL_EXEC_START) from v$sql_monitor ;
-- строк с уникальным и неуникальным SQL_ID за эти 5 дней - 795 и 1280
select count(distinct SQL_ID) from v$sql_monitor ;
select count(*) from v$sql_monitor ;
-- сколько вхождений в sql_monitoring в разрезе по дням
select count(*),to_char(SQL_EXEC_START,'YYYY-MM-DD') from v$sql_monitor group by to_char(SQL_EXEC_START,'YYYY-MM-DD') order by 2 ;
-- проверяем, что SQL_EXEC_ID уникален
-- сгруппируем по сессии и имени старта, иногда больше 1, неожиданно. Видимо это разные sql_id. Запросы ниже:
select SID, SQL_EXEC_START, COUNT(SQL_EXEC_ID) from v$sql_monitor group by SID, SQL_EXEC_START order by 3 desc ;
-- проверяем гипотезу, SQL_ID и SQL_EXEC_ID при счётчике больше единицы разные
-- однако SQL_EXEC_ID - повторяется для разных наборов SID, SQL_EXEC_START, QL_ID
-- вывод - для идентификации конкретного долгоиграющего запроса можно опираться на связку
-- трёх SID, SQL_EXEC_START, SQL_ID. На SQL_EXEC_ID не стоит опираться. Запросы исследования ниже:
select SID, SQL_EXEC_START, SQL_EXEC_ID, SQL_ID from v$sql_monitor
where sid = 1099 and SQL_EXEC_START = TO_DATE('09.06.2017 16:53:41','DD-MM-YYYY HH24:MI:SS') ;
select STATUS, SID, SQL_EXEC_ID, SQL_EXEC_START, SQL_ID
from v$sql_monitor group by SID, SQL_EXEC_ID, SQL_EXEC_START, SQL_ID, STATUS order by 4 desc, 3 ;
-- применительно к V$SQL_PLAN_MONITOR - там именно есть планы и базовые статистики выполнения. Если анализировать
-- по статистикам и планам - то может пригодиться
-- дополнительные вводные: (1) смотрим только на активные запросы; (2) периодичность контроля выставляем раз в минуту (пять минут).
-- Если запрос выполняется быстрее - он не попадёт в активные, что для отлова именно долгоиграющих приемлемо
-- пример простого сенсора
select SID, SESSION_SERIAL#, SQL_ID, SQL_EXEC_START, ELAPSED_TIME, CPU_TIME, STATUS, USERNAME,
MODULE, ACTION, SERVICE_NAME, CLIENT_IDENTIFIER, CLIENT_INFO, PROGRAM, PROCESS_NAME
from v$sql_monitor where status = 'EXECUTING' ;
-- пример более сложного сенсора
select SID, SESSION_SERIAL#, SQL_ID, SQL_EXEC_START, ELAPSED_TIME, CPU_TIME, STATUS, USERNAME, MODULE, ACTION, SERVICE_NAME,
CLIENT_IDENTIFIER, CLIENT_INFO, PROGRAM, PROCESS_NAME from v$sql_monitor
where status = 'EXECUTING' or status = 'QUEUED' ;
-- количество запросов в SQL monitor по дням
select count(*),to_char(SQL_EXEC_START,'YYYY-MM-DD') from v$sql_monitor group by to_char(SQL_EXEC_START,'YYYY-MM-DD') order by 2 ;
-- смотрим данные по запросу с SQL_ID = 33ptjkjhhumr2
select SID, SESSION_SERIAL#, SQL_ID, SQL_EXEC_START, ELAPSED_TIME, CPU_TIME, STATUS, USERNAME, MODULE, ACTION, SERVICE_NAME,
CLIENT_IDENTIFIER, CLIENT_INFO, PROGRAM, PROCESS_NAME from v$sql_monitor where sql_id = '33ptjkjhhumr2' ;
select SID, COUNT(SQL_EXEC_ID) from v$sql_monitor group by SID, SQL_EXEC_ID order by 2 desc ;
про CloudControl (Enterprise Manager, EM) Кроме того при использовании CloudControl (Enterprise Manager, EM) существует отдельный механизм сбора ещё одного, отдельного, набора метрик и счётчиков, характерного именно для EM, доступного в виде большого массива показателей, без агрегации, а также с часовой и дневной агрегацией через таблицы EM_METRIC_VALUES, EM_METRIC_VALUES_DAILY, EM_METRIC_VALUES_HOURLY и представления GC_METRIC_VALUES, GC_METRIC_VALUES_DAILY, GC_METRIC_VALUES_DAILY_AGG, GC_METRIC_VALUES_HOURLY, GC_METRIC_VALUES_HOURLY_AGG в схеме SYSMAN БД EM
select a1.ENTITY_TYPE, a1.ENTITY_NAME, a1.ENTITY_GUID, a1.PARENT_ME_TYPE, a1.PARENT_ME_NAME,
a1.PARENT_ME_GUID, a1.TYPE_META_VER, a1.METRIC_GROUP_NAME, a1.METRIC_COLUMN_NAME,
a1.COLUMN_TYPE, a1.COLUMN_INDEX, a1.DATA_COLUMN_TYPE, a1.METRIC_GROUP_ID,
a1.METRIC_GROUP_LABEL, a1.METRIC_GROUP_LABEL_NLSID, a1.METRIC_COLUMN_ID,
a1.METRIC_COLUMN_LABEL, a1.METRIC_COLUMN_LABEL_NLSID, a1.DESCRIPTION, a1.SHORT_NAME,
a1.UNIT, a1.IS_FOR_SUMMARY, a1.IS_STATEFUL, a1.NON_THRESHOLDED_ALERTS, a1.METRIC_KEY_ID,
a1.KEY_PART_1, a1.KEY_PART_2, a1.KEY_PART_3, a1.KEY_PART_4, a1.KEY_PART_5, a1.KEY_PART_6,
a1.KEY_PART_7, a1.COLLECTION_TIME, a1.COLLECTION_TIME_UTC, a1.COUNT_OF_COLLECTIONS,
a1.AVG_VALUE, a1.MIN_VALUE, a1.MAX_VALUE, a1.STDDEV_VALUE
from SYSMAN.GC$METRIC_VALUES_HOURLY a1 ;
Применительно к БД существует большое количество метрик EM, которые отражают не дельты абсолютных показателей на начало и конец периода, а эти же параемтры, усреднённые на секунду и на транзакцию. Для графического представления динамики такие показатели в расчёте на секунду являются вполне приемлемыми. С помощью менеджера отчётов EM можно создать суммарный отчёт по профилю нагрузки БД со многими показателями. Ниже приводится используемый мной текущий перечень показателей для подобного отчёта:
Кстати, таким же способом можно сформировать отчёт по утилизации ресурсов операционной системы не завершено ... Белонин С.С. (С), октябрь 2010 года (даты последующих модификаций не фиксируются)
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
(C) Белонин С.С., 2000-2024. Дата последней модификации страницы:2019-12-04 00:43:28