Как Oracle считает SQL_ID: разбираем по символам 🔍

Коллеги, всем привет! 👋
На связи Денис.

В курсе у меня была ссылка на статью о том, как формируется SQL_ID. И она протухла - так бывает 🤷🏻‍♂️ Полез разбираться заново и по ходу пьесы наткнулся на DBMS_SQL_TRANSLATOR (прошлый пост). Обещал рассказать про его функцию SQL_ID - рассказываю.

SQL_ID без выполнения запроса

select dbms_sql_translator.sql_id('select * from dual')   sql_id,
       dbms_sql_translator.sql_hash('select * from dual') hash_value
  from dual;

SQL_ID        HASH_VALUE
------------- ----------
a5ks9fhw2v9s1  942515969

Выполняем сам запрос - в V$SQL ровно те же a5ks9fhw2v9s1 и 942515969. Запрос никуда не отправляли, а SQL_ID уже знаем.

Как он считается
Oracle нигде это не документирует, но давно раскопано (Tanel Poder, Carlos Sierra). Tanel даже выложил скрипт, а я повторил руками:

1️⃣ MD5 от текста запроса + chr(0) в конце.
2️⃣ Берём последние 8 байт и переворачиваем каждые 4 байта.
3️⃣ Получилось 64-битное число - пишем его в base32 с алфавитом 0123456789abcdfghjkmnpqrstuvwxyz (без e, i, l, o).
4️⃣ HASH_VALUE - это младшие 32 бита того же числа.

md5 = 02FC540D4440ADB2 7409CBA2 01A72D38
8 байт:      74 09 CB A2 | 01 A7 2D 38
переворот:   A2CB0974    | 382DA701
base32(A2CB0974382DA701) = a5ks9fhw2v9s1
0x382DA701 = 942515969 = HASH_VALUE

Что значит каждый символ
13 символов × 5 бит = 65 бит, а число 64-битное. Поэтому:

a5ks9fhw2v9s1
символ 1     - 4 бита: только 0-9,a,b,c,d,f,g
символы 2-6  - старшая половина
символ 7     - 3 бита старшей + 2 бита HASH_VALUE
символы 8-13 - 30 младших бит HASH_VALUE

Проверил по 700+ курсорам в V$SQL: первый символ ни разу не вышел за 0-g. А HASH_VALUE по SQL_ID восстанавливается однозначно: dbms_utility.sqlid_to_sqlhash('a5ks9fhw2v9s1') = 942515969. Обратно - нет, половина бит теряется.

Подводные камни
🔹 Текст байт в байт. SELECT * FROM dual - уже 3vjxpmhhzngu4.
🔹 Точка с запятой: select * from dual; даст 143pd7y3v0tyz. SQL*Plus её отрезает, функция - нет.
🔹 Текст берите из SQL_FULLTEXT, а не из SQL_TEXT: в SQL_TEXT переводы строк заменены пробелами, и SQL_ID не сойдётся.
🔹 Хозяйке на заметку: для пары курсоров вида BEGIN dbms_output.enable(NULL); END; SQL_ID не сходился. Оказалось, клиент прислал текст с лишним chr(0) в конце, которого в V$SQL не видно 😄
🔹 Под профилем трансляции в V$SQL свой SQL_ID у переведённого текста, а в USER_SQL_TRANSLATIONS.SQL_ID - у исходного.

Проверял на Oracle 26ai (23.26), 18c и 11g: SQL_ID одного текста везде одинаковый. Только в 11g самой DBMS_SQL_TRANSLATOR нет (она с 12c), там - ORA-00904.

Итог
SQL_ID - это просто кусок MD5 от текста в base32, ничего магического. Знаешь текст - знаешь SQL_ID, хоть на тесте, хоть на проде.

Кстати, как получить SQL_ID сразу после выполнения, я рассказывал в посте про set feedback on SQL_ID.

А вы знали, что последние символы SQL_ID - это почти HASH_VALUE? Пишите в чатик 💬

С вами был Денис. Всем хорошей пятницы 🤝

#oracle #sql #sql_id #dbms_sql_translator #оптимизация #performance #фишки #Denis_Kivilev

Канал Oracle Developer | Чатик 💬
Мини-курс Оптимизация: Быстрый старт 🚀

📱 YouTube 📱 ВКонтакте 📱 LinkedIn 📱Threads
RUTUBE