Oracle Analytics Cloud：利用状況を把握する ～活用編～

# はじめに
[Oracle Analytics Cloud：利用状況を把握する ～準備編～](https://qiita.com/tyo38/items/5ed90689b69992bfbb6f)という記事で、Oracle Analytics Cloud（**OAC**）のUsage Trackingの設定方法を紹介しました。
こちらの記事では、収集したログの活用を目指してみたいと思います。

# Usage Tracking表（論理SQLログ）の列の説明（抜粋）

- START_TS
    - 論理問合せが送信された日時を示します。
- END_TS
    - 論理問合せの完了日時を示します。ユーザが問合せを終了する前にそのページから移動した場合、最終フェッチは発生せず、タイムアウト値(3600)が記録されます。 ただし、ユーザがタイムアウト前にそのページに戻ると、フェッチはその時点で完了し、end_ts時間として記録されます。
- TOTAL_TIME_SEC (Responce Time)
    - クエリの開始からPresentationサーバによるデータのフェッチの開始までの時間です。Elapced Time（START_TSからEND_TSまでの所要時間）とResponce Timeに相違がある場合、それは通常、全行のフェッチに必要な時間によるものです。検索結果は全行のフェッチを待たずに描画されることがあります。
- COMPILE_TIME_SEC
    - BIサーバがデータをコンパイルして送信する時間です。
- CUM_DB_TIME_SEC
    - バックエンド物理データベースを待機した合計時間(秒単位)です。物理クエリが複数に分割された場合は、その合計待機時間です。

ドキュメント：[使用状況トラッキング表の理解](https://docs.oracle.com/cd/E83857_01/paas/analytics-cloud/acabi/track-usage.html#GUID-2C21BB2B-E7C8-4F2C-97D6-12F5A361A56F)

# 一般的な指針および傾向
### DBサーバに負荷をかけているクエリを見つける
- CUM_DB_TIME_SECの値が大きいクエリ
- TOTAL_TIME_SECとCUM_DB_TIME_SECの値が近いクエリ
  - ほとんどの時間がバックエンドのDBを待機した時間

### BIサーバに負荷をかけているクエリを見つける
- COMPILE_TIME_SECの値が大きなクエリ
    - 「コンパイルに時間がかかっている＝複雑な論理SQL」である可能性が高い
    - クエリを複数に分割したり、表現方法を単純化することで負荷軽減の可能性がある

# OACでUsage Tracking表を検索
今回、データはAutonomous Data Warehouse（**ADW**）に保管されるように設定してあります。
ADWへの接続定義を設定する方法は、こちらを参考にしてください。

[Oracle Analytics Cloud：はじめてのビジュアライゼーション](https://qiita.com/tyo38/items/6dfcc2f1722b615c08a3)

LOGCALQUERIES表をドラッグ＆ドロップで選択し、データセットとして保存します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2342206/0718a0fd-7097-6890-c242-e4b9653ba0bc.png)

保存したデータセットを使用してワークブックを新規に作成します。
コントロールキーを押しながら、「START_DT」「TOTAL_TIME_SEC」「COMPILE_TIME_SEC」「CUM_DB_TIME_SEC」「SAW_SRC_PATH」の5つを選択します。
そのままの状態で右クリックし、「ビジュアライゼーションの選択」をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2342206/d6a005c8-0e4d-1480-6100-1526cd10f81c.png)
「表」をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2342206/d08aac55-5d0b-f44b-0f93-81e2b2f20aff.png)
COMPILE_TIME_SECで降順にソートします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2342206/e3741f59-2d0f-2694-6d00-0af47f0e6706.png)

:::note
QUERY_SRC_CDで「Report」のみにフィルタすると、アンサーレポートのログのみを表示できます。
:::

# 問題のあるクエリの候補を探す
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2342206/769655f3-6290-299b-da1f-8b3ba435e4f7.png)
上の2つのクエリ（アンサーレポート）は、いずれもCOMPILE_TIME_SECとTOTAL_TIME_SECが同じ数値で、CUM_DB_TIME_SECは小さくなっています（秒単位なので0秒と表示されています）。
これは、バックエンドのDBにクエリを発行する前に時間がかかっていることを示しており、DB側は瞬時に結果を返していることがわかります。

### COMPILE_TIME_SECの数値が大きなアンサーレポート
1番上のアンサーレポートを開いてみます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2342206/efd35209-34c5-7322-c751-b2d851aec343.png)
FORECAST関数を使用した6期間先までの予測を含んだグラフでした。
非表示のビューや除外されている列があれば、削除してみましょう。

### CUM_DB_TIME_SECの数値が大きなアンサーレポート
3番めのアンサーレポートを見てみると、CUM_DB_TIME_SECつまりDBでの処理時間の合計が11秒であることがわかります。
一方、TOTAL_TIME_SECは2秒です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2342206/733a9433-44dc-e9f7-62bd-2057ad783a73.png)
これは、このアンサーレポートの実行時に物理的なSQLが複数に分割され、それぞれの物理SQLの合計が11秒であったことがわかります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2342206/302cea77-a0fd-e516-662e-71aab6817aef.png)

このアンサーレポートを開いてみます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2342206/8cb27737-1471-9653-95fe-f699496253be.png)
選択ステップと階層列を使用したレポートでした。

# 物理SQLの確認
選択ステップと階層列を使用したレポートが、バックエンドのDBにどのような物理SQLを発行しているのかを確認したいと思います。
### データセットの拡張
「データ」に移動し、データセットの編集アイコンをクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2342206/f89107f4-432b-4c83-bbc7-a22a7bcc3099.png)
データセット編集用にブラウザの新しいタブが開きます。
「結合ダイアグラム」タブに移動します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2342206/bb568c19-617a-718a-2237-dc571bdf513a.png)
「自動結合表」をオフにして、「PHYSICALQUERIES」表をドラッグ＆ドロップします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2342206/207f4a78-d7cd-d591-1a28-ee65f8986c74.png)
取り込んだ「PHYSICALQUERIES」表を「LOGICALQUERIES」表の上にドラッグ＆ドロップします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2342206/d1a1ee14-ffd7-e237-e5cc-0a78de01978c.png)
結合条件を設定するために、2つの表を間のアイコンをクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2342206/de57581b-e0db-20a2-bed3-42d805340dc9.png)
LOGICALQUERIES表の **ID** 列とPHYSICALQUERIES表の **LOGICAL_QUERY_ID** 列で結合します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2342206/e0ce707c-1481-a611-8422-abdc88961615.png)
データセットを保存します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2342206/57bf49a6-e4c8-3190-b3c8-08df3fdf5da0.png)
ワークブックのタブに戻り、「ビジュアル化」に移動します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2342206/e16d1ba1-c3fe-f00f-9688-5b176171c0a1.png)

### 物理SQLの確認
物理SQLを確認するアンサーレポートを右クリックし、メニューから「選択項目の保持」をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2342206/1267ed2b-c399-fb8c-e466-35f9ace5425b.png)
PHYSICALQUERIESを展開し、「QUERY_TEXT」をドラッグ＆ドロップで表に含めます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2342206/d297bfce-ebb1-4adb-52a0-19c4212a24ff.png)
分割実行された物理SQLを確認することができました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2342206/b306dcf3-2c08-835b-c237-e45adb356bbb.png)

# ダッシュボードを作ってみる
こちらの記事も参考にしてみてください。
- [Oracle Analytics Cloud：Usage Trackingデータでダッシュボードっぽいのを作る](https://qiita.com/tyo38/items/adda7e236a18aa09ac1e)