Vyhľadanie hodnoty SQL_ID pre dopyt služby Oracle Analytics

Pri ladení dopytov pre službu Oracle Analytics prihliadate na dátový model, návrh zostavy, návrh panela a konfiguráciu siete a niekedy je potrebné preskúmať aj databázu.

Tabuľka V$SQL

Pri skúmaní databázy potrebujete poznať hodnotu SQL_ID príkazu SQL, aby ste mohli sledovať konkrétny dopyt prostredníctvom histórie aktívnych relácií (ASH), depozitára AWR a nástroja Oracle SQLTXPLAIN (SQLT).

V tejto téme sa dozviete, ako vyhľadať hodnotu SQL_ID dolovaním dát v tabuľkách sledovania použitia služby Oracle Analytics a v systémovej tabuľke databázy Oracle s názvom V$SQL (alebo GV$SQL, ak ide o databázu RAC).

Tabuľka V$SQL je systémová tabuľka databázy Oracle, ktorá sleduje štatistiky pre jednotlivé dopyty SQL. Pre každý spustený príkaz SQL existuje v tabuľke jeden riadok a každý riadok je jednoznačne identifikovaný stĺpcom SQL_ID . Pomocou tejto hodnoty SQL_ID môžete sledovať daný príkaz SQL v celej databáze Oracle.

V tabuľke V$SQL (alebo v tabuľke GV$SQL, ak ide o databázu RAC) nájdete množstvo užitočných informácií o svojich dopytoch.

Tabuľky sledovania použitia

Tabuľky sledovania použitia nesledujú priamo hodnotu SQL_ID, ale môžete ľahko spätne trasovať hodnotu SQL_ID do tabuľky V$SQL (alebo tabuľky GV$SQL, ak ide o databázu RAC).

Po aktivovaní sledovania použitia v službe Oracle Analytics sa všetky dopyty sledujú v dvoch tabuľkách:

  • S_NQ_ACCT: obsahuje logické dopyty
  • S_NQ_DB_ACCT: obsahuje fyzické dopyty

Logický dopyt sa protokoluje v tabuľke logických dopytov a fyzický dopyt (alebo dopyty nasadené logickým dopytom) sa protokoluje v tabuľke fyzických dopytov.

Tabuľky logických a fyzických dopytov môžete spojiť na základe stĺpca ID logického dopytu. Stĺpcom ID logického dopytu v logickej tabuľke je stĺpec ID a vo fyzickej tabuľke je to stĺpec LOGICAL_QUERY_ID.

Kľúčom k vyhľadaniu hodnoty SQL_ID je stĺpec PHYSICAL_HASH_ID vo fyzickej tabuľke. Hodnota PHYSICAL_HASH_ID sa zapíše aj do tabuľky V$SQL (alebo tabuľky GV$SQL) v stĺpci ACTION.

Oracle BI Server vypočíta hašovací kód z textu logického dopytu SQL a z textu fyzických dopytov SQL. Hašovací kód fyzického dopytu SQL každého dopytu SQL, ktorý sa spustí na serveri Oracle BI Server, sa zaznamená v stĺpci ACTION v tabuľke V$SQL. Ďalšie informácie nájdete v časti Priradenie záznamu S_NQ_ACCT k protokolu dopytov BI.

Ďalšie informácie o rôznych stĺpcoch sledovania použitia nájdete v témach Sledovanie použitia a Základné informácie o tabuľkách sledovania použitia.

Nastavenie

Teraz, keď viete, kde nájsť hodnotu PHYSICAL_HASH_ID, môžete napísať príkaz SQL na koreláciu dopytu SQL, ktorý je spustený v databáze, s konkrétnou analýzou, ktorá je spustená v službe Oracle Analytics.

Ak sa tabuľky sledovania použitia nachádzajú v rovnakej inštancii databázy ako dátový sklad, môžete napísať jeden dopyt na vyhľadanie hodnoty SQL_ID.

Ak sa tabuľky sledovania použitia a dátový sklad nachádzajú v rôznych inštanciách databázy Oracle, je potrebné spustiť dva dopyty. Najprv spustite dopyt na získanie hodnoty PHYSICAL_HASH_ID dopytu, ktorý chcete preskúmať, z tabuliek sledovania použitia. Potom získajte hodnotu SQL_ID z tabuľky V$SQL pomocou hodnoty PHYSICAL_HASH_ID na filtrovanie stĺpca ACTION.

Príklad 1

V tomto príklade vychádzame z predpokladu, že tabuľky sledovania použitia a dátový sklad sa nachádzajú v rovnakej inštancii databázy. V dopyte je možné použiť ľubovoľný stĺpec alebo všetky stĺpce z tabuľky sledovania použitia a tabuľky V$SQL, ale v tomto príklade vyberiete nasledujúcu podmnožinu:

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;

Príklad 2

V tomto príklade vychádzame z predpokladu, že tabuľky sledovania použitia a dátový sklad sa nachádzajú v rôznych databázach Oracle.

Najprv napíšte dopyt na tabuľky sledovania použitia na získanie hodnoty 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;

Následne napíšte dopyt na tabuľku V$SQL na získanie hodnoty SQL_ID s použitím hodnôt PHYSICAL_HASH_ID ako filtra:

select

o.action,

o.sql_id,

o.sql_text

from

v$sql o

where

o.action = ‘<physical_hash_id>’

order by o.sql_id;

Ak máte databázu RAC, namiesto tabuľky V$SQL použijete na získanie hodnoty SQL_ID tabuľku GV$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;

Táto metóda nefunguje, ak riešite problémy s inicializačnými blokmi, pretože tabuľka sledovania použitia inicializačných blokov (S_NQ_INITBLOCK) neobsahuje hašovacie ID fyzického dopytu.

Príklad 3

V tomto príklade použijete tematickú oblasť A - Sample Sales a vyberiete len niekoľko stĺpcov v tabuľkách sledovania použitia.

Z oblasti A - Sample Sales spustíte nasledujúci dopyt:

[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

]]

Keď z logickej tabuľky sledovania použitia vyberiete stĺpce start_ts, id, hash_id, query_src_cdquery_text, získate hodnotu ID E841EBB79217270A660CDD3EFB5D986C.

Popis GUID-5FA7BA53-6767-4839-9341-A6AC0AACDFEB-default.jpg je uvedený nižšie
.jpg

Potom vyberte stĺpce logical_query_id, hash_id, physical_hash_idquery_text z fyzickej tabuľky sledovania použitia, kde LOGICAL_QUERY_ID je E841EBB79217270A660CDD3EFB5D986C.

Popis GUID-6842B138-0FD5-4DA9-A092-B423EB7459C2-default.jpg je uvedený nižšie
.jpg

Keď sa pozriete do protokolu správy relácií, logický dopyt vytvorí kód SQL, ktorý sa nachádza v stĺpci 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

]]

Nakoniec vyberte stĺpce action, sql_id a sql_text z tabuľky V$SQL, kde Action je bd6708b8.

Popis GUID-094521FA-317B-430D-9644-3C3E44D91900-default.jpg je uvedený nižšie
.jpg

Dopyt v stĺpci sql_text je rovnaký ako kód SQL vo fyzickej tabuľke sledovania použitia. Tu vidíte, že hodnota sql_id pre príslušný dopyt je 1gxhc0acmztwk.

Ak znovu spustíte rovnaký dopyt, do logickej tabuľky sledovania použitia sa pridá ďalší riadok s novou hodnotou ID, ale hodnota hash_id je rovnaká.

Popis GUID-82CD3499-E550-4DD4-8C96-E3DB3CE3232E-default.jpg je uvedený nižšie
.jpg

Podobne sa ďalší riadok pridá aj do fyzickej tabuľky sledovania použitia s novou hodnotou logical_query_id, ale hodnoty hash_id a physical_hash_id zostávajú rovnaké.

Popis GUID-BF05E975-45FB-4F40-9AE4-9E40A1124F35-default.jpg je uvedený nižšie
.jpg

Príklad 4

Hodnota physical_hash_id sa znovu použije aj v prípade, že spustíte podobný logický dopyt na tematickú oblasť, ktorá je založená na rovnakých logických a fyzických modeloch. Ak napríklad spustíte podobný dopyt na tematickú oblasť 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

]]

Tento logický dopyt vytvorí rovnaký kód SQL, ktorý ste videli v dopyte spustenom na tematickú oblasť 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

]]

Najprv vyhľadajte ID v logickej tabuľke sledovania použitia. Môžete si všimnúť, že hodnota hash_id logického dopytu sa líši od dopytu spusteného na tematickú oblasť A – Sample Sales:

Popis GUID-16D78632-88BA-446F-A6D7-496DA5662869-default.jpg je uvedený nižšie
.jpg

Vo fyzickej tabuľke sledovania použitia vidieť, že aj keď sa hodnota hash_id logického dopytu líši, hodnota physical_hash_id je rovnaká.

Popis GUID-91049191-7E76-407D-B15B-A74160942148-default.jpg je uvedený nižšie
.jpg

Príklad 5

V ďalšom príklade spustíte rovnaké dopyty, ale tentoraz kliknete na tlačidlo Obnoviť. Všimnite si premennú OBIS_REFRESH_CACHE=1, ktorá označuje obnovenie. Podľa očakávania sa do logickej tabuľky sledovania použitia vloží ďalší riadok, ale má inú hodnotu logical hash_id.

Popis GUID-BFA0DEA8-423A-414C-8BC9-E1B8EA382689-default.jpg je uvedený nižšie
.jpg

Pri vykonávaní dopytu na fyzickú tabuľku sledovania použitia však môžete vidieť, že všetky dopyty majú rovnakú hodnotu physical_hash_id.

Popis GUID-D6CC7144-9177-4CDA-B57A-1A4E4A6615A5-default.jpg je uvedený nižšie
.jpg

Pretože všetky tieto štyri logické dopyty majú rovnakú hodnotu physical_hash_id, v tabuľke V$SQL sa pre tento dopyt zobrazuje len jeden záznam.

Popis GUID-13AE24EE-9C17-48D9-976F-49FF882A5E42-default.jpg je uvedený nižšie
.jpg

Príklad 6

Pomocou rovnakej metódy môžete riešiť problémy s dopytmi na vizualizáciu dát, ktoré sú založené na tematickej oblasti (RPD) alebo databázovom pripojení. V prípade databázových pripojení to zahŕňa vizualizácie dát aj dopyty použité na vyplnenie množín dát.

Typ dopytu, teda či ide o vizualizáciu dát, množinu dát, analýzu, ODBC atď., môžete určiť z hodnoty stĺpca QUERY_SRC_CD. Napríklad dopyty na vizualizáciu dát majú hodnotu Visual Analyzer, dopyt na vyplnenie množiny dát má hodnotu data-prep, analýzy majú hodnotu Report a dopyty pre niektoré volania interných procedúr ODBC majú hodnotu Soap.

Ak chcete generovať príkaz SQL na vizualizáciu dát, pre súvisiacu množinu dát je potrebné zmeniť prístup k dátam na hodnotu Naživo.

V tomto príklade vytvoríte dopyt na vizualizáciu dát (podobný predošlým dopytom) na množinu dát na základe pripojenia k databáze 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' */

Vytvorí sa nasledujúci kód 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 <= 125001

V logickej tabuľke sledovania použitia môžete nájsť hodnotu hash_id, ktorá sa používa ako filter na fyzickú tabuľku sledovania použitia.

Popis GUID-C053C7FB-251A-4021-A850-1F6006BEBFF1-default.jpg je uvedený nižšie
.jpg

Vo fyzickej tabuľke sledovania použitia môžete nájsť hodnotu physical_hash_id.

Popis GUID-EB289F16-56B0-4AFB-8C96-2D7B55680B7B-default.jpg je uvedený nižšie
.jpg

Keď vykonáte dopyt na tabuľku V$SQL, znovu nájdete hodnotu SQL_ID.

Popis GUID-CD805B03-A8AD-40F0-AE84-BD28AC8B6EF8-default.jpg je uvedený nižšie
.jpg

Môžete vyhľadať aj hodnoty hash_id logického dopytu a physical_hash_id v protokole správy relácií (pozrite si zvýraznené sekcie v predošlých príkladoch protokolu). To znamená, že hodnotu SQL_ID môžete vyhľadať zo samotného protokolu. Výhoda tabuliek sledovania použitia spočíva v tom, že položky v protokole správy relácií sú dočasné, takže ak nezhromažďujete hašovacie ID v tom istom čase, v ktorom sa dopyt spúšťa, nezachovajú sa.

Dáta sa však do tabuliek sledovania použitia zapíšu až po dokončení dopytu. Takže ak riešite problémy s dlho prebiehajúcim dopytom, ktorý sa ešte nedokončil, a chcete určiť hodnotu sql_id, hodnoty hash_id logického dopytu a physical_hash_id môžete získať z protokolu správy relácií.

Ak riešite problémy s príkazom SQL a potrebujete prešetriť databázu, môžete spojiť informácie v tabuľkách sledovania použitia a systémovej tabuľke V$SQL (alebo tabuľke GV$SQL) na rýchle vyhľadanie hodnoty SQL_ID pre príkaz SQL, ktorý prešetrujete.