Oracle Analytics問合せのSQL_IDの検索

Oracle Analyticsの問合せをチューニングする場合、データ・モデル、レポート設計、ダッシュボード設計、およびネットワーク構成を確認しますが、場合によってはデータベースを調査する必要があります。

V$SQL表

データベースの調査では、アクティブ・セッション履歴(ASH)、自動ワークロード・リポジトリ(AWR),およびOracle SQLTXPLAIN (SQLT)を通じて特定の問合せをトラッキングできるように、SQL文のSQL_IDを把握する必要があります。

このトピックでは、Oracle Analytics使用状況トラッキング表とV$SQLというOracleデータベース・システム表(RACデータベースではGV$SQL)をマイニングしてSQL_IDを検索する方法について説明します。

V$SQL表は、個々のSQL問合せの統計をトラッキングするOracleデータベース・システム表です。この表には、実行したSQL文ごとに1つの行があり、各行はSQL_ID列によって一意に識別されます。このSQL_IDを使用して、Oracleデータベース全体で指定したSQL文をトラッキングできます。

V$SQL表(RACデータベースではGV$SQL)で、問合せに関する多くの有益な情報を見つけることができます。

使用状況トラッキング表

使用状況トラッキング表は、SQL_IDを直接トラッキングしませんが、V$SQL表(RACデータベースではGV$SQL)に戻ってSQL_IDを簡単にトレースできます。

Oracle Analyticsで使用状況トラッキングを有効にすると、すべての問合せが2つの表でトラッキングされます:

S_NQ_ACCT: 論理問合せを含みます
S_NQ_DB_ACCT: 物理問合せを含みます

論理問合せは論理問合せ表に記録され、物理問合せ(または論理問合せによって生成された問合せ)は物理問合せ表に記録されます。

論理表と物理表は、論理問合せID列で結合できます。論理表での論理問合せID列はIDで、物理表でのそれはLOGICAL_QUERY_ID.です

SQL_IDを見つけるためのキーは、物理表のPHYSICAL_HASH_ID列です。PHYSICAL_HASH_ID値は、V$SQL表(またはGV$SQL表)のACTION列にも書き込まれます。

Oracle BIサーバーは、論理SQL問合せのテキストと物理SQL問合せのテキストからハッシュ・コードを計算します。Oracle BIサーバーから実行された任意のSQL問合せの物理SQLハッシュ・コードは、V$SQL表のACTION列に記録されます。詳細は、S_NQ_ACCTレコードとBI問合せログの関連付けを参照してください

様々な使用状況トラッキング列の詳細は、使用状況のトラッキングおよび使用状況トラッキング表の理解を参照してください。

設定

PHYSICAL_HASH_IDを検索する場所がわかっていれば、データベースで実行されるSQL問合せをOracle Analyticsで実行される特定の分析に関連付けるSQL文を記述できます。

使用状況トラッキング表がデータ・ウェアハウスと同じデータベース・インスタンスにある場合、SQL_ID.を検索する単一の問合せを記述できます

使用状況トラッキング表とデータ・ウェアハウスが異なるOracleデータベース・インスタンスにある場合、2つの問合せを実行する必要があります。最初に、使用状況トラッキング表から調査する問合せのPHYSICAL_HASH_IDを取得する問合せを実行します。次に、PHYSICAL_HASH_IDの値を使用してACTION列をフィルタし、V$SQL表からSQL_IDを取得します。

例1

この例では、使用状況トラッキング表とデータ・ウェアハウスは同じ場所にあると仮定します。問合せでは使用状況トラッキング表とV$SQL表のいずれかまたはすべての列を使用できますが、この例では次のサブセットを選択します:

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;

例2

この例では、使用状況トラッキング表とデータ・ウェアハウスは異なるOracleデータベースにあると仮定します。

最初に、使用状況トラッキング表に対する問合せを記述して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;

次に、PHYSICAL_HASH_IDの値をフィルタとして使用し、V$SQL表に対する問合せを記述してSQL_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;

RACデータベースが対象の場合は、V$SQL表のかわりにGV$SQL表を使用して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;

初期化ブロック使用状況トラッキング表(S_NQ_INITBLOCK)には物理ハッシュIDが含まれていないため、初期化ブロックをトラブルシューティングする場合はこの方法は動作しません。

例3

この例では、A - Sample Salesサブジェクト領域を使用して、使用状況トラッキング表から少数の列のみを選択します。

A - Sample Salesから、次の問合せを実行します:

[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

]]

使用状況トラッキング論理表からstart_ts、id、hash_id、query_src_cdおよびquery_textを選択すると、ID値のE841EBB79217270A660CDD3EFB5D986Cが取得されます。

GUID-5FA7BA53-6767-4839-9341-A6AC0AACDFEB-default.jpgの説明が続きます
.jpgの説明

次に、LOGICAL_QUERY_IDがE841EBB79217270A660CDD3EFB5D986Cであるという条件で使用状況トラッキング物理表からlogical_query_id、hash_id、physical_hash_idおよびquery_textを選択します:

GUID-6842B138-0FD5-4DA9-A092-B423EB7459C2-default.jpgの説明が続きます
.jpgの説明

セッション管理ログを確認すると、論理問合せによってQUERY_TEXT列内に次のSQLコードが生成されます。

[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

]]

最後に、Actionがbd6708b8.であるという条件でV$SQL表からaction、sql_idおよびsql_textを選択します:

GUID-094521FA-317B-430D-9644-3C3E44D91900-default.jpgの説明が続きます
.jpgの説明

sql_text列の問合せは、使用状況トラッキング物理表で示されたものと同じSQLコードです。ここから、対象となっている問合せのsql_idが1gxhc0acmztwkであることがわかります。

同じ問合せを再度実行すると、使用状況トラッキング論理表に別の行が新しいIDで追加されますが、hash_idは同じです

GUID-82CD3499-E550-4DD4-8C96-E3DB3CE3232E-default.jpgの説明が続きます
.jpgの説明

同様に、新しいlogical_query_idを持つが、hash_idとphysical_hash_idの両方は同じままの別の行が使用状況トラッキング物理表に追加されます

GUID-BF05E975-45FB-4F40-9AE4-9E40A1124F35-default.jpgの説明が続きます
.jpgの説明

例4

また、physical_hash_idは、同じ論理および物理モデルに基づいたサブジェクト領域に対して同様の論理問合せを実行する場合に再利用できます。たとえば、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

]]

この論理問合せによって、A – Sample Salesに対して実行した問合せで示されたものと同じSQLコードが生成されます:

[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

]]

最初に、使用状況トラッキング論理表を使用してIDを検索します。論理hash_idが、A – Sample Salesに対して実行した問合せと異なることがわかります。

GUID-16D78632-88BA-446F-A6D7-496DA5662869-default.jpgの説明が続きます
.jpgの説明

使用状況トラッキング物理表で、論理hash_idが異なっていても、physical_hash_idは同じであることがわかります

GUID-91049191-7E76-407D-B15B-A74160942148-default.jpgの説明が続きます
.jpgの説明

例5

この例では、同じ問合せを実行しますが、今回は「リフレッシュ」をクリックします。リフレッシュを示す変数OBIS_REFRESH_CACHE=1に注意してください。予想どおり、別の行が使用状況トラッキング論理表に挿入されますが、その論理hash_idは異なります。

GUID-BFA0DEA8-423A-414C-8BC9-E1B8EA382689-default.jpgの説明が続きます
.jpgの説明

ただし、使用状況トラッキング物理表を問い合せると、すべての問合せが同じphysical_hash_id.を持っていることがわかります

GUID-D6CC7144-9177-4CDA-B57A-1A4E4A6615A5-default.jpgの説明が続きます
.jpgの説明

これらの4つの論理問合せはすべて同じphysical_hash_idを持つため、この問合せではV$SQL表で単一のレコードのみが示されます。

GUID-13AE24EE-9C17-48D9-976F-49FF882A5E42-default.jpgの説明が続きます
.jpgの説明

例6

同じ方法を使用して、サブジェクト領域(RPD)またはデータベース接続に基づくデータ・ビジュアライゼーション問合せをトラブルシューティングできます。データベース接続では、これにはデータ・ビジュアライゼーションと、データセット移入のために使用される問合せの両方が含まれます。

問合せタイプ(データ・ビジュアライゼーション、データセット、分析、ODBCなど)をQUERY_SRC_CD列の値から特定できます。たとえば、データ・ビジュアライゼーション問合せは値Visual Analyzerを、データセット移入のために使用される問合せはdata-prepの値を、分析は値Reportを、およびいくつかの内部ODBCプロシージャ・コールの問合せは値Soapをそれぞれ持ちます。

データ・ビジュアライゼーションのSQL文を生成するには、基礎となるデータセットでデータ・アクセスを「ライブ」に変更する必要があります。

この例では、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' */

これにより次の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

使用状況トラッキング論理表から、使用状況トラッキング物理表に対するフィルタとして使用されているhash_idを検索できます

GUID-C053C7FB-251A-4021-A850-1F6006BEBFF1-default.jpgの説明が続きます
.jpgの説明

使用状況トラッキング物理表から、physical_hash_id.を検索できます

GUID-EB289F16-56B0-4AFB-8C96-2D7B55680B7B-default.jpgの説明が続きます
.jpgの説明

V$SQL表を問い合せると、またSQL_IDがわかります。

GUID-CD805B03-A8AD-40F0-AE84-BD28AC8B6EF8-default.jpgの説明が続きます
.jpgの説明

論理hash_idとphysical_hash_idは、セッション管理ログでも検索できます(前のログ例の強調表示されたセクションを参照)。つまり、ログ自体からSQL_IDを検索できます。使用状況トラッキング表を使用するメリットは、セッション管理のログ・エントリは一時的なものであるため、問合せの実行と同時にハッシュIDを収集しなければ、それらは失われてしまうというところにあります。

ただし、データが使用状況トラッキング表に書き込まれるのは、問合せの完了時のみです。そのため、まだ完了していない長時間実行問合せをトラブルシューティングして、sql_idを特定する場合、セッション管理ログから論理hash_idとphysical_hash_idを取得できます。

SQL文をトラブルシューティングして、データベースを調査する必要がある場合、使用状況トラッキング表とV$SQLシステム表(またはGV$SQL)の情報を結合して、調査対象のSQL文のSQL_IDをすばやく検索できます。