Zjištění SQL_ID pro dotaz služby Oracle Analytics
Při ladění dotazů pro Oracle Analytics se zabýváte datovým modelem, návrhem sestavy, návrhem panelu, konfigurací sítě a někdy je třeba prozkoumat i databázi.
Tabulka V$SQL
Pro zkoumání databáze potřebujete znát SQL_ID příkazu SQL, abyste mohli sledovat konkrétní dotaz prostřednictvím historie aktivní relace (ASH), automatického úložiště pracovního zatížení (AWR) a Oracle SQLTXPLAIN (SQLT).
V tomto tématu se dozvíte, jak zjistit SQL_ID pomocí tabulek pro sledování využití služby Oracle Analytics a systémové tabulky databáze Oracle V$SQL (nebo GV$SQL pro databázi RAC).
V$SQL je systémová tabulka databáze Oracle, která sleduje statistiky jednotlivých dotazů SQL. V tabulce je jeden řádek pro každý spuštěný příkaz SQL a každý řádek je jednoznačně identifikován sloupcem SQL_ID . Tento sloupec SQL_ID můžete použít ke sledování daného příkazu SQL v celé databázi Oracle.
Mnoho užitečných informací o dotazech najdete v tabulce V$SQL (nebo GV$SQL pro databázi RAC).
Tabulky sledování využití
Tabulky sledování využití nesledují přímo SQL_ID, ale můžete snadno vysledovat SQL_ID zpět do tabulky V$SQL (nebo GV$SQL pro databázi RAC).
Pokud v nástroji Oracle Analytics povolíte sledování využití, budou všechny dotazy sledovány ve dvou tabulkách:
S_NQ_ACCT- obsahuje logické dotazyS_NQ_DB_ACCT- obsahuje fyzické dotazy
Logický dotaz je zaznamenán v logické tabulce a fyzický dotaz (nebo dotazy vyvolané logickým dotazem) jsou zaznamenány v tabulce fyzických dotazů.
Logické a fyzické tabulky můžete spojit na základě sloupce ID logického dotazu. Sloupec ID logického dotazu v logické tabulce je ID a ve fyzické tabulce je to LOGICAL_QUERY_ID.
Klíčem k nalezení SQL_ID je sloupec PHYSICAL_HASH_ID ve fyzické tabulce. Hodnota PHYSICAL_HASH_ID se také zapíše do sloupce V$SQL (nebo GV$SQL) ve sloupci ACTION.
Server Oracle BI vypočítá kód hash z textu logického dotazu SQL a textu fyzických dotazů SQL. Fyzický kód hash SQL všech dotazů SQL spuštěných ze serveru Oracle BI je zaznamenán ve sloupci ACTION v V$SQL. Další informace najdete v části Přiřazení záznamu S_NQ_ACCT k protokolu dotazů BI.
Další informace o různých sloupcích pro sledování využití naleznete v částech Sledovat použití a Jak rozumět tabulkám sledování využití.
Nastavení
Nyní, když víte, kde najít PHYSICAL_HASH_ID, je napsání příkazu SQL, který koreluje SQL běžící v databázi s konkrétní analýzou spuštěnou ve službě Oracle Analytics, poměrně jednoduchým úkolem.
Pokud jsou tabulky sledování využití ve stejné instanci databáze jako datový sklad, můžete pro zjištění SQL_ID napsat jediný dotaz.
Pokud jsou tabulky sledování využití a datový sklad v různých instancích databáze Oracle, je třeba spustit dva dotazy. Nejprve spustíte dotaz, který z tabulek sledování využití získá PHYSICAL_HASH_ID dotazu, který chcete prozkoumat. Za druhé získáte SQL_ID z V$SQL pomocí hodnoty PHYSICAL_HASH_ID pro filtrování sloupce ACTION.
Příklady
V tomto příkladu předpokládejme, že tabulky sledování využití a datový sklad jsou umístěny společně. V dotazu lze použít libovolné nebo všechny sloupce z tabulek sledování využití a V$SQL, ale v tomto příkladu vyberete následující 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;
Ve druhém příkladu předpokládejte, že tabulky sledování využití a datový sklad jsou umístěny v různých databázích Oracle.
Nejprve napište dotaz do tabulek sledování využití, abyste získali 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;
Zadruhé napište dotaz na V$SQL , abyste získali SQL_ID, přičemž jako filtr použijte hodnoty PHYSICAL_HASH_ID.
select o.action, o.sql_id, o.sql_text from v$sql o where o.action = ‘<physical_hash_id>’ order by o.sql_id;
Pokud máte databázi RAC, použijete k získání SQL_ID místo tabulky V$SQL tabulku 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;
Tato metoda nefunguje, pokud řešíte problémy s inicializačními bloky, protože tabulka sledování využití inicializačních bloků (S_NQ_INITBLOCK) neobsahuje ID fyzického algoritmu hash.
V dalším příkladu použijete cílovou oblast „A - Ukázkové prodeje“ a vyberete pouze několik sloupců z tabulek sledování využití.
Z oblasti „A - Ukázkové prodeje“ spustíte následující dotaz:
[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 ]]
Při výběru start_ts, id, hash_id, query_src_cd a query_text z logické tabulky sledování využití získáte hodnotu ID E841EBB79217270A660CDD3EFB5D986C:
Dále vyberte logical_query_id, hash_id, physical_hash_id a query_text z fyzické tabulky sledování využití, kde logical_query_id = 'E841EBB79217270A660CDD3EFB5D986C':
Když se podíváte na protokol správy relací, logický dotaz vytvoří SQL, které je ve sloupci 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
]]Nakonec vyberte action, sql_id a sql_text z V$SQL, kde action = 'bd6708b8':
Dotaz ve sloupci sql_text je stejný SQL jako ve fyzické tabulce sledování využití. Z toho je vidět, že sql_id pro daný dotaz je '1gxhc0acmztwk'.
Pokud stejný dotaz spustíte znovu, do logické tabulky sledování využití se přidá další řádek s novým ID, ale hash_id zůstane stejné:
Podobně je do fyzické tabulky sledování využití přidán další řádek s novým logical_query_id, ale hash_id i physical_hash_id zůstávají stejné:
physical_hash_id se také znovu použije, pokud spustíte podobný logický dotaz na cílovou oblast, která je založena na stejném logickém a fyzickém modelu. Například zde spustíte podobný dotaz na „C - Ukázkové náklady“.
[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ý dotaz vyvolá stejný SQL dotaz, který jste spustili pro „A - Ukázkové prodeje“:
[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
]]Nejprve použijte logickou tabulku sledování využití k nalezení ID. Všimněte si, že hodnota logického parametru hash_id se liší od dotazu spuštěného u „A – Ukázkové prodeje“:
Ve fyzické tabulce sledování využití vidíte, že i když se logický parametr hash_id liší, parametr physical_hash_id je stejný:
V dalším příkladu spustíte stejné dotazy (jako výše), ale tentokrát kliknete na tlačítko Obnovit. Všimněte si proměnné OBIS_REFRESH_CACHE=1, která označuje obnovení. Podle očekávání je do logické tabulky sledování využití vložen další řádek, který má však jiné logical hash_id.
Když se však dotazujete na fyzickou tabulku sledování využití, vidíte, že všechny dotazy mají stejné physical_hash_id.
Protože všechny tyto čtyři logické dotazy mají stejné physical_hash_id, zobrazí se v V$SQL pro tento dotaz pouze jeden záznam:
Stejnou metodu můžete použít k řešení problémů s dotazy na vizualizaci dat na základě cílové oblasti (RPD) nebo databázového připojení. V případě databázových připojení to zahrnuje jak vizualizace dat, tak dotazy používané k vyplnění datových sad.
Z hodnoty sloupce QUERY_SRC_CD lze určit typ dotazu, tj. vizualizaci dat, datovou sadu, analýzu, ODBC atd. Například dotazy pro vizualizaci dat mají hodnotu 'Visual Analyzer', dotaz použitý k vyplnění datové sady má hodnotu 'data-prep, analýzy mají hodnotu 'Report' a dotazy pro některá interní volání procedur ODBC mají hodnotu 'Soap'.
Chcete-li vygenerovat příkaz SQL pro vizualizaci dat, musíte pro podkladovou datovou sadu změnit přístup k datům na „Live“.
V tomto příkladu vytvoříte dotaz pro vizualizaci dat (podobný výše uvedeným dotazům) na datovou sadu založenou na připojení k databázi 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' */
Ten vyvolá následující 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 <= 125001V logické tabulce sledování využití můžete najít hash_id, který se používá jako filtr vůči fyzické tabulce sledování využití:
Ve fyzické tabulce sledování využití můžete zjistit physical_hash_id:
Při dotazování na V$SQL opět zjistíte SQL_ID:
Logické hash_id a physical_hash_id najdete také v protokolu správy relací (viz zvýrazněné části ve fragmentech protokolu výše). To znamená, že SQL_ID můžete zjistit ze samotného protokolu. Výhodou použití tabulek pro sledování využití je, že záznamy protokolu v nástroji Správa relací jsou pomíjivé, takže pokud neshromáždíte ID algoritmu hash současně se spuštěním dotazu, budou ztracena.
Data se však do tabulek sledování využití zapisují pouze po dokončení dotazu. Pokud tedy řešíte problém s dlouhotrvajícím dotazem, který ještě nebyl dokončen, a chcete zjistit sql_id, můžete získat logické hash_id a physical_hash_id z protokolu správy relací.
Pokud řešíte problém s příkazem SQL a potřebujete prozkoumat databázi, můžete spojit informace v tabulkách sledování využití a v systémové tabulce V$SQL (nebo GV$SQL) a rychle najít SQL_ID pro zkoumaný příkaz SQL.