Задача по оптимизации
Полную постановку смотрите в посте вторника.

Объяснение
Типичная проблема при неправильном использовании механизмов СУБД. DBA очень часто негодуют по этому поводу.

Итак, поехали разбирать.

1️⃣ Представление v$sqlarea показывает все выполнявшиеся запросы в БД (parent-курсоры).
2️⃣ Каждый запрос имеет свой sql_id (уникальный номер), hash_value (хэш от текста запроса) и др.
3️⃣ Как только СУБД начинает выполнять запрос, происходит его парсинг. В том числе, берется hash от текста запроса и сравнивается с тем, что уже есть в SGA в кэше запросов. Если запрос не найден, то производится жесткий разбор (hard parse). В итоге, разобранный запрос помещается в кэш запросов.

Поменяете регистр одной буквы в запросе - это будет новый запрос и новый hard parse со всеми сопутствующими расходами.

Следующие запросы, с точки зрения БД, разные.

select * from tab1 where id = 1;
select * from tab1 where id = 2;

Для каждого будет сгенерирован свой sql_id, свой план(возможно), будут помещены в кэш-запросов и т.п. множество накладных расходов.

4️⃣ Для решения подобной ситуации используются переменные связывания (bind vars). Вместо жестко заданного литерала (1, 2…) используется подстановка значения:

select * from tab1 where id = :var;

теперь в var можно подставлять значения 1, 2…и т.д.
Не будет hard parse при подстановке значений. Не забивается кэш-запросов, DBA довольны.

Том Кайт в своей книге “Oracle для профессионалов” буквально на первых же страницах пишет про связные переменные.

Это было теоретическое введение.

Перейдем к нашему примеру.
Некое приложение соединяющееся c БД под пользователем ONE_C выполняло однотипный запрос аж 11166 раз (см. скриншоты ⬆️).
Текст запросов отличается ровно в одной детали - использование разных ID в предикате (where t1…. = число). Да, из задания это не совсем очевидно.
Поскольку в текстовом виде это абсолютно разные запросы, бедная СУБД выполнила аж 11166 раза hard pars забив кэш-запросов.

Решение простое. Использовать переменную связывания в предикате, в которую подставлять конкретное значение.

select ... where t1… = :id

будет выполнен 1 раз hard parse и всё.

⚠️ Не надо нагружать СУБД бесполезной никому не нужной нагрузкой

К сожалению, в рассматриваемой БД таких запросов не мало. Нужно явно править приложение 🛠
Этим грешат начинающие DBD, разработчики, пишущие на других языках, выполняющие запросы к СУБД Oracle.

Уровень задачи легкий, т.к. это должен знать каждый кто использует СУБД Oracle. Это основы.

На курсе по оптимизации будем разбирать множество интересных кейсов 😉

Обсудить в чатике

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

——
Всем хорошей пятницы и выходных 🎊

#юмор

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

Итак. Текст запроса

select t.first_name
  from hr.employees t
  join hr.departments d on d.department_id = t.department_id
 where t.first_name like 'Alex%';

План запроса на скриншоте ⬆️

Вопрос. Таблица departments указана в запросе, используется для JOIN с таблицей employees. Однако, в плане запроса она отсутствует. Почему так произошло?

Разбор, как всегда, в четверг 🎓

Обсудить в чатике

#задача
Oracle Developer

Задача о трансформации запроса
Постановку смотрите в посте вторника.

Немного теоретической части
Любой запрос при hard parse проходит через стадию “оптимизации” для построения плана запроса. Одним из этапов оптимизации является трансформация запроса.

Например, запрос

select * from tab1 where col1 = … or col2 = … 

при определенных условиях будет преобразован в запрос вида:

select * from tab1 where col1 = …
union all
select * from tab1 where col2 = … and col1 not in ...

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

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

1️⃣ В плане запроса в блоке Outline Data есть намек, на то, что таблица была исключена из JOIN - ELIMINATE_JOIN(@"SEL$1" "D"@"SEL$1")

2️⃣ Если хочется копнуть глубже. Можно выполнить трассировку оптимизатора и посмотреть через какие этапы прошел запрос при построении плана. В результате будет файл с данными на сервере СУБД.

Сформировать такой файл можно разными способами. Приведу только один (запрос должен выполняться впервые):

alter session set events '10053 trace name context forever';
alter session set tracefile_identifier='PLAN_TRC_EXAMPLE1';

select t.first_name
  from hr.employees t
  join hr.departments d on d.department_id = t.department_id
 where t.first_name like 'Alex%';

alter session set events '10053 trace name context off';

В результате выполнения в каталоге на сервере будет создан trc-файл с меткой PLAN_TRC_EXAMPLE1.
Заглянув в секцию с преобразованиями, можно заметить интересную картину (см. скриншот выше ⬆️).

⚠️ для запросов, которые уже выполнялись используйте - dbms_sqldiag.dump_trace.

На курсе по оптимизации будем разбирать подобные вопросы 🎓

Обсудить в чатике

#решениезадачи #оптимизация
Oracle Developer

Шутка в тему оптимизации 😉

Всем хорошей пятницы и выходных 🎊

#юмор
Oracle Developer

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

Рад сообщить, о старте пятого потока курса “Oracle PL/SQL.Основы”.

Он будет полезен: QA-инженерам, разработчикам, аналитикам.

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

🔹22 видео с теорией;
🔹18 практик = единый проект;
🔹13 онлайн встреч в Zoom с разбором вопросов;
🔹закрытый телеграм-канал;

Практика построена таким образом, чтобы вы с нуля, шаг за шагом, создали API на PL/SQL для прототипа платежной системы. Начав с анонимных блоков вы закончите своим мини-фреймворком для Unit-тестирования. Пример.

Подробности и программа - здесь. Отзывы - здесь.

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

⚠️ Группа 10 человек, 5 мест уже забронировано.

#plsql_basic

Задача

Дан триггер:

create or replace trigger my_tab_seq_tg
   before insert on my_tab
   for each row
begin
   if inserting then
      if :new.seq_id is null then
         select some_seq.nextval into :new.seq_id from dual;
      end if;
    end if;
end;
/

Вопросы: все ли в порядке? можно ли его как то улучшить/изменить?

Уровень сложности: легкий

#задача

Ревью триггера
Постановка в посте вторника.

Исходный код триггера:

create or replace trigger my_tab_seq_tg
   before insert on my_tab
   for each row
begin
   if inserting then
      if :new.seq_id is null then
         select some_seq.nextval into :new.seq_id from dual;
      end if;
    end if;
end;

Начнем-с.
Триггер - это PL/SQL-код, который вызывается при наступлении определенных событий, указанных в его определении.
В нашем случае: триггер на операцию insert на таблицу my_tab, срабатывает для каждой новой строки (for each row) до вставки (before).
В теле триггера происходит проверка с использованием функции inserting происходит ли вставка. Если поле новой строки :new.seq_id не заполнено до начала операции, то значение берется из последовательности some_seq.

Опираясь на название и код, триггер предназначен только для генерации ID для некоего поля, если оно не заполнено.
С этим понятно.

Возникают некоторые моменты.
1️⃣ Зачем проверять, что происходит вставка (INSERTING), если триггер срабатывает только на INSERT? Ответ - абсолютно бессмысленно.

2️⃣ Зачем использовать SQL-запрос “select some_seq.nextval into :new.seq_id from dual;” для получения следующего значения, что приведет к переключению контекста между SQL и PL/SQL-машинами?
Нет никакого смысла. Это вносит задержку, пусть и минимальную, в операцию insert. Такая конструкция была справедлива для старых версий Oracle, но с 11й вполне можно использовать:
:new.seq_id := some_seq.nextval

3️⃣ Проверку “ :new.seq_id is null ”, можно сократить до :new.seq_id := coalesce(:new.seq_id, some_seq.nextval).
А можно воспользоваться конструкцией when в условиях срабатывания: when (new.seq_id is null).

Итоговый код триггера:

create or replace trigger my_tab_seq_tg
  before insert on my_tab
  for each row
  when (new.seq_id is null)
begin
  :new.seq_id := some_seq.nextval;
end;
/

Есть еще интересная холиварная тема: а зачем нам такой триггер в принципе? Коллеги в чатике отчасти это уже обсудили.

Это, всего лишь, один из примеров, которые мы разбираем на курсе по Oracle PL/SQL. Если вы пишете или читаете код на PL/SQL и не раскусили суть задачи, вполне вероятно, что стоит записать на курс и добить пробелы.
⚠️ Осталось 5 мест. Если сейчас нет возможно оплатить, можно забронировать место - пишите в личку.

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

Задача

Вопрос из теста в курсе PL/SQL.Основы. Рассчитан на изучающих PL/SQL.

На скриншоте представлен код пакета. Вызывается процедура пакета:

begin
  my_pack.say_hello();
end;
/

Что будет выведено в буфер вывода? Варианты ниже ⬇️

Разбор, как всегда, в четверг 🎓

#задача

Задача на переменные пакета и его состояние

Постановка в посте вторника

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

В нашем случае создается пакет my_pack. В теле пакета объявлена внутренняя глобальная переменной g_hello и задан блок инициализации.

Глобальная внутренняя переменная видна во всем теле пакета в любой его части.

Блок инициализации выполняется, при первом обращении к пакету для инициализации его состояния. Так же может выполниться повторно, если состояние пакета было сброшено (например, перекомпиляция).

Порядок инициализации в пакете: значения в глобальных объектах, затем блок инициализации.

На момент выполнения процедуры say_hello значение в переменной будет ‘Hello’.

Правильный ответ: А

Это самые основы. Эти и другие темы будем разбирать на курсе PL/SQL.Основы старт 9.09 🎓

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

Всем хорошей пятницы и выходных 🎊

#юмор
Oracle Developer

Задача

Вам на ревью поступил следующий PL/SQL-код:

create or replace function get_text(p_id t1.id%type)
return varchar2
is
  type my_type_cur is ref cursor;
  v_curs my_type_cur;
  v_text t1.text%type;
begin
  open v_curs for
    select text from t1 where id = p_id;

  fetch v_curs into v_text;

  close v_curs;

  return v_text;
end;
/

Что делает данный код? Корректный ли он?
Как вы считаете, можно ли его переписать?
Действительно ли нужно использовать курсор?

Подобные вещи мы разбираем на 3х месячном курсе PL/SQL.Основы. Старт 9 сентября.

Обсуждение, как всегда, в четверг 🎓

#задача
Oracle Developer

код к анализу ниже ⬇️

Ревью функции с курсором

Постановка: смотрите пост вторника.

Анализ
Кратко: я бы такой код не пропустил на ревью.
см код выше
1️⃣ Стр 2. Возвращается тип без привязки к колонки таблицы. Нет причин не использовать жесткую привязку к колонке text.
2️⃣ Стр 4. Избыточное объявление курсора. Можно использовать системный тип sys_refcursor.
3️⃣ Стр 13 и 16. Закрывать курсор надо 100%. В штатном режиме он закроется. А вот в случае возникновения исключения - нет закрытия. Ошибка неопытного курсоро-пользователя. Добавляется блок exception с проверкой на открытость курсора и его закрытием if v_curs%is_open then close v_curs; end if;
4️⃣ Но это частности. Основной вопрос к коду: зачем использовать явный курсор? Ответ: не имеет никакого смысла. Код можно переписать, например, так:

function get_text_new(p_id t1.id%type)
return t1.text%type
is
  v_text t1.text%type;
begin
  select max(t1.text)
    into v_text
    from t1 where t1.id = p_id;
   return v_text;
end;
/

▫️В данном варианте, не важно id уникально или нет (как в первом так и во втором будет рендомно отдавать какую-то строку).
Есть ли значение по переданному id или нет. Поведение осталось прежним.

▫️Должна ли вызывающая среда знать, о том что строка не найдена? Не имея, каких-либо доп вводных ответить на этот вопрос сложно.

▫️Можно ли использовать result_cache? Определенно сказать сложно. Если таблица t1 постоянно меняется (это не справочник), то нет. Если меняется редко - можно попробовать. Опять же при условии, что тип колонки text проходит по ограничениям result cache.

▫️Если функция используется в SQL, то стоит подумать насчет pragma udf, deterministic (осторожно).

Мало чего ясно, но очень хочется понимать?
Есть возможность с 9-го сентября ворваться в магию PL/SQL 🎩
Напишите мне, если хотите забронировать место на новый поток. Оплатить можно непосредственно перед стартом.

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

#решениезадачи #cursor
@oracle_dbd