Znajdowanie identyfikatora SQL_ID zapytania Oracle Analytics
Podczas dostrajania zapytań dla Oracle Analytics należy przyjrzeć się modelowi danych, projektowi raportu, projektowi pulpitu informacyjnego i konfiguracji sieci, a czasami trzeba też zbadać bazę danych.
Tabela V$SQL
Aby zbadać bazę danych, trzeba znać identyfikator SQL_ID instrukcji SQL, który umożliwia śledzenie konkretnego zapytania za pomocą narzędzi Active Session History (ASH), Automatic Workload Repository (AWR), i Oracle SQLTXPLAIN (SQLT).
W tym temacie pokazano, jak znaleźć identyfikator SQL_ID poprzez eksplorację danych w tabelach śledzenia użycia Oracle Analytics oraz w tabeli systemu bazy danych Oracle noszącej nazwę V$SQL (lub GV$SQL w przypadku bazy danych RAC).
V$SQL to tabela systemu bazy danych Oracle służąca do śledzenia statystyk pojedynczych zapytań SQL. Każdy wiersz tej tabeli odpowiada jednej uruchamianej instrukcji SQL i jest jednoznacznie określony za pomocą kolumny SQL_ID. Używając tego identyfikatora SQL_ID, można śledzić daną instrukcję SQL w całej bazie danych Oracle.
W tabeli V$SQL (lub GV$SQL w przypadku bazy danych RAC) można znaleźć wiele użytecznych informacji dotyczących wykonywanych zapytań.
Tabele śledzenia użycia
Tabele śledzenia użycia nie umożliwiają bezpośredniego śledzenia identyfikatora SQL_ID, ale identyfikator SQL_ID można z łatwością prześledzić wstecz do tabeli V$SQL (lub GV$SQL w przypadku bazy danych RAC).
Po włączeniu śledzenia użycia w Oracle Analytics wszystkie zapytania będą śledzone w dwóch tabelach:
S_NQ_ACCT: zawiera zapytania logiczneS_NQ_DB_ACCT: zawiera zapytania fizyczne
Zapytanie logiczne jest rejestrowane w tabeli zapytań logicznych, a zapytanie fizyczne (lub zapytania uruchamiane przez zapytanie logiczne) jest rejestrowane w tabeli zapytań fizycznych.
Tabele logiczne i fizyczne można łączyć za pomocą kolumny "ID zapytania logicznego". Kolumną zawierającą ID zapytania logicznego w tabeli logicznej jest ID, a w tabeli fizycznej - LOGICAL_QUERY_ID.
Kluczem umożliwiającym znalezienie identyfikatora SQL_ID jest kolumna PHYSICAL_HASH_ID w tabeli fizycznej. Wartość PHYSICAL_HASH_ID jest także zapisywana w tabeli V$SQL (lub GV$SQL) w kolumnie ACTION.
Oracle BI Server oblicza kod haszowania na podstawie tekstu logicznego zapytania SQL oraz tekstu fizycznych zapytań SQL. Fizyczny kod haszowania SQL wszelkich zapytań SQL uruchamianych z serwera Oracle BI jest rejestrowany w kolumnie ACTION w tabeli V$SQL. Więcej informacji jest dostępnych pod hasłem Tworzenie powiązania rekordu S_NQ_ACCT z dziennikiem zapytań BI.
Aby uzyskać więcej informacji dotyczących różnych kolumn śledzenia użycia, zob. Śledzenie użycia i Objaśnienie tabel śledzenia użycia.
Konfiguracja
Gdy wiadomo już, gdzie można znaleźć wartość PHYSICAL_HASH_ID, można napisać instrukcję SQL tworzącą korelację między zapytaniem SQL wykonywanym w bazie danych a określoną analizą wykonywaną w Oracle Analytics.
Gdy tabele śledzenia użycia znajdują się w tej samej instancji bazy danych co hurtownia danych, można napisać jedno zapytanie znajdujące identyfikator SQL_ID.
Jeśli tabele śledzenia użycia oraz hurtownia danych znajdują się w różnych instancjach bazy danych Oracle, należy uruchomić dwa zapytania. Najpierw należy uruchomić zapytanie ściągające identyfikator PHYSICAL_HASH_ID zapytania, które ma być badane, z tabel śledzenia użycia. Następnie należy ściągnąć identyfikator SQL_ID z tabeli V$SQL, używając identyfikatora PHYSICAL_HASH_ID w celu filtrowania kolumny ACTION.
Przykład 1
W tym przykładzie założymy, że tabele śledzenia użycia oraz hurtownia danych znajdują się w tej samej lokalizacji. W zapytaniu można użyć dowolnych albo wszystkich kolumn z tabel śledzenia użycia i tabeli V$SQL, ale w tym przykładzie wybrany zostaje następujący ich podzbiór:
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;
Przykład 2
W tym przykładzie założymy, że tabele śledzenia użycia i hurtownia danych znajdują się w różnych bazach danych Oracle.
Najpierw należy napisać zapytanie względem tabel śledzenia użycia, aby pobrać identyfikator 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;
Następnie należy napisać zapytanie względem tabeli V$SQL, aby pobrać identyfikator SQL_ID, używając wartości PHYSICAL_HASH_ID jako 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;
W przypadku bazy danych RAC należy zamiast tabeli V$SQL używać tabeli GV$SQL do pobrania identyfikatora 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;
Ta metoda nie działa podczas rozwiązywania problemów z blokami inicjalizacyjnymi, ponieważ tabela śledzenia użycia bloków inicjalizacyjnych (S_NQ_INITBLOCK) nie zawiera fizycznego ID haszowania.
Przykład 3
W tym przykładzie użyty jest obszar tematyczny "A - Sample Sales" i zostaje wybranych tylko kilka kolumn z tabel śledzenia użycia.
Z obszaru "A - Sample Sales" należy uruchomić następujące zapytanie:
[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 ]]
Po wybraniu wartości start_ts, id, hash_id, query_src_cd i query_text z tabeli logicznej śledzenia użycia zostanie zwrócona wartość ID wynosząca E841EBB79217270A660CDD3EFB5D986C.
Następnie należy wybrać wartości logical_query_id, hash_id, physical_hash_id i query_text z tabeli fizycznej śledzenia użycia, gdzie wartość LOGICAL_QUERY_ID wynosi E841EBB79217270A660CDD3EFB5D986C.
Sprawdzając dziennik zarządzania sesjami, można zauważyć, że zapytanie logiczne uruchamia kod SQL znajdujący się w kolumnie 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
]]Na końcu należy wybrać wartości action, sql_id i sql_text z tabeli V$SQL, gdzie wartość Action wynosi bd6708b8.
Zapytanie w kolumnie sql_text jest takim samym kodem SQL jak ten widoczny w tabeli fizycznej śledzenia użycia. Na podstawie tej informacji można stwierdzić, że wartość sql_id dla analizowanego zapytania wynosi 1gxhc0acmztwk.
Ponowne uruchomienie tego zapytania spowoduje dodanie kolejnego wiersza do tabeli logicznej śledzenia użycia, który będzie miał nową wartość ID, ale wartość hash_id pozostanie taka sama.
Podobnie do tabeli fizycznej śledzenia użycia zostanie dodany kolejny wiersz z nową wartością logical_query_id, ale wartości hash_id i physical_hash_id pozostaną takie same.
Przykład 4
Wartość physical_hash_id zostanie także użyta ponownie w przypadku uruchomienia podobnego zapytania logicznego w odniesieniu do obszaru tematycznego opartego na tych samych modelach logicznym i fizycznym. Jeśli na przykład zostanie uruchomione podobne zapytanie w odniesieniu do obszaru "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 ]]
To zapytanie logiczne uruchamia taki sam kod SQL jak ten, który został użyty w uruchomionym zapytaniu, w odniesieniu do obszaru "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
]]Najpierw należy użyć tabeli logicznej śledzenia użycia, aby znaleźć identyfikator. Widać, że logiczny identyfikator hash_id jest inny niż w zapytaniu uruchomionym w odniesieniu do obszaru "A - Sample Sales".
W tabeli fizycznej śledzenia użycia widać, że co prawda logiczna wartość hash_id jest inna, ale wartość physical_hash_id jest taka sama.
Przykład 5
W tym przykładzie są uruchamiane takie same zapytania, ale tym razem zostaje kliknięta opcja Odśwież. Należy zwrócić uwagę na zmienną OBIS_REFRESH_CACHE=1, która wskazuje odświeżenie. Zgodnie z oczekiwaniami do tabeli logicznej śledzenia użycia został wstawiony kolejny wiersz, ale ma on inną wartość logical hash_id.
Jednak wykonanie zapytania w odniesieniu do tabeli fizycznej śledzenia użycia pokaże, że wszystkie zapytania mają taką samą wartość physical_hash_id.
Ponieważ te cztery zapytania logiczne mają taki sam identyfikator physical_hash_id, to dla tego zapytania jest widoczny tylko jeden rekord w tabeli V$SQL.
Przykład 6
Za pomocą tej metody można też rozwiązywać problemy z zapytaniami wizualizacji danych opartych na obszarze tematycznym (RPD) lub połączeniu z bazą danych. W przypadku połączeń z bazą danych obejmuje to zarówno wizualizacje, jak i zapytania służące do wypełniania zbiorów danych.
Na podstawie wartości w kolumnie QUERY_SRC_CD można ustalić typ zapytania, taki jak m.in. wizualizacja danych, zbiór danych, analiza lub ODBC. W tym przykładzie zapytania wizualizacji danych mają wartość Visual Analyzer, zapytanie służące do wypełnienia danymi zbioru danych ma wartość data-prep, analizy mają wartość Report, a zapytania do obsługi wywołań niektórych wewnętrznych procedur ODBC mają wartość Soap.
Aby wygenerować instrukcję SQL dla wizualizacji danych, należy zmienić wartość ustawienia "Dostęp do danych" dla źródłowego zbioru danych na "Bezpośrednio".
W tym przykładzie zostanie utworzone zapytanie wizualizacji danych (podobne do wcześniejszych zapytań) w odniesieniu do zbioru danych opartego na połączeniu z bazą danych 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' */
Co powoduje uruchomienie następującego kodu 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 <= 125001Z poziomu tabeli logicznej śledzenia użycia można znaleźć wartość hash_id, która jest używana jako filtr w odniesieniu do tabeli fizycznej śledzenia użycia.
Z poziomu tabeli fizycznej śledzenia użycia można znaleźć wartość physical_hash_id.
Po wykonaniu zapytania w odniesieniu do tabeli V$SQL zostanie ponownie znaleziony identyfikator SQL_ID:
Logiczny identyfikator hash_id oraz identyfikator physical_hash_id można również znaleźć w dzienniku zarządzania sesjami (zob. wyróżnione sekcje we wcześniejszych przykładach z dziennika). Oznacza to, że identyfikator SQL_ID można znaleźć z poziomu samego dziennika. Korzystanie z tabel śledzenia użycia ma tę zaletę, że wpisy dziennika zarządzania sesjami są tymczasowe, więc jeśli ID haszowania nie są gromadzone w tym samym czasie, w którym jest uruchamiane zapytanie, zostają one utracone.
Dane są jednak zapisywane w tabelach śledzenia użycia tylko po ukończeniu wykonywania zapytania. Dlatego jeśli podczas rozwiązywania problemów z długotrwałym zapytaniem, które nie zostało ukończone, trzeba ustalić wartość sql_id, można pobrać logiczną wartość hash_id i wartość physical_hash_id z dziennika zarządzania sesjami.
Jeśli podczas rozwiązywania problemów z instrukcją SQL konieczne jest zbadanie bazy danych, można połączyć informacje z tabel śledzenia użycia oraz tabeli systemowej V$SQL (lub GV$SQL), aby szybko znaleźć identyfikator SQL_ID badanej instrukcji SQL.