Профилирование PL/SQL-кода

Допустим ваш PL/SQL-код “тормозит”. Как узнать где? На что именно тратится больше всего времени?
На помощь приходят профилировщики.

Итак, в Oracle, есть два типа профилировщиков
1. Старый - реализован при помощи dbms_profiler.
2. Новый - иерархический, реализован при помощи dbms_hprof. Появился в 11g.

Я имел дело и со старым и с новым. Старый вносит увеличивает время выполнения PL/SQL-кода, может просто не подойти для боевой среды.
Рекомендую к использованию последний, хоть он и немного сложноват в первоначальной настройки (потребуется помощь DBA).

Использование:
1. Создается объект DIRECTORY на локальную папку на сервере + права
2. В программе вызывает dbms_hprof в начале и в конце профилирования.
3. Формируется отчет в html-формате на диске.

HTML-отчет достаточно подробный. Локализовать проблему не составит труда.

Если интересно могу запилить видос по иерархическому профилировщику 🎥

#plsql #dbms_hprof

1000 подписчиков

Всем привет! Сегодня пост не совсем про Oracle.
Незаметно для меня на прошлой недели наш канал достиг цифры 1000 подписчиков ☺️
Для меня это радостная новость. Когда я его создавал, а было это 27-го февраля, я и не подозревал, что он так вырастет.

Цель была простая: делиться знаниями.
Надеюсь, эту задачу я выполняю и вам интересно читать мои посты, решать задачки, смотреть обучающие видео 😉

Это мой личный проект, продвигаю его за свой скромный рекламный бюджет.

Некоторые из вас пишут мне, задают вопросы, предлагают коррективы. Я очень ценю это.
Хочу озвучить на весь канал.
Если вам есть что рассказать, милости прошу, я с удовольствием опубликую ваш пост с указанием авторства 🙋‍♂️

Приглашайте знакомых, подписывайтесь на youtube-канал.
Впереди много интересного о разработке в СУБД Oracle, спасибо, что остаетесь здесь 👍🏻

Сегодня будет легкая задачка в формате голосования.

Вопрос: Какой групповой функции не существует?

Смотрите объяснения в посте в четверг 🎓

#sql #задача

Задача: Какой групповой функции не существует:
1) AVG
2) MULTIPLY
3) COUNT
4) STDDEV

Решение задачи:
1) AVG - среднее значение. Очень часто применяется.
2) COUNT - количество элементов. Очень часто применяется.
3) STDDEV - возвращает стандартное отклонение списка чисел. Применяется крайне редко. На моей памяти, ни разу не видел. Экзотика. Тем не менее, такая функция есть.
4) MULTIPLY - умножение, вроде казалось бы логично, если есть SUM - сложение. Но к сожалению, такой функции нет. Можно реализовать свою групповую функцию. Как-нибудь запилю пост или видео на эту тему.

Правильный ответ: MULTIPLY.

Надеюсь вам понравилась задачка 🎓

#sql #решениезадачи #групповыефункции

Ошибка в PL/SQL на разных языках

You asked…
Необходимо чтобы наше приложение на PL/SQL возбуждало исключения на разных языках в зависимости от настроек NLS.

...and we said
Команда Тома Кайта предлагает следующее решение:
1. Создать пользовательскую процедуру raise_application_error_i18n
2. Создать таблицу с кодами ошибок, языками и соответствующими сообщениями.
3. При вызове функции raise_application_error_i18n с определенным кодом, получать текущие настройки NLS и искать в этой таблице сообщение об ошибке.
4. Далее через обычное пользовательское исключение raise_application_error возбуждать ошибку с переданным кодом и найденным в таблице сообщением.

Подробности в статье Тома.

#asktom

Поиск проблем “тормозов”. Причины.

Довольно частый вопрос на собеседованиях “Пользователи начали жаловаться, что всё стало тормозить. Какие могут быть причины? С чего начнете?”.
И действительно, с чего начать? Как подойти к этому вопросу? Как понять, где проблемы?

Причин может быть множество. Перечислю некоторые из них.
1. На самом сервере СУБД проводились работы, была изменена его конфигурация.
2. ДБА накатили патч на СУБД.
3. СУБД перевели на новую версию.
4. Были изменены параметры СУБД, её конфигурация.
5. Сбоила сеть.
6. Было обновление самого прикладного ПО, функционал изменился.
7. По какой-то причине, СУБД не обладает актуальной статистикой по данным в объектах.
8. Параллельно основным сессиям, в тот момент, работала другая сессия, нагружающая СУБД (например, запустили отчет)
9. Изменился план выполнения запроса(ов), что является следствием некоторых пунктов перечисленных выше.
10. Выросли ожидания на получение разделяемых ресурсов, что может являться следствием пунктов выше.

Конечно, на собеседовании, от вас хотят услышать про последние два пункта.
В реальной жизни, нельзя исключать другие.

В следующем посте, расскажу про то как исследовать проблемы в сессии с точки зрения планов выполнения запросов.

#теория #оптимизация

Задача: дана табличка, в ней четыре строки, столбец “n”.
Значения в столбце: 1, 2, 3, 4.
Дан запрос. Каков будет результат его выполнения (как будут заполнены ячейки).

Запрос:
select n,
sum(n) over () sum1,
sum(n) over (order by n) sum2,
sum(n) over (partition by n) sum3
from demo;

Обращаю ваше внимание, что подобного рода задачи мне пару раз встречались на собеседованиях. Естественно, никто вам не даст время на то чтобы создать таблицу, забить значениями и выполнить запрос. Это все решается прямо в реал-тайме.

Смотрите объяснение в посте в четверг 🎓

#sql #задача

DML без прерывания выполнения при возникновении ошибок (log errors)

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

Представьте вам нужно вставить 100 записей, 10 из них могут вызвать ошибку. При этом нужно, чтобы вставка остальных 90 записей прошла успешно. Для этого используется расширение команд DML => log errors.

Видео будет небольшое, но информативное.

Приятного просмотра!

🎥 Смотреть видео - 5 мин

#видео #dml #logerrors

Задача: что будет в результатах выполнения запроса. Полную постановку смотрите в посте вторника.

Решение:
Эта задача на знание аналитических функций. Тема не простая. Требует знаний и, самое главное, практики. В формате постов на канале такое не объяснить. Эта тема будет рассматриваться в моих обучающих курсах.

Итак:
1) sum1 - это итоговая сумма по всем значениям колонки n. Т.е. в каждой ячейке будет: 10 (1+2+3+4).
2) sum2 - это нарастающий итог. Значение текущей ячейки суммируется с суммой в предыдущей ячейки. Например: для n = 1, это будет 0+1, для n = 2 => 1 + 2, для n = 3 => 3 + 3. Есть особенности, если n - одинаковые.
3) sum3 - инструкция partition by разбивает нашу таблицу на условные партиции и в них считает сумму. Поскольку у нас повторяющихся n нет, то сумма будет равна n.

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

#sql #решениезадачи #аналитическиефункции

Поиск проблем в сессии

Продолжаем про проблемы с производительностью.
Допустим мы выяснили, что никто ничего не менял, никаких работ с СУБД не было. Все работает в штатном режиме. Раньше пользователь жмакал условную “кнопку” и функционал отрабатывал 1 секунду. В один прекрасный момент, время увеличилось до минуты. Пользователя это не устраивает.

Сейчас не рассматриваем средний слой, сеть и прочее, предположим, что это вообще не менялось и не вносит никаких “тормозов”. Соответственно, остается только слой БД. Кстати, приложение обвешанное метриками, сузит область поиска.

Итак, начать стоит с разбора конкретной сессии, которая кроется за функционалом этой “кнопки”. Функционал - это PL/SQL + SQL.

Обычно, идут по пути наименьшего сопротивления, и исследуют на предмет тормозов SQL, если же ничего криминального в запросах не обнаруживается, берутся за PL/SQL.

В следующих постах, я немного раскрою тему про исследование SQL-запросов.

#теория #оптимизация

Какие запросы выполняются в сессии

Есть несколько способов получить информацию по запросам в БД.

1) AWR-отчет - это срез самочувствия БД за определенный период. В нем очень много разной информации. Нас как разработчиков заинтересует раздел с TOP-сессиями в БД в различных разрезах. AWR-отчет это как средняя температура по больнице. В нем видна работа вообще всех сессий, нас же интересует конкретная. Тем не менее, при удаленной диагностики (БД не на вашей площадке), вполне подойдет, чтобы понять картину происходящего в целом.

2) Трейс-файл - специальный файл, который снимается с начала до конца работы конкретного функционала. Соответственно, в него попадут только те запросы, которые выполняла наша сессия. В нем будут реальные планы запросов, ожидания, другая информация.
Также, будет крайне полезен, при удаленной диагностики.

3) Представления v$* и dba_* - системные представления, которые показывают выполняющиеся сессии (планы, ожидания и т.п.) в данный момент + есть возможность посмотреть историю. Яркими представителями являются v$active_session_history и dba_hist_active_sess_history.
Подходит, когда доступ к БД у вас есть. Нет необходимости снимать трейс-файл, достаточно будет посмотреть в эти view.

#теория #оптимизация

Задача: обойти таблицу с использованием иерархического запроса, начиная с вершины (строка у которой нет потомков). В запросе вывести уровень иерархии, поля name, year текущего уровня и поля name, year родителя.

Структура таблицы:

create table example
(
  name   varchar2(200 char) not null,
  year   number(4) not null,
  p_name varchar2(200 char),
  p_year number(4)
  -- еще столбцы
);

alter table example add constraint example_pk primary key (name, year);
alter table example add constraint example_fk foreign key (p_name, p_year)
  references example (name, year);

Смотрите объяснения в посте в четверг 🎓

#sql #задача

You asked…
У меня есть Master-таблица с автогенерируемым PK. Я создал процедуру, в которой делаю INSERT в мастер таблицу, получаю в ней сгенерированный ID (PK).
Далее использую его для вставки в child-таблицы.
Таких таблиц у меня много.

1) Является ли это хорошей практикой, использовать процедуры для выполнения вставки?
2) Нужно ли создавать для каждой таблицы обертку в виде процедуры?

...and we said
Если кратко. То это хороший принцип построения архитектуры. В статье Тома, есть ссылка на другую статью "SmartDb\ThickDB" раскрывающую принцип модульности, интерфейсов, сокрытия реализации. Рекомендую изучить.

Подробности в статье Тома.

От себя добавлю. В наших проектах, мы стараемся делать низкоуровневые API для работы с физическими объектами. Бизнес-логика при этом реализуется на среднем слое.
Конечно, не в каждом проекте это применимо. Например, кто-то использует ORM для работы с БД.

#asktom #архитектура

Задача: постановку смотрите в посте вторника.

Решение:

select level
       ,t.name
       ,t.year
       ,prior name parent_name
       ,prior year parent_year
  from example t
 connect by prior t.name = t.p_name
    and prior t.year = t.p_year
   start with t.p_name is null and t.p_year is null;

* level - уровень иерархии.
* name, year - поля текущего уровня иерархии.
* prior - через конструкцию prior можно обращаться к полям родителя.
* соединение предка с потомком:
в соединении участвуют два поля name/year, соответственно, надо через AND перечислить оба поля.
* start with - нужен для указания с какого узла мы разворачиваем нашу иерархию. В данном случае, стартуем с узлов, у которых нет предков.

Может смутить наличие двух полей, участвующих в иерархии. Именно в таком виде, мне встречалась задача на собеседовании.

#sql #решениезадачи #иерархическиезапросы

Всем привет!
Отпуск закончился 🌴
Я с новыми силами берусь за любимое дело.
Итак, поехали 😉

План запроса - это то, как СУБД будет выполнять наш запрос.
А именно:
1. Какие структуры хранения будет использовать;
2. Как будет извлекать из них данные;
3. Как будет соединять таблицы;
4. Как будет сканировать партиции;
5. Как будет выполнять другие необходимые действия.

sql_id - это уникальный идентификатор SQL-запроса.
plan_hash_value - это уникальный идентификатор плана SQL-запроса.
У одного запроса может быть несколько планов выполнения.

Планы запросов формирует стоимостной оптимизатор. За основу берется хранящаяся статистика по объектам, участвующих в запросе. А также подсказки разработчика (hints, хинты).

Статистика - это регулярно собирающаяся информация по объектам БД. Например: количество строк, количество уникальных значений, глубина индексного дерева и прочие характеристики.

Как читать план запроса?
Обсудим в одном из следующих постов.

#теория #оптимизация