Trovare il valore di SQL_ID per una query di Oracle Analytics
Quando si ottimizzano le query per Oracle Analytics, è necessario analizzare il modello dati, la progettazione dei report, la progettazione dei dashboard, la configurazione di rete e talvolta il database.
Tabella V$SQL
Per analizzare il database, è necessario conoscere il valore di SQL_ID dell'istruzione SQL, in modo da tenere traccia della query specifica in Active Session History (ASH), Automatic Workload Repository (AWR), e Oracle SQLTXPLAIN (SQLT).
In questo argomento viene illustrato come trovare il valore di SQL_ID nelle tabelle di registrazione dell'uso di Oracle Analytics e nella tabella di sistema del database Oracle denominata V$SQL (o GV$SQL per un database RAC).
V$SQL è una tabella di sistema del database Oracle in cui vengono registrate le statistiche per le singole query SQL. La tabella include una riga per ciascuna istruzione SQL eseguita e ogni riga è identificata in modo univoco dal valore nella colonna SQL_ID. È possibile utilizzare questo valore di SQL_ID per tenere traccia di una determinata istruzione SQL nel database Oracle.
La tabella V$SQL (o GV$SQL per un database RAC) contiene molte informazioni utili sulle query.
Tabelle di registrazione dell'uso
Nelle tabelle di registrazione dell'uso non viene registrato direttamente il valore di SQL_ID, ma è possibile rintracciare facilmente il valore di SQL_ID nella tabella V$SQL (o GV$SQL per un database RAC).
Quando si abilita la registrazione dell'uso in Oracle Analytics, tutte le query vengono registrate in due tabelle:
S_NQ_ACCT: contiene query logicheS_NQ_DB_ACCT: contiene query fisiche
La query logica viene registrata nella tabella delle query logiche, mentre la query fisica (o le query generate dalla query logica) viene registrata nella tabella delle query fisiche.
È possibile unire le tabelle logica e fisica nella colonna dell'ID della query logica. La colonna dell'ID della query logica nella tabella logica è denominata ID, mentre nella tabella fisica è denominata LOGICAL_QUERY_ID.
La chiave per trovare il valore di SQL_ID è la colonna PHYSICAL_HASH_ID nella tabella fisica. Il valore di PHYSICAL_HASH_ID è scritto anche nella colonna ACTION della tabella V$SQL (o GV$SQL).
Oracle BI Server calcola il codice hash in base al testo della query SQL logica e delle query SQL fisiche. Il codice hash SQL fisico di qualsiasi query SQL eseguita da Oracle BI Server viene registrato nella colonna ACTION della tabella V$SQL. Per ulteriori informazioni, vedere Associare il record S_NQ_ACCT al log delle query BI.
Per ulteriori informazioni sulle diverse colonne di registrazione dell'uso, vedere Registrare le informazioni sull'uso e Comprendere le tabelle di registrazione dell'uso.
Impostazione
Dopo aver trovato il valore di PHYSICAL_HASH_ID, è possibile scrivere un'istruzione SQL che metta in relazione la query SQL in esecuzione nel database con un'analisi specifica in esecuzione in Oracle Analytics.
Quando le tabelle di registrazione dell'uso si trovano nella stessa istanza di database del data warehouse, è possibile scrivere un'unica query per trovare il valore di SQL_ID.
Se le tabelle di registrazione dell'uso e il data warehouse si trovano in istanze di database Oracle diverse, è necessario eseguire due query. Eseguire in primo luogo una query per recuperare il valore di PHYSICAL_HASH_ID della query che si desidera analizzare nelle tabelle di registrazione dell'uso. In secondo luogo, estrarre il valore di SQL_ID dalla tabella V$SQL utilizzando il valore di PHYSICAL_HASH_ID per filtrare la colonna ACTION.
Esempio 1
In questo esempio, si supponga che le tabelle di registrazione dell'uso e il data warehouse condividano la stessa posizione. È possibile utilizzare nella query, una o tutte le colonne delle tabelle di registrazione dell'uso e della tabella V$SQL, ma in questo esempio si seleziona il seguente 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;
Esempio 2
In questo esempio, si supponga che le tabelle di registrazione dell'uso e il data warehouse si trovino in database Oracle diversi.
Scrivere in primo luogo una query sulle tabelle di registrazione dell'uso per ottenere il valore di 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;
In secondo luogo, scrivere una query sulla tabella V$SQL per recuperare il valore di SQL_ID, utilizzando i valori di PHYSICAL_HASH_ID come 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;
Se si dispone di un database RAC, per recuperare il valore di SQL_ID è necessario utilizzare la tabella GV$SQL anziché V$SQL:
select o.action, o.sql_id, o.sql_text from gv$sql o where o.action = '<physical_hash_id>' order by o.sql_id;
Questo metodo non funziona se si sta eseguendo la risoluzione dei problemi dei blocchi di inizializzazione, in quanto la tabella di registrazione dell'uso dei blocchi di inizializzazione (S_NQ_INITBLOCK) non contiene alcun ID hash fisico.
Esempio 3
In questo esempio viene utilizzata l'area argomenti A - Sample Sales e vengono selezionate solo alcune colonne delle tabelle di registrazione dell'uso.
Da A - Sample Sales, eseguire la query riportata di seguito:
[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 ]]
Quando si seleziona start_ts, id, hash_id, query_src_cd e query_text nella tabella logica di registrazione dell'uso, si ottiene il valore E841EBB79217270A660CDD3EFB5D986C per ID.
A questo punto, selezionare logical_query_id, hash_id, physical_hash_id e query_text nella tabella fisica di registrazione dell'uso, dove il valore di LOGICAL_QUERY_ID è E841EBB79217270A660CDD3EFB5D986C.
Quando si esamina il log di gestione delle sessioni, la query logica genera il codice SQL riportato nella colonna 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
]]Infine, selezionare action, sql_id e sql_text nella tabella V$SQL dove il valore di Action è bd6708b8.
La query nella colonna sql_text utilizza lo stesso codice SQL visibile nella tabella fisica di registrazione dell'uso. In questa tabella, il valore di sql_id per la query in questione è 1gxhc0acmztwk.
Se si esegue di nuovo la stessa query, alla tabella logica di registrazione dell'uso viene aggiunta una nuova riga con un nuovo ID, ma lo stesso valore per hash_id.
Allo stesso modo, un'altra riga viene aggiunta alla tabella fisica di registrazione dell'uso con un nuovo valore per logical_query_id, mentre entrambi i valori di hash_id e physical_hash_id rimangono invariati.
Esempio 4
Il valore di physical_hash_id viene riutilizzato anche se si esegue una query logica simile su un'area argomenti basata sugli stessi modelli logici e fisici. Nell'esempio, se si esegue una query simile su 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 ]]
Questa query logica genera lo stesso codice SQL visibile nella query eseguita su 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
]]Utilizzare in primo luogo la tabella logica di registrazione dell'uso per trovare l'ID. È possibile notare che il valore logico per hash_id è diverso da quello della query eseguita su A – Sample Sales.
Nella tabella fisica di registrazione dell'uso, sebbene il valore logico per hash_id sia diverso, il valore per physical_hash_id è lo stesso.
Esempio 5
In questo esempio vengono eseguite le stesse query, ma questa volta si fa clic su Aggiorna. Si noti come la variabile OBIS_REFRESH_CACHE=1 denota l'aggiornamento. Come previsto, viene inserita un'altra riga nella tabella logica di registrazione dell'uso, ma con un valore logico diverso per logical hash_id.
Tuttavia, quando si esegue la query nella tabella fisica di registrazione dell'uso, tutte le query hanno lo stesso valore per physical_hash_id.
Poiché tutte e quattro le query logiche hanno lo stesso valore per physical_hash_id, nella tabella V$SQL è visibile un solo record per questa query.
Esempio 6
È possibile utilizzare lo stesso metodo per risolvere i problemi delle query di visualizzazione dati in base a un'area argomenti (RPD) o una connessione al database. Per le connessioni al database, ciò include sia le query di visualizzazioni dati che quelle utilizzate per inserire dati nei data set.
Il valore della colonna QUERY_SRC_CD consente di determinare il tipo di query, ossia visualizzazione dati, inserimento dati nel data set, analisi, ODBC e così via. Ad esempio, le query di visualizzazione dati, le query utilizzate per inserire dati in un data set, le query di analisi e le query per alcune chiamate di procedura interna ODBC sono identificate, rispettivamente, dai seguenti valori: Visual Analyzer, data-prep, Report e Soap.
Per generare un'istruzione SQL per una visualizzazione dati, è necessario modificare il valore Data Access in Live per il data set sottostante.
In questo esempio viene creata una query di visualizzazione dati (simile alle query precedenti) su un data set in base a una connessione a un database 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' */
Tale query genera il seguente codice 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 <= 125001Nella tabella logica di registrazione dell'uso è riportato il valore per hash_id utilizzato come filtro per la tabella fisica di registrazione dell'uso.
Nella tabella fisica di registrazione dell'uso è riportato il valore per physical_hash_id.
Quando si esegue una query sulla tabella V$SQL, viene nuovamente restituito il codice per SQL_ID:
I valori logici per physical_hash_id e hash_id sono riportati anche nel log di gestione delle sessioni (vedere le sezioni evidenziate negli esempi dei log precedenti). Di conseguenza, il valore per SQL_ID è riportato nel log stesso. Tuttavia, utilizzare le tabelle di registrazione dell'uso risulta più vantaggioso in quanto le voci nel log di gestione delle sessioni sono temporanee e pertanto vanno perse, a meno che non si raccolgano gli ID hash nel momento stesso in cui si esegue la query.
Tuttavia, i dati vengono scritti nelle tabelle di registrazione dell'uso solo una volta completata una query. Pertanto, se si sta eseguendo la risoluzione dei problemi di una query con tempi di esecuzione lunghi che non è ancora stata completata e si desidera determinare il valore per sql_id, è possibile recuperare il valore per physical_hash_id e il valore logico per hash_id dal log di gestione delle sessioni.
Se si sta eseguendo la risoluzione dei problemi di un'istruzione SQL ed è necessario analizzare il database, è possibile unire le informazioni nelle tabelle di registrazione dell'uso e nella tabella di sistema V$SQL (o GV$SQL) per trovare rapidamente il valore di SQL_ID per l'istruzione SQL che si sta analizzando.