А какой подход у вас, к обработке awrstatspack-данных

Здравствуйте.

Пролог.

Есть пара вопросов, которые уже много лет любопытно уточнить у причастной общественности.
Но.
В моём болотистом-низменном крае и людей в теме: мало и обстановка в ит-направлении, на большинстве предприятий, не способствует.
Вот, поэтому, попробую с помощью хабра удовлетворить своё любопытство.
Вопрос касается подхода к обработке awr|statspack-данных, по наблюдаемой-поддерживаемой oracle-бд.

Итак к сути.

Завязка

Речь идёт о разборе кейсов с деградацией продуктивности субд и/или аномалиях в потреблении каких то ресурсов, в/для работы субд.
В общем случае, тут, конечно, может быть много каких факторов и дело в лёгкую может дойти до рассмотрения всяких ханганалайз, системстейт, эвент трейсов.
Я подразумеваю либо начало разбора кейсов, когда работа идёт с более менее широко употребимыми средствами-инструментами анализа состояния субд.
Либо такие кейсы в которых подробное рассмотрение работы кода субд не требуется, т.е. факторы инцидента устанавливаются без использования чего нибудь, вроде oradebug;
Ну. Т.е., это факторы типа: а у нас сроки, нам нада, мы запустили скриптик;
По моему впечатлению, классика жанра, в обработке awr-данных: это рассматривание awr-отчётов.
Смотрят awr-отчёты: не просто так, по какой то потребности.
Потребность эта, всегда, происходит от работы (или не работы) сопровождаемой субд.
Ну и соответственно: смотрят с определённым целеполаганием понять, чем обусловлена вот такая работа (или не работа) наблюдаемой субд.
Кроме простых awr-отчётов есть ещё compare period awr-отчёты.
Ну. Бывает так что у людей: SE и диагностик-пака нет, тогда: статспак, как вариант и у него тоже есть отчёты.
В результативности такого подхода к анализу каких то изменений в состоянии субд, один из критично важных пререквайремент-ов: это степень понимания oracle-субд, как системы обслуживания скл-команд, того кто занимается этим рассмотрением.
Ну т.е.: насколько достаточно этот человек понимает какие и как срабатывают механизмы субд, при обслуживании ей поступающего на неё потока скл-команд.
Какие события (event в терминах oracle-субд) возникают при работе какого то мех-ма.
Какими каунтерами активности (statistic в терминах oracle-субд) описывается работа какого то мех-ма субд.
Он должен так же понимать: какие временные затраты на обработку каких либо событий, в каком то мех-ме данной oracle-субд, имеют место быть, в типовом состоянии этой субд.
Какая (в количественном смысле) активность выполняется, в работе того или иного мех-ма субд.
Ну, то есть: OWI-based тюнинг методология + понимание эвентов, статистик, параметров субд + понимание взаимодействия субд с ОС-ью маст-хв.
Ибо.
Если такого понимания нет, в достаточной полной мере, углядеть аномалию, понять факторы которые её вызывают, сориентироваться что с ними (с факторами этими) делать и до каких пор делать ну человек не сможет.
Офкоус: oracle (и не только oracle) как вендор постоянно говорит и что то делает для достижения такого эксплуатационного свойства своих субд как zero administrative level;
Ну вот addm например есть оно из этой оперы.
Однако же не всегда, оно, говорит что либо про причины какого то инцидента/аномалии, оно больше говорит про то что с этим делать.
Насколько точно говорит и насколько адекватно: это вопрос отдельный.

К чему я это всё.
С одной стороны: понимание работы мех-мов/подсистем субд нужно для того чтобы разглядывая какой нибудь awr-отчёт, как врач данные анализов, быстро смекнуть что ещё надо посмотреть/проверить.
Это первое.
Второе: кроме достаточно полного понимания работы субд, как сервисного механизма, надо располагать ещё одной информацией про рассматриваемую субд какой у неё бейзлайн. Ну или по другому сказать какая она, в своём нормальном состоянии.
Сколько у неё, в этом самом нормальном состоянии, вот этих самых временных затрат, различных активностей.
А нельзя ли как то бы, чем то бы так чтобы и бейзлайн показывало, и про взаимосвязи тоже что нибудь говорило, содержательное и побыстрее и понимания в субд поменьше требовало.
Вот, собственно, про мои вопросы.

Тут, отскакивая в сторону, оговорюсь: нормальное состояние субд это, конечно, понятие дискутируемое, надо бы тут уточнить что я имею в виду.
Строго говоря (и, увы, в идеальном мире), однозначное формальное определение нормального состояния (ну, в смысле работы) субд берётся не волюнтаристским решением кого то там.
Всё происходит от требований проекта, к функционалу-продуктивности сервиса и, соответственно к его серверной инфр-ре, частью которого является субд.
Соответственно при отстройке-запуске проекта делается, в частности, ПСИ, в рамках которого делается нагрузочное тестирование и вымеряется: а действительно ли, вот то что по проекту отстроили оно позволяет выполнять требования ТЗ, на работу сервиса.
Ну и если: да позволяет/выполняет, тогда можно рассматривать рабочие показатели компонент серверного ландшафта, в частности субд, под заданной, по ТЗ, нагрузкой и из них выводить конкретику: правила и условия что считаем нормой.
Тут я умышленно обтекаемо выражаюсь: выводить, чтобы сильно не углубляться в процесс вывода норм технической эксплуатации серверной инфр-ры проекта.
Т.е.: я тут хочу сказать, по существу, что, по идее, вот это понятие: "нормальное состояние субд" это не должна быть какая то отсебятина.
При нормальном запуске проекта у неё не может не быть формального определения.
Со всеми, от него происходящими, организационно-техническими последствиями правилами мониторинга, зонирования инцидентов, правилами ресурсных/архитектурных работ с ростом нагрузки и т.п.

В рамках данной статьи, предлагаю считать нормальным состоянием субд: такое её состояние при котором, считается, что субд обслуживает информационные сервисы, с ней работающие приемлемо.
Ну, по рабоче-крестьянски выражаясь: когда жалоб нет, на работу субд.

При этом, все понимают, что фактическое состояние субд, даже в рамках нормальной её работы: может меняться по ряду причин.
Ну. Например: ночью запускаются кронтаб-таски по физическому/логическому резервированию, а днём из нет.
Ночью работают репорт-задачи, какие нибудь etl-и, ещё что то там такого плана.
А пользователи ночью спят, в основной массе своей. Зато днём работают.
Т.е. состояние субд может быть существенно разным, в смысле профиля нагрузки, задействованных механизмов субд, степени их активности.

И тут тогда вопрос а что такое бейзлан субд, это что именно: чего и сколько, становится открытым.
А какой то один, же awr|статспак-отчёт, за конкретный период времени: он, ну, он описывает состояние субд в этот момент времени, не более того.
Т.е., ну, да: может быть известно что в этот момент времени в субд была какая то аномалия. А в чём она именно выражается, насколько (и в чём) это отклонение от нормы вопрос открытый.

Безусловно: никто не запрещает построить N отчётов и начать их просматривать.
Да, тут, в итоге может сложится достаточно полное представление о том что там, в этой субд, в норме и что такое была какая то аномалия.
Но. Это же долго и непросто.

"Тут критик воскликнет: здесь всё в чёрном цвете, ведь есть " EM-консоль, есть EMGС.
Да. Есть. А так же есть аналоги, более-менее платные-проприетарные: всякие спотлайты, спвьюверы и т.п.
Тоже могут рисовать всякие красивые графики, раскладки сервисного времени субд, дриллинг-даун классов ожиданий, ash-визуализация и даже тот же бейзлайн-субд могут определить и относительного него показывать изменения.
Алерты, графики заданных эвентов/каунтеров за заданный период времени это всё да, есть.
Но опять же:

1. достаточно полное знание субд оно не отменяет. Оно, в отношении типовых операций со стат-данными по работе субд упрощает жизнь. Для чего то не типового надо разбираться и настраивать инструмент. Ещё вопрос позволяет ли оно то что от него может захотется.
2. Сам по себе инструмент чего то требует. И я тут даже не столько о деньгах. Например та же em-консоль: это ява-сервлет, который запускается из недо jvm в ORACLE_HOME и работает с субд т.е. отправляет ей данные и получает от неё данные. Часто в xml-формате. Часто бывает так что базейка с этим ява-сервлетом ну вот не могут они прямо сейчас пообщаться. И кто то из них, кому то, приготовил NГигабайт этих xml-ек. Потом начинается обработка этого объёма данных со всеми сопутствующими эффектами жор цпу, пейджинг-сваппинг, занятие дисковой ёмкости под эти xml-ки, ротирование их.

Ну т.е.: есть своя область применимости, у этих инструментов.
И моё мнение не очень то широкая, не очень то для общего случая.

Так вот, возвращаясь к вопросам. К чему я подвожу, этим лонгридом.

Кульминация

1. В общем случае: работа с одними awr|статспак-отчётами не провайдит контекст состояния субд. Т.е. если возникает какой то инцидент/аномалия, в работе субд, хотелось бы (да что там: нужно) понимать оно относительно чего: инцидент/аномалия. Т.е. норма она какая.
2. Контекст можно посмотреть в чём нибудь типа em-консоли. Но с оговорками. Надо чтобы оно было и работало. Надо чтобы были сами данные, которые средство будет отображать. А их может и не быть, ну вот нет диагностик-пака, нет awr-репозитория, нет долговременного сбора-хранения данных базой саму про себя что, как, сколько чего в ней делается/не делается. Ну и всё. Значит ставить статспак например и прикручивать к нему либо что то самописное, либо какой нибудь spviewer. В обоих случаях вопросец: а что и как оно может/не может и за какие затраты, ресурсные/временные.
3. Ну и эти, самые, взаимосвязи. Анализ то есть. Куда смотреть то, что как понимать, чтобы факторы аномалии, в работе субд, раскопать. Хинты бы какие, подсказки, а лучше прямые указания и чтобы побыстрее и поменьше экспертизы в субд требовало.

Про контекст. Тут, вроде как, всё просто.
Что awr-репозиторий, что статспак: это просто набор таблиц.
Ну. В случае awr-а: вендор, вполне резонно, предлагает пользоваться не именно таблицами, а DBA_HIST_* представлениями.
Хотя и если очень хочется, то можно запрашивать на напрямую SYS.WRH$_* таблицы;
Суть в том что: вполне себе пишутся-нарабатываются sql-скрипты, которыми можно опросить таблицы awr, или статспак-репозитория и получить данные для OWI-анализа субд.
Т.е., ту же картинку-график которую рисует, например, em-консоль, про структуру сервисного времени субд, про стуктуру временных затрат на классы ожиданий, по структуре временных затрат на эвенты внутри классов ожиданий, по профилю нагрузки, по ОС-статстистике (dba_hist_osstat) и прочие запросики.
Дальше визуализировать, данные от этих запросов, каким больше нравится способом.
Хоть питоном, хоть в заббикс засунуть, хоть в прометеус, хоть в эксель кому как больше нравится/удобно/можно.
И вот получается, практически такое же, как в той же em-консоли, представлеие информации о состоянии субд.
Дальше, уже поглядывая на графики и видя где аномалия, где не аномалия, можно изучать, предметно, состояние субд, ну допустим теми же awr|statspack-репортами.
Вот тут первый вопрос.
Складывается такое впечатление, рассмотрение субд делается практически исключительно через работу с awr|statspack-отчётами, или с чём то типа em-консоли (чаще всего в ней).
Любопытно почему так, ну: не удобно же, затратно-долго (см. пропозиции выше).

Про эксель, кстати.
На гитхабе есть проект который генерирует такой отчёт: oracle-awr-report
Ну. Why not, как говорится.
Скачал, проектик cmod u+x oracle-awr-report.py, отsed-ил конфиг и всё генерит репорт по какой надо бд.
Кастомизируется как угодно, питон же.
Т.е. чего народ вот такого рода утилками не пользуется, не у всех же emgc, для at a glance ознакомления с состоянием субд, через графики;

Второй момент, про анализ рассматриваемых данных, всех или какой то их части.
Тут, на примере, попробую пояснить.
Предположим такую обстановку: есть и работает стандалон-субд, на сервере субд.
Кроме субд на этом сервере больше нет ничего, в смысле приложений/сервисов.
Т.е.: ресурсное потребление на сервере это только субд, больше нечему.
Субд работает в нормальном режиме (в смысле определённом выше) и тут прилетает вот такое:

график 1

При этом dmesg, алерт-лог субд не показывают никакого криминала: ошибок нигде никаких нет.
Ворклоад субд, в смысле набора инфосистем, сервисов работающих с субд, набора скл-команд которые они отправляют в субд, режима выдачи этих скл-команд в субд (в смысле настроек скл-сессий, скл-клиентов, значений биндов) не менялся. Могла поменяться например интенсивность выдачи скл-команд.
Настройки субд/ОС-и, программное обеспечение субд/ОС-и, ресурсное обеспечение сервера субд, серверная инфр-ра: не менялась.

Ну и, конечно же, с графиком, поступает лидерский вопрос: а что это оно? А то вот скоро период наибольшей нагрузки: проблем не будет?.

Ну, конечно: первое что приходит в голову: посмотреть а что, какие скл-команды создают потребление цпу.
Ну. В той же em-консоли есть возможность сделать дрилл-доун в график цпу-потребления и выйти на информацию по конкретным скл-командам.
Или, как вариант, можно запросить данные в sys.dba_hist_sqlstat.
Для начала, хотя бы просто сгруппировать потребление цпу-времени, всеми скл-статементами, по каждому awr-снашоту.
Тут получается такая картина:

Да, конечно, тут по Y-оси данные: не в процентах утилизации, как на картинке из заббикса.
Тут данные в единицах процессорного времени, какие они там, в sys.dba_hist_sqlstat, постоянно забываю.
Но не суть, суть в том что вроде как нет такой же динамики, ну вот так чтобы прямо очевидно/не дискутируемо было.
При этом, если в этой же бд спросить данные в sys.dba_hist_osstat, то получится та же картинка, что и в заббиксе:

график 2

Ну. И что это значит: что и почему ест цпу-время и как это искать, например awr|statspack-отчётами, или чем то типа em-консоли.
Причём, очень желательно быстро, ну порядка получаса.
И не затратно, чтобы не особо включать голову, ну или включать но в другое время и разово.

Тут можно привлечь алгоритмы машинного анализа данных, к расследованию аномалии.
Само расследование можно провести по двум направлениям.
Первый вариант: раз априорно известно что именно и только субд потребляет ресурсы сервера субд и, судя по всему это не какой то баг, а именно последствия от обработки пользовательскими скл-командами табичных данных ну, таки копнуть: а всё таки есть/не есть такой сабсет скл-команд, у которого потребление процессорного времени больше всего похоже на то что наблюдается по sys.dba_hist_osstat
Данные по статистике работы скл-команд: есть в sys.dba_hist_sqlstat

Копнуть можно по разному, для поиска такого сабсета скл-команд.
Один из вариантов: векторизация и метрики расстояния между векторами евклидово расстояние, косинусная мера.
Т.е.: вот та кривая потребления процессорного времени в user-моде, которую видно на график 2: это пос-ть значений, полученный на данных из таблицы sys.dba_hist_sqlstat сколько, на момент какого то конкретного awr-снапшота (на конкретный snap_id) было потреблено цпу-времени цифра.
Т.е.: это набор цифр, т.е.: вектор.
Можно получить такой же набор цифр по каждому конкретному скл-статементу, описанному в sys.dba_hist_sqlstat сколько этот скл-статемент потребил цпу-времени (sys.dba_hist_sqlstat.cpu_time_delta) на момент времени какого то конкретного awr-снапшота (на конкретный snap_id)
Если, когда то, этот скл-статемент ничего не потреблял, обозначить это нулём.
Т.о.: по каждому скл-статементу: можно получить пос-ть цифр, по тому же набору awr-снапшотов, т.е.: вектор, той же длинны, что и вектор с данными по общесистемному потреблению цпу-времени.
Ну. Понятно я подразумеваю что данные всегда упорядочены по возрастанию snap_id awr-снапшотов.
Дальше: дело техники.
Вот у нас есть вектор полученный на данных от sys.dba_hist_osstat, ну, назовём его, условно, главным.
И вот есть вектора, про ту же величину (цпу-потребление) по каждому скл-статементу.
Вот для каждого вектора, соотв-го какому то скл-ю: вычисляем расстояние, от этого вектора, до главного вектора и упорядочиваем вектора, по скл-ям, по возрастанию расстояния.
Top-N векторов с минимальной метрикой (расстоянием), от главного вектора, и будут бест-сабстетом.
Причём вектора (все, и эталонный и с ним сравниваемые) можно нормировать на 1-цу.
И вычислять расстояние уже между нормированными векторами.
Тогда это будет мера подобия в смысле, ну, если так можно выразиться временного профиля потребления цпу-времени, данным скл-ем.
Без нормировки дистантная метрика будет тем меньше чем больше данный вектор (ну, в прикладном смысле данный скл) походит на общесистемное цпу-потребление не только качественно (в смысле динамики потребления цпу во времени) но и количественно.
Есть и другие алгоритмы, подходы как найти бест-сабсет.
Генетика, про которую говорил в прошлой своей заметке;
rFSA-пакет, если говорить про cran-r
Attribute-importance анализ.

Если так и сделать, т.е. найти такой бест-сабсет, в смысле степени подобия характера и кол-ва потребления цпу-времени, скл-командами сабсета к общесистемному цпу-потреблению, то, в данном-конкретном кейсе, окажется, что всё в их работе, до и после аномалии одинаково, за исключением двух характеристик.
Ну, одна из них, понятно, цпу-потребление, а вторая:

график 3

Всё остальное: кол-во выполнений/ед. времени, кол-во обработанных строк/ед. времени, кол-во парсов/ед. времени, версий, фетчей, чтений-запсей практически без изменений.

Ну. Это уже вполне себе явное указание что случилось в субд.
Но для полноты картины: пройдёмся по второму варианту разбора.
Можно применить другой анализ данных.
Динамику метрики: user-потребление цпу-времени, на уровне всей субд, можно попробовать объяснить каунтерами активности субд, как предикатами (в лит-ре ещё используют термин атрибуты).
Т.е.: выполнить attribute-importance анализ и найти те предикаты-атрибуты которые наиболее сильно влияют на значение метрики.
Данные по статистикам субд: sys.dba_hist_stat_name, sys.dba_hist_sysstat
Делаем (cran-r пакетом randomForest) такой анализ данных, получаем ответ:

Ну, вполне себе явно видно кластер из 5-ти точек в верхнем правом квадранте и что это за статистики.
Графики этих статистик:

график 4

Ну. Оба варианта указывают, с разной степенью определённости, что происходит: в системе.
Есть, в этой бд, некоторое подмножества скл-команд, которые, вдруг, стали читать мутирующие табличные данные.
А базе, по этому поводу, приходится обеспечивать им CR-чтения.
Т.е., контрольный в голову: мы тут должны увидеть, в этой субд, ровно такую же, во времени, динамику по блокчейнджам.
При этом надо будет найти бест-сабсет по сегментам данных которые в наибольшей мере определяют/объясняют эту динамику субд, по блокчейнджам.
Данные по работе субд с сегментами данных: sys.dba_hist_seg_stat
Графики:

график 5

Ну. Понятно на правом графике, тут условное обозначение, а в оригинале номера объектов бд.
Дальше уже дело техники: по номерам объектов вычислить имена объектов бд.
Уточнить, в sys.dba_hist_sqltext sql_idскл-статементов, которые с ними работают, причём потребовать чтобы это были не селекты и не плскл-статементы (условие на поле COMMAND_TYPE)
Дальше показать, с помощью данных из sys.dba_hist_sqlstat что да, эти скл-и начали: и чаще выполняться и обрабатывать больше строк.
Дальше находятся инфосистемы, откуда приходят эти скл-и, ответственные, запускаются-выполняются организационно-административные процессы, назовём их так.

Так вот, я о чём.
При наработанных скриптах: вот такой анализ делается за полчаса времени, с чаем и перекурами.
При этом: обращаю ваше внимание экспертиза в собственно субд, потребовалась на самом последнем шаге.
Когда, от информации выданной от attribute-importance анализа стало нужно проинтерпретировать связь значимых каунтеров активности субд, имея в виду их семантику и объясняемой, статистиками базы, метрики цпу-потребление базы.
Т.е. вот тут и разово может потребоваться условный миддл/сеньёр дба
А остальные действия по обработке данных вполне себе может делать джун-дба.

Ну и вот, не наблюдаю, в интернетиках, в товарных кол-вах, статей, примеров, наработок каких то на использование алгоритмов ml-обработки данных для прикладного разбора инцидентов с продуктивностью/ресурсным потреблением в работе субд.
И вот в этом второй вопрос: почему так.
Ведь вот оно реально экономит время, не требует, каких то там сред/средств особых, достаточно например питона/cran-r;
Ну. Данные ещё нужны, это да, про это чуть ниже.
Про алгоритмы ml-сейчас из каждой электророзетки вещают, в т.ч. и по русски.
И на питоне. И на cran-r;
И с уклоном в теоретическую часть и сколько угодно примеров на работу с данными с конкретными пакетами-модулями.
И: практически ничего нет, в отношении прикладного использования этого аппарата численных методов, в прикладных целях, для рассмотрения изменений состояния oracle-субд.
Вот любопытно: why so.

Финал и эпилог.

Про авр/статспак данные: как их копить и где.
В самой, исходной субд лучше не увеличивать период времени удержания до каких то больших времён.
Что такое больших времён, это вопрос дискутируемый.
Проблематика в том что объём awr|статспак данных пропорционален, кроме величины периода удержания, кол-ву разных скл-команд, поступающих в субд, кол-ву объектов субд.
Ну и если база общецелевая, или тем более ad-hoc запросы приходят, часто/много (аналитика какая то): объёмы будут бодро стремится к бесконечности.
Это тормознёт работу mmon-а и/или его слейвов, т.е. сбор новых авр-снашпотов, ротирование старых авр-снапшотов.
А так же будет создавать новые проблемы, ну кроме дисковой ёмкости под хранение awr-данных.
Например тормознётся работа какого то адвайзера, который работает с sys.WR[IHM]_* таблицами-индексами, или может обнаружится нежданчик при апгрейде если окажется что там структура таблиц/индексов awr-репозитория должна поменяться, а там данных на много десятков/сотен гигабайт.

Стандартное вендорское решение: awr-варехауз.
Ну. Имеет место быть.
В зависимости от потребностей/возможностей работы с авр/статспак данными, возможно, будет достаточно вынимать из субд авр/статспак данные, складывать их куда то, успешно выложенные данные ротировать в бд.
Куда именно, ну зависит от того что есть и что потом и как нужно делать с этими данными.
Как вариант, если часто и достаточно оперативно работать с выложенными данными не надо: файлохранилка, хдфс, ceph.
Ну и, туда же: atop-логи, если есть.

Хранить стат-данные по работе субд, сервера субд, а в идеале по всей серверной инфр-ре продового сревиса, надо, в общем случае, много для чего.
Ну. К примеру: вдруг понадобится вывести факторы, значимо влияющие на время выполнения какой либо бизнес-операции в сервисе.
Вот, взять данные по, например среднему, или медианному, времени выполнения этой бизнес-операции.
И объяснить её, статистиками среды обслуживания.
А потом вывести модель зависимости времени выполнения бизнес-операции, от найденных значимых (для неё факторов).
Получить из стат-массива данных по состоянию инфр-ры тренды, для факторов.
И уже можно будет говорить, вот у нас модель зависимости, вот у нас рост какой параметров модели, вот за пределы допусков выйдем тогда то и потому то.

Спасибо за ваше внимание, время, спокойной работы, хорошей зарплаты.