Коллеги, всем привет! 👋
На связи Денис.
Под постом про 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