Recherche de la valeur SQL_ID pour une requête Oracle Analytics
Lorsque vous réglez les requêtes pour Oracle Analytics, vous devez examiner le modèle de données, la conception de rapport, la conception de tableau de bord, la configuration réseau et, parfois, la base de données.
Table V$SQL
Pour vous pencher sur la base de données, vous devez connaître la valeur SQL_ID de l'instruction SQL afin de pouvoir suivre la requête concernée via ASH (Active Session History), le référentiel AWR (Automatic Workload Repository) et Oracle SQLTXPLAIN (SQLT).
Cette rubrique explique comment rechercher la valeur SQL_ID en explorant les tables de suivi de l'utilisation Oracle Analytics et la table système de base de données Oracle V$SQL (ou GV$SQL pour une base de données RAC).
V$SQL est une table système de base de données Oracle qui suit les statistiques des requêtes SQL individuelles. La table contient une ligne pour chaque instruction SQL que vous exécutez et chaque ligne est identifiée de manière unique par la colonne SQL_ID. Vous pouvez utiliser cette valeur SQL_ID pour suivre une instruction SQL spécifique dans la base de données Oracle.
Vous trouverez de nombreuses informations utiles sur vos requêtes dans la table V$SQL (ou GV$SQL pour une base de données RAC).
Tables de suivi de l'utilisation
Les tables de suivi de l'utilisation ne suivent pas directement la valeur SQL_ID mais vous pouvez tracer facilement une valeur SQL_ID et remonter vers la table V$SQL (ou GV$SQL pour une base de données RAC).
Lorsque vous activez le suivi de l'utilisation dans Oracle Analytics, toutes les requêtes sont suivies dans deux tables :
S_NQ_ACCT: contient les requêtes logiquesS_NQ_DB_ACCT: contient les requêtes physiques
Les requêtes logiques sont consignées dans la table logique et les requêtes physiques (ou les requêtes générées dynamiquement par les requêtes logiques) sont consignées dans la table physique.
Vous pouvez joindre les tables logique et physique au niveau de la colonne d'ID de requête logique. La colonne d'ID de requête logique est ID dans la table logique et LOGICAL_QUERY_ID dans la table physique.
La clé pour identifier une valeur SQL_ID est la colonne PHYSICAL_HASH_ID de la table physique. La valeur PHYSICAL_HASH_ID est également écrite dans V$SQL (ou GV$SQL) dans la colonne ACTION.
Le serveur Oracle BI Server calcule un code de hachage à partir du texte de la requête SQL logique et du texte des requêtes SQL physiques. Le code de hachage SQL physique de n'importe quelle requête SQL exécutée à partir du serveur Oracle BI Server est enregistré dans la colonne ACTION dans V$SQL. Pour plus d'informations, reportez-vous à Association de l'enregistrement S_NQ_ACCT au journal des requêtes BI.
Pour plus d'informations sur les différentes colonnes de suivi de l'utilisation, reportez-vous à Suivi de l'utilisation et à Présentation des tables de suivi de l'utilisation.
Configuration
Maintenant que vous savez où trouver la valeur PHYSICAL_HASH_ID, il est relativement simple d'écrire une instruction SQL qui met en corrélation la requête SQL exécutée dans la base de données avec une analyse spécifique exécutée dans Oracle Analytics.
Lorsque les tables de suivi de l'utilisation se trouvent dans la même instance de base de données que l'entrepôt de données, vous pouvez écrire une requête unique pour identifier la valeur SQL_ID.
Si les tables de suivi de l'utilisation et l'entrepôt de données se trouvent dans des instances de base de données Oracle différentes, vous devez exécuter deux requêtes. D'abord, exécutez une requête pour extraire la valeur PHYSICAL_HASH_ID de la requête qui vous intéresse à partir des tables de suivi de l'utilisation. Ensuite, extrayez la valeur SQL_ID à partir de V$SQL en utilisant la valeur PHYSICAL_HASH_ID pour filtrer la colonne ACTION.
Exemples
Dans cet exemple, nous partons du principe que les tables de suivi de l'utilisation et l'entrepôt de données sont localisés au même endroit. Tout ou partie des colonnes des tables de suivi de l'utilisation et V$SQL peut être utilisé dans la requête mais, dans cet exemple, vous sélectionnez le sous-ensemble suivant :
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;
Dans le deuxième exemple, nous partons du principe que les tables de suivi de l'utilisation et l'entrepôt de données sont localisés dans des bases de données Oracle différentes.
D'abord, écrivez une requête sur les tables de suivi de l'utilisation pour obtenir la valeur 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;
Ensuite, écrivez une requête sur V$SQL pour obtenir la valeur SQL_ID, en utilisant les valeurs PHYSICAL_HASH_ID comme filtres.
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 vous disposez d'une base de données RAC, au lieu de V$SQL, vous utilisez la table GV$SQL pour obtenir la valeur 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;
Cette méthode ne fonctionne pas si vous dépannez des blocs d'initialisation car la table de suivi de leur utilisation (S_NQ_INITBLOCK) ne contient aucun ID de hachage physique.
Dans l'exemple suivant, vous allez utiliser le domaine 'A - Sample Sales' et sélectionner uniquement quelques colonnes dans les tables de suivi de l'utilisation.
Dans le domaine 'A - Sample Sales', exécutez la requête suivante :
[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 ]]
Lorsque vous sélectionnez start_ts, id, hash_id, query_src_cd et query_text dans la table logique de suivi de l'utilisation, vous obtenez la valeur ID E841EBB79217270A660CDD3EFB5D986C :
Ensuite, sélectionnez logical_query_id, hash_id, physical_hash_id et query_text dans la table physique de suivi de l'utilisation où logical_query_id = E841EBB79217270A660CDD3EFB5D986C :
Lorsque vous consultez le journal de gestion des sessions, la requête logique génère dynamiquement la requête SQL présente dans la colonne 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
]]Enfin, sélectionnez action, sql_id et sql_text dans V$SQL, où action = ‘bd6708b8' :
La requête dans la colonne sql_text est identique à la requête SQL visible dans la table physique de suivi de l'utilisation. Vous pouvez ainsi voir que la valeur sql_id de la requête en question est ‘1gxhc0acmztwk’.
Si vous exécutez à nouveau la même requête, une autre ligne est ajoutée à la table logique de suivi de l'utilisation avec un nouvel ID, mais la valeur hash_id est la même :
De même, une autre ligne est ajoutée à la table physique de suivi de l'utilisation avec une nouvelle valeur logical_query_id, mais les valeurs hash_id et physical_hash_id restent les mêmes :
La valeur physical_hash_id est également réutilisée si vous exécutez une requête logique similaire sur un domaine basé sur les mêmes modèles logique et physique. Par exemple, vous allez exécuter ici une requête similaire sur le domaine '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 ]]
Cette requête logique génère dynamiquement la même requête SQL que dans la requête exécutée sur le domaine '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
]]D'abord, employez la table logique de suivi de l'utilisation pour trouver l'ID. Vous pouvez constater que la valeur hash_id logique est différente de la requête exécutée sur le domaine 'A – Sample Sales' :
Dans la table physique de suivi de l'utilisation, vous pouvez voir que même si la valeur hash_id logique est différente, la valeur physical_hash_id est la même :
Dans un autre exemple, vous exécutez les mêmes requêtes (que ci-dessus), mais vous cliquez sur Actualiser. Faites attention à la variable OBIS_REFRESH_CACHE=1, qui reflète l'actualisation. Comme prévu, une autre ligne est insérée dans la table logique de suivi de l'utilisation, mais sa valeur logical hash_id est différente.
Cependant, lorsque vous interrogez la table physique de suivi de l'utilisation, vous pouvez constater que toutes les requêtes présentent la même valeur physical_hash_id.
Puisque ces quatre requêtes logiques ont toutes la même valeur physical_hash_id, un seul enregistrement est présent dans V$SQL pour cette requête :
Vous pouvez recourir à la même méthode pour dépanner des requêtes de visualisation de données sur la base d'un domaine (RPD) ou d'une connexion à la base de données. Pour les connexions à la base de données, les visualisations de données et les requêtes utilisées pour remplir les ensembles de données sont incluses.
Vous pouvez déterminer le type de requête (visualisation de données, ensemble de données, analyse, ODBC, etc.) à partir de la valeur de la colonne QUERY_SRC_CD. Par exemple, les requêtes de visualisation de données présentent la valeur 'Visual Analyzer', une requête utilisée pour remplir un ensemble de données présente la valeur 'data-prep', les analyses présentent la valeur 'Report' et les requêtes pour certains appels de procédure ODBC internes présentent la valeur 'Soap'.
Afin de générer une instruction SQL pour une visualisation de données, vous devez définir l'accès direct aux données pour l'ensemble de données sous-jacent.
Dans cet exemple, vous créez une requête de visualisation de données (semblable aux requêtes ci-dessus) sur un ensemble de données basé sur une connexion à une base de données 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' */
Elle génère dynamiquement la requête SQL suivante :
[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 <= 125001Dans la table logique de suivi de l'utilisation, vous pouvez trouver la valeur hash_id utilisée comme filtre sur la table physique de suivi de l'utilisation :
Dans la table physique de suivi de l'utilisation, vous pouvez trouver la valeur physical_hash_id :
Lorsque vous interrogez V$SQL, vous obtenez à nouveau la valeur SQL_ID :
Vous pouvez également identifier la valeur hash_id logique et la valeur physical_hash_id dans le journal de gestion des sessions (reportez-vous aux sections mises en surbrillance dans les fragments de code de journal ci-dessus). Cela signifie que vous pouvez trouver la valeur SQL_ID dans le journal lui-même. L'avantage du recours aux tables de suivi de l'utilisation est que les entrées du journal de gestion des sessions sont éphémères. Ainsi, sauf si vous collectez les ID de hachage en même temps que vous exécutez la requête, ils sont perdus.
Cependant, les données sont uniquement écrites dans les tables de suivi de l'utilisation une fois qu'une requête est terminée. Par conséquent, si vous dépannez une requête à longue durée d'exécution qui n'est pas encore terminée et que vous voulez déterminer la valeur sql_id, vous pouvez obtenir la valeur hash_id logique et la valeur physical_hash_id dans le journal de gestion des sessions.
Si vous dépannez une instruction SQL et que vous devez examiner la base de données, vous pouvez joindre les informations des tables de suivi de l'utilisation et de la table système V$SQL (ou GV$SQL) afin de trouver rapidement la valeur SQL_ID de l'instruction SQL qui vous intéresse.