Una aclaración corta pero necesaria del SQL_ID.
Oracle cuando realiza parsing de una consulta, genera con un algoritmo un Hashing MD5 de 128 bits de largo para cada consulta. Luego toma los últimos 64 bits de dicho hashing y crea lo que conocemos hoy en día como SQL_ID, el cual tiene 13 caracteres de largo. Este se compone de una mezcla de números y letras (base32), es decir 32 caracteres en total que serán usados para dar forma al hashing de SQL_ID final (de 13 caracteres).
Por ejemplo si ejecutamos una simple consulta como: select * from dual ; veremos que en el interior de la shared_pool se creará un nuevo SQL_ID, seguido de un hash_value. Ejemplo:
SQL> select * from dual;
D
-
X
SQL>
SQL> select sql_id, hash_value from v$sql
where sql_text = 'select * from dual'; 2
SQL_ID HASH_VALUE
------------- ----------
a5ks9fhw2v9s1 942515969
Lo anterior entrega el SQL_ID: a5ks9fhw2v9s1 y a partir de dicho SQL_ID oracle también crea el valor del identificador HASH_VALUE. Este valor era el que se usaba para identificar a una consulta en las versiones oracle 9i hacia atrás. Hay una diferencia eso si que cabe recalcar, en oracle9i y en oracle10g el algoritmo que se ocupa para crear ese hash_value es distinto. La vista GV$SQL o V$SQL posee la columna old_hash_value para mostrar también el valor hash_value que tendría nuestro SQL_ID pero como si fuese visto sobre un motor oracle9i o anterior.
A modo de ejemplo oracle crea el hash_value de la consulta ocupando el siguiente algoritmo (ocupando como parámetro el SQL_ID):
(la consulta original está en la página del genial TanelPoder, abajo en la bibliografia se encuentra el link original)
select
trunc(mod(sum((instr('0123456789abcdfghjkmnpqrstuvwxyz',substr(lower(trim('a5ks9fhw2v9s1')),level,1))-1)
*power(32,length(trim('a5ks9fhw2v9s1'))-level)),power(2,32))) hash_value
from
dual
connect by
level <= length(trim('a5ks9fhw2v9s1'))
Si ejecutamos la consulta usando el sql_id del ejemplo anterior para la query "select * from dual", veremos que entrega el mismo hash_value visto en la consulta anterior:
HASH_VALUE
----------
942515969
Como mencionamos anteriormente el sql_id es una mezcla de hashing md5 y funciones Hexadecimales con 32 caracteres que retorna un número de 13 dígitos de largo. El algoritmo usado para generar lo anterior que mencionamos que da forma al SQL_ID, puede ser visto con la siguiente función que ha creado el señor Carlos Sierra (al final se encuentra el link hacia la fuente original)
CREATE OR REPLACE FUNCTION compute_sql_id (sql_text IN CLOB)
RETURN VARCHAR2 IS
BASE_32 CONSTANT VARCHAR2(32) := '0123456789abcdfghjkmnpqrstuvwxyz';
l_raw_128 RAW(128);
l_hex_32 VARCHAR2(32);
l_low_16 VARCHAR(16);
l_q3 VARCHAR2(8);
l_q4 VARCHAR2(8);
l_low_16_m VARCHAR(16);
l_number NUMBER;
l_idx INTEGER;
l_sql_id VARCHAR2(13);
BEGIN
l_raw_128 := /* use md5 algorithm on sql_text and produce 128 bit hash */
SYS.DBMS_CRYPTO.hash(TRIM(CHR(0) FROM sql_text)||CHR(0), SYS.DBMS_CRYPTO.hash_md5);
l_hex_32 := RAWTOHEX(l_raw_128); /* 32 hex characters */
l_low_16 := SUBSTR(l_hex_32, 17, 16); /* we only need lower 16 */
l_q3 := SUBSTR(l_low_16, 1, 8); /* 3rd quarter (8 hex characters) */
l_q4 := SUBSTR(l_low_16, 9, 8); /* 4th quarter (8 hex characters) */
/* need to reverse order of each of the 4 pairs of hex characters */
l_q3 := SUBSTR(l_q3, 7, 2)||SUBSTR(l_q3, 5, 2)||SUBSTR(l_q3, 3, 2)||SUBSTR(l_q3, 1, 2);
l_q4 := SUBSTR(l_q4, 7, 2)||SUBSTR(l_q4, 5, 2)||SUBSTR(l_q4, 3, 2)||SUBSTR(l_q4, 1, 2);
/* assembly back lower 16 after reversing order on each quarter */
l_low_16_m := l_q3||l_q4;
/* convert to number */
SELECT TO_NUMBER(l_low_16_m, 'xxxxxxxxxxxxxxxx') INTO l_number FROM DUAL;
/* 13 pieces base-32 (5 bits each) make 65 bits. we do have 64 bits */
FOR i IN 1 .. 13
LOOP
l_idx := TRUNC(l_number / POWER(32, (13 - i))); /* index on BASE_32 */
l_sql_id := l_sql_id||SUBSTR(BASE_32, (l_idx + 1), 1); /* stitch 13 characters */
l_number := l_number - (l_idx * POWER(32, (13 - i))); /* for next piece */
END LOOP;
RETURN l_sql_id;
END compute_sql_id;
/
Si creamos esa función en nuestra BD y la ejecutamos indicando nuestro Statement completo como parámetro veremos el mismo SQL_ID que habíamos visto en la shared pool al comienzo:
SQL> SELECT compute_sql_id('select * from dual') sql_id from dual ;
SQL_ID
--------------------------------------------------------------------------------
a5ks9fhw2v9s1
Ahora surge una pregunta que muchas personas pueden hacerse ¿Puede repetirse un SQL_ID para dos consultas absolutamente distintas? La respuesta es SI. Ya en el hash_value las colisiones se sabia que podían existir (y eso que el algoritmo trabaja en ese caso con un Hashing de 32bits). Para evitar eso se creo el SQL_ID basado en un algoritmo que genera un número único mucho más largo. En algunas pruebas realizadas por un DBA del Cern llamado Luca Canali (a quien recomiendo leer mucho para eso ver link de su nota al respecto en la bibliografía) se encontró que se necesitaron generar casi 10billones de pruebas con consultas de texto similares para generar algunas pocas colisiones. Eso demuestra que actualmente el SQL_ID es la manera más eficiente de asegurar la identificación única para una consulta en la base de datos.
En resumen el SQL_ID es fruto de funciones Hexadecimales y conversiones MD5 que resulta en un número de 13 digitos, y el hash_value es formado por los últimos 32bits del hashing md5 del SQL_ID.
Aquí la bibliografía que recomiendo leer Bibliografía:
https://blog.tanelpoder.com/2009/02/22/sql_id-is-just-a-fancy-representation-of-hash-value/
