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

Когда говорят об оптимизации, во многих головах, всплывают только запросы и их изменение.
Но это далеко не так.

Перечислю, лишь, наиболее крупные области:
1. Настройка сервера.
2. Настройка СУБД.
3. Алгоритмическая оптимизация приложения.
4. Выбор структур хранения данных.
5. Оптимизация PL/SQL-кода.
6. Оптимизация SQL-запросов.

Если первые два пункта прерогатива - администраторов (DBA), то за остальные пункты отвечаем мы - разработчики (DBD).

Области настолько огромные и сложные, что может потребовать не один год, чтобы хоть как-то приблизиться к их пониманию. Никогда не будет момента, когда можно будет сказать “вот теперь я знаю всё про оптимизацию”.

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

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

Небольшое голосование ✅

Хотели бы пройти курс по оптимизации?
- поиск проблем (AWR, ASH, профайлейры и др.);
- основы оптимизации запросов;
- основы оптимизации PL/SQL кода;
- алгоритмическая оптимизация;
- другие темы.

теория + практика, домашние задания 🎓

Палец вверх, если "Да" 👍🏻

Задача: необходимо написать запрос, без применения PL/SQL, получающий из строки типа “str1;str2;str3” (разделитель “;”) 3 строки результата - str1, str2, str3.
Значений перечисленных через ";" может быть любое количество. Нельзя затачиваться на количество строк = 3.

Необходимый результат:
str1
str2
str3

Смотрите решение в посте в четверг 🎓

#sql #задача

Виды соединения таблиц

Представьте запрос типа:

select * 
  from T1
  join T2 on T2.t1_id = T1.id;

Задумывались ли вы, как СУБД будет их соединять?

Существуют три основных типа соединения:
1. Nested loops (цикл в цикле).
2. Merge join (соединение слиянием).
3. Hash join (hash-соединение).

Каждый тип соединения подходит для своих ситуаций. Зависит от структур данных, количества выбираемых данных и других факторов.

Чаще всего, особенно в OLTP-системах, подходит nested-loops.

СУБД выбираем автоматически какой тип соединения применить. Иногда оптимизатор ошибается. При помощи хинтов, мы можем подсказать, какой тип выбрать.

Важно не путать соединение на логическом уровне (left join, cross join, join и др.) c физическим уровнем.

В следующем посте, расскажу про соединение через nested loops.

#оптимизация #nestedloops #hashjoin #mergejoin

Задача: необходимо написать запрос, без применения PL/SQL, получающий из строки типа “str1;str2;str3” (разделитель “;”) 3 строки результата - str1, str2, str3.
Значений перечисленных через разделитель может быть любое количество. Нельзя затачиваться на количество строк = 3.

Принцип решения:
Применяем возможности иерархических запросов и регулярок.
* instr(str, ';', 1, level - 1) > 0 - находит вхождение разделителя и генерит след уровень
* regexp_substr(str, '[^;]+', 1, level) - получает вхождение на текущем уровне.

Если добавить вторым столбцом level (уровень иерархии), станет более понятней.

Запрос:
select regexp_substr(str, '[^;]+', 1, level) str
from (select 'str1;str2;str3' str from dual) t
connect by instr(str, ';', 1, level - 1) > 0;

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

#sql #решениезадачи

Алгоритм соединения вложенными циклами (Nested loops join) — разновидность алгоритма соединения.

В общем случае, алгоритм получает на вход n таблиц и условия соединения. Результатом его работы является набор строк с результатами соединения.

Упрощая до двух таблиц, алгоритм можно описать следующим образом: для каждой строки одной из таблиц (ведущей) выполняется поиск в другой таблице (ведомой) строк, соответствующих условию соединения.

Псевдокод работы:

Для каждой строки [r] из [Ведущая таблица]
 Для каждой строки [s] из [Ведомая таблица]
  Если УдовлетворяетУсловию ([r],[s],[Условие соединения])
    Вывести ([r],[s]);        

Крайне желательно, чтобы у ведомой таблицы был индекс на поля соединения. Привет индексам на foreign key.

В плане запроса выглядит как показано на скриншоте. Шаг соединения двух таблиц - NESTED LOOPS.

В следующем посте расскажу когда и где применять.

#оптимизация #nestedloops

Когда использовать Nested loops?
Как вы уже поняли из предыдущего поста данный метод проще всего представить в виду двух циклов: первый - верхний, второй - вложенный.

Соответственно, чем меньше итераций на каждом из циклов тем лучше.
Крайне желательно, чтобы выбирался незначительный объем как из первой так и из второй таблиц. На первое место ставят таблицу с низким количеством записей удовлетворяющим условиям в where, и дальше от неё пляшут.

Идеальный случай:
1. В первую таблицу заходим по PK или низкоселективному индексу.
2. Во вторую таблицу заходим по низкоселективному индексу.

По поводу индекса во второй таблице, скорее всего, это будет индекс на поля связки с master-таблицей. Это одна из причин, почему на FK делают индексы.

Речь шла про оперативные таблицы, а не справочники с малым количеством записей.

#оптимизация #nestedloops

Пример:
Есть клиент, у него есть банковские счета.
Таблица клиентов - 10М, банковских счетов - 100М.
Мы знаем PK клиента, нам нужно вернуть информацию по клиенту и всю информацию по банковским счетам.
Nested loops подходит идеально для этого случая. Для первого цикла будет выбрана одна запись (PK), для второго цикла от силы до 10 записей.

select cl.*, ba.*
  from client cl
  join bank_account ba on ba.cl_id = cl.cl_id
 where cl.cl_id = … ;

#оптимизация #nestedloops

Задача: требуется из строки, содержащей цифры, удалить все цифры.
Например строка: 'Приветствую363283 тебя3434!'

Требуемый результат: ‘Приветствую тебя!’

Смотрите решение в посте в четверг 🎓

#plsql #задача

Nested loops, коллекции и хинты

Довольно часто коллекции используются как источник данных.

Например:
На каком-то из предыдущих шагов нашей программы мы наполнили коллекцию. Значения в ней это number. ID наших клиентов. По этим клиентам мы должны получить информацию по банковским счетам. И что-то сделать с результатом.

Код:

create or replace type t_numbers is table of number(30);
/

declare
  v_ids t_numbers := t_numbers(10, 999, 7777);
begin
  for i in (select *
              from table(v_ids) t
              join bank_account ba on ba.cl_id = value(t)) loop
    -- что-то делаем
  end loop;
end;
/

Идеальный вариант для nested loops. На первой итерации всего три строки. Вторая итерация, так же отберет небольшое количество (до 10 счетов на одного клиента).

Смотрим предполагаемый план выполнения запроса. Он в заголовке.
Oracle предлагает соединять таблицы через hash_join. В run-time план может поменяться, но тем не менее почему так получилось?

#оптимизация #nestedloops #hints

Потому что Oracle заранее не знает, какое количество элементов\строк будет в коллекции.
1. Если мы знаем хотя бы порядок, можно использовать хинт cardinality.
2. Далее, мы понимаем, что эта коллекция должна идти первой в соединении - используем хинт leading.
3. И наконец, понимая, что nested loops будет лучшим решением в данной ситуации задаем хинт use_nl.

Таким образом, мы жестко забиваем гвоздями план выполнения.

Итоговый запрос:

select /*+ cardinality(t 10) use_nl(t ba) leading(t ba) */ 
       *
  from table(t_numbers(:v_ids)) t
  join bank_account ba on ba.cl_id = value(t);

План в заголовке.

Не везде и всегда это подходит, поэтому я утверждаю, что оптимизация довольно сложный процесс.
Пожалуй, на этом примере, я пока остановлю свои посты про оптимизацию SQL-запросов. В следующих про PL/SQL 😉

#оптимизация #nestedloops #hints

Задача: требуется из строки, содержащей цифры, удалить все цифры.
Например строка: 'Приветствую363283 тебя3434!'
Требуемый результат: ‘Приветствую тебя!’

Решение:
1) Используем функцию translate и её особенность (см. документацию).

select translate('Приветствую363283 тебя3434!',
                 'x0123456789',
                 'x') res
  from dual;

2) Используем функцию regexp_replace с соответствующим шаблоном.

select regexp_replace('Приветствую363283 тебя3434!',
                      '[[:digit:]]+',
                      '') res
  from dual;

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

#sql #решениезадачи

Профилирование 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 #задача