PR

Oracleで大量データへのSelectを高速化する手順！ステップごとに順番に解説

プログラミング
記事内に広告が含まれています。
この記事でわかること
  • Oracleの大量データへのSelect文を高速化する全体手順
  • Select文をピンポイントで高速化する手順

Oracleデータベースは数あるデータベースの中でも、高機能なデータベース製品です。
大量データを処理できるため、大規模システムに採用されることが多いです。

しかし、データの状態によって大量データへのSelect文でパフォーマンスが悪いことがあります。

この記事では、このような大量データへのSelect文でパフォーマンスが出ないときに、SQLを高速化する手順を解説します。

実際の開発現場で役立つ内容となっていますので、ぜひ最後までご覧ください。

Oracleのデータアクセスの仕組み

Oracleには、効率よくデータアクセスするために、「オプティマイザ」と呼ばれる機能があります。「オプティマイザ」は、データへの最適なアクセス方法を計算し「実行計画」と呼ばれる結果を作成します。

Oracleは、この実行計画を使ってデータにアクセスすることで、Select文などの実行結果を最適なパフォーマンスで返却します。

Select文の実行結果が遅い理由

オプティマイザは、SQL実行時のデータベースの状況やCPUの使用率の状況などから実行計画を作成します。そのため、実行時によってはデータアクセス効率がよくない実行計画が作成されることがあります。

このような実行計画が作成された場合に、Select文の実行結果を返却するまでに時間がかかってしまいます。

Select文の遅い原因の特定方法

実行するSelect文に対して、どの様なデータアクセスなのかをオプティマイザが作成した実行計画を表示して確認します。

実行計画は、SQLを実行することで確認できます。

sqlplusにログイン

まずはsqlplusで対象のデータベースにログインします。

sqlplus username/password@TNS_alias

EXPLAIN PLAN文の実行

SQL文の実行計画を生成します。実行計画を確認したいSelect文の先頭に「EXPLAIN PLANステートメント」を設定して実行します。

以下の様なSQLとなります。

EXPLAIN PLAN FOR
Select empno, ename FROM emp WHERE empno = 1234;

「EXPLAIN PLAN文の実行」でエラーとなる場合は、プラン表が設定されていない可能性があります。以下のコマンドを実行しプラン表を設定してください。

@?/rdbms/admin/utlxplan.sql

実行計画の整形設定

SQLセッションで以下コマンドを実行することで、実行計画が見やすくなります。実行計画を表示する前に実行しておきます。

SET LINESIZE 130
SET PAGESIZE 0
SET LONG 1000

実行計画の表示

DBMS_XPLAN パッケージを使用して実行計画を表示します。

以下のSQLを実行します。

Select * FROM TABLE(DBMS_XPLAN.DISPLAY);

実行計画の確認

実行計画は以下のように表示されます。

実行計画の各行には、クエリを実行する際の各ステップが示されます。

--------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------
|   0 | Select STATEMENT            |                 |     1 |    13 |     2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP             |     1 |    13 |     2 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_IDX         |     1 |       |     1 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=1234)

各項目の説明

表示される情報は以下です。

  • Id: 各操作の識別子。
  • Operation: 実行される操作。
  • Name: 操作が対象とするオブジェクトの名前（テーブル名やインデックス名）。
  • Rows: 各操作によって予測される行数。
  • Bytes: 各操作によって予測されるバイト数。
  • Cost: コストベースオプティマイザが計算したコスト。

出力内容の説明

今回のSelect文の実行計画では、以下の内容が表示されます。

  • Select STATEMENT: 最上位の操作。この操作は、全体のSelect文を示しています。
  • TABLE ACCESS BY INDEX ROWID: インデックスを使用してテーブルの行をアクセスする操作。ここでは EMP テーブルから行を取得しています。
  • INDEX UNIQUE SCAN: インデックススキャンの種類。この場合、EMP_IDX インデックスを利用して一意なスキャンを行っています。
ポイント；条件の確認

Predicate Information セクションには、各操作に関連する条件が記載されています。

access(“EMPNO”=1234): この条件により、EMPNO 列が 1234 に一致する行がインデックスを使用して検索されることが示されています。

より詳細な情報の確認

上記より詳細な情報を確認したい場合は、DBMS_XPLAN.DISPLAY 関数に追加のパラメータを渡します。

Select * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALLSTATS LAST'));

こちらの出力結果は以下のようになります。

--------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------
|   0 | Select STATEMENT            |                 |     1 |    13 |     2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP             |     1 |    13 |     2 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_IDX         |     1 |       |     1 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=1234)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan (rows marked '-' are inactive)

この詳細な表示では、動的統計情報や適応プランの有効化などの追加情報が含まれるようになります。

Select文を高速化する準備（統計情報の最適化）

実行計画は、統計情報から作成されます。そのため、統計情報が正しい状態になっていないと、最適な実行計画が作成されずアクセス効率が下がります。

ここでは、統計情報を最適化する手順を紹介します。

統計情報収集の全体の流れ

統計情報は、いくつかの単位で取得することができます。
取得漏れを防止するため、ここではすべての統計情報を収集します。

全ての統計情報の取得ステップ
  • Step1
    データベース全体の統計情報の取得
    EXEC DBMS_STATS.GATHER_DATABASE_STATS();
  • Step2
    スキーマ全体の統計情報の取得
    EXEC DBMS_STATS.GATHER_SCHEMA_STATS('schema_name');
  • Step3
    対象テーブルの統計情報の取得
    EXEC DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name');
  • Step4
    対象インデックスの統計情報の取得
    EXEC DBMS_STATS.GATHER_INDEX_STATS('schema_name', 'index_name');
  • Step5
    コミット
    COMMIT;

これで統計情報の収集は完了です。

Select文をピンポイントで高速化させる手順

処理の遅いSelect文は、インデックスを利用して検索することで高速になります。

インデックスは、Select文の先頭にヒント句を設定することでインデックスを使って検索させることができます。

ヒント句は/*+ xxxxxx */で指定が可能となります。

インデックスを利用させるには/+ INDEX_FFS(テーブル名 インデックス名) */で指定します。

以下の様に実行します。

Select /*+ INDEX_FFS(emp emp_idx) */ empno, ename FROM emp WHERE empno = 1234;

インデックスが利用されていない、高速化されない場合

上記対策をしても高速化されない（インデックスが利用されない）場合は、以下のことを試してみてください。 ほとんどが以下二つのどちらかにあてはまります。

ヒント句のスペルミス

ヒント句を利用すると強制的にインデックスを利用させれます。にもかかわらず、インデックスが利用されない場合は、ヒント句が無視されている可能性が高いです。

ヒント句はスペルミスがあっても、エラーとならず無視される仕様のためスペルミスがないか確認してください。

インデックスが存在しているか確認

ヒント句を適切に設定しているにもかかわらず、インデックスが利用されない場合は、そもそもインデックスが存在していない、または対象テーブにに設定されていない可能性が高いです。

インデックスは以下のSQLでどのテーブルに設定されているか確認できます。

Select index_name, table_name, uniqueness
FROM user_indexes
WHERE table_name = 'YOUR_TABLE_NAME';

大文字小文字違いの同名テーブルが存在している場合は、確認しているインデックスが対象のテーブルに存在しているか確認してください。 対象でないほうのテーブルに設定されている場合があります。

まとめ

  • Select文を高速化するにはインデックスを利用して検索する
  • インデックスが利用されているかは、実行計画を確認する
  • インデックスを利用してSelectするには、統計情報の収集とヒント句を設定する

Oracleは高性能なデータベースなため、採用されることが多いデータベースです。

大量データに対するSelect文を高速化する手法を身につけておくことで、現場で直面したときに役立ちます。

Select文でパフォーマンスがでなくて困っている場合は、ここで紹介した内容を試してみてください。

本記事が皆様の参考になれば幸いです。

関連記事：【AWS】LambdaをJavaで作成するには？環境構築から実行方法まで解説！

参考書籍

コメント

タイトルとURLをコピーしました