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

Пролог.

Есть пара вопросов, которые уже много лет любопытно уточнить у причастной общественности.
Но. В моём болотистом-низменном крае — и людей в теме: мало и обстановка в ит-направлении, на большинстве предприятий, не способствует. Вот, поэтому, попробую с помощью хабра удовлетворить своё любопытство. Вопрос касается подхода к обработке 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-визуализация и даже тот же бейзлайн-субд могут определить и относительного него показывать изменения. Алерты, графики заданных эвентов/каунтеров за заданный период времени — это всё да, есть. Но опять же:

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

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

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

Кульминация

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

Про контекст. Тут, вроде как, всё просто. Что 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
график 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-логи, если есть.

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

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

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

А какой подход у вас к обработке awr|statspack-данных?