Bei der Optimierung von Abfragen für Oracle Analytics prüfen Sie das Datenmodell, das Berichtsdesign, das Dashboard-Design, die Netzwerkkonfiguration und in manchen Fällen die Datenbank.
V$SQL-Tabelle
Für Datenbankprüfungen müssen Sie die SQL_ID
der SQL-Anweisung kennen, damit Sie die jeweilige Abfrage über Active Session History (ASH), Automatic Workload Repository (AWR) und Oracle SQLTXPLAIN (SQLT) verfolgen können.
In diesem Thema erfahren Sie, wie Sie die SQL_ID
in den Nutzungstrackingtabellen von Oracle Analytics und der Oracle-Datenbanksystemtabelle V$SQL
(bzw. GV$SQL
bei einer RAC-Datenbank) finden.
V$SQL
ist eine Oracle-Datenbanksystemtabelle, die Statistiken für individuelle SQL-Abfragen verfolgt. Die Tabelle enthält für jede ausgeführte SQL-Anweisung eine Zeile. Diese werden jeweils in der Spalte SQL_ID
eindeutig identifiziert. Anhand dieserSQL_ID
können Sie eine SQL-Anweisung in der Oracle-Datenbank verfolgen.
Die Tabelle V$SQL
(bzw. GV$SQL
bei einer RAC-Datenbank) enthält viele nützliche Informationen zu Ihren Abfragen.
Nutzungstrackingtabellen
In den Nutzungstrackingtabellen wird die SQL_ID
nicht direkt verfolgt. Sie können die SQL_ID
aber einfach auf die Tabelle V$SQL
(bzw. GV$SQL
bei einer RAC-Datenbank) zurückführen.
Wenn Sie das Nutzungstracking in Oracle Analytics aktivieren, werden alle Abfragen in zwei Tabellen verfolgt:
S_NQ_ACCT
: Enthält logische Abfragen.S_NQ_DB_ACCT
: Enthält physische Abfragen.Die logischen Abfragen werden in der logischen Tabelle und die physischen Abfragen (oder von logischen Abfragen gestartete Abfragen) in der Tabelle für physische Abfragen protokolliert.
Sie können die logische und die physische Tabelle anhand der Spalte für die logische Abfrage-ID verknüpfen. Die Spalte für die logische Abfrage-ID in der logischen Tabelle lautet "ID". In der physischen Tabelle lautet sie LOGICAL_QUERY_ID
.
Zum Ermitteln der SQL_ID
benötigen Sie die Spalte PHYSICAL_HASH_ID
in der physischen Tabelle. Der Wert PHYSICAL_HASH_ID
wird auch in V$SQL
(bzw. GV$SQL
) in die Spalte ACTION
geschrieben.
Oracle BI Server berechnet einen Hashcode aus dem Text der logischen SQL-Abfrage und dem Text der physischen SQL-Abfragen. Der Hashcode der physischen SQL von SQL-Abfragen, die aus Oracle BI Server ausgeführt werden, wird in der Spalte ACTION
in V$SQL
aufgezeichnet. Weitere Informationen finden Sie unter S_NQ_ACCT-Datensatz mit dem BI-Abfragelog verknüpfen.
Weitere Informationen zu den verschiedenen Nutzungstrackingspalten finden Sie unter Nutzung verfolgen und Nutzungstrackingtabellen.
Setup
Wenn Sie erst einmal wissen, wo Sie die PHYSICAL_HASH_ID
finden, ist es relativ einfach, eine SQL-Anweisung zu schreiben, die die in der Datenbank ausgeführte SQL mit einer spezifischen in Oracle Analytics ausgeführten Analyse korreliert.
Wenn sich die Nutzungstrackingtabellen in derselben Datenbankinstanz wie das Data Warehouse befinden, können Sie eine einzelne Abfrage zum Ermitteln der SQL_ID
schreiben.
Wenn sich die Nutzungstrackingtabellen und Ihr Data Warehouse in unterschiedlichen Oracle-Datenbankinstanzen befinden, müssen Sie zwei Abfragen ausführen. Zunächst führen Sie eine Abfrage aus, um die PHYSICAL_HASH_ID
der gewünschten Abfrage aus den Nutzungstrackingtabellen abzurufen. Dann rufen Sie die SQL_ID
anhand des Wertes der PHYSICAL_HASH_ID
von V$SQL
ab, um die Spalte ACTION
zu filtern.
Beispiele
In diesem Beispiel befinden sich die Nutzungstrackingtabellen und das Data Warehouse in derselben Instanz. Sie können beliebige oder alle Spalten aus der Nutzungstrackingtabelle und der Tabelle V$SQL
in der Abfrage verwenden. In diesem Beispiel wählen Sie aber die folgende Teilmenge aus:
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;
Im zweiten Beispiel befinden sich die Nutzungstrackingtabellen und das Data Warehouse in unterschiedlichen Oracle-Datenbanken.
Schreiben Sie zunächst eine Abfrage für die Nutzungstrackingtabellen, um die PHYSICAL_HASH_ID
abzurufen.
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;
Schreiben Sie dann eine Abfrage für V$SQL
, um die SQL_ID
abzurufen, und verwenden Sie die Werte von PHYSICAL_HASH_ID
dabei als Filter.
select o.action, o.sql_id, o.sql_text from v$sql o where o.action = ‘<physical_hash_id>’ order by o.sql_id;
Bei einer RAC-Datenbank rufen Sie die SQL_ID
von der Tabelle GV$SQL
und nicht von V$SQL
ab.
select o.action, o.sql_id, o.sql_text from gv$sql o where o.action = '<physical_hash_id>' order by o.sql_id;
Diese Methode funktioniert nicht bei der Fehlerbehebung von Initialisierungsblöcken, da die Nutzungstrackingtabelle für Initialisierungsblöcke (S_NQ_INITBLOCK
) keine physische Hash-ID enthält.
Im nächsten Beispiel verwenden Sie den Themenbereich "A - Sample Sales" und wählen nur einige Spalten aus den Nutzungstrackingtabellen aus.
Führen Sie die folgende Abfrage aus "A - Sample Sales" aus:
[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 ]]
Wenn Sie start_ts, id, hash_id, query_src_cd
und query_text
von der logischen Tabelle des Nutzungstrackings auswählen, erhalten Sie den ID
-Wert E841EBB79217270A660CDD3EFB5D986C:
Wählen Sie als Nächstes logical_query_id, hash_id, physical_hash_id
und query_text
aus der physischen Tabelle des Nutzungstrackings aus, wobei logical_query_id = E841EBB79217270A660CDD3EFB5D986C
:
Im Verwaltungssessionlog sehen Sie, dass die logische Abfrage die SQL in der Spalte QUERY_TEXT
startet:
[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 ]]
Wählen Sie schließlich action
, sql_id
und sql_text
aus V$SQL
aus, wobei action = ‘bd6708b8'
:
Die Abfrage in der Spalte sql_text
ist dieselbe SQL, die auch in der physischen Tabelle des Nutzungstrackings steht. Dort sehen Sie, dass die sql_id
für die betreffende Abfrage ‘1gxhc0acmztwk’
lautet.
Wenn Sie dieselbe Abfrage erneut ausführen, wird der logischen Tabelle des Nutzungstrackings eine weitere Zeile mit einer neuen ID, aber mit derselben hash_id
hinzugefügt:
Gleichermaßen wird eine weitere Zeile zur physischen Tabelle des Nutzungstrackings mit einer neuen logical_query_id
hinzugefügt. Dabei bleiben aber hash_id
und physical_hash_id
gleich:
Die physical_hash_id
wird auch wiederverwendet, wenn Sie eine ähnliche logische Abfrage für einen Themenbereich ausführen, der auf denselben logischen und physischen Modellen basiert. In diesem Beispiel führen Sie eine ähnliche Abfrage für "C - Sample Costs" aus:
[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 ]]
Diese logische Abfrage startet die gleiche SQL wie die Abfrage, die Sie für "A - Sample Sales" ausgeführt haben:
[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 ]]
Ermitteln Sie zunächst die ID anhand der logischen Tabelle des Nutzungstrackings. Beachten Sie, dass die logische hash_id
sich von der für "A - Sample Sales" ausgeführten Abfrage unterscheidet:
Sie sehen in der physischen Tabelle des Nutzungstrackings, dass die logische hash_id
zwar anders lautet, die physical_hash_id
jedoch gleich ist:
In einem weiteren Beispiel führen Sie die gleichen Abfragen (wie oben) aus, klicken aber dieses Mal auf "Aktualisieren". Die Variable OBIS_REFRESH_CACHE=1
kennzeichnet die Aktualisierung. Wie erwartet wird eine weitere Zeile in die logische Tabelle des Nutzungstrackings eingefügt, die aber eine andere logische hash_id
aufweist.
Bei einer Abfrage der physischen Tabelle des Nutzungstrackings sehen Sie aber, dass alle Abfragen dieselbe physical_hash_id
aufweisen.
Da diese vier logischen Abfragen alle dieselbe physical_hash_id
aufweisen, sehen Sie nur einen einzelnen Datensatz in V$SQL
für diese Abfrage:
Mit derselben Methode können Sie Fehler mit Datenvisualisierungsabfragen basierend auf einem Themenbereich (RPD) oder einer Datenbankverbindung beheben. Für Datenbankverbindungen bezieht sich das sowohl auf Datenvisualisierungen als auch auf Abfragen zum Auffüllen von Datasets.
Sie können den Abfragetyp (Datenvisualisierung, Dataset, Analyse, ODBC usw.) anhand des Wertes der Spalte QUERY_SRC_CD
bestimmen. Beispiel: Datenvisualisierungsabfragen haben den Wert ‘Visual Analyzer’
, eine Abfrage zum Auffüllen eines Datasets hat den Wert ‘data-prep
, Analysen den Wert ‘Report’
und Abfragen für einige interne ODBC-Prozeduraufrufe den Wert ‘Soap’
.
Um eine SQL-Anweisung für eine Datenvisualisierung zu generieren, müssen Sie den Datenzugriff für das zugrunde liegende Dataset in "Live" ändern.
In diesem Beispiel erstellen Sie eine Datenvisualisierungsabfrage (ähnlich wie die oben gezeigten Abfragen) für ein Dataset basierend auf einer Verbindung zu einer Oracle-Datenbank.
[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' */
Dadurch wird die folgende SQL gestartet:
[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
In der logischen Tabelle des Nutzungstrackings finden Sie die hash_id
, die zum Filtern der physischen Tabelle des Nutzungstrackings verwendet wird:
In der physischen Tabelle des Nutzungstrackings finden Sie die physical_hash_id
:
Durch eine Abfrage von V$SQL
ermitteln Sie die SQL_ID
:
Sie finden die logische hash_id
und physical_hash_id
auch im Verwaltungssessionlog (siehe hervorgehobene Abschnitte in den oben gezeigten Log-Snippets). Das bedeutet, dass Sie die SQL_ID
aus dem Log selbst ermitteln können. Der Vorteil der Nutzungstrackingtabellen besteht hierbei darin, dass Logeinträge in Verwaltungssessions ephemer sind. Wenn Sie die Hash-IDs als nicht zeitgleich mit der Abfrageausführung erfassen, gehen sie verloren.
Daten werden allerdings nur in die Nutzungstrackingtabellen geschrieben, wenn eine Abfrage abgeschlossen wird. Wenn Sie also Fehler bei einer Abfrage mit langer Ausführungszeit beheben, die noch nicht abgeschlossen ist, und Sie die sql_id
bestimmen möchten, können Sie die logische hash_id
und physical_hash_id
aus dem Verwaltungssessionlog abrufen.
Wenn Sie die Datenbank bei der Fehlerbehebung für eine SQL-Anweisung prüfen müssen, können Sie Informationen in den Nutzungstrackingtabellen und in der Systemtabelle V$SQL
(bzw. GV$SQL
) verknüpfen, um die SQL_ID
für die gewünschte SQL-Anweisung schnell zu ermitteln.