Продолжаем посты про оптимизацию.

Виды планов запроса
Существует два вида планов запросов:

1. Гипотетический или потенциальный (explain) - это то как Оракл предположительно будет выполнять запрос. Его можно посмотреть без выполнения самого запроса. При этом не нужен sql_id, plan_hash_value. Иногда такой тип планов называют design time plan.

2. Фактически или реальный план (execution) - это то как реально выполнялся запрос. Соответственно, чтобы получить такой план, запрос необходимо выполнить хотя бы один раз. Иногда такой тип планов называют run time plan.

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

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

Для каждого типа плана существует несколько способов их просмотра.

Я уже почти смонтировал видео, о том как посмотреть планы. Думаю, в среду оно появится на канале. Следите за постами.

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

Задача:
необходимо проверить есть ли схема "HR2" в базе данных.

Смотрите объяснения в посте в четверг 🎓

#задача

В чем разница между потенциальным планом и планом выполнения

You asked…
Привет, Том!
Привет, у меня вопрос по поводу потенциального и реального плана. Я читал, что план выполнения - это план, который Oracle намеревается использовать для запроса, и фактический план выполнения может отличаться; если это так, то как мне получить реальный план выполнения. Заранее спасибо.

...and we said
Представьте, что вы планируете автопутешествие. Вы открываете навигатор и строите маршрут. Это ваш гипотетический план.

Пришел день выезда. Вы садитесь за руль, включаете радио и слышите, что по вашему маршруту произошла крупная авария. Вы не хотите сидеть в многочасовой пробке. Вы открываете навигатор и строите маршрут заново. Затем вы едите по новому маршруту. Это и есть ваш реальный план маршрута.

Гипотетический план - это предсказание.
Фактический план - это то как реально все прошло.

Существует множество способов получить план выполнения. Ссылки в статье.

Подробности в статье Тома.

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

Задача:
Необходимо проверить есть ли схема HR2 в базе данных.

Решение:
-- 1 способ (подойдет для любых пользователей)

declare
  v_schema user_objects.object_name%type := 'HR2';
begin
  dbms_output.put_line('Схема '||dbms_assert.schema_name(v_schema)||' существует.');
exception
  when dbms_assert.invalid_schema_name then
      dbms_output.put_line('Схемы '||v_schema||' не существует');
end;
/

-- 2 способ (подойдет для привилегированных пользователей)

select count(*) from dba_users t where t.username = 'HR2';

Наверняка, есть и другие способы.

Рекомендую посмотреть модули в пакете dbms_assert. Довольно интересный пакетик, о котором мало кто знает.

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

Как посмотреть план запроса

Всем привет!
Первое видео из тех, за которые вы проголосовали 👍

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

Чего не будет - как читать планы запросов, как оптимизировать запросы. Я потихоньку формирую курс по оптимизации, в котором будет рассмотрен этот вопрос.

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

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

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

Представления

Представления - объекты, базы данных, представляющие собой либо виртуальную таблицу, либо физическую (в зависимости от типа). Они создаются с помощью запроса соединяющего одну или несколько таблиц.

Виды представлений
1. Обычные (view) - содержат в себе только определение как получать данные.
2. Материализованные (materialized view) - помимо определения содержат в себе данные.
3. Системные (system dictionary) - обычные представления, но с отображением системной информацией, предопределены в СУБД.

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

#представления

Сегодня будет немного необычная задачка в формате голосования.

Можно ли менять данные в таблицах через обычное представление?
Представление в вашей схеме, построено на ваших таблицах, т.е. права есть.

Вопрос довольно частый на собеседованиях.

Смотрите объяснения в посте в четверг 🎓

#задача

Зачем нужны представления

1. Уменьшение сложности - “за кадром” происходит выполнение сложного запроса по соединению таблиц, агрегации данных и т.д.

2. Повышение безопасности - сокрытие некоторых полей.

3. Повышение удобства - работаем только с необходимыми данными, обращаемся к одному объекту, не таскаем кучу разных таблиц.

4. Переименование столбцов таблицы - можно представить конечную выборку в более удобном виде.

5. Настройка данных для пользователей - агрегация данных и другие преобразования

6. Предварительная материализация данных - материализованные представления, содержат данные, таким образом, обращаясь к ним не тратится время на выполнения сборки данных с разных источников.

7. Сокрытие реального источника данных - иногда бывает полезно при переименовании исходной таблицы. Чтобы не ломать весь код, view создают с аналогичным названием.

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

#представления

Задача: Можно ли менять данные в таблицах через обычное представление?

Решение:
Правильный ответ: зависит от ситуации.

Данные в таблицах не получится менять через представления в следующих случаях:
1. Oracle не понимает какую строку менять в исходных таблицах. Например, есть агрегаты\группировки и т.д.
2. Если Oracle не понимает какую строку менять и нет триггера instead of.
3. Представление создано с опцией read only, которая запрещает изменение данных в исходной таблице(ах).

Если представление создано в другой схеме и нет необходимых грантов, так же менять данные не получится.

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

#решениезадачи #представления

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

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

Всякий раз, когда нужен доступ к представлению, Oracle должен выполнить запрос,по которому определено представление, и вернуть результат. Этот процесс наполнения представления называется разрешением представления (view resolution) и он повторяется при каждом обращении пользователя к представлению.

Синтаксис создания

CREATE OR REPLACE VIEW view_name AS
SELECT columns
FROM tables
[WHERE conditions];

#представление

Материализованные представления

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

Чаще всего, материализованные представления разделяют на два типа:
1. Обновляемые по commit’у (on commit)
2. Обновляемые вручную (on demand) - по умолчанию.

Синтаксис создания по ссылке.

Для обновления вручную (on demand) используется процедура: dbms_mview.refresh(‘название м.п.’);

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

#представления

Журнальные группы изменений (log group)

При обновлении, материализованное представление полностью очищается и заполняется заново. Если в заполнении участвует удаленная таблица или запрос тяжелый, то потребуется достаточно большое время. Как с этим бороться? Нельзя ли применять инкремент изменений, а не обновлять все?

Да можно! Для этого сделали Log-группы (материализованные журналы изменений), в которые накапливается инкремент изменений. Соответственно, при обновлении мат представления изменения берутся из журнала изменений. За счет чего происходит, быстрое обновление данных.

Материализованное представление должно быть с опцией: REFRESH FAST

Синтаксис:

CREATE MATERIALIZED VIEW LOG ON таблица
TABLESPACE имя_табл_пространства
WITH PRIMARY KEY
INCLUDING NEW VALUES;

#представления

Системные представления/системные словари

Системные представления/системные словари - информация практически обо всех объектах в базе данных, о свойствах, о том как себя “чувствует” база данных, информация о сессиях и др.

Они создаются только системой, заполняются только системой. Максимум на них можно выдать грант на select.

1) user_xxx - информация про объекты схемы текущего пользователя. Доступны всем.
2) all_xxx - информация про объекты схемы текущего пользователя + объекты других схем, на которые есть права у текущего пользователя. Добавляется имя схемы. Доступны всем.
3) dba_xxx - информация вообще обо всем. Доступны только пользователям с ролью DBA, либо тем кому специально выдали права.

Не всегда представления с префиксом dba есть с префиксами user и all.

#представления

Условная компиляция

УК - это механизм позволяющий компилировать код/часть PL/SQL-кода в зависимости от заданных условий (флагов компиляции, переменных пакетов).
Иными словами, задав какое-то условие (флаг) вы можете отсекать часть кода в скомпилированной программе.
Довольно распространено в языке С, да и в других языках.

В PL/SQL так же есть возможность задавать то, какой код попадет в скомпилированный объект.

Используются специальный директивы $IF $THEN $ELSE $ERROR для задания ветвлений и обрамления кода. Список директив и возможности расширяются в новых версиях СУБД.

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

Я уже подготовил видео, за которое вы проголосовали по условной компиляции. Можете подписаться на мой youtube-канал, чтобы получить уведомление о его выходе или дождаться поста в телеграм-канале.

#компиляция

Использование условной компиляции

1) В зависимости от версии Оракла использовать тот или иной функционал.
К примеру, в поздних версия СУБД появляются некоторые возможности, но ПО поставляется на разные версии. Чтобы ничего не сломать, можно использовать УК.

2) Исключение некоторого кода из prod-версии.
Например, на дев-версии вы включаете вывод информации в буфер, некие ассерты, отладочный код и т.д. Для прод-среды этот код вреден.

3) Конфигурация PL/SQL-приложения в зависимости от каких-то условий.
Вы поставляете ваше ПО в N организаций. Дистрибутив и патчи одинаковые. Но одна из организаций не оплатил некоторую опцию. Воспользовавшись УК, можно реализовать логику вкл\выкл функционала.

4) Включение/выключение функционала в зависимости от ОС, аппаратных платформ и т.д.
Этот прием в основном используется самим создателями встроенных системных PL/SQL-модулей.

Напишите в комментариях, если вы знаете другие варианты использования 💬

#компиляция

Условная компиляция [видео]

Всем привет!

Второе видео из тех, за которые вы проголосовали 📊

В этом видео, я расскажу, что такое условная компиляция PL/SQL-кода, зачем она нужна, приведу примеры. Решил сделать его максимально сжатым и информативным.
Уже после монтажа, понял, что все равно видюха получилась почти на 7 минут 🤷🏻‍♂️
Хотелось бы, делать видосы не более 5 минут. Буду исправляться 👨🏻‍💻

В планах, сформировать курс уровня Advanced по PL/SQL, в котором будут эти и другие фишки СУБД.
В процессе написания курс для новичков по PL/SQL.

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

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

#видео #компиляция

Условная компиляция в запросах

You asked…
Привет, Том!
Я пытаюсь использовать условную компиляцию в запросе для курсора в процедуре.
При компиляции не получаю ошибок. Я получаю две колонки в курсоре, но ожидаю три, т.к. передаю входной параметр, который должен выбирать какой курсор отдавать. И должно быть три колонки. Пожалуйста, подскажи, что я не так делаю.

...and we said
Подсказка в названии. Вы используете условную компиляцию. И уже на этапе компиляции отсекается код, который не соответствует условию. Если вы хотите изменить поведение скомпилированного кода, вам необходимо изменить условия и заново его скомпилировать. С учетом этого, в run-time изменить поведение процедуры не получится.
Если вы все таки хотите добиться получения разных курсоров, то вам необходимо использовать динамический SQL в процедуре.

Подробности в статье Тома.

#asktom #компиляция

Все же, зачем нужна условная компиляция?

На днях прилетает вопрос от подписчика:
“Рассуждал с коллегой, чем условная компиляция отличается от заключения кода в простые if-else? Защиты тут нет, source в итоге можно смотреть, да и скорее всего сами пакеты. Просто что в памяти получается этого кода не будет, не захламлять лишней логикой?”

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

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

Рассмотрим простейший примера, показанные на рисунке. Процедура Proc1 отсутствует. flag = false.

1) Обычное условие - будет ошибка, т.к. компилятор доберется при компиляции до строки с Proc1, увидет, что её нет, и откажется компилировать код.

2) Условная компиляция - ошибки не будет, т.к. в код для компиляции вызов этой функции просто не попадет.

Мы исключаем/добавляем код перед самой компиляцией, проверку зависимостей и т.д. Это, пожалуй, самое главное отличие.

Всем хороших выходных! 👯‍♀️

#компиляция

Всем привет!
Это будет заключительный пост в этом году. Впереди предновогодняя суета и времени, для написания постов, не будет.

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

Подведу итоги с точки зрения канала. Он был создан 27-го февраля. Изначально, я думал о нем как источнике информации для своих студентов. Но вскоре понял, что темы могут быть полезны для более широкого круга специалистов.
В целом, цифра ~1200 подписчиков говорит мне, о том, что я был прав.

Конечно, формат коротких постов, а их ~200, не может охватить все темы и все тонкости, связанные с ними. Некоторые темы я освещаю на своем youtube-канале. Вышло уже 15 видео. Тем не менее, этого все равно недостаточно.

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

Спасибо за ваши сообщения со словами благодарности и поддержки. Для меня это очень важно 🔥

От себя хочу пожелать в новом году, осуществить задуманное, развиваться в профессиональном плане, делать, пусть маленькие, но шаги к осуществлению желаемого. Пусть следующий год будь проще. Спасибо, что остаетесь на канале 🤝

С наступающим новым годом! 🎄