Trouver le SQL_ID d'une interrogation Oracle Analytics
Lorsque vous réglez des interrogations pour Oracle Analytics, vous examinez le modèle de données, la conception des rapports, la conception des tableaux de bord et la configuration du réseau et, parfois, vous devez étudier la base de données.
Table V$SQL
Pour les investigations de base de données, vous devez connaître le SQL_ID de l'énoncé SQL afin de pouvoir suivre cette interrogation spécifique dans l'historique des sessions actives (ASH), le référentiel AWR et Oracle SQLTXPLAIN (SQLT).
Cette rubrique vous explique comment trouver le SQL_ID en exploitant les tables de suivi de l'utilisation d'Oracle Analytics et la table du système de base de données Oracle nommée V$SQL (ou GV$SQL pour une base de données RAC).
La table V$SQL est une table du système de base de données Oracle qui permet le suivi des statistiques pour les interrogations SQL individuelles. Il y a une rangée dans la table pour chaque énoncé SQL que vous exécutez et chaque rangée est identifiée de manière unique par la colonne SQL_ID . Vous pouvez utiliser ce SQL_ID pour assurer le suivi d'un énoncé SQL particulier dans l'ensemble de la base de données Oracle.
Vous trouverez de nombreuses informations utiles sur vos interrogations 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 permettent pas de tracer directement le SQL_ID, mais vous pouvez facilement suivre le SQL_ID jusqu'à 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 interrogations font l'objet d'un suivi dans deux tables :
S_NQ_ACCT: Contient les interrogations logiquesS_NQ_DB_ACCT: Contient les interrogations physiques
L'interrogation logique est journalisée dans la table des interrogations logiques et l'interrogation physique (ou les interrogations générées par l'interrogation logique) est journalisée dans la table des interrogations physiques.
Vous pouvez joindre les tables logiques et physiques dans la colonne ID de l'interrogation logique. La colonne ID de l'interrogation logique dans la table logique est ID. Dans la table physique, il s'agit de LOGICAL_QUERY_ID.
La clé pour trouver le SQL_ID est la colonne PHYSICAL_HASH_ID dans la table physique. La valeur PHYSICAL_HASH_ID est également écrite dans la table V$SQL (ou la table GV$SQL) dans la colonne ACTION.
Oracle BI Server calcule un code de hachage à partir du texte de l'interrogation SQL logique et du texte des interrogations SQL physiques. Le code de hachage SQL physique de toute interrogation SQL exécutée à partir d'Oracle BI Server est enregistré dans la colonne ACTION de la table V$SQL. Pour plus d'informations, voir Associer l'enregistrement S_NQ_ACCT au journal d'interrogations BI.
Pour plus d'informations sur les différentes colonnes de suivi de l'utilisation, voir Suivi de l'utilisation et Comprendre les tables de suivi de l'utilisation.
Configuration
Maintenant que vous savez où trouver l'élément PHYSICAL_HASH_ID, vous pouvez écrire un énoncé SQL qui établit une corrélation entre l'interrogation SQL exécutée dans la base de données et 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 seule interrogation pour trouver le SQL_ID.
Si les tables de suivi de l'utilisation et votre entrepôt de données se trouvent dans des instances de base de données Oracle différentes, vous devez exécuter deux interrogations. Tout d'abord, vous exécutez une interrogation pour extraire la valeur PHYSICAL_HASH_ID de l'interrogation que vous souhaitez étudier à partir des tables de suivi de l'utilisation. Ensuite, vous extrayez le SQL_ID de la table V$SQL en utilisant la valeur de PHYSICAL_HASH_ID pour filtrer la colonne ACTION.
Exemple 1
Dans cet exemple, nous supposons que les tables de suivi de l'utilisation et l'entrepôt de données sont colocalisés. Toutes les colonnes des tables de suivi de l'utilisation et de la table V$SQL peuvent être utilisées dans l'interrogation, mais dans l'exemple, vous sélectionnez le sous-jeu 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;
Exemple 2
Dans cet exemple, nous supposons que les tables de suivi de l'utilisation et l'entrepôt de données se trouvent dans des bases de données Oracle différentes.
Tout d'abord, écrivez une interrogation 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 interrogation sur la table V$SQL pour obtenir le SQL_ID, en utilisant les valeurs de PHYSICAL_HASH_ID comme filtre :
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 avez une base de données RAC, au lieu de la table V$SQL, vous utilisez la table GV$SQL pour obtenir le 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 l'utilisation des blocs d'initialisation (S_NQ_INITBLOCK) ne contient pas d'ID hachage physique.
Exemple 3
Dans cet exemple, vous utilisez le domaine A - Sample Sales et vous ne sélectionnez que quelques colonnes des tables de suivi de l'utilisation.
À partir de A - Sample Sales, exécutez l'interrogation 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, LOGICAL_QUERY_ID ayant la valeur E841EBB79217270A660CDD3EFB5D986C.
Lorsque vous consultez le journal Gérer les sessions, l'interrogation logique génère le code SQL qui se trouve 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 la table V$SQL où Action est bd6708b8.
L'interrogation figurant dans la colonne sql_text est le même code SQL que celui qui figure dans la table physique de suivi de l'utilisation. Il en ressort que le sql_id de l'interrogation en question est 1gxhc0acmztwk.
Si vous exécutez à nouveau la même interrogation, une autre rangée est ajoutée à la table logique de suivi de l'utilisation avec un nouvel ID, mais le hash_id est le même .
De même, une autre rangée est ajoutée à la table physique de suivi de l'utilisation avec un nouveau logical_query_id, mais le hash_id et le physical_hash_id restent identiques .
Exemple 4
Le physical_hash_id est également réutilisé si vous exécutez une interrogation logique similaire sur un domaine basé sur les mêmes modèles logiques et physiques. Par exemple, si vous exécutez une interrogation similaire sur 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 interrogation logique génère le même code SQL que celui utilisé dans l'interrogation exécutée sur 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
]]Tout d'abord, utilisez la table logique de suivi de l'utilisation pour trouver l'ID. Vous constatez que la valeur hash_id logique est différente de celle de l'interrogation exécutée sur A – Sample Sales :
Dans la table physique de suivi de l'utilisation, vous voyez que même si le hash_id logique est différent, le physical_hash_id est le même .
Exemple 5
Dans cet exemple, vous exécutez les mêmes interrogations, mais cette fois-ci, vous cliquez sur Actualiser. Notez la variable OBIS_REFRESH_CACHE=1 pour indiquer l'actualisation. Comme prévu, une autre rangée est insérée dans la table logique de suivi de l'utilisation, mais elle a une valeur logical hash_id différente.
Cependant, lorsque vous interrogez la table physique de suivi de l'utilisation, vous constatez que toutes les interrogations ont le même physical_hash_id.
Comme ces quatre interrogations logiques ont toutes le même physical_hash_id, vous ne voyez qu'un seul enregistrement dans la table V$SQL pour cette interrogation.
Exemple 6
Vous pouvez utiliser la même méthode pour dépanner les interrogations de visualisation de données basées sur un domaine (RPD) ou une connexion à une base de données. Pour les connexions à une base de données, cela comprend à la fois les visualisations de données et les interrogations utilisées pour alimenter les jeux de données.
La valeur de la colonne QUERY_SRC_CD permet de déterminer le type d'interrogation (visualisation de données, jeu de données, analyse, ODBC, etc.). Par exemple, les interrogations sur la visualisation des données ont la valeur Visual Analyzer, une interrogation utilisée pour alimenter un jeu de données a la valeur data-prep, les analyses ont la valeur Report et les interrogations pour certains appels de procédures ODBC internes ont la valeur Soap.
Pour générer un énoncé SQL pour une visualisation de données, vous devez faire passer l'accès aux données à Direct pour le jeu de données sous-jacent.
Dans cet exemple, vous créez une interrogation de visualisation de données (similaire aux interrogations précédentes) sur un jeu 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' */
Qui génère le code SQL suivant :
[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 le hash_id qui est utilisé comme filtre dans la table physique de suivi de l'utilisation .
Dans la table physique de suivi de l'utilisation, vous pouvez trouver le physical_hash_id.
Lorsque vous interrogez la table V$SQL, vous trouvez le SQL_ID.
Vous pouvez également trouver le hash_id logique et le physical_hash_id dans le journal Gérer les sessions (voir les sections mises en évidence dans les exemples de journal précédents). Autrement dit, vous pouvez trouver le SQL_ID dans le journal lui-même. L'avantage des tables de suivi de l'utilisation est que les entrées du journal Gérer les sessions sont temporaires. Mais si vous ne collectez pas les ID hachages en même temps que vous exécutez l'interrogation, ils sont perdus.
Cependant, les données ne sont écrites dans les tables de suivi de l'utilisation que lorsqu'une interrogation est terminée. Ainsi, si vous dépannez une interrogation de longue durée qui ne s'est pas encore terminée et que vous souhaitez déterminer le sql_id, vous pouvez obtenir le hash_id logique et le physical_hash_id à partir du journal Gérer les sessions.
Si vous dépannez un énoncé 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 de système V$SQL (ou GV$SQL) pour trouver rapidement le SQL_ID de l'énoncé SQL que vous examinez.