はじめに

こんにちは、SREセクションの子安です。

Oisixのサービスでは、データベースに Amazon RDS for Oracle Enterprise Edition (EE) を採用し、可用性とスケーラビリティを確保するために Active Data Guard 構成を組んでいます。

サービスの特性上、商品の情報を閲覧するような「読み取り（参照系）」の処理がとても多いのが特徴です。

そのため、読み取り専用の リードレプリカ に処理を逃がすことで、書き込みを行う プライマリDB の負荷を減らし、サービス全体のパフォーマンスを安定させています。

今回は、この構成ならではの「リードレプリカでだけSQLのパフォーマンスが急に悪化する問題」と、それを解決するためにデータベースエンジニアとしての経験を活かして取り組んだ、ちょっとした工夫についてお話しします。

そもそも「実行計画」ってなんだっけ?

本題に入る前に、まず「実行計画」について簡単におさらいをします。

Oracleをはじめとする多くのデータベースは、私たちが書いたSQLを受け取ると、そのまま実行するわけではありません。

「どの順番でテーブルを結合しようかな？」「この条件なら、どのインデックスを使うのが一番速いかな？」といったように、SQLを最も効率的に実行するための手順を内部的に組み立てます。

この「データベースが考えた最適な処理手順書」こそが、実行計画（Execution Plan） です。料理人がレシピ（SQL）を見て、最高の味付けや調理手順（実行計画）を考えるのに似ていますね。

なぜ実行計画を「固定」する必要があるのか?

実はこの実行計画、データベースが持つ統計情報（テーブルの行数など）や、その時の状況によって変化することがあります。昨日まで高速だったSQLが、今日は全く違う手順で実行されてしまい、パフォーマンスが急激に悪化する…なんてことも起こり得ます。

そこで重要になるのが「実行計画の固定化」です。一度見つけた「最高のレシピ」を保存しておき、いつでも同じ手順で実行できるようにして、パフォーマンスを安定させるのです。Oracleでは、SQL Plan Management (SPM) という機能を使ってこれを実現します。

直面した「壁」

さて、ここからが本題です。

課題：リードレプリカのSQLに対する実行計画の固定化

私たちの構成では、前述の通り「参照系のSQLはすべてリードレプリカで処理する」というルールで運用しています。これにより、プライマリDBの負荷を軽減できるという大きなメリットがあります。しかし、ここで問題が発生しました。リードレプリカでのみ実行される特定のSQLの実行計画が、ある日突然非効率なものに変わってしまい、パフォーマンスが劣化する事象がたびたび起きたのです。

「それなら、いつものように実行計画を固定すればいいじゃないか」と考えますよね。ところが、リードレプリカでしか実行されないSQLは、実行計画を固定化できないという大きな壁にぶつかりました。

なぜリードレプリカでは固定化できないのか？

Active Data Guard構成では、プライマリDBが「親」、リードレプリカは「子」のような関係です。実行計画のキャッシュや固定化といった重要な設定変更は、すべて親であるプライマリDBしか許可されていません。リードレプリカはあくまで読み取り専用なので、たとえリードレプリカ側で一時的に良い実行計画が生まれたとしても、それを保存・固定する権限がないのです。

つまり、実行計画を安定させるためには、対象のSQLを一度プライマリDBで実行してあげる必要があるという制約がありました。

さらなる課題：やっかいな「バインド変数」問題

「じゃあ、問題のSQLを手動でプライマリDBで実行すればいいんだ！」と考えたのですが、ここにもう一つ、やっかいな問題がありました。アプリケーションが発行するSQLには「バインド変数」が使われているのです。

アプリケーションが実行するSQLは、こんな形をしています。

-- アプリケーションが投げるSQL
SELECT * FROM test_table WHERE id = :1;

この :1 は「1番目の変数」という意味で、プログラム側で具体的な値（例えば 50）がセットされます。これを位置指定バインド変数と呼びます。

しかし、このSQLはそのままでは実行できず、私たちが手動で再現しようとすると少し形が変わってしまいます。

-- 人間が手動で実行するSQL
VAR var1 NUMBER
EXEC :var1 := 50;
SELECT * FROM test_table WHERE id = :var1;

このように :var1 という名前をつけた変数を使う必要があり、これを名前付きバインド変数と呼びます。

データベースにとって、:1 と :var1 は全くの別人です。たとえSQLの他の部分が同じでも、この変数の書き方が違うだけで「SQL_ID」というSQLの識別番号が変わってしまい、「違うSQL」として扱われてしまうのです。これでは、プライマリDBで実行しても意味がありません。

これまでの一時対応と、その限界

これまでは、以下のような方法でなんとか対応していました。

コードを修正する
問題のSQLを、一時的にプライマリDBに向けるようにアプリケーションの向き先を変更する
実行計画を固定する
プライマリDBでSQLが実行されたのを確認し、SPMで実行計画を固定する。
コードを元に戻す
再度、リードレプリカに向くように修正する

しかし、この方法にはいくつかの課題がありました。

スピードが出ない：コード修正や有識者によるレビューで、問題解決までに時間がかかってしまう。
再現が難しい：アプリケーション経由で実行しないと、正しい実行計画が再現できないことがある。

パフォーマンス問題は時間との勝負です。もっと素早く、より簡易的な方法で完結できないだろうか…？

解決策：Pythonで "なりすまし" 実行

私たちがたどり着いたのが、Pythonの cx_Oracle ドライバ を使う方法です。

cx_Oracle は、まさにアプリケーションと同じように、:1 や :2 という形式のバインド変数を扱えるため、SQLの文字列を一切変えることなく実行できます。

これを使って、SREの作業環境から直接プライマリDBにSQLを実行し、実行計画を覚えさせます。

実践！Pythonを使った実行計画の固定手順

実際にどのように対応したか、テストテーブルを例にご紹介します。

1. 対象SQLの確認

まず、リードレプリカでパフォーマンスが悪化しているSQLの SQL_ID とSQL本文を正確に確認します。

-- Replica側で実行
SQL> select sql_id, sql_text from v$sql where sql_text = 'select * from test_table where id = :1';

SQL_ID        SQL_TEXT
------------- ------------------------------------
93aqagsa8gp3h select * from test_table where id = :1

また、プライマリDBでSQLが存在しないことを確認します。

-- Primary側で実行
SQL > select sql_id,sql_text from v$sql where sql_text = 'select * from test_table where id = :1';

no rows selected

2. Pythonスクリプトの準備

次に、確認したSQL文をそのまま実行するPythonスクリプトを作成します。

sql_bind_example.py

import cx_Oracle

# 接続先はプライマリDBを指定！
USERID = "*****"
PASSWORD = "*****"
DESTINATION = "*****"

# リードレプリカ側で確認したSQL文を、一字一句変えずに設定
sql = "select * from test_table where id = :1"

# データベースに接続してSQLを実行
connection = cx_Oracle.connect(USERID, PASSWORD, DESTINATION)
cursor = connection.cursor()

# 第2引数でバインド変数に値を渡す（値は適当でOK）
cursor.execute(sql, ('1'))

# 結果は使わないが、念のためループを回しておく
for row in cursor:
    print(row[0])

cursor.close()
connection.close()

print("Execution finished!")

3. スクリプトの実行

このPythonスクリプトを実行します。

$ python ./sql_bind_example.py

4. 結果確認

最後に、プライマリDB側で、意図した SQL_ID でSQLが実行されたかを確認します。

-- Primary側で実行
SQL> select sql_id, sql_text from v$sql where sql_text = 'select * from test_table where id = :1';

SQL_ID        SQL_TEXT
------------- ------------------------------------
93aqagsa8gp3h select * from test_table where id = :1

リードレプリカ側で確認した SQL_ID (93aqagsa8gp3h) と同じIDで、プライマリDB側にSQLをキャッシュさせることに成功しました。あとは、この SQL_ID を使って、SPMで適切な実行計画を固定すれば対応完了です。

得られた成果と、これからのこと

この仕組みを利用することで今後の改善が見込めました。

コード修正が不要：PythonからプライマリDB側に実行できることでアプリケーションのコード修正が不要になった
スピード向上：パフォーマンス問題発生時の初動対応速度を向上できる
安定運用：リードレプリカでしか実行されないSQLでも、実行計画を安定化させることができる

おわりに

今回は、Amazon RDS for OracleとActive Data Guard構成という特定の環境下で、参照系SQLの実行計画を安定させるための改善活動についてご紹介しました。

今回紹介した方法は、私たちの環境やチーム体制においてはベストな選択でしたが、これが唯一の正解というわけではありません。システムの特性やチーム間の役割分担によって、最適な解決策は変わってくるはずです。「銀の弾丸はない。だからこそ、現場に合わせた柔軟な工夫が大切だ」と改めて感じました。

実行計画の世界は本当に奥が深く、安定性と柔軟性のバランスを取るのは常に悩ましい問題です。しかし、こうした制約の中でどう立ち向かうか、そこにデータベースエンジニアとしての面白さや、腕の見せ所があるのかもしれません。

この記事が、同じような課題に直面している誰かのヒントになれば幸いです。

Oisix ra daichi Creator's Blog（オイシックス・ラ・大地クリエイターズブログ）

オイシックス・ラ・大地株式会社のエンジニア・デザイナーが執筆している公式ブログです。

RDS for Oracleの『バインド変数』問題をPythonで解決！リードレプリカの実行計画を固定化した小ワザ