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

Хинтовать или не хинтовать? Вот в чём вопрос!

Друзья, всем привет ☀️

Если вы когда-либо занимались оптимизацией запросов — наверняка рано или поздно вставал вопрос: использовать хинты или нет?

Интересно ваше мнение. Применяете ли вы хинты в проектах? При каких условиях? А может, у вас их вообще запрещено использовать?
👉 Поделиться мнением в чатике

Кстати, это видео — с практики пятого потока курса «Оптимизация Oracle SQL».
Тема оптимизации довольно сложная и без дополнительных встреч, было бы тяжело.
Поэтому каждую неделю, ребята собираются на 1–1.5 часа и обсуждают пройденный материал, делятся мнениями, задают вопросы.
Обычно минут 20 уделяем на "пробежаться по прослушанной лекции", далее практические моменты и Q/A.

Полную версию видео с ответом на вопрос про хинты опубликуем уже в четверг! 🎓

Всем хорошей рабочей недели! 🔥

#оптимизация #hints #Denis_Kivilev
Канал Oracle Developer | Чатик 💬
Мини-курс Оптимизация: Быстрый старт 🚀

Хинтовать или не хинтовать? Пусть решает команда

Я встречался с разными подходами и мнениями.

1️⃣ Например, в некоторых командах Qiwi было принято "не хинтовать". Поддерживай статистику в актуальном состоянии - тогда Oracle сам будет рулить с использованием CBO (cost based optimizer), и будет тебе счастье.
Вплоть до минуса на код-ревью. Система — OLTP.

К слову сказать, я не сильно полагаюсь на CBO, и если ты понимаешь логику работы запроса - то почему бы его не стабилизировать?
И в нашей команде хинтование я поощрял.

2️⃣ Например, в "IT Магнит" мы хинтовали почти все запросы. Это была специфика Big Data в DWH: параллельные запросы, временные таблицы… Очень часто CBO ошибался. Там без хинтов никак не обойтись.

3️⃣ Ещё один пример — из моей же практики. Пилили коммерческое ПО для банков. Сколько тогда поели г...на из-за CBO у заказчиков.
К слову сказать, пилить ПО на заказ и поставлять его в разные организации — это задачка на порядок сложнее, чем когда система твоя, до которой ты можешь легко дотянуться.

Например, у тебя 10 заказчиков, и у всех у них есть какие-нибудь свои прикольчики.
Допустим, админы в банке решили не собирать статистику. Да, такое бывает. Ну и какой, на фиг, CBO поможет с запросами при такой постановке?

Поев немало... проблем, мы коллегиально решили хинтовать максимум запросов.

Если стоит выбор между «срочно решаем проблему на ПРОДе у заказчика» и «заранее хинтануть запрос и не иметь геморроя» — я выбираю второе 😊

Подводя итог
Универсального ответа нет. Каждый случай индивидуален по-своему. Решайте командой.

Каким принципом руководствуетесь вы? Что думаете? Обсудить в чатике 💬

Кстати, это видео — с практики пятого потока курса «Оптимизация Oracle SQL» ❤️

#Denis_Kivillev #оптимизация #hints
Канал Oracle Developer | Чатик 💬
Мини-курс Оптимизация: Быстрый старт 🚀