エムスリーテックブログ

エムスリー(m3)のエンジニア・開発メンバーによる技術ブログです

Oracleのクエリ実行履歴を網羅的に収集するための実践的アプローチ

Oracle

エムスリーエンジニアリンググループ、データ基盤チームの石塚です。 この記事はデータ基盤チーム & Unit9（エビデンス創出プロダクトチーム） ブログリレー5日目の記事です。前回は三浦さんの 「SQL課題：月の集合を連続した期間の集合にまとめてください」でした。

エムスリーが提供するサービスの中には、今も一部でオンプレミス環境のOracleを活用しているシステムがあります。私たちは、これらのシステムについてもコスト効率やメンテナンス性をさらに向上させるため、Oracleへの依存を段階的に減らしていくプロジェクトを進めています。

このプロジェクトを推進する上で、「そもそも、どのシステムがどれくらいOracleを参照しているのか？」という実態の正確な把握が大きな課題となりました。特に、SQLを解析してどのテーブルに、誰が、いつアクセスしたかを観測し、Oracleへの依存度を正確に測定する必要がありました。プロジェクトの進捗を可視化したり、各担当チームに具体的なデータをもって協力をお願いしたりするためにも、この参照記録は不可欠な情報でした。

そこで私たちは、Oracle上で実行された参照クエリを詳細に記録し、分析するための仕組みを構築することにしました。

ところが、いざ進めてみると「昨日実行したはずのクエリがない！？」という思わぬ壁にぶつかります。本記事では、その原因だったv$sqlareaの揮発性の仕様と、私たちがどのようにしてその課題と向き合い、v$sqlstatsなどを組み合わせてクエリの収集率を向上させていったか、その試行錯誤の道のりをご紹介します。

最初の試みと「クエリが消える」問題の発生

なぜv$sqlareaを選んだのか

Oracleのクエリ履歴を取得できるビューはいくつかありますが、私たちは当初v$sqlarea*1の活用を決めました。

その理由は、私たちが実現したかった「SQLを解析して、どのテーブルに、誰が、いつアクセスしたか」を分析する上で、理想的な情報が揃っていたからです。v$sqlareaはクエリ本文だけでなく、PARSING_USER_ID（実行ユーザー）、PARSING_SCHEMA_NAME（実行スキーマ）、そしてMODULE（実行モジュール）といった、クエリの呼び出し元を特定しやすい付加情報が豊富でした。特にMODULE情報には「python@サーバー名」のように記録されるため、どのアプリケーションから実行されたクエリなのかを後から分析する上で非常に有利だと考えました。

当初の連携アーキテクチャ

当初のアーキテクチャは以下の通りです。

  1. 日次連携: 1日に1回、深夜（24時前）にEmbulkを使い、Oracleのv$sqlareaの当日分データをBigQueryへ連携。
  2. データ整形: 連携後、Cloud FunctionsをトリガーしてBigQuery上の生データを整形。
  3. テーブル提供: 整形後のデータを、テーブル単位で「どのユーザーが」「どのクエリを」使ったか分かるような見やすい形のテーブルにして、分析用途で提供。

「消えたクエリ」の謎

この仕組みで順調にクエリ利用状況を可視化できるはずでした。しかし、運用を開始してしばらく経ったある日、利用者からこんな連絡が舞い込みます。

「バッチからのアクセスがありそうなんですが、BigQueryで提供してもらっている参照記録には、そのクエリが残っていないように見えます…」

私たちは早速調査を開始。バッチサーバー側の実行ログやプログラムを確認すると、確かに対象のクエリはOracleデータベースに向けて実行されています。しかし、私たちがBigQuery上に構築した参照記録のテーブルに、そのデータは存在していませんでした。

ここで、私たちはv$sqlareaの性質を思い出します。これは「動的パフォーマンスビュー」であり、データは永続的に保存されるわけではなく、揮発性のメモリ上に一時的に保持されるという点です。

日次での連携では、深夜の連携タイミングより前にメモリ上から消えてしまったクエリは、永遠に追跡できないのではないか？ この仮説から、私たちはv$sqlareaの揮発性との戦いを始めることになりました。

解決策：精度向上のための合わせ技

v$sqlareaの揮発性が原因だと仮説を立てた私たちは、まずシンプルな解決策を試みました。

対策1：連携頻度の強化という試行錯誤

「メモリから消える前に連携すればいい」。この単純な発想から、まず1日1回だった連携を毎時連携に変更しました。 しかし、結果は無情にも変わらず。指摘のあったバッチのクエリは、依然として参照記録に現れませんでした。

ただ、一つ収穫もありました。取得できるクエリの総数がわずかに増加したのです。このことから、「連携頻度を上げる」という方向性自体は間違っていないと確信しました。

そこで次に、BigQueryに連携したv$sqlareaのデータを使い、クエリの実行回数を時間帯ごとに分析しました。すると、AM6時〜AM11時の時間帯にクエリの実行が集中していることが分かりました。

この分析結果に基づき、私たちは次の仮説を立てます。 「クエリが集中するピークタイムだけ、さらに連携頻度を上げれば、効率的に取得率を上げられるのではないか？」

早速、この仮説を検証するため、AM6時〜AM11時の間は10分間隔で連携するよう、アーキテクチャを修正。その結果、ついに取得できていなかったバッチのクエリが、参照記録に記録されるようになりました。

対策2：最後の1割を埋めるためのv$sqlstats活用

問題のクエリは取得できましたが、「他のクエリにも、まだ連携漏れがあるのではないか？」という疑念が拭えませんでした。

そこで、自分たちの手法がどれくらいのクエリをカバーできているのか、"正解"と比較して精度を測定することにしました。

その"正解"として白羽の矢を立てたのが、Oracleの監査機能であるUNIFIED_AUDIT_TRAIL*2です。これは、データベースに対するほぼ全ての操作を記録できる強力な機能で、パフォーマンスへの影響は大きいものの、最も信頼性の高いログです。 私たちはQA環境でこの監査ログを有効にし、v$sqlareaを高頻度で取得する我々の手法のレコードと突き合わせ、カバー率を測定することにしました。

その結果、カバー率は約90%。

十分なカバー率なのではと思う一方で、「残りの1割にこそ、見つけたいクエリが隠れているかもしれない」という懸念を捨てきれませんでした。

そこで、この1割の漏れを埋めるために私たちが次に取った対策が、v$sqlstats*3の活用です。

v$sqlstatsはv$sqlareaと同様にクエリの統計情報を記録する動的パフォーマンスビューですが、一つ決定的な違いがあります。それは、共有プールからデータが無効化された後も、情報が揮発しないという特徴です。

ただし、トレードオフもあります。v$sqlstatsはv$sqlareaに比べて、PARSING_USER_IDなどの付加情報が少なく、得られる情報が限定的です。

ビュー メリット デメリット
v$sqlarea 付加情報が豊富 揮発性があり、消えやすい
v$sqlstats 揮発せず、残りやすい 付加情報が少ない

この特性を活かし、私たちは以下のようなフォールバック構成を考案しました。

基本は情報リッチなv$sqlareaを使い、もしそこにデータがなければ、情報は少なくなるが確実に記録が残っているv$sqlstatsの情報で補完する。

この合わせ技で再度カバー率を計算してみたところ、結果はほぼ100%！ ついに、私たちは信頼性の高いクエリ履歴収集の仕組みを手に入れたのです。

参照記録テーブルをもとに参照数を可視化している様子

まとめ

本記事では、Oracleのクエリ履歴をBigQueryへ連携する際に直面したv$sqlareaの揮発性の仕様と、それを乗り越えるための試行錯誤の過程をご紹介しました。

今回の取り組みで得られた重要なポイントは以下の通りです。

  • v$sqlareaは便利だが揮発性がある: 付加情報が豊富ですが、メモリから消えやすいため、日次連携などではクエリの取りこぼしが発生します。
  • データに基づいた高頻度連携が有効: クエリの実行時間帯を分析し、ピークタイムに連携頻度を上げることで、効率的に収集率を改善できました。
  • v$sqlstatsとの合わせ技が現実解: v$sqlareaの弱点を、揮発しないv$sqlstatsで補完することで、パフォーマンスへの影響を抑えつつ、収集率を大きく向上させることができました。

Oracleの動的パフォーマンスビューは、手軽に豊富な情報を得られる一方で、その特性を正しく理解しないと思わぬ落とし穴にはまることがあります。今回の私たちの経験が示すように、それぞれのビューのメリット・デメリットを把握し、目的に応じて複数のビューを組み合わせて活用することが、より良いデータ収集に繋がるのではないでしょうか。

最後までお読みいただき、ありがとうございました。

We are hiring!!

エムスリーでは、データ活用やシステム改善に一緒に取り組んでくれるエンジニアを募集しています！ご興味のある方は、ぜひ以下のリンクからご応募ください。

jobs.m3.com

*1:https://docs.oracle.com/cd/F32587_01/refrn/V-SQLAREA.html

*2:補足：なぜ最初からUNIFIED_AUDIT_TRAILを使わなかったのか?理由は主に以下の2点です。1.本番環境で常時監査を有効にすることによる、パフォーマンス劣化を懸念したため。2.v$sqlareaで取得できるMODULE情報などが、当初の目的（呼び出し元の特定）に十分だったため。このため、まずは影響の少ない動的パフォーマンスビューからのアプローチを選択しました。

*3:https://docs.oracle.com/cd/F32587_01/refrn/V-SQLSTATS.html