Căutaţi SQL_ID pentru o interogare Oracle Analytics
Când optimizaţi interogările pentru Oracle Analytics, luaţi în considerare modelul de date, designul rapoartelor, designul dashboardurilor şi configuraţia reţelei, iar uneori trebuie să investigaţi baza de date.
Tabelul V$SQL
Pentru investigaţiile bazei de date, trebuie să ştiţi ce SQL_ID are instrucţiunea SQL pentru a urmări interogarea respectivă în istoricul sesiunilor active (ASH), Automatic Workload Repository (AWR), şi Oracle SQLTXPLAIN (SQLT).
Acest subiect vă arată cum să căutaţi un SQL_ID explorând tabelele de urmărire a utilizării Oracle Analytics şi tabelul de sistem de baze de date Oracle denumit V$SQL (sau GV$SQL pentru o bază de date RAC).
Tabelul V$SQL este un tabel de sistem de baze de date Oracle, care urmăreşte statisticile pentru interogări SQL individuale. În tabel există câte un rând pentru fiecare instrucţiune SQL pe care o rulaţi şi fiecare rând este identificat în mod unic de coloana SQL_ID. Puteţi utiliza acest SQL_ID pentru a urmări o instrucţiune SQL dată prin toată baza de date Oracle.
Veţi găsi multe informaţii utile despre interogări în tabelul V$SQL (sau GV$SQL pentru o bază de date RAC).
Tabele de urmărire utilizare
Tabelele de urmărire a utilizării nu urmăresc în mod direct SQL_ID, dar puteţi urmări uşor SQL_ID înapoi la tabelul V$SQL (sau GV$SQL pentru o bază de date RAC).
Când activaţi Urmărire utilizare în Oracle Analytics, toate interogările sunt urmărite în două tabele:
S_NQ_ACCT: Conţine interogări logiceS_NQ_DB_ACCT: Conţine interogări fizice
Interogarea logică este jurnalizată în tabelul de interogări logice, iar interogarea fizică (sau interogările generate dinamic de interogarea logică) este jurnalizată în tabelul de interogări fizice.
Puteţi efectua join pentru tabelele logic şi fizic în coloana cu ID-ul interogării logice. Coloana cu ID-ul interogării logice din tabelul logic este ID şi în tabelul fizic este LOGICAL_QUERY_ID.
Cheia găsirii SQL_ID este coloana PHYSICAL_HASH_ID din tabelul fizic. Valoarea PHYSICAL_HASH_ID este scrisă şi în tabelul V$SQL (sau tabelul GV$SQL), în coloana ACTION.
Oracle BI Server calculează codul hash din textul interogării SQL logice şi din textul interogărilor SQL fizice. Dacă există interogări SQL care rulează de pe Oracle BI Server, codul hash SQL fizic este înregistrat în coloana ACTION din tabelul V$SQL. Pentru informaţii suplimentare, consultaţi Asociaţi înregistrarea S_NQ_ACCT cu jurnalul de interogări BI.
Pentru mai multe informaţii despre diferitele coloane de urmărire a utilizării, consultaţi Urmărire utilizare şi Înţelegerea tabelelor de urmărire a utilizării.
Configurare
Acum că ştiţi unde să găsiţi PHYSICAL_HASH_ID, puteţi scrie o instrucţiune SQL care să coreleze instrucţiunea SQL care rulează în baza de date cu o anumită analiză care rulează în Oracle Analytics.
Când tabelele Urmărire utilizare se află în aceeaşi instanţă de bază de date ca şi data warehouse-ul, puteţi să scrieţi o singură interogare pentru a căuta SQL_ID.
Dacă tabelele Urmărire utilizare şi data warehouse-ul se află în instanţe de bază de date Oracle diferite, trebuie să rulaţi două interogări. Mai întâi, rulaţi o interogare pentru a extrage PHYSICAL_HASH_ID al interogării pe care doriţi să o investigaţi, din tabelele Urmărire utilizare. Apoi extrageţi SQL_ID din tabelul V$SQL utilizând valoarea PHYSICAL_HASH_ID pentru a filtra coloana ACTION.
Exemplul 1
În acest exemplu, presupuneţi că tabelele Urmărire utilizare şi data warehouse-ul sunt co-locate. În interogare pot fi utilizate oricare sau toate coloanele din tabelele Urmărire utilizare şi V$SQL, dar în acest exemplu, selectaţi următorul 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;
Exemplul 2
În acest exemplu, presupuneţi că tabelele Urmărire utilizare şi data warehouse-ul se află în baze de date Oracle diferite.
Mai întâi, scrieţi o interogare de rulat în tabelele Urmărire utilizare pentru a prelua 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;
Apoi scrieţi o interogare de rulat în tabelul V$SQL pentru a prelua SQL_ID, utilizând valorile PHYSICAL_HASH_ID ca filtru.
select o.action, o.sql_id, o.sql_text from v$sql o where o.action = ‘<physical_hash_id>’ order by o.sql_id;
Dacă aveţi o bază de date RAC, în loc de tabelul V$SQL, utilizaţi tabelul GV$SQL pentru a prelua 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;
Această metodă nu funcţionează dacă rezolvaţi problemele blocurilor de iniţializare, deoarece tabelul de urmărire a utilizării blocurilor de iniţializare (S_NQ_INITBLOCK) nu conţine niciun ID hash fizic.
Exemplul 3
În acest exemplu, utilizaţi zona de subiect A - Vânzări eşantion şi selectaţi doar câteva coloane din tabelele Urmărire utilizare.
Din A - Vânzări eşantion, rulaţi următoarea interogare:
[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 ]]
Când selectaţi start_ts, id, hash_id, query_src_cd şi query_text din tabelul logic de urmărire a utilizării, preluaţi valoarea pentru ID E841EBB79217270A660CDD3EFB5D986C.
Apoi selectaţi logical_query_id, hash_id, physical_hash_id şi query_text din tabelul fizic de urmărire a utilizării, unde LOGICAL_QUERY_ID este E841EBB79217270A660CDD3EFB5D986C.
Când priviţi jurnalul Administrare sesiuni, interogarea logică generează dinamic codul SQL din coloana 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
]]La final, selectaţi action, sql_id şi sql_text din tabelul V$SQL, unde Action este bd6708b8.
Interogarea din coloana sql_text este acelaşi cod SQL observat în tabelul fizic de urmărire a utilizării. Aici vedeţi că sql_id pentru interogarea respectivă este 1gxhc0acmztwk.
Dacă rulaţi din nou aceeaşi interogare, este adăugat un alt rând în tabelul logic de urmărire a utilizării cu un ID nou dar hash_id este acelaşi.
În mod similar, un alt rând este adăugat în tabelul fizic de urmărire a utilizării, cu un logical_query_id nou, dar hash_id şi physical_hash_id rămân neschimbate.
Exemplul 4
Şi physical_hash_id este reutilizat dacă rulaţi o interogare logică similară în zona de subiect, care este bazată pe aceleaşi modele logic şi fizic. De exemplu, dacă rulaţi o interogare similară pentru C - Costuri eşantion:
[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 ]]
Această interogare logică generează dinamic acelaşi cod SQL observat la rularea pentru A – Vânzări eşantion:
[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
]]Mai întâi, utilizaţi tabelul logic de urmărire a utilizării pentru a căuta ID-ul. Puteţi observa că hash_id logic este diferit de interogarea rulată pentru A – Vânzări eşantion:
În tabelul fizic de urmărire a utilizării, observaţi că deşi hash_id logic este diferit, physical_hash_id este acelaşi.
Exemplul 5
În acest exemplu, rulaţi aceleaşi interogări, dar de data aceasta faceţi clic pe Reactualizare. Observaţi variabila OBIS_REFRESH_CACHE=1 pentru a denota reactualizarea. Conform aşteptărilor, este inserat un alt rând în tabelul logic de urmărire a utilizării, dar are un logical hash_id diferit.
Dar când interogaţi tabelul fizic de urmărire a utilizării, puteţi observa că toate interogările au acelaşi physical_hash_id.
Deoarece aceste patru interogări logice au toate acelaşi physical_hash_id, veţi vedea o singură înregistrare în tabelul V$SQL pentru această interogare.
Exemplul 6
Puteţi utiliza aceeaşi metodă pentru a rezolva problemele interogărilor pentru vizualizările de date, pe baza conexiunii la o zonă de subiect (RPD) sau la o bază de date. Pentru conexiunile la baza de date, aceasta include atât vizualizările de date, cât şi interogările utilizate pentru a popula seturile de date.
Puteţi stabili care este tipul interogării, adică, vizualizare de date, set de date, analiză, ODBC etc., după valoarea din coloana QUERY_SRC_CD. De exemplu, interogările pentru vizualizările de date au valoarea Visual Analyzer, o interogare utilizată pentru a popula un set de date are valoarea data-prep, analizele au valoarea Report, iar interogările pentru unele apeluri procedurale ODBC interne au valoarea Soap.
Pentru a genera o instrucţiune SQL pentru o vizualizare de date, trebuie să modificaţi Acces la date la Live pentru setul de date de bază.
În acest exemplu, creaţi o interogare de vizualizare de date (similară cu interogările anterioare) pentru un set de date pe baza unei conexiuni la o bază de date 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' */
Care generează dinamic următorul cod 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 <= 125001Din tabelul logic de urmărire a utilizării, puteţi căuta hash_id care este utiliza ca filtru pentru tabelul fizic de urmărire a utilizării.
Din tabelul fizic de urmărire a utilizării, puteţi să căutaţi physical_hash_id.
Când interogaţi tabelul V$SQL, căutaţi SQL_ID din nou.
Puteţi căuta hash_id şi physical_hash_id logice şi în jurnalul Administrare sesiuni (consultaţi secţiunile evidenţiate în fragmentele de jurnal anterioare). Acest lucru înseamnă că puteţi căuta SQL_ID în jurnalul în sine. Beneficiul utilizării tabelelor de urmărire a utilizării este că intrările de jurnal din Administrare sesiuni sunt temporare, deci, dacă nu colectaţi ID-uri hash în acelaşi timp cu rularea interogării, acestea se pierd.
Dar datele sunt scrise în tabelele de urmărire a utilizării abia la finalizarea interogării. Deci dacă rezolvaţi problemele unei interogări cu rulare îndelungată, care nu a fost finalizată încă, şi doriţi să aflaţi sql_id, puteţi prelua hash_id şi physical_hash_id logice din jurnalul Administrare sesiuni.
Dacă rezolvaţi problemele unei instrucţiuni SQL şi trebuie să investigaţi baza de date, puteţi efectua join pentru informaţiile din tabelele de urmărire a utilizării şi tabelul de sistem V$SQL (sau GV$SQL) pentru a căuta rapid valoarea SQL_ID pentru instrucţiunea SQL pe care o investigaţi.