buffer busy waits を Right Growing Index で発生させてみる(Oracle Database)

本記事では Oracle Database の待機イベントである buffer busy waits を Right Growing Index という事象で発生させてみます。
彡(^)(^)

1. buffer busy waits とは

buffer busy waits は下記のマニュアルに記載があります。Oracle Database の待機イベントとなります。

Oracle Database データベース・パフォーマンス・チューニング・ガイド 23ai
11 パフォーマンス・ビューを使用したインスタンスのチューニング
buffer busy waits
https://docs.oracle.com/cd/G11854_01/tgdba/instance-tuning-using-performance-views.html#GUID-03401D0F-DB3E-49E5-89E0-2F2A6164A5C0
この待機は、複数のプロセスがバッファ・キャッシュ内のいくつかのバッファに同時にアクセスしようとしていることを示します。
バッファのクラスごとに、待機統計についてV$WAITSTATを問い合せます。バッファ・ビジー待機を持つ一般的なバッファ・クラスには、
data block、segment header、undo headerおよびundo blockがあります。

Oracle Database の複数プロセスがバッファ・キャッシュ上のブロックに同時アクセスした際に、競合が発生すると本待機イベントが発生します。
この待機イベント/競合はいくつかの発生パターンが有るのですが、この記事では Right Growing Index と呼ばれる事象でデータ・ブロックの競合を発生させてみます。

2. Right Growing Index とは

一般的な B-Tree索引 の場合、索引のツリーは索引キーに使用した列値の順番でソートされます。下記記事の図が良さげなので引用させて頂きます。

How Oracle B-tree Indexes Work
https://blog.toadworld.com/2017/05/08/how-oracle-b-tree-indexes-work

索引に対する更新が単調増加するワークロード特性の場合、ソートされた索引ツリーの右端のリーフに更新が集中します。
この特性を持つ索引への更新が複数プロセスで実行されると、更新が集中する右端のリーフで競合が発生します。

上記の右端のリーフで発生する競合 および 競合で発生する問題/性能遅延が、いわゆる Right Growing Index と呼ばれる事象になります。

3. 検証環境(OCI Compute, Autonomous Database)

以下の環境で検証しました。Compute → Autonomous Database Serverless となります。ある程度の負荷を掛けても I/Oネック が発生しない環境が良かったので、こういう時に Exadata を安価に使える Autonomous Database は便利ですやね。
彡(^)(^)

Autonomous Database の ECPU数は 8 で Autoscaling を有効化、ストレージは 1TB ほど確保しました。

Private Compute には Instant Client をインストールしておきます。

sudo dnf install oracle-instantclient-release-23ai-el8.x86_64
sudo dnf install oracle-instantclient-basic
sudo dnf install oracle-instantclient-sqlplus.x86_64
sudo dnf install oracle-instantclient-tools.x86_64
sudo dnf install oracle-instantclient-jdbc.x86_64

4. テーブルと索引と実行したワークロード(SQL)

buffer busy waits を発生させるために作成したテーブルと索引は下記になります。TIMESTAMP列のみの超シンプル構造！

CREATE TABLE BUFFER_BUSY_WAITS_TEST (
  C1 TIMESTAMP
);

CREATE INDEX BUFFER_BUSY_WAITS_TEST_I1 ON BUFFER_BUSY_WAITS_TEST(C1);

buffer busy waits を発生させる SQL は下記です。SYSTIMESTAMP関数の結果をひたすら INSERT します。SYSTIMESTAMPは時間経過と共に単調増加するので、多重実行すれば Right Growing Index が発生します。

busy.sql

CONNECT ADMIN/<パスワード>@<Autonomous Database の接続文字列>
SET ECHO ON;
SET TIME ON;
SET TIMING ON;
BEGIN
  FOR i IN 1..1000000
  LOOP
    INSERT INTO BUFFER_BUSY_WAITS_TEST VALUES(SYSTIMESTAMP);
    IF i MOD 1000 = 0 THEN
      COMMIT;
    END IF;
  END LOOP;
  COMMIT;
END;
/
EXIT;

上記の SQL を多重で実行します。nohup でバックグラウンド実行して SSH接続 が切れても終了しないようにします。

export ORACLE_HOME=/usr/lib/oracle/23/client64
export PATH=${ORACLE_HOME}/bin:${PATH}
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &

1セッションの実行結果を確認してみます。1セッション辺りの実行時間はざっくり3分38秒程度でしょうか。他のセッションもだいたい同じような傾向でした。

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Fri Apr 25 10:17:15 2025
Version 23.7.0.25.01

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected.
SQL> SET TIME ON;
10:17:15 SQL> SET TIMING ON;
10:17:15 SQL> BEGIN
10:17:15   2  	FOR i IN 1..1000000
10:17:15   3  	LOOP
10:17:15   4  	  INSERT INTO BUFFER_BUSY_WAITS_TEST VALUES(SYSTIMESTAMP);
10:17:15   5  	  IF i MOD 1000 = 0 THEN
10:17:15   6  	    COMMIT;
10:17:15   7  	  END IF;
10:17:15   8  	END LOOP;
10:17:15   9  	COMMIT;
10:17:15  10  END;
10:17:15  11  /

PL/SQL procedure successfully completed.

Elapsed: 00:03:38.05
10:20:53 SQL> EXIT;
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.1.0

5. buffer busy waits の発生状況を ADB-S の Performance Hub で確認する。

Autonomous Database には Performance Hub という性能情報を GUI で確認できるインターフェースが標準装備されています。この Performance Hub で buffer busy waits の発生状況を確認します。

対象の Autonomous Database から Performance Hub というボタンをクリックして遷移します。ASH Analytics の ASH Dimension を Wait Events に変更します。

buffer busy waits の発生が確認できます。茶色の帯でサンプリングされているところですね。

6. buffer busy waits の発生状況を ASH(Active Session History) で確認する。

buffer busy waits の発生状況を ASH(Acitve Session History) から確認します。ASH は具体的に言うと下記のディクショナリ/動的ビューが該当します。

• V$ACTIVE_SESSION_HISTORY
• GV$ACTIVE_SESSION_HISTORY
• DBA_HIST_ACTIVE_SESS_HISTORY

今回は実行直後に GV$ACTIVE_SESSION_HISTORY を CSV形式 で出力して中身を確認します。日付時刻などは適宜編集しましょう。

gv_ash.sql

CONNECT ADMIN/<パスワード>@接続文字列
SET ECHO ON;
SET TIME ON;
SET LINESIZE 32767;
SET PAGESIZE 32767;
SET TRIMSPOOL ON;
SET TERMOUT OFF;
SET FEEDBACK OFF;
SET MARKUP CSV ON;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY/MM/DD HH24:MI:SS.FF';

SPOOL gv_active_session_history.csv
SELECT *
  FROM GV$ACTIVE_SESSION_HISTORY
 WHERE SAMPLE_TIME BETWEEN TO_TIMESTAMP('2025/04/25 10:16:00', 'YYYY/MM/DD HH24:MI:SS')
                       AND TO_TIMESTAMP('2025/04/25 10:22:00', 'YYYY/MM/DD HH24:MI:SS');
SPOOL OFF;

EXIT;

出力した CSV を Excel で開いて EVENT列など一部の列と行を抜粋して確認してみます。

ASH(GV$ACTIVE_SESSION_HISTORYビュー)でも buffer busy waits の発生を確認できました。

7. HASH PARTITION で buffer busy waits を解消してみる。

今回発生させた buffer busy waits は索引のリーフブロックで発生した競合が原因となります。HASH PARTITION で競合の解消を試みます。

DROP TABLE BUFFER_BUSY_WAITS_TEST PURGE;

CREATE TABLE BUFFER_BUSY_WAITS_TEST (
  C1 TIMESTAMP
)
PARTITION BY HASH (C1) PARTITIONS 8
;

CREATE INDEX BUFFER_BUSY_WAITS_TEST_I1 ON BUFFER_BUSY_WAITS_TEST(C1) LOCAL;

HASH PARTITION でデータを分散、ローカル索引(LOCAL句)で索引もパーティション化します。

同一のワークロードを実行します。

export ORACLE_HOME=/usr/lib/oracle/23/client64
export PATH=${ORACLE_HOME}/bin:${PATH}
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &

Performance Hub を確認します。

パーティション化していないケースと比較して buffer busy waits の発生が減少していることを確認できました。

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Fri May 2 08:56:43 2025
Version 23.7.0.25.01

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected.
SQL> SET TIME ON;
08:56:43 SQL> SET TIMING ON;
08:56:43 SQL> BEGIN
08:56:43   2  	FOR i IN 1..1000000
08:56:43   3  	LOOP
08:56:43   4  	  INSERT INTO BUFFER_BUSY_WAITS_TEST VALUES(SYSTIMESTAMP);
08:56:43   5  	  IF i MOD 1000 = 0 THEN
08:56:43   6  	    COMMIT;
08:56:43   7  	  END IF;
08:56:43   8  	END LOOP;
08:56:43   9  	COMMIT;
08:56:43  10  END;
08:56:43  11  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:32.50
08:58:16 SQL> EXIT;
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.1.0

1セッションあたりの実行時間も1分32秒程度と改善していることが確認できます。

8. 逆キー索引 で buffer busy waits を解消してみる。

逆キー索引で buffer busy waits の解消を試みます。Oracle Database の逆キー索引は列値のバイト値を逆に格納してソートすることで、単調増加する列値でも特定ブロックに更新が集中しないようになります。

逆キー索引はBツリー索引の一種であり、列の順序は保ちながら、各索引キーのバイトを物理的に逆にします。
たとえば、索引キーが20であり、このキーに対して標準Bツリー索引で格納される16進数の2バイトがC1,15である場合、逆キー索引では、バイトが15,C1として格納されます。

逆キー索引の注意事項として、バイト値を逆にして格納する影響で RANGE SCAN が利用不可となります。索引経由の範囲検索が不可になって一本釣り専用になってしまうということですね。
彡(ﾟ)(ﾟ)

DROP TABLE BUFFER_BUSY_WAITS_TEST PURGE;

CREATE TABLE BUFFER_BUSY_WAITS_TEST (
  C1 TIMESTAMP
);

CREATE INDEX BUFFER_BUSY_WAITS_TEST_I1 ON BUFFER_BUSY_WAITS_TEST(C1) REVERSE;

索引作成の REVERSE句 で逆キー索引とします。

同一のワークロードを実行します。

export ORACLE_HOME=/usr/lib/oracle/23/client64
export PATH=${ORACLE_HOME}/bin:${PATH}
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &
nohup sqlplus /nolog @busy.sql >> $(date +busy_sql_%Y%m%d_%H%M%S_%4N_$$.log) 2>&1 &

Performance Hub を確認します。

通常の B-Tree索引のケースと比較して buffer busy waits の発生が減少していることを確認できました。

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Fri May 2 09:19:37 2025
Version 23.7.0.25.01

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected.
SQL> SET TIME ON;
09:19:37 SQL> SET TIMING ON;
09:19:37 SQL> BEGIN
09:19:37   2  	FOR i IN 1..1000000
09:19:37   3  	LOOP
09:19:37   4  	  INSERT INTO BUFFER_BUSY_WAITS_TEST VALUES(SYSTIMESTAMP);
09:19:37   5  	  IF i MOD 1000 = 0 THEN
09:19:37   6  	    COMMIT;
09:19:37   7  	  END IF;
09:19:37   8  	END LOOP;
09:19:37   9  	COMMIT;
09:19:37  10  END;
09:19:37  11  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:58.30
09:20:35 SQL> EXIT;
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.1.0

1セッションあたりの実行時間も1分弱程度と改善していることが確認できます。

9. まとめ

ブロック競合による buffer busy waits を発生させて、更に対策も確認できました。参考にしてくださいね。
彡(^)(^)