Identificando el SQL_ID de una consulta

En el siguiente artículo vamos a ver dos métodos para obtener el SQL_ID de una sentencia utilizando una parte del texto de la misma (o una secuencia que nos permita identificarla).
Previamente generé una tabla "T1" con dos campos "C1 y C2" y un único registro, esta tabla la voy a utilizar para realizar una consulta y poder ejemplificar el procedimiento.

Ejecutamos una consulta sobre la tabla:

SQL> select c1, c2 from t1;

        C1 C2
---------- --------------------
         1 AAA

Al ejecutarse el query, pasa por todas las fases normales de procesamiento: 
- análisis de la sintaxis (parsing)
- análisis de las variables (binding)
- ejecución (executing)
- recuperación de datos (fetching)
Entre otras cosas se genera el plan de ejecución y se le asigna un "SQL_ID" a la sentencia.

Ahora utilizando la siguiente consulta a la vista  v$sql, vamos a buscar en la shared pool, el SQL_ID de la sentencia ejecutada, indicándole una porción del texto para poder  filtrar por el campo SQL_TEXT:

SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text
      FROM  v$sql
      WHERE sql_text like '%<PORCION DE TEXTO A BUSCAR>%'
Ejemplo:

SQL> SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text
  2    FROM  v$sql
  3    WHERE sql_text like '%from t1%'
  4  ;

La salida es la siguiente:

SQL_ID        PLAN_HASH_VALUE SQL_TEXT
------------- --------------- ----------------------------------------
5sb9kw2ms5p5q      3617692013 select c1, c2 from t1

La consulta se encuentra en memoria y podemos obtener de esta manera su SQL_ID y PLAN_HASH_VALUE.

En el caso que la consulta ya no se encuentre en memoria, podemos buscarla en los snapshots de AWR, utilizando las vistas DBA_HIST_SQLSTAT y DBA_HIST_SQLTEXT.

Para poder demostrar esto vamos forzamos la eliminación de la sentencia de la shared pool (obteniendo el ADDRESS, el HASH_VALUE y ejecutando el comando de "PURGE") pero previamente tomamos un snapshot de AWR para asegurarnos que la información quede persistida en el repositorio.

Ejecución de snapshot:

SQL> exec dbms_workload_repository.create_snapshot;

Consulta para obtener los datos necesarios para el "purge":

SQL> select address, hash_value from v$sqlarea where sql_id like '5sb9kw2ms5p5q';

ADDRESS          HASH_VALUE
---------------- ----------
00000000ED46F4B0 2810369206

Ejecutando el procedimiento purge del paquete dbms_shared_pool  eliminamos la consulta de la shared pool (debemos pasarle como parámetros los datos obtenidos anteriormente).

SQL> exec dbms_shared_pool.purge('00000000ED46F4B0,2810369206','C');

PL/SQL procedure successfully completed

Una vez hecho esto, la consulta ya no se está en shared pool. Si volvemos a consultar ya no la encontramos:

SQL> SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text
  2    FROM  v$sql
  3    WHERE sql_text like '%from t1%'
  4  ;

SQL_ID        PLAN_HASH_VALUE SQL_TEXT
------------- --------------- ----------------------------------------

Ahora utilizando la siguiente consulta podemos buscar en el repo de AWR:

select t.sql_id,
    t.sql_text,
    s.executions_total,
    s.elapsed_time_total
from DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t
where
s.snap_id between {snap_id} and {snap_id};
and t.sql_text like '%from t1%';

En nuestro ejemplo: (no indicamos intervalo de snaps, buscamos en todos)

SQL> select t.sql_id,
  2  t.sql_text,
  3 s.executions_total,
  4 s.elapsed_time_total
 5 from DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t
 6 where t.sql_text like '%from t1%';

Podemos ver que la consulta fue encontrada:

SQL_ID        SQL_TEXT                                             EXECUTIONS_TOTAL ELAPSED_TIME_TOTAL
------------- ---------------------------------------------------- ---------------- ------------------
5sb9kw2ms5p5q select c1, c2 from t1;                                              2                 57