De SQL_ID voor een Oracle Analytics query zoeken
Wanneer u query's voor Oracle Analytics afstemt, kijkt u naar het gegevensmodel, het rapportontwerp, het dashboardontwerp en de netwerkconfiguratie, en af en toe moet u de database onderzoeken.
De V$SQL-tabel
Voor een onderzoek van de database moet u de SQL_ID van het SQL-statement kennen, zodat u de specifieke query kunt controleren via Active Session History (ASH), Automatic Workload Repository (AWR), en Oracle SQLTXPLAIN (SQLT).
In dit onderwerp leest u hoe u de SQL_ID kunt vinden door mining uit te voeren op de Oracle Analytics-tabellen voor gebruikscontrole en de Oracle-databasesysteemtabel genaamd V$SQL (of GV$SQL voor een RAC-database).
De V$SQL-tabel is een Oracle-databasesysteemtabel waarmee de statistieken voor afzonderlijke SQL-query's worden bijgehouden. Voor elk SQL-statement dat u kunt uitvoeren is er één rij in de tabel aanwezig en elke rij wordt uniek geïdentificeerd door de kolom SQL_ID . U kunt deze SQL_ID gebruiken om een bepaald SQL-statement in de hele Oracle database bij te houden.
In de V$SQL-tabel (of de GV$SQL-tabel voor een RAC-database) vindt u heel veel informatie over uw query's.
Tabellen voor gebruikscontrole
Met de tabellen voor gebruikscontrole wordt de SQL_ID niet rechtstreeks gecontroleerd, maar de SQL_ID kan eenvoudig worden herleid naar de V$SQL-tabel (of de GV$SQL-tabel voor een RAC-database).
Wanneer u gebruikscontrole in Oracle Analytics activeert, worden alle query's in twee tabellen gecontroleerd:
S_NQ_ACCT: bevat logische query's.S_NQ_DB_ACCT: bevat fysieke query's.
De logische query wordt in de logische querytabel gelogd en de fysieke query (of query's die door de logische query worden voortgebracht) worden in de fysieke querytabel gelogd.
U kunt de logische en fysieke tabellen samenvoegen in de kolom 'Logische query-ID'. De kolom 'ID logische query' in de logische tabel is ID en in de fysieke tabel is dit LOGICAL_QUERY_ID.
De sleutel tot het vinden van de SQL_ID is de kolom PHYSICAL_HASH_ID in de fysieke tabel. De waarde PHYSICAL_HASH_ID wordt ook naar de V$SQL-tabel (of de GV$SQL-tabel) geschreven in kolom ACTION.
De Oracle BI server berekent een hashcode op basis van de tekst van de logische SQL-query en de tekst van de fysieke SQL-query's. De hashcode van de fysieke SQL, afkomstig van willekeurige SQL-query's die vanuit de Oracle BI server worden uitgevoerd, wordt opgenomen in de kolom ACTION in de V$SQL-tabel. Zie voor meer informatie: S_NQ_ACCT-record koppelen met het BI querylogbestand.
Zie Gebruik controleren en Gebruikscontroletabellen voor meer informatie over de diverse kolommen voor gebruikscontrole.
Instellen
Nu u de PHYSICAL_HASH_ID weet te vinden, kunt u een SQL-statement schrijven dat de SQL-query die in de database wordt uitgevoerd, correleert met een specifieke analyse die in Oracle Analytics wordt uitgevoerd.
Wanneer de tabellen voor gebruikscontrole zich in dezelfde database-instance bevinden als het datawarehouse, kunt u een enkele query schrijven om de SQL_ID te vinden
Wanneer de tabellen voor gebruikscontrole en het datawarehouse zich in verschillende Oracle database-instances bevinden, moet u twee query's uitvoeren. Eerst voert u een query uit om de PHYSICAL_HASH_ID van de te onderzoeken query uit de tabellen voor gebruikscontrole op te vragen. Vervolgens vraagt u de SQL_ID op van de V$SQL-tabel met behulp van de waarde van de PHYSICAL_HASH_ID om de kolom ACTION te filteren.
Voorbeeld 1
In dit voorbeeld gaat u ervan uit dat de tabellen voor gebruikscontrole en het datawarehouse zich op dezelfde locatie bevinden. Alle kolommen uit de tabellen voor gebruikscontrole en de V$SQL-tabellen kunnen in de query worden gebruikt, maar in dit voorbeeld selecteert u de volgende subset:
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;
Voorbeeld 2
In dit voorbeeld gaat u ervan uit dat de tabellen voor gebruikscontrole en het datawarehouse zich in verschillende Oracle-databases bevinden.
Schrijf eerst een query met betrekking tot de tabellen voor gebruikscontrole om de PHYSICAL_HASH_ID op te halen:
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;
Schrijf vervolgens een query met betrekking tot de V$SQL-tabel om de SQL_ID op te halen met behulp van de waarden van PHYSICAL_HASH_ID als filter:
select o.action, o.sql_id, o.sql_text from v$sql o where o.action = ‘<physical_hash_id>’ order by o.sql_id;
Hebt u een RAC-database in plaats van de V$SQL-tabel, dan gebruikt u de GV$SQL-tabel om de SQL_ID op te halen:
select o.action, o.sql_id, o.sql_text from gv$sql o where o.action = '<physical_hash_id>' order by o.sql_id;
Deze methode werkt niet als u problemen met initialisatieblokken wilt oplossen, omdat de tabel (S_NQ_INITBLOCK) voor het controleren van het gebruik van initialisatieblokken geen fysieke hash-ID bevat.
Voorbeeld 3
In dit voorbeeld gebruikt u het onderwerpgebied 'A: voorbeeldomzet' en selecteert u slechts enkele kolommen uit de tabellen voor gebruikscontrole.
Vanuit ‘A: voorbeeldomzet’ voert u de volgende query uit:
[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 ]]
Wanneer u start_ts, id, hash_id, query_src_cd en query_text uit de logische tabel voor gebruikscontrole selecteert, krijgt u de ID-waarde E841EBB79217270A660CDD3EFB5D986C.
Selecteer vervolgens logical_query_id, hash_id, physical_hash_id en query_text uit de fysieke tabel voor gebruikscontrole, waarin LOGICAL_QUERY_ID de waarde E841EBB79217270A660CDD3EFB5D986C heeft.
Wanneer u in het logbestand 'Sessies beheren' kijkt, ziet u dat de SQL-code in de kolom QUERY_TEXT door de logische query wordt voortgebracht:
[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
]]Selecteer ten slotte action, sql_id en sql_text uit de V$SQL-tabel waarbij Action de waarde bd6708b8 heeft.
De query in de kolom sql_text is dezelfde SQL-code als in de fysieke tabel voor gebruikscontrole. Hieraan ziet u dat de sql_id voor de desbetreffende query 1gxhc0acmztwk is.
Als u dezelfde query nogmaals uitvoert, wordt er nog een rij met een nieuwe ID aan de logische tabel voor gebruikscontrole toegevoegd, maar de hash_id is dezelfde.
Ook wordt nog een rij met een nieuwe logical_query_id aan de fysieke tabel voor gebruikscontrole toegevoegd, maar de hash_id en de physical_hash_id blijven hetzelfde.
Voorbeeld 4
De physical_hash_id wordt ook opnieuw gebruikt als u een soortgelijke logische query uitvoert met betrekking tot een onderwerpgebied dat is gebaseerd op dezelfde logische en fysieke modellen. Als u bijvoorbeeld een soortgelijke query uitvoert met betrekking tot 'C: bemonsteringskosten':
[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 ]]
Door deze logische query wordt dezelfde SQL-code voortgebracht als in de query die u met betrekking tot 'A: voorbeeldomzet' hebt uitgevoerd:
[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
]]Gebruik eerst de logische tabel voor gebruikscontrole om de ID te vinden. Zoals u ziet, verschilt de logische hash_id van de query die met betrekking tot 'A: voorbeeldomzet' is uitgevoerd.
U ziet in de fysieke tabel voor gebruikscontrole dat, hoewel de logische hash_id anders is, de physical_hash_id dezelfde is.
Voorbeeld 5
In dit voorbeeld voert u dezelfde query's uit, maar deze keer klikt u op Vernieuwen. Met de variabele OBIS_REFRESH_CACHE=1 wordt het vernieuwen aangegeven. Zoals verwacht wordt er nog een rij in de logische tabel voor gebruikscontrole ingevoegd, maar deze heeft een andere logical hash_id.
Wanneer u echter een query uitvoert op de fysieke tabel voor gebruikscontrole, ziet u dat alle query's dezelfde physical_hash_id. hebben
Aangezien deze vier logische query's allemaal dezelfde physical_hash_id hebben, ziet u slechts één record in de V$SQL-tabel voor deze query.
Voorbeeld 6
U kunt dezelfde methode gebruiken voor het oplossen van problemen met query's voor visualisatie van gegevens op basis van een onderwerpgebied of een databaseverbinding. Voor databaseverbindingen geldt dit voor zowel visualisaties van gegevens als query's voor het vullen van gegevenssets.
Uit de waarde van de kolom QUERY_SRC_CD kunt u het querytype bepalen, dat wil zeggen visualisatie van gegevens, gegevensset, analyse, ODBC enzovoort. Zo hebben bijvoorbeeld query's voor visualisatie van gegevens de waarde Visual Analyzer, een query voor het vullen van een gegevensset heeft de waarde data-prep, analyses hebben de waarde Report en query's voor sommige interne ODBC-procedureaanroepen hebben de waarde Soap.
Als u een SQL-statement wilt genereren voor een visualisatie van gegevens, moet u 'Gegevenstoegang' wijzigen in 'Live' voor de onderliggende gegevensset.
In dit voorbeeld maakt u een query voor een visualisatie van gegevens (soortgelijk aan de vorige query's) met betrekking tot een gegevensset op basis van een verbinding met een Oracle database:
[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' */
Hierdoor wordt de volgende SQL-code voortgebracht:
[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 <= 125001In de logische tabel voor gebruikscontrole kunt u de hash_id vinden die wordt gebruikt als een filter voor de fysieke tabel voor gebruikscontrole.
In de fysieke tabel voor gebruikscontrole vindt u de physical_hash_id.
Wanneer u een query uitvoert op de V$SQL-tabel, vindt u opnieuw de SQL_ID.
In het logbestand 'Sessies beheren' kunt u ook de logische hash_id en de physical_hash_id vinden (zie de gemarkeerde secties in de vorige logbestandvoorbeelden). Dat betekent dat u de SQL_ID in het logbestand zelf kunt vinden. Het voordeel van het gebruik van de tabellen voor gebruikscontrole is dat logbestandsgegevens in 'Sessies beheren' tijdelijk zijn. Ze gaan dus verloren, tenzij u tegelijkertijd met het uitvoeren van de query hash-ID's verzamelt.
Gegevens worden echter alleen naar de tabellen voor gebruikscontrole geschreven wanneer een query wordt voltooid. Dus als u problemen aan het oplossen bent met een langdurige query die nog niet is voltooid en u wilt de sql_id bepalen, dan kunt u de logische hash_id en physical_hash_id uit het logbestand 'Sessies beheren' halen.
Als u problemen met een SQL-statement aan het oplossen bent en u wilt de database onderzoeken, kunt u informatie aan de tabellen voor gebruikscontrole en de V$SQL-systeemtabel (of GV$SQL) toevoegen om snel de SQL_ID te vinden voor het SQL-statement dat u onderzoekt.