Использование dbms_application_info

Рассмотрим приложение относящееся к некой платежной системе.
1. Приложение называется - wallet-app.
2. Модуль, в котором была создана сессия, относится к проведению платежей - payment-processing.
3. Конкретное выполняющееся действие “приватный платеж p2p” - processing private payment.

Как установить эти значения?

Название приложения:

begin
   dbms_application_info.set_client_info('wallet-app');
end;

Можно задать сразу после создания сессии.

Название модуля и действия:

begin
   dbms_application_info.set_module('payment-processing', 'processing private payment');
end;

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

Где посмотреть выставленные значения ?

В системном представлении:

select t.client_info, t.module, t.action, t.*
  from v$session t;

или непосредственно в сессии:

dbms_application_info.read_module
dbms_application_info.read_client_info

#пакеты #dbms_application_info

Прогресс для длительных операций

Знакома ли вам такая ситуация? Запускается какой-то тяжелый процесс (джоб, скрипт) и вы понятия не имеете на каком этапе находится выполнение, сколько осталось до конца, сколько прошло? А очень хотелось бы 🔮

Это как скачивание файла. В браузере можно посмотреть прогресс.
Так бы взять и перенести это в Oracle 🧙🏻‍♀️

Однако, в Oracle уже есть встроенный механизм, которые это делает для тяжелых SQL-запросов.
Мы его можем переиспользовать под свои нужны - для отображения прогресса выполнения в PL/SQL-программах.

Используется для этого наш старый знакомый пакет dbms_application_info и процедура - set_session_longops.

Первый вызов инициализирует условный “прогресс бар”, последующие вызовы его инкрементируют.

Сам “прогресс бар” доступен в системном представлении - v$session_longops.

В четверг будет видео на эту тему со всеми подробностями и примерами. Не пропустите 🎥

#пакеты #dbms_application_info

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

Запилил видос про отображение прогресса для длительных операций в PL/SQL.
Немного теории, примеры, настройка IDE для отображения.

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

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

#видео #пакеты #dbms_application_info

Учебные схемы

На днях, смотрел репозиторий Oracle на github. Наткнулся на репу содержащую копию образцов схем Oracle Database, которые устанавливаются вместе с Oracle Database Enterprise Edition. Эти схемы используются в документации Oracle для демонстрации концепций языка SQL и других функций базы данных.

Схемы в репозитории:
- HR: Human Resources
- OE: Order Entry
- PM: Product Media
- IX: Information Exchange
- SH: Sales History
- BI: Business Intelligence

Не все DBD знают как вытаскивать данные с github-репозиториев.
Кратко, два способа:
1. Скачать архив с содержимым репы (на скрине).
2. Воспользоваться git-клиентом. Если он у вас уже стоит, значит вы уже знаете, что делать с репозиториями :)

Напомню, что в сервисе Oracle Live SQL можно легко накатить любую из схем на вашу временную схему. Видос по теме.

#hr #ссылки

Задача: Как получить записи с четными id?
Для примера, возьмем таблицу employees схемы HR.

select * from hr.employees t 
 where … ?

Смотрите готовый SQL запрос в решении в посте в четверг 🎓

#sql #задача

Скрипты для DBA и не только

Всем привет!

Опять же, на прошлой недели копался в репозиториях на github. Случайно наткнулся на репу Tim Hall. Автора сайта https://oracle-base.com/, он же DBA, DBD, тренер и т.д. Известная личность в Oracle-сообществе. На его сайте, достаточно, много интересных статей.

Так вот, в репозитории у него сборка полезных скриптов как для DBA, так и для DBD. Рассортированы по версиям Oracle, с понятными названиями.
Вполне могут пригодиться в работе, особенно для Middle/Senior DBD. Лично для себя нашел кое-что интересное.

Добавляйте в закладки, вдруг пригодится 😉

#ссылки

Задача: Как получить записи с четными id?
Для примера, возьмем таблицу employees схемы HR.

Принцип решения:
Классическая задача. Нужно взять остаток от деления на два, если оно равно нулю, то это четное число.

Запрос:

select * 
  from employees t 
 where mod(t.employee_id, 2) = 0;

В SQL-запросах такая задача встречается довольно редко, зато в PL/SQL очень часто. Формулировка только немного другая. Каждые N записей (например, 500) выполнять commit.

if mod(v_counter, 500) = 0 then
  commit;
end if;

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

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

Обработка SQL-запроса

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

Обработка состоит из нескольких стадий (см рисунок):
- парсинг или разбор (parsing)
- оптимизация (optimization)
- генерация плана (row source generation)
- выполнение (execution).

В зависимости от запроса, база данных может опустить некоторые из стадий.
Если запрос не найден в общей области памяти (shared pool), т.е. ни разу не разбирался, то будет выполнен ресурсоемкий жесткий разбор (hard parse), иначе мягкий разбор (soft parse).

Стадии парсинга (parse):
- проверка синтаксиса (syntax check)
- семантическая проверка (semantic check)
- проверка на наличие такого же запроса в разделяемой области памяти (shared pool check).
- и некоторые другие проверки.

В следующих постах будут подробности о стадиях, в т.ч. парсинге.

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

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

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 для этого примера ниже ⬇️

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