Оптимизация - следующий этап обработки SQL-запроса.

Назначение: ускорить выполнение SQL-запроса.

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

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

Этап оптимизации выполняется хотя бы 1 раз (hard parse) для нового уникального sql-запроса. БД никогда не оптимизирует DDL-запросы, если они не включают компоненты DML, например подзапрос, требующий оптимизации.

Более подробно процесс оптимизации опишу в следующих постах.

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

Что же происходит на этапе оптимизации?

Оптимизатор должен всегда возвращать правильный результат как можно быстрее.

Суть проста: выбрать план, который является самым эффективным с учетом статистики и возможных путей доступа к данным.
Для этого выполняются шаги:
1. Генерация набора потенциальных планов для оператора SQL на основе доступных путей доступа.
2. Оценка стоимости каждого плана на основе статистики в словаре данных для характеристик распределения и хранения данных в таблицах, а также индексов, к которым обращается оператор.
3. Сравнение затрат на планы и выбор плана с наименьшими затратами.

Разберем пример на рисунке.
Запрос выбирает сотрудников, которые являются менеджерами. Статистика базы данных показывает, что немногие сотрудники являются менеджерами, поэтому чтение индекса, за которым следует доступ к таблице по rowid, может быть, более эффективным, чем полное сканирование таблицы.

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

В продолжение выбора эффективного плана.

Рассмотрим еще один пример. Предположим, что сотрудников в роли "менеджер" очень много, например, более 80%. Это значит, что оптимизатор выберет другой план выполнения. В котором, будет уже не поиск по индексу, а полное сканирование таблицы.

На всякий случай, напомню, что считается эффективным использовать индекс, если при этом выбирается не более 5-10% строк (разные литературные источники дают разные проценты).

Именно, поэтому при внезапном изменении статистики, может измениться план в приложении, которое вы не меняли.

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

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

27го февраля каналу стукнул годик 🎂
Не буду вспоминать о статистике и прочих вещах.
Хочется сказать вам огромное спасибо за то, что остаетесь здесь, за лайки под видосами, за обратную связь в личку,
Видя это, я понимаю, что все делается не зря. 😉

Впереди много интересного о разработке в СУБД Oracle, оставайтесь с нами, не переключайте канал 📺😀

#др

Что происходит в оптимизаторе

Вспоминаем. Функция оптимизатора - выбрать оптимальный план запроса.

Из парсера, на вход оптимизатору подаются подготовленные распарсенные блоки информации о запросе.

С этими данными происходит три этапа работы:

1. Трансформация - в зависимости от версии СУБД, запрос трансформируется/переписывается в эквивалентный с применением различных правил трансформации. На выходе получается трансформированный запрос.
Трансформации может и не быть, если исходный запрос трансформировать не получится.
Около 10 правил трансформации описаны в документации.

2. Оценка - на основе статистики об объектах из системных словарей, оценивается запрос, его трудоемкость/стоимость.
⚠️ вот почему так важна актуальная статистка по объектам.

3. Генерация плана - генерирует и возвращает план на этап оценки для сравнения с другими планами, либо отправляет на следующий этап (row-source generator), если он оптимален.

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

Задача:
Напишите запрос для вычисления количества полных лет некоего человека на текущую дату.

Дата рождения: 21.01.1984.

Как всегда, разбор решения в четверг 🎓

#задача

Задача:
Напишите запрос для вычисления количества полных лет некоего человека на текущую дату.
Дата рождения: 21.01.1984.

Решение задачи:

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

select trunc(months_between(sysdate, date'1984-01-21') / 12) full_year
  from dual;

Пояснения:
1. date '1984-01-21' - легкий способ задать дату. В данном случае, дату рождения.
2. months_between - функция возвращает количество месяцев между двумя датами.
3. / 12 - делим на количество месяцев в году, получаем года с даты рождения.
4. trunc(...) - отсекаем от цифры дробное, т.к. нам нужно количество полных лет.

Ответ: 37 лет.

#решениезадачи #арифметикадат

Трансформация запроса

Продолжаем про этапы работы с запросом.

На входе в оптимизатор происходит трансформация.
В зависимости от версии СУБД, запрос трансформируется (переписывается) в семантически эквивалентный запрос. Трансформация происходит, если запрос будет выполняться более эффективней. Для это применяются правила трансформации.

Приведу одно правило, с которым вы наверняка сталкивались, если смотрели планы запросов.

OR expansion (раскрытие условия OR)
Пример показан на скриншоте.
Первоначальный запрос разворачивается на два запроса объединенных быстрой операцией union all.

Условие применения: по каждому условию может быть использован индекс и это будет дешевле, чем изначальный запрос.

В данном случае на каждое поле (job, deptno) есть свой индекс. При этом стоимость прохода по двум индексам и операция объединения оказались более эффективны, чем full scan таблицы (полное сканирование).

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

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

Transformer: Predicate Pushing (проталкивание предиката)

Еще один пример трансформации. Смотрите скриншот.
Представление основанное на объединении выборок из двух таблиц. Из этого представления выбираются строки для подразделения с id = 20.

Как выполнялся бы исходный запрос без трансформации?
а. Получаем все строки из emp1.
б. Получаем все строки из emp2.
в. Применяем дорогую операцию объединения UNION.
г. Фильтруем результат по deptno = 20.

После трансформации:
а. Фильтруем по индексу строки из emp1 подставив предикат с deptno = 20.
б. Фильтруем по индексу строки из emp2 подставив предикат с deptno = 20.
в. Применяем дорогую операцию объединения UNION к значительно меньшему объему данных.

Профит 👍
Смотрите explain plan для этого примера ниже ⬇️

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

Explain plan для этого примера

SQL-инъекции

Отвлечемся от темы оптимизации.

Что такое SQL-injection?
Один из распространённых способов взлома сайтов и программ, работающих с базами данных, основанный на внедрении в запрос произвольного SQL-кода (спасибо Wikipedia).

А как внедрить то?
1. Предположим на сайте есть поля ввода, данные с этих полей передаются на сервер.
2. На сервере, они никак не обрабатываются, не проверяются, а вставляются в запрос как есть (такое бывает особенно в CMS’ках).
3. Запрос выполняет СУБД.
4. Результаты отправляются на сайт.

Запрос был вида:
select * from bank_account ba where account_id = id
“id” - пришло с сайта, это значение, типа 1234567.
Хакер или тулза по поиску уязвимостей: отправляет вместо “id”, например “id or 1=1”

Какой итоговый запрос получается?

select * from bank_account ba where account_id = id or 1=1

или cо значением:

select * from bank_account ba where account_id = 1234567 or 1=1

и что он вернет? правильно все банковские аккаунты.

Пример невероятно упрощенный. Главное чтобы поняли суть. Помимо “1 = 1”, отправить можно что угодно, хоть drop table.

В Oracle, такие кейсы можно прошляпить при использовании динамического SQL. Когда запрос конструируется на лету, и не используются переменные связывания (bind variables).
Подробно динамический SQL я разбираю в курсе “Oracle PL/SQL. Основы”, который уже почти готов 🎓

Так же возможен кейс, когда средний слой, неправильно работает с входными данными (пример с полями) и конструирует запрос "на лету", отправляя его в Oracle. Надеюсь, такой кейс нельзя встретить в промышленных системах 😊

Кажется, что ошибка глупая, ну как можно не проверить входные данные? И тем не менее, время от времени SQL-инъекции происходят.

Почему я упомянул CMS? В большинстве случаев, системы управления сайтом имеют открытый код. Никто вам не мешает, скачать исходники и посмотреть, правильно ли авторы CMS работают с БД.

Так же никто не отменял различные сканеры уязвимостей, которые долбят сайты/системы на предмет внедрения через поля форм. Подставляют в разные поля разные кусочки запросов типа 1 = 1, смотрят реакцию.

Тема достаточно интересная. Легкое гугление даст тонну статей по SQL-инъекциям, с разными примерами.

#security

Задача: Необходимо разрешить вставку значений в поле “vname”, состоящих только из латинских букв в верхнем регистре.
Остальные попытки с неподходящими значениями - отбивать с ошибкой.
PL/SQL, триггеры - использовать нельзя.

1. Таблица:

create table tab1(
  vname varchar2(100 char) not null
);

2. Языковой параметр сессии устанавливается в Russian.

alter session set nls_language = russian;

3. Команды для тестов:

insert into tab1 values ('ABC');-- ok
insert into tab1 values ('aBC');-- ошибка
insert into tab1 values ('abc');-- ошибка
insert into tab1 values ('АБВ');-- ошибка

В этот раз, было бы интересно получить решения от вас, друзья 😉
Если захотите поучаствовать, то крайний срок, до 19:00 четверга.

Самые интересные решения опубликую в пятницу с сохранением авторства 🏆

#задача

Как читать план запроса Oracle (последовательность выполнения шагов).

Друзья, всем привет!
По многочисленным просьбам, я таки запилил видос про то, как читать планы запросов🔮

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

А еще, я экспериментирую с форматами видео, звуком и визуальной подачей материала. Если зайдет - не забудьте поставить палец вверх под видосом 👍

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

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

#видео #оптимизация

Задача: Необходимо разрешить вставку значений в поле “vname”, состоящих только из латинских букв в верхнем регистре.

Решение:
Задачка не так проста, как кажется на первый взгляд.

1. Необходимо ограничение (constraint), на столбец "vname", которое бы контролировало допустимость значений.
2. Что-то в этом ограничении нужно проверять.
На ум приходит регулярное выражение типа ^[A-Z]+$.
Бьюсь об заклад, что большинство мысленно, именно так решило эту задачу 🍅
Однако, Oracle не дремлет, и как всегда, подкинул баг/фичу 🐞

Регулярка не работает, при смене национальной языковой поддержки на nls_language = russian.
Смена nls_language, приводит к смене nls_sort.
Когда nls_sort не равен binary (по умолчанию), начинаются спецэффекты. Регулярка перестает работать.
Причем: в версиях 11g/12c все ок. В версия выше - начинаются проблемы.
С учетом этого, вариант с регуляркой ^[A-Z]+$ не подходит.
Привет тем, кто переходит на версии выше.

Ниже ограничения, которые успешно отрабатывают при любом nls_sort, с ссылкой на автора:

@v1talys

alter table tab1
  add constraint check_vname
  check (not regexp_substr(vname, '[ABCDEFGHIJKLMNOPQRSTUVWXYZ]{'||length(vname)||'}') is null);

или

alter table tab1
  add constraint check_vname2
  check(trim(translate(vname, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', ' ')) is null);

——-

@alexeionin

alter table tab1 add constraint tab1$vname$chk check (trim(translate(vname,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','                          ')) is null)

или

alter table tab1 add constraint tab1$vname$chk check (regexp_count(vname, '[^[:upper:]]|[^A-Z]')=0);

——-

Игорь Дроздов

alter table tab1 add constraint lat_upper check(regexp_like(vname, '^[ABCDEFGHIJKLMNOPQRSTUVWXYZ]+$'));

—--
@SergeyElagin

alter table tab1 add constraint lat_upper check(regexp_like(vname collate binary_ci,'^[A-Z]+$','c'));

—--
Я не буду комментировать решения коллег, лишь скажу спасибо за старания 🤝

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

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

Задача:
Опять задачка на регулярки, давно крутилась у меня в голове.
Итак, дан запрос:

select count(*)
  from dual
 where regexp_like('A_^bc\D','[A-z]+');

nls_sort = binary (подвоха в этом нет),

Возможные варианты: 0️⃣ или 1️⃣.
Попробуйте, не выполняя в СУБД этот запрос, выбрать правильный вариант 🔮

Как всегда, смотрите решение в четверг 🎓

#задача

Задача: какой будет результат выполнения запроса:

select count(*)
  from dual
 where regexp_like('A_^bc\D','[A-z]+');

nls_sort равен binary ❗️

Решение: будет 1️⃣

Возможно кто-то шокирован этим ответом. Ведь в строчке присутствуют спец символы типа “_^\”. А в паттерне мы не указывали спец символы. Как так то?

Указание диапазона “A-z” содержит в себе подводные камни.
Может помнит кто-то из курса университета про ASCII табличку? Assembler? )
В общем, между символом “Z” и “a” есть еще шесть спец символов, которые как раз таки попадают в диапазон A-z (см. скрин).

Соответственно, регулярка их пропускает.

Эта особенность проявляется не только в Oracle, но и в других языках, например в Java.
Будьте внимательны 👀

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

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

Вакансия: Программист PL/SQL

Полное описание в вакансии.

Краткое:
- задачи OLTP-разработки (клики с сайтов/порталов), интеграционные решения точка-точка и средствами Informatica, работы с front-end на Oracle Application Express (APEX) и .NET, проектирование структур данных хранения, работа c legacy-решениями.
- ожидаемый уровень скилов Middle/Senior PL/SQL Developer .
- уровень з/п обсуждается индивидуально по итогам интервью.
- резюме (или краткое описание опыта разработки в свободной форме) просьба направлять на почту OFRuzaykina@rolf.ru Ольге Рузайкиной или в телегу https://t.me/Kapustin_Anton Капустину Антону.

#вакансия

Выбираем видео 🎥

Друзья, всем привет!
Пора выбирать тему для следующего видоса.

Для тех, кто вступил недавно. На нашем канале можно выбрать тему для обучающего видео, которое я сделаю.
А еще, вы можете предложить какую-то другую тему. Причем, тема может быть для любого уровня (Junior и выше).

Темы:
1. Отладка PL/SQL-кода.
2. Распараллеливаем выполнение задач через dbms_parallel_execute.
3. Практика. Логирование прикладных сообщений в БД (секционирование, типы сообщений и т.п.)
4. Оптимизация. Виды индексного доступа (unique scan, range scan и т.д,)
5. Полнотекстовый поиск средствами СУБД (почти как поиск в Яндексе =)
6. Что-нибудь другое - предлагайте в комментах или пишите в личку @denis_dbd. Не стесняйтесь 😉

Кстати, впервые решил сделать комментарии к посту. Посмотрим, как зайдёт 🤷🏻‍♂️

#видео