データポータルでOracle Databaseのスロークエリーレポートを作成

#概要/背景
Oracle Databaseの運用保守の中で実行に時間がかかるSQL(スロークエリ)を改善したく、
まずはどのSQLが遅いのか把握したかったため、スロークエリを可視化することにしました。
その流れをメモを兼ねて紹介します。
・SQLの実行時間/実行回数は、DB管理者のみが見れる状態
・アプリケーション開発者は、どのSQLが遅いか等把握できない状態のため、見える化しスロークエリの改善を促したい
・BIツールを用いて可視化できないか検討し、Gsuiteを利用していることもありデータポータルを用い作成

#今回作ったもの
Oracle Databaseで実行されているSQLのスロークエリをレポートした画面

■使用グラフ・コントロール
＜グラフ＞
①円グラフ：一目でどのSQLが遅いかわかるようシンプルにTOP10を表示
②100%積み上げ縦棒グラフ：直近1週間での割合を表示
③ヒートマップ付きデータ表：スキーマ、SQLID、SQL文、平均実行時間などの元データを表示(図は平均実行時間でソート)
④時系列グラフ：SQLID毎の推移を表示。ディメンションに日付、内訳ディメンションにSQLIDを選択し、日毎のSQL毎の推移を表示
＜コントロール＞
⑤プルダウンリスト：アプリ開発者がそれぞれ使用しているスキーマを選択。
⑥高度なフィルタ：複数スキーマ選択用。「次に含まれる」を選び、カンマ区切りで値を入力し複数一括で選択可能。
⑦期間設定：グラフの表示期間を設定。デフォルト1か月。

#目次
データポータル
環境
注意点
作成手順
所感

#データポータル
Googleが無料で提供している、データをグラフ、表などで可視化するBIツール。
ドラッグ＆ドロップ、クリックで直感的に作れるので専門知識不要。
Excelやスプレッドシートでグラフを作るようなイメージ。
https://marketingplatform.google.com/intl/ja/about/data-studio/

#環境
・Google Chrome: 89.0.4389.82（Official Build） （64 ビット）
・Oracle Database 19c
　※ディクショナリからスロークエリ情報取得⇒DBA_HIST_SQLSTAT、DBA_HIST_SNAPSHOT、DBA_HIST_SQLTEXT)
・rclone-v1.52.0
　※サーバ⇔クラウドストレージ同期が可能なツール

#注意点
※データポータルからのOracle Databaseへの接続はサポートされていない
※csvはデータポータルから自動読み込み機能無し
※スプレッドシートから読み込む場合はセル数制限500万まで
※データポータルに直接csvをアップロードする場合はUTF-8
※csvからスプレッドシートへ書き込むGASにファイルを追加する場合、同じようにRcloneからアップロードしないと権限の関係でうまく動かない
#作成手順
下記流れで作成。
１．SQL実行時間/実行回数等を取得してOracle Databaseのテーブルに蓄積
２．１のデータをcsv出力
３．２のcsvをGoogleDriveへUP
４．csvからスプレッドシートへ書き込み
５．スプレッドシートをデータポータルから読み込み
###１．SQL実行時間/実行回数等を取得してOracle Databaseのテーブルに蓄積
Oracle Databaseに管理用テーブルを作成し、下記ディクショナリビューのSELECT結果を情報として蓄積。データの蓄積はバッチ化した。
・DBA_HIST_SQLSTAT
　SQL統計情報の履歴情報。実行時間や実行回数等を取得。
　　取得カラム：ELAPSED_TIME_DELTA(実行時間)、EXECUTIONS_DELTA(実行回数)など
・DBA_HIST_SNAPSHOT
　スナップショットに関する情報。DBA_HIST_SQLSTATのSNAP_ID等と紐づけて取得時間帯を算出。
・DBA_HIST_SQLTEXT
　SQL文のテキスト冒頭部分。SQL全文は表示されない点に注意。V$SQLから別で確認が必要。

＜蓄積データ抽出SQL参考＞
AWR(DBA_HIST表)を使用した確認SQL
###２．１のデータをcsv出力
※データポータルからOracle Databaseに接続できないため、csvを出力。
SPOOL OFFで
スプレッドシートが500万セルの制限あるためその範囲に収まるよう条件で絞る。
下記を記載した適当な.sqlファイルを作り@で実行。　例)SQL> @sqlstat.sql

--成形
SET MARKUP CSV ON
SET PAGESIZE 0
SET LINESIZE 10000
SET TERMOUT OFF
SET HEAD ON
SET FEEDBACK     OFF

--出力
SPOOL [出力階層]/[出力ファイル名].csv
   [データ出力用SQL]
SPOOL OFF

###３．２のcsvをGoogleDriveへUP
Rcloneを導入し出力したcsvファイルをGoogleDriveへUP
/sbin/rclone copy [出力ファイル名].csv GoogleDrive:[GoogleDriveの階層]

※Rclone
　サーバに置いたままだとcsvをGoogle系ツールから読み込めない＋人の手が必要になるため、
　GoogleDriveにアップロードできるツールとしてRcloneを導入。
　cronバッチで動かすことで自動アップロードが可能。
＜参考＞
・rclone コマンドで google ドライブにデータを転送する(rcloneインストール方法と使い方)
・rcloneを使用したGoogle Driveのバックアップ

###４．csvからスプレッドシートへ書き込み
※csvのままだとデータポータルから自動で読み込めないため、スプレッドシート化。
書き込み先スプレッドシートのスクリプトエディタで下記作成し保存、トリガーで毎日動くよう設定。
データは全消し全入れとした。

function csvImport() {
//変数
  var file = DriveApp.getFilesByName("[出力ファイル名].csv").next();
  var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sh = sheet.getSheetByName("[シート名]");
//シートクリア
  sh.clear();
//スプレッドシートへ書き込み
  sh.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

###５．スプレッドシートをデータポータルから読み込み
データのレポートへの追加⇒Googleスプレッドシート⇒対象のシート選択⇒追加
グラフを追加⇒好きなグラフを選ぶ⇒反映させたいデータをディメンション、指標に選択
　ディメンション：データのカテゴリ。ここでは「スキーマ名、SQLID、SQL_TEXT」
　指標：データの種類。ここでは「SQL実行時間、実行回数、平均実行時間」
※指標で合計(SUM)、平均(AVG)の選択を間違えないよう注意

#所感
・SQLの速度改善を推進したいお客様から喜びの声を多く頂いたので、他にも役立つ情報を持つだけでなく分析し見えるようにしていきたい。
・このレポートは展開したばかりでまだ直接的な貢献には繋がっていないが、今後もっと使用してもらいSQL処理速度改善につなげたい。
・データポータルからOracle Databaseへの接続ができない、csvだと手動でデータポータルに読み込ませないといけない、セル制限がある、など作成途中に出てきた問題を都度回避して今の形になったが、もっとスマートにできたのではないかと反省。事前調査は大切だと痛感した。
・今回のお題は「スロークエリ」だったが、他にも「レコード数分析(断片化率)」や単純に「オブジェクト一覧」「依存関係」の表示画面を作っていきたい。