Oracle Developer👨🏻‍💻

Один запрос - пять SQL_ID 🤷🏻‍♂️ Коллеги, всем привет! 👋 На связи Денис. Под постом про SQL_ID o1ga спросила: почему Oracle не нормализует текст перед хэшированием? Обсуждали полдня, разбираю. Проверяем Берём запрос:

select count(*) from hr.employees where department_id = 50;

И пишем его пятью способами: капсом, с лишним пробелом, с комментарием и с другим литералом (60 вместо 50):

вариант                      sql_id         план
select count(*) ... = 50     0t6zm0g0cb5jf  2271004725
SELECT COUNT(*) ... = 50     9vq3m92g68wf1  2271004725
лишний пробел                cr05nbnshfn1a  2271004725
select /* report */ ...      ag1rnuz6z28yj  2271004725
тот же, но = 60              70cp0jaa5knrt  2271004725

Пять SQL_ID, пять курсоров, пять hard parse. А план один и тот же. Это к версии Александра: план совпал, но курсоры всё равно разные. Почему так По документации всё просто: текст хэшируется, хэш ищется в shared pool, потом текст сравнивается символ в символ, включая пробелы, регистр и комментарии. Почему не нормализуют (моё мнение): 🔹 Хэш от байтов - копейки, а делается он на каждый parse call. Нормализация - это уже разбор текста. 🔹 Регистр в литерале 'Abc' трогать нельзя, комментарий-хинт /*+ ... */ выкидывать тоже нельзя. Простой upper + trim сломал бы запросы. 🔹 Одинаковый текст - ещё не один курсор. В той же доке один SQL_ID в схемах OE и SH, а курсоры-потомки разные: таблицы-то разные. А нормализовать Oracle умеет В V$SQL есть EXACT_MATCHING_SIGNATURE - сигнатура по нормализованному тексту: без лишних пробелов и в верхнем регистре всё, кроме литералов. Есть ещё FORCE_MATCHING_SIGNATURE - та же сигнатура, но литералы заменены на bind. Смотрим на те же пять вариантов (одинаковая буква - одинаковая сигнатура):

вариант                  exact  force
select count(*) ... = 50   A      X
SELECT COUNT(*) ... = 50   A      X
лишний пробел              A      X
select /* report */ ...    B      Y
тот же, но = 60            C      X

Регистр и пробелы exact-сигнатура прощает, комментарий и литерал - нет. Force-сигнатура прощает ещё и литерал. Короче говоря, нормализация есть, просто не для SQL_ID. Статический SQL в PL/SQL Кирилл подсказал: PL/SQL приводит статический SQL к единому виду сам. Проверил:

declare l number; begin select count(*) into l from hr.employees where department_id = 50; end;
DECLARE L NUMBER; BEGIN SELECT   COUNT(*) INTO L FROM HR.EMPLOYEES   WHERE DEPARTMENT_ID = 50; END;

В V$SQL одна строка и 2 выполнения:

9vq3m92g68wf1  SELECT COUNT(*) FROM HR.EMPLOYEES WHERE DEPARTMENT_ID = 50

Верхний регистр, по одному пробелу, INTO выкинут, комментарии вырезаны, переменные стали :B1. Литерал 50 остался литералом. И SQL_ID совпал с моим запросом капсом из SQL*Plus 😄 Где это применить 🔹 Литералы против bind. 10 000 запросов where salary = <i> на 18c: с литералами 10 044 hard parse и 6.89 с, с bind - 1 hard parse и 0.36 с. 🔹 Один запрос в трёх стилях в коде приложения - три курсора в shared pool. Единый стиль SQL в команде - не только про красоту. 🔹 Ищете в V$SQL одинаковые запросы с разными литералами - группируйте по FORCE_MATCHING_SIGNATURE. 🔹 cursor_sharing = force заменяет литералы на :"SYS_B_0", но регистр не трогает: select и SELECT остались двумя SQL_ID. Проверял на 11g, 18c и 26ai: всё совпало. Итог SQL_ID - хэш от текста байт в байт, и это by design. Нормализацию за вас сделает PL/SQL, а в приложении - bind-переменные и единый стиль. А у вас в команде есть единый стиль SQL или каждый пишет как привык? Пишите в чатик 💬 С вами был Денис. Всем хорошего дня 🤝 #oracle #sql #sql_id #plsql #оптимизация #performance #фишки #Denis_Kivilev Канал Oracle Developer | Чатик 💬 Мини-курс Оптимизация: Быстрый старт 🚀 📱 YouTube 📱 ВКонтакте 📱 LinkedIn 📱Threads RUTUBE