Búsqueda del identificador SQL_ID de una consulta de Oracle Analytics
Al ajustar las consultas para Oracle Analytics, debe comprobar el modelo de datos, el diseño de informes, el diseño de paneles de control, la configuración de red y, en algunos casos, debe investigar la base de datos.
Tabla V$SQL
Para las investigaciones de base de datos, necesita conocer el SQL_ID de la sentencia SQL para poder realizar un seguimiento de la consulta específica en el Historial de sesiones activas (ASH), el Repositorio de carga de trabajo automática (AWR) y Oracle SQLTXPLAIN (SQLT).
En este tema se muestra cómo puede buscar el SQL_ID mediante la minería de las tablas de seguimiento de uso de Oracle Analytics y la tabla del sistema de base de datos Oracle V$SQL (o GV$SQL para una base de datos RAC).
V$SQL es una tabla del sistema de base de datos Oracle que realiza un seguimientos de las estadísticas de consultas SQL individuales. Hay una fila en la tabla para cada sentencia SQL que ejecute, y cada fila se identifica de forma única por la columna SQL_ID. Puede utilizar este SQL_ID para realizar el seguimiento de una sentencia SQL determinada en la base de datos Oracle.
Encontrará una gran cantidad de información útil sobre las consultas en la tabla V$SQL (o en GV$SQL en el caso de una base de datos RAC).
Tablas de seguimiento de uso
Las tablas de seguimiento de uso no realizan un seguimiento de uso directamente del SQL_ID, pero puede rastrear fácilmente el SQL_ID hasta la tabla V$SQL (o GV$SQL en el caso de una base de datos RAC).
Cuando activa el seguimiento de uso en Oracle Analytics, se realiza un seguimiento de todas las consultas en dos tablas:
S_NQ_ACCT: contiene las consultas lógicasS_NQ_DB_ACCT: contiene las consultas físicas
La consulta lógica se registra en la tabla lógica, y la consulta física (o las consultas generadas por la consulta lógica) se registran en la tabla de consultas físicas.
Puede unir las tablas lógica y física en la columna de ID de consulta lógica. La columna de ID de consulta lógica en la tabla lógica es ID, y en la tabla física es LOGICAL_QUERY_ID.
La clave para buscar el SQL_ID es la columna PHYSICAL_HASH_ID de la tabla física. El valor PHYSICAL_HASH_ID también se escribe en V$SQL (o en GV$SQL) en la columna ACTION.
Oracle BI Server calcula un código hash a partir del texto de la consulta SQL lógica y del texto de las consultas SQL físicas. El código hash SQL físico de todas las consultas SQL ejecutadas desde Oracle BI Server se registra en la columna ACTION en V$SQL. Para obtener más información, consulte Asociación del registro S_NQ_ACCT con el log de consultas de BI.
Para obtener más información sobre las distintas columnas de seguimiento de uso, consulte Seguimiento del uso y Descripción de las tablas de seguimiento de uso.
Configuración
Ahora que sabe dónde puede encontrar el PHYSICAL_HASH_ID, es una tarea relativamente sencilla escribir una sentencia SQL que correlacione el SQL que se ejecuta en la base de datos con un análisis específico que se ejecute en Oracle Analytics.
Cuando las tablas de seguimiento de uso se encuentran en la misma instancia de base de datos que el almacén de datos, puede escribir una única consulta para buscar el SQL_ID.
Si las tablas de seguimiento de uso y su almacén de datos están en diferentes instancias de base de datos Oracle, debe ejecutar dos consultas. En primer lugar, ejecute una consulta para extraer el PHYSICAL_HASH_ID de la consulta que desee investigar de las tablas de seguimiento de uso. En segundo lugar, extraiga el SQL_ID de V$SQL utilizando el valor de PHYSICAL_HASH_ID para filtrar la columna ACTION.
Ejemplos
En este ejemplo, suponga que las tablas de seguimiento de uso y el almacén de datos tienen la misma ubicación. Todas o cualquier de las columnas del seguimiento de uso y las tablas V$SQL se pueden utilizar en la consulta, pero en este ejemplo, debe seleccionar el siguiente subjuego:
select o.sql_id, to_char(l.start_ts, 'YYYY-MM-DD HH24:MI:SS'), l.id as l_id, p.logical_query_id, l.hash_id, l.saw_src_path, l.query_src_cd, l.success_flg, l.num_db_query, l.query_text, p.query_text, o.sql_text, p.physical_hash_id, o.action from usage_tracking.s_nq_acct l, usage_tracking.s_nq_db_acct p, v$sql o where l.id = p.logical_query_id and o.action = p.physical_hash_id and l.start_dt > trunc(sysdate - 1) and l.end_dt < trunc(sysdate) order by l.start_ts, l.id, l.hash_id, p.physical_hash_id, o.sql_id;
En el segundo ejemplo, suponga que las tablas de seguimiento de uso y el almacén de datos están ubicadas en diferentes bases de datos Oracle.
En primer lugar, escriba una consulta de las tablas de seguimiento de uso para obtener el PHYSICAL_HASH_ID.
select to_char(l.start_ts, 'YYYY-MM-DD HH24:MI:SS'), l.id as l_id, p.logical_query_id, l.hash_id, l.saw_src_path, l.query_src_cd, l.success_flg, l.num_db_query, l.query_text, p.query_text, p.physical_hash_id from usage_tracking.s_nq_acct l, usage_tracking.s_nq_db_acct p where l.id = p.logical_query_id and l.start_dt > trunc(sysdate - 1) and l.end_dt < trunc(sysdate) order by l.start_ts, l.id,l.hash_id, p.physical_hash_id;
En segundo lugar, escriba una consulta de V$SQL para obtener el SQL_ID utilizando los valores de PHYSICAL_HASH_ID como filtro.
select o.action, o.sql_id, o.sql_text from v$sql o where o.action = ‘<physical_hash_id>’ order by o.sql_id;
Si tiene una base de datos RAC, en lugar de V$SQL, utilice la tabla GV$SQL para obtener el SQL_ID.
select o.action, o.sql_id, o.sql_text from gv$sql o where o.action = '<physical_hash_id>' order by o.sql_id;
Este método no funciona si está solucionando problemas de los bloques de inicialización, ya que la tabla de seguimiento de uso de bloques de inicialización (S_NQ_INITBLOCK) no contiene un ID de hash físico.
En el siguiente ejemplo, debe utilizar el área temática ‘A - Sample Sales’ y seleccionar solo unas pocas columnas de las tablas de seguimiento de uso.
En ‘A - Sample Sales’, ejecute la siguiente consulta:
[2022-02-04T15:11:17.629+00:00] [OBIS] [TRACE:2] [USER-0] [] [ecid: e49b96a8-33c4-4ba7-a877-e564d207eca1-00242531,0:1:38:3] [sik: bootstrap] [tid: dd1bc700] [messageId: USER-0] [requestid: 33e30020] [sessionid: 33e30000] [username: oacadmin] ############################################## [[ -------------------- SQL Request, logical request hash: cee7ec94 SET VARIABLE QUERY_SRC_CD='Report';SELECT 0 s_0, "A - Sample Sales"."Offices"."D1 Office" s_1, "A - Sample Sales"."Base Facts"."10- Variable Costs" s_2, "A - Sample Sales"."Base Facts"."11- Fixed Costs" s_3 FROM "A - Sample Sales" ORDER BY 2 ASC NULLS LAST FETCH FIRST 125001 ROWS ONLY ]]
Si selecciona start_ts, id, hash_id, query_src_cd y query_text en la tabla de seguimiento de uso, obtendrá el valor de ID E841EBB79217270A660CDD3EFB5D986C:
A continuación, seleccione logical_query_id, hash_id, physical_hash_id y query_text de la tabla física de seguimiento de uso, donde logical_query_id = E841EBB79217270A660CDD3EFB5D986C:
Si comprueba el log de gestión de sesiones, la consulta lógica genera el SQL que está en la columna QUERY_TEXT:
[2022-02-04T15:11:17.637+00:00] [OBIS] [TRACE:2] [USER-18] [] [ecid: e49b96a8-33c4-4ba7-a877-e564d207eca1-00242531,0:1:38:5] [sik: bootstrap] [tid: dd1bc700] [messageId: USER-18] [requestid: 33e30020] [sessionid: 33e30000] [username: oacadmin] -------------------- Sending query to database named 01 - Sample App Data (ORCL) (id: <<62275>>), client type Oracle Call Interface (OCI), connection pool named Sample Relational Connection, logical request hash cee7ec94, physical request hash bd6708b8: [[
WITH
SAWITH0 AS (select sum(T5398.Cost_Fixed) as c1,
sum(T5398.Cost_Variable) as c2,
T5257.Office_Dsc as c3,
T5257.Office_Key as c4
from
BISAMPLE.SAMP_OFFICES_D T5257 /* D30 Offices */ ,
BISAMPLE.SAMP_REVENUE_F T5398 /* F10 Billed Rev */
where ( T5257.Office_Key = T5398.Office_Key )
group by T5257.Office_Dsc, T5257.Office_Key),
SAWITH1 AS (select 0 as c1,
D1.c3 as c2,
D1.c2 as c3,
D1.c1 as c4,
D1.c4 as c5
from
SAWITH0 D1)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4
from
SAWITH1 D1
order by c2 ) D1 where rownum <= 125001
]]Finalmente, seleccione action, sql_id y sql_text de V$SQL donde la acción = ‘bd6708b8':
La consulta de la columna sql_text es el mismo SQL que aparece en la tabla física de seguimiento de uso. Aquí puede ver que el sql_id de la consulta en cuestión es ‘1gxhc0acmztwk’.
Si vuelve a ejecutar la misma consulta, se agrega otra fila a la tabla lógica de seguimiento de uso con un nuevo ID, pero el hash_id es el mismo:
De forma similar, se agrega otra fila a la tabla física de seguimiento de uso con un nuevo logical_query_id, pero hash_id y physical_hash_id no cambian.
El physical_hash_id también se reutiliza si ejecuta una consulta lógica similar en un área temática que esté basada en los mismos modelos lógico y físico. Por ejemplo, aquí debe ejecutar una consulta similar en ‘C - Sample Costs’:
[2022-02-04T16:10:17.862+00:00] [OBIS] [TRACE:2] [USER-0] [] [ecid: e49b96a8-33c4-4ba7-a877-e564d207eca1-00242ce7,0:1:15:3] [sik: bootstrap] [tid: 58504700] [messageId: USER-0] [requestid: 13c9003c] [sessionid: 13c90000] [username: oacadmin] ############################################## [[ -------------------- SQL Request, logical request hash: 7b5ea9b1 SET VARIABLE QUERY_SRC_CD='Report';SELECT 0 s_0, "C - Sample Costs"."Offices"."D1 Office" s_1, "C - Sample Costs"."Base Facts"."10- Variable Costs" s_2, "C - Sample Costs"."Base Facts"."11- Fixed Costs" s_3 FROM "C - Sample Costs" ORDER BY 2 ASC NULLS LAST FETCH FIRST 125001 ROWS ONLY ]]
Esta consulta lógica genera el mismo SQL que aparece en la consulta que ha ejecutado en ‘A – Sample Sales’:
[2022-02-04T16:10:17.866+00:00] [OBIS] [TRACE:2] [USER-18] [] [ecid: e49b96a8-33c4-4ba7-a877-e564d207eca1-00242ce7,0:1:15:5] [sik: bootstrap] [tid: 58504700] [messageId: USER-18] [requestid: 13c9003c] [sessionid: 13c90000] [username: oacadmin] -------------------- Sending query to database named 01 - Sample App Data (ORCL) (id: <<52912>>), client type Oracle Call Interface (OCI), connection pool named Sample Relational Connection, logical request hash 7b5ea9b1, physical request hash bd6708b8: [[
WITH
SAWITH0 AS (select sum(T5398.Cost_Fixed) as c1,
sum(T5398.Cost_Variable) as c2,
T5257.Office_Dsc as c3,
T5257.Office_Key as c4
from
BISAMPLE.SAMP_OFFICES_D T5257 /* D30 Offices */ ,
BISAMPLE.SAMP_REVENUE_F T5398 /* F10 Billed Rev */
where ( T5257.Office_Key = T5398.Office_Key )
group by T5257.Office_Dsc, T5257.Office_Key),
SAWITH1 AS (select 0 as c1,
D1.c3 as c2,
D1.c2 as c3,
D1.c1 as c4,
D1.c4 as c5
from
SAWITH0 D1)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4
from
SAWITH1 D1
order by c2 ) D1 where rownum <= 125001
]]En primer lugar, utilice la tabla lógica de seguimiento de uso para buscar el ID. Observe que el hash_id lógico es diferente de la consulta ejecutada en ‘A – Sample Sales’:
En la tabla física de seguimiento de uso, puede ver que aunque el hash_id lógico es diferente, el physical_hash_id es el mismo:
En otro ejemplo, puede ejecutar las mismas consultas (como se indica anteriormente), pero en este caso, haga clic en Refrescar. Observe la variable OBIS_REFRESH_CACHE=1 que indica el refrescamiento. Como se esperaba, se inserta otra fila en la tabla lógica de seguimiento de uso, pero esta tiene un logical hash_id diferente.
Sin embargo, cuando consulta la tabla física de seguimiento de uso, puede ver que todas las consultas tienen el mismo physical_hash_id.
Dado que estas cuatro consultas lógicas tienen todas el mismo physical_hash_id, solo aparece un único registro en V$SQL para esta consulta:
Puede utilizar el mismo método para solucionar problemas de consultas de visualización de datos en un área temática (RPD) o una conexión de base de datos. Para las conexiones de base de datos, esto incluye tanto las visualizaciones de datos como las consultas utilizadas para rellenar juegos de datos.
Puede determinar el tipo de consulta, es decir, la visualización de datos, el juego de datos, el análisis, ODBC, etc., a partir del valor de la columna QUERY_SRC_CD. Por ejemplo, las consultas de visualización de datos tienen el valor ‘Visual Analyzer’, una consulta que se utiliza para rellenar un juego de datos tiene el valor ‘data-prep, los análisis tienen el valor ‘Report’ y las consultas para algunas llamadas internas de procedimiento ODBC tienen el valor ‘Soap’.
Para generar una sentencia SQL para una visualización de datos, debe cambiar Acceso a datos a ‘Live’ para el juego de datos subyacente.
En este ejemplo, crea una consulta de visualización de datos (similar a las consultas anteriores) en un juego de datos según una conexión a una base de datos Oracle.
[2022-02-03T19:42:06.564+00:00] [OBIS] [TRACE:2] [USER-0] [] [ecid: f95b5f1b-1e5c-4604-b82a-3eb3717f3aa6-002aa26e,0:1:1:3] [sik: bootstrap] [tid: 58b0a700] [messageId: USER-0] [requestid: ed830023] [sessionid: ed830000] [username: oacadmin] ############################################## [[
-------------------- SQL Request, logical request hash:
3158b05
SET VARIABLE QUERY_SRC_CD='Visual Analyzer',SAW_SRC_PATH='{"viewID":"view!1","currentCanvas":"canvas!1"}',ENABLE_DIMENSIONALITY = 1; SELECT
0 s_0,
XSA('oacadmin'.'BISAMPLE_OfficeRevenue')."SAMP_OFFICES_D"."OFFICE_DSC" s_1,
XSA('oacadmin'.'BISAMPLE_OfficeRevenue')."SAMP_REVENUE_F"."COST_FIXED" s_2,
XSA('oacadmin'.'BISAMPLE_OfficeRevenue')."SAMP_REVENUE_F"."COST_VARIABLE" s_3
FROM XSA('oacadmin'.'BISAMPLE_OfficeRevenue')
ORDER BY 2 ASC NULLS LAST
FETCH FIRST 125001 ROWS ONLY
/* AdditionalDetail='VisualAnalyzer' */
Esta genera el siguiente SQL:
[2022-02-03T19:42:06.960+00:00] [OBIS] [TRACE:2] [USER-18] [] [ecid: f95b5f1b-1e5c-4604-b82a-3eb3717f3aa6-002aa26e,0:1:1:5] [sik: bootstrap] [tid: 58b0a700] [messageId: USER-18] [requestid: ed830023] [sessionid: ed830000] [username: oacadmin] -------------------- Sending query to database named 'oacadmin'.'BISAMPLE' (id: <<147945>>), client type OCI 10g/11g, connection pool named 'oacadmin'.'BISAMPLE', logical request hash 3158b05, physical request hash c48e8741: [[
WITH
SAWITH0 AS (select T1000005.OFFICE_KEY as c1,
T1000005.OFFICE_DSC as c2
from
BISAMPLE.SAMP_OFFICES_D T1000005),
SAWITH1 AS (select T1000008.OFFICE_KEY as c1,
T1000008.COST_FIXED as c2,
T1000008.COST_VARIABLE as c3
from
BISAMPLE.SAMP_REVENUE_F T1000008),
SAWITH2 AS (select D1.c2 as c1,
D2.c2 as c2,
D2.c3 as c3
from
SAWITH0 D1 inner join SAWITH1 D2 On D1.c1 = D2.c1),
SAWITH3 AS (select D102.c1 as c1,
sum(D102.c2) as c2,
sum(D102.c3) as c3
from
SAWITH2 D102
group by D102.c1)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select 0 as c1,
D110.c1 as c2,
D110.c2 as c3,
D110.c3 as c4
from
SAWITH3 D110
order by c2 ) D1 where rownum <= 125001En la tabla lógica de seguimiento de uso, puede encontrar el hash_id que se utiliza como filtro en la tabla física de seguimiento de uso:
En la tabla física de seguimiento de uso, puede encontrar el physical_hash_id:
Cuando consulta V$SQL, encuentra de nuevo el SQL_ID:
También puede encontrar el hash_id lógico y el physical_hash_id en el log de Gestionar sesiones (consulte las secciones resaltadas en los fragmentos de log anteriores). Esto significa que puede encontrar el SQL_ID en el propio log. La ventaja de utilizar las tablas de seguimiento de uso es que las entradas de log de Gestionar sesiones son efímeras, por lo que a menos que recopile los ID de hash al mismo tiempo que se ejecuta la consulta, estas se pierden.
Sin embargo, los datos solo se escriben en las tablas de seguimiento de uso cuando se completa una consulta. Por ello, a la hora de solucionar el problema de una consulta de larga ejecución que aún no se ha completado, si desea determinar el sql_id, puede obtener el hash_id lógico y el physical_hash_id del log de Gestionar sesiones.
Al solucionar un problema de una sentencia SQL, si necesita investigar la base de datos, puede unir la información de las tablas de seguimiento de uso y la tabla del sistema V$SQL (o GV$SQL) para buscar rápidamente el SQL_ID de la sentencia SQL que está investigando.