Задача:
Два запроса. Результат выполнения один и тот же. Таблица одна и та же и т.д. Разница только в текстах.

1. select * from employees;
2. select * FROM employees;

Одинаковые ли запросы с точки зрения базы данных?
Объясните для себя почему вы выбрали именно этот вариант.

Как всегда, объяснение в четверг 😉

#задача

Parsing (Разбор)

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

Далее происходит следующее:
1. Синтаксическая и семантическая проверки.
2. Проверка прав доступа к объектам.
3. Выделение места в Private SQL Area (приватная область в памяти сессии) под запрос.
4. Поиск в библиотечном кэше разделяемой области памяти (shared pool) такого запроса. Если запрос найден (library cache hit), то происходит его немедленное выполнение (soft parse). Если не найден (library cache miss), то происходит его обработка и помещение в shared pool (hard parse). Поиск происходит по sql_id.

sql_id - уникальное хэш-значение текста запроса (см. представление v$sql).

Синтаксический анализ не может отловить все ошибки (дублирование значения в PK и др). Эти ошибки возникнут только на этапе выполнения.

Поскольку, hard parse трудоемкая операция необходимо стараться снижать количество жестких разборов. Например, использовать связные переменные (bind variables).

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

Задача: Одинаковые ли запросы?

1. select * from employees;
2. select * FROM employees;

Решение задачи:
С точки зрения базы данных это разные запросы,т.к. Sql_id у них разный. Это легко проверить, выполнив сначала эти запросы, затем запросив инфу из системного представления v$sql. Тем не менее, планы (plan_hash_value) и нормализованное представление одинаковые (exact_matching_signature).

select t.sql_id
      ,t.plan_hash_value
      ,t.exact_matching_signature
      ,t.sql_text
      ,t.*
  from v$sql t
 where lower(t.sql_text) like 'select * from employees%'
 order by t.sql_text;

Любое изменение в запросе (пробел, перевод строки, разница в регистрах и т.д.) порождает новую версию запроса в разделяемой памяти (shared pool).

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

#решениезадачи #архитектура #оптимизация

Оптимизация - следующий этап обработки 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