Finde SQL_ID for en Oracle Analytics-forespørgsel
Når du optimerer forespørgsler for Oracle Analytics, kigger du på datamodellen, rapportdesignet, instrumentbrætdesignet og netværkskonfigurationen, og nogle gange er du nødt til at undersøge databasen.
Tabellen V$SQL
Ved databaseundersøgelser skal du kende SQL-sætningens SQL_ID, så du kan spore den specifikke forespørgsel gennem Active Session History (ASH), Automatic Workload Repository (AWR), og Oracle SQLTXPLAIN (SQLT).
Dette emne viser dig, hvordan du finder SQL_ID ved hjælp af mining af anvendelsessporingstabellerne i Oracle Analytics og systemtabellen i Oracle-databasen med navnet V$SQL (eller GV$SQL for en RAC-database).
Tabellen V$SQL er en Oracle-databasesystemtabel, der sporer statistik for individuelle SQL-forespørgsler. Der er én række i tabellen for hver SQL-sætning, som du kører, og hver række identificeres entydigt af kolonnen SQL_ID. Du kan bruge denne SQL_ID til at spore en given SQL-sætning i hele Oracle-databasen.
Du finder masser af nyttige oplysninger om dine forespørgsler i tabellen V$SQL (eller GV$SQL for en RAC-database).
Anvendelsessporingstabeller
Anvendelsessporingstabeller sporer ikke SQL_ID direkte, men du kan nemt spore SQL_ID tilbage til V$SQL-tabellen (eller GV$SQL for en RAC-database).
Når du aktiverer anvendelsessporing i Oracle Analytics, spores alle forespørgslerne i to tabeller:
S_NQ_ACCT: Indeholder logiske forespørgslerS_NQ_DB_ACCT: Indeholder fysiske forespørgsler
Den logiske forespørgsel logges i den logiske tabel, og den fysiske forespørgsel (eller forespørgsler, der oprettes af den logiske forespørgsel) logges i den fysiske tabel.
Du kan sammenkæde den logiske og den fysiske tabel i kolonnen med id'en for logisk forespørgsel. Kolonnen med id'en for logisk forespørgsel i den logiske tabel er ID, og i den fysiske tabel er det LOGICAL_QUERY_ID.
Nøglen til at finde SQL_ID er kolonnen PHYSICAL_HASH_ID i den fysiske tabel. Værdien i PHYSICAL_HASH_ID skrives også til tabellen V$SQL (eller tabellen GV$SQL) i kolonnen ACTION.
Oracle BI Server beregner en hash-kode fra teksten i den logiske SQL-forespørgsel og teksten i de fysiske SQL-forespørgsler. Hash-koden for den fysiske SQL for enhver SQL-forespørgsel, der køres fra Oracle BI Server, registreres i kolonnen ACTION i tabellen V$SQL. Se Associate S_NQ_ACCT Record with the BI Query Log. for at få flere oplysninger
Se Spore anvendelse og Om anvendelsessporingstabellerne for at få flere oplysninger om de forskellige anvendelsessporingskolonner.
Opsætning
Nu hvor du ved, hvor du finder PHYSICAL_HASH_ID, kan du skrive en SQL-sætning, der korrelerer den SQL-forespørgsel, som kører i databasen, med en specifik analyse, der kører i Oracle Analytics.
Når anvendelsessporingstabellerne findes i den samme databaseinstans som data warehouse, kan du skrive en enkelt forespørgsel for at finde SQL_ID.
Hvis anvendelsessporingstabellerne og dit data warehouse findes i forskellige Oracle-databaseinstanser, skal du køre to forespørgsler. Først kører du en forespørgsel for at hente PHYSICAL_HASH_ID for den forespørgsel, som du vil undersøge, fra anvendelsessporingstabellerne. Derefter henter du SQL_ID fra tabellen V$SQL ved hjælp af værdien i PHYSICAL_HASH_ID for at filtrere kolonnen ACTION.
Eksempel 1
Lad os i dette eksempel antage, at anvendelsessporingstabellerne og data warehouse findes det samme sted. En hvilken som helst af eller alle kolonnerne fra anvendelsessporingstabellerne og tabellen V$SQL kan bruges i forespørgslen, men i dette eksempel vælger du følgende undersæt:
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;
Eksempel 2
Lad os i dette eksempel antage, at anvendelsessporingstabellerne og data warehouse findes i forskellige Oracle-databaser.
Skriv først en forespørgsel på anvendelsessporingstabellerne for at hente 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;
Skriv derefter en forespørgsel på V$SQL for at hente SQL_ID, hvor du bruger værdierne i PHYSICAL_HASH_ID som 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;
Hvis du har en RAC-database, skal du bruge tabellen GV$SQL i stedet for tabellen V$SQL til at hente 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;
Denne metode virker ikke, hvis du foretager fejlfinding af initialiseringsblokke, da anvendelsessporingstabellen for initialiseringsblokke (S_NQ_INITBLOCK) ikke indeholder en fysisk hash-id.
Eksempel 3
I dette eksempel bruger du emneområdet ‘A - Sample Sales’ og vælger kun nogle få kolonner fra anvendelsessporingstabellerne.
Kør følgende forespørgsel fra A - Sample Sales:
[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 ]]
Når du vælger start_ts, id, hash_id, query_src_cd og query_text fra den logiske anvendelsessporingstabel, får du ID-værdien E841EBB79217270A660CDD3EFB5D986C.
Vælg derefter logical_query_id, hash_id, physical_hash_id og query_text fra den fysiske anvendelsessporingstabel, hvor LOGICAL_QUERY_ID er E841EBB79217270A660CDD3EFB5D986C.
Når du kigger på loggen for styring af sessioner, opretter den logiske forespørgsel den SQL-kode, der findes i kolonnen 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
]]Vælg til sidst action, sql_id og sql_text fra tabellen V$SQL, hvor Action er bd6708b8.
Forespørgslen i kolonnen sql_text er den samme SQL-kode som den, der ses i den fysiske anvendelsessporingstabel. Fra denne kan du se, at sql_id for den relevante forespørgsel er 1gxhc0acmztwk.
Hvis du kører den samme forespørgsel igen, føjes der en række mere til den logiske anvendelsessporingstabel med en ny id, men hash_id er den samme.
På samme måde føjes der en række mere til den fysiske anvendelsessporingstabel med en ny logical_query_id, men både hash_id og physical_hash_id forbliver den samme.
Eksempel 4
physical_hash_id genbruges også, hvis du kører en lignende logisk forespørgsel på et emneområde, der er baseret på de samme logiske og fysiske modeller. Her kører du for eksempel en lignende forespørgsel på 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 ]]
Denne logiske forespørgsel opretter den samme SQL-kode som den, der ses i den forespørgsel, som du kørte på 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
]]Brug først den logiske anvendelsessporingstabel til at finde id'en. Du kan se, at den logiske hash_id er forskellig fra id'en i den forespørgsel, du kørte på A – Sample Sales:
I den fysiske anvendelsessporingstabel kan du se, at selvom den logiske hash_id er anderledes, er physical_hash_id den samme.
Eksempel 5
I dette eksempel kører du de samme forespørgsler, men denne gang klikker du på Opfrisk. Bemærk variablen OBIS_REFRESH_CACHE=1, der angiver opfriskningen. Som forventet indsættes der en række mere i den logiske anvendelsessporingstabel, men den har en anden logical hash_id.
Når du forespørger den fysiske anvendelsessporingstabel, kan du imidlertid se, at alle forespørgslerne har den samme physical_hash_id.
Da disse fire logiske forespørgsler alle har den samme physical_hash_id, ser du kun en enkelt record i tabellen V$SQL for denne forespørgsel:
Eksempel 6
Du kan bruge den samme metode til at foretage fejlfinding af datavisualiseringsforespørgsler, der er baseret på et emneområde (RPD) eller en databaseforbindelse. For databaseforbindelser inkluderer dette både datavisualiseringer og forespørgsler, der bruges til at udfylde datasæt.
Du kan bestemme forespørgselstypen, det vil sige datavisualisering, datasæt, analyse, ODBC osv., fra værdien i kolonnen QUERY_SRC_CD. Datavisualiseringsforespørgsler har for eksempel værdien Visual Analyzer, en forespørgsel, der bruges til at udfylde et datasæt, har værdien data-prep, analyser har værdien Report, og forespørgsler for visse interne ODBC-procedurekald har værdien Soap.
Hvis du vil generere en SQL-sætning for en datavisualisering, skal du ændre Dataadgang til Live for det underliggende datasæt.
I dette eksempel opretter du en datavisualiseringsforespørgsel (på samme måde som de tidligere forespørgsler) på et datasæt, der er baseret på en forbindelse til en 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' */
Derved oprettes følgende SQL-kode:
[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 <= 125001I den logiske anvendelsessporingstabel kan du finde hash_id, der bruges som et filter på den fysiske anvendelsessporingstabel.
I den fysiske anvendelsessporingstabel kan du finde physical_hash_id.
Når du forespørger tabellen V$SQL, finder du SQL_ID igen:
Du kan også finde den logiske hash_id og physical_hash_id i loggen Håndter sessioner (se de fremhævede sektioner i de tidligere logeksempler). Dette betyder, at du kan finde SQL_ID i selve loggen. Fordelen ved at bruge anvendelsessporingstabellerne er, at logposter i Håndter sessioner er midlertidige, så medmindre du indsamler hash-id'er, samtidig med at du kører forespørgslen, går de tabt.
Data skrives imidlertid kun til anvendelsessporingstabellerne, når en forespørgsel fuldføres. Så hvis du foretager fejlfinding af en forespørgsel, der kører i lang tid og endnu ikke er fuldført, og du vil bestemme sql_id, kan du hente den logiske hash_id og physical_hash_id fra loggen Håndter sessioner.
Hvis du foretager fejlfinding af en SQL-sætning, og du skal undersøge databasen, kan du sammenkæde oplysninger i anvendelsessporingstabellerne og systemtabellen V$SQL (eller GV$SQL) for hurtigt at finde SQL_ID for den SQL-sætning, som du undersøger.