Направление Oracle  
  Анализ настройки и функционирования экземпляра  


Настоящие материалы являются авторскими, права автора защищены Законами РФ и международными соглашениями. Для использования настоящих материалов вам необходимо ознакомиться и полностью принять лицензионное соглашение. В случае, если вы не принимаете настоящее лицензионное соглашение полностью, вы не имеете права пользоваться настоящими материалами

Начальные слова

С учётом появления в 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 можно создать суммарный отчёт по профилю нагрузки БД со многими показателями. Ниже приводится используемый мной текущий перечень показателей для подобного отчёта:

 
Styled Text - ---
Average Active Sessions: Throughput Active Sessions Using CPU: Wait Bottlenecks Active Sessions Waiting: I/O: Wait Bottlenecks Active Sessions Waiting: Other: Wait Bottlenecks
Current Logons Count: Database limits Cumulative Logons Per Sec Current Open Cursors Count: Database Limits Network Bytes Per Second: Throughput
Generic Alert Log Error Status: Alert Log Error Status Generic Alert Log Error Status: DB Alert Log Error Status Media Failure Alert Log Error Status: Alert Log Error Status Media Failure Alert Log Error Status: DB Alert Log Error Status
 
Styled Text - ---
Buffer Cache Hit (%): Efficiency Cursor Cache Hit (%): Efficiency Data Dictionary Hit (%): Efficiency Library Cache Hit (%): Efficiency
PGA Cache Hit (%): Efficiency Redo Log Allocation Hit (%): Efficiency   
 
Styled Text - ---
DB Time (seconds): System Time Model DB CPU (seconds): System Time Model Database CPU Time (%): Efficiency Wait Time (%): Wait Bottlenecheck
 
Styled Text - ---
Physical Reads Per Sec: Thtoughput Physical Reads Direct Per Sec: Thtoughput Physical Reads Direct Lobs Per Sec: Thtoughput Physical Writes Per Sec: Thtoughput
Physical Writes Direct Per Sec: Thtoughput Physical Writes Direct Lobs Per Sec: Thtoughput  
 
Styled Text - ---
Database Block Gets Per Sec: Thtoughput Database Block Changes Per Sec: Thtoughput Consistent Read Gets Per Sec: Throughput Consistent Read Changes Per Sec: Throughput
Consistent Read Blocks Created Per Sec: Throughput Consistent Read Undo Records Applied Per Sec: Throughput  
 
Styled Text - ---
Executes Per Sec: Throughput Executes Performed without Parses (%): Throughput Hard Parses Per Sec: Throughput Open Cursors Per Sec: Throughput
Parse Failure Count Per Sec: Throughput Recursive Calls Per Sec: Throughput Soft Parse (%): Trhroughput Total Parses Per Sec: Throughput
Total Tables Scan Per Sec: Throughput Total Index Scan Per Sec: Througput CPU Calls Per Sec: Troughput User Calls (%): Throughput
User Commits Pr Sec: Throughput User Rollbacks Pr Sec: Throughput User Rollback Undo Records Applied Per Sec: Throughput Failed Parsing (SQL ) Time Per Second: System Time Model
 
Styled Text - ---
Redo Generated Per Sec: Throughput Redo Writes Per Sec: Throughput  
 
Styled Text - ---
Sorts in Memory (%): Efficiency Sorts in Memory Per Sec Throughput Sorts to Disk Per Sec Throughput 
 
Styled Text - ---
DB Time Spent Waiting (%): Waits By Wait Class Enqueue Deadlocks Per Sec: Throughput Enqueue Request Per Sec: Throughput Enqueue Timeouts Per Sec: Throughput
Enqueue Waits Per Sec: Throughput   
 
Styled Text - ---
Fast Recovery Area Size: Fast Recovery Free Archive Area (KB): Archive Area Free Dump Area (KB): Archive Area 

Кстати, таким же способом можно сформировать отчёт по утилизации ресурсов операционной системы

не завершено ...

Белонин С.С. (С), октябрь 2010 года

(даты последующих модификаций не фиксируются)


 
     
   
   
    Нравится      

(C) Белонин С.С., 2000-2018. Дата последней модификации страницы:2018-01-09 13:25:41