「なぜ、あのSQLは遅いのか」を正しく的確に調査する方法：しばちょう先生の試して納得！ DBAへの道（改）（2）（1/3 ページ）

データベース技術や知識は、座学だけでなく「実際に手を動かして、実際に試して」理解を深めよう──。日本オラクルのデータベーススペシャリストが「新人をDBAに育てる際に使用する課題」をベースに、DBAがすぐ実践できる即効テクニックを紹介。今回は「“SQLの実行計画と処理時間の関係”の後編」を解説します。

[柴田長，日本オラクル]

※本連載は、日本オラクルの技術解説ブログ「Oracle Technology Network：しばちょう先生の試して納得！DBAへの道」より、提供者の許可の下、最新情報の追記とともに一部改訂して転載するものです。

連載バックナンバー

　皆さんこんにちは。日本オラクルの“しばちょう”こと、柴田長（しばたつかさ）です。

　今回は、第1回「“SQLの実行計画”から処理時間の差を理解する」の続きとして、より実践的な「実行計画を使って性能差を納得する流れ」を体験していただきたいと思います。「SQLが速い／遅い」という事実だけで終わりにするのではなく、「なぜ速いのか／遅いのか」までを分析し、それを現場で実践できるようにしましょう。

バックナンバー

• 第1回 演習1：実演習前の環境準備
• 第1回 演習2：「バッファキャッシュ」上のデータをフラッシュする
• 第1回 演習3：初期化パラメータ「FILESYSTEMIO_OPTIONS」を適切に設定する
• 第1回 演習4：「TBL2」表と「TBL3」表から、それぞれ1レコード（col1=10000）だけ検索する実行時間を測定する

　早速、第1回の演習で作成したスキーマ環境で、以下の演習にチャレンジしてみてください。

演習1：TRYユーザーでデータベースへ接続後、「set autotrace on」を実行する

　「SQL*Plus」から「TRY」ユーザーでデータベースに接続し、「set autotrace on」を実行します。

$ sqlplus TRY/TRY12345
SQL> set autotrace on
　
SP2-0618: セッション識別子が見つかりません。PLUSTRACEロールが有効かを確認してください。
SP2-0611: STATISTICSレポートを使用可能にするときにエラーが発生しました。

　上記のコマンドを実行したところ、上記のように「PLUSTRACEロールが付与されていない」というエラーメッセージが表示されてしまいました。こうなった人は多いと思います。このコマンドを使うにはちょっとした事前準備が必要です。事前準備は以下のように行います。

$ sqlplus / as sysdba
SQL>
　
/* PLUSTRACEロールの作成 */
@?/sqlplus/admin/plustrce.sql
ロールが作成されました。
/* 作成されたPLUSTRACEロールを対象ユーザーに付与 */
grant PLUSTRACE to TRY ;
　
権限付与が成功しました。

　ここでは、PLUSTRACEロールを作成するスクリプトがOracle Homeディレクトリ（Oracle Databaseをインストールした場所）に保存されているので、「@?/sqlplus/admin/plustrce.sql」と実行します。このようにごく簡単な操作ですが、「つまづきがち」なポイントを3つ紹介します。

　1つ目は、SQL*Plusにおいて「?」はOracle Homeディレクトリに自動的に置換されます。ですから「@?/sqlplus/admin/plustrce.sql」という記述は、「@$ORACLE_HOME/sqlplus/admin/plustrce.sql」という意味になります。

　2つ目は「スクリプトの名称」です。PLUSTRACEロールの作成スクリプトなので「plustrace.sql」としてしまいがちですが、正しい名称は「plustrce.sql」です。Traceの「a」が欠けたファイル名であることに注意してください。

　3つ目は、PLUSTRACEロールの付与を行ったにもかかわらず、「set autotrace on」の実行時に相変わらずエラーが出力される場合があることです。これは、ロールでの権限付与は「接続済みのセッションには反映されない」仕様のためです。この場合には、新たに接続したセッションで再度試してみてください。

　ちなみに「set autotrace on」を実行すると、それを無効にする「set autotrace off」コマンドを実行するまでの間、そのセッションで実行した全てのSQLの実行結果、実行計画、パフォーマンス統計が出力され続けます。set autotrace on以外にも次のような設定が可能です。参考にしてみてください。

ワンポイント

set autotrace on以外の設定	内容
SET AUTOTRACE ON EXPLAIN	SQL文が実行され、SELECT文の場合はその結果を表示する。また、実行計画のみを表示する
SET AUTOTRACE ON STATISTICS	SQL文が実行され、SELECT文の場合はその結果を表示する。また、統計のみを表示する
SET AUTOTRACE TRACEONLY	SQL文が実行されるが、SELECT文の場合でも結果を表示しない。ただし、実行計画と統計は表示する
SET AUTOTRACE TRACEONLY EXPLAIN	SELECT文は実行されないが、DML文の場合は実行される。また、実行計画のみを表示する
SET AUTOTRACE TRACEONLY STATISTICS	SQL文が実行されるが、SELECT文の場合でも結果は表示しない。ただし、統計のみを表示する

演習2：SQLの実行計画の取得（1）　SELECT文の実行計画を取得する

　第1回の演習4『演習4：「TBL2」表と「TBL3」表から、それぞれ1レコード（col1=10000）だけ検索する実行時間を測定する』で実行した2つのSELECT文の実行計画を取得します。

$ sqlplus TRY/TRY12345
SQL>
set autotrace on
select * from TBL2 where col1 = 10000;
　
      COL1 COL2
---------- --------------------------------------------------------------------------------
     10000 10000AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
　
実行計画
----------------------------------------------------------
Plan hash value: 1207070705
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    65 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TBL2         |     1 |    65 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_TBL2_COL1 |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
　
Predicate Information (identified by operation id):
---------------------------------------------------
　
   2 - access("COL1"=10000)
　
統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          4  physical reads
          0  redo size
        580  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
　

「TBL2」表の実行計画を取得

$ sqlplus TRY/TRY12345
SQL>
set autotrace traceonly
select * from TBL3 where col1 = 10000;
　
実行計画
----------------------------------------------------------
Plan hash value: 2300171468
　
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   171 | 11115 |  4227   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TBL3 |   171 | 11115 |  4227   (1)| 00:00:01 |
--------------------------------------------------------------------------
　
Predicate Information (identified by operation id):
---------------------------------------------------
　
   1 - filter("COL1"=10000)
　
統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      15390  consistent gets
      15385  physical reads
          0  redo size
        712  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
　

「TBL3」表の実行計画を取得

　実行計画は複数の「Operation」で構成されており、ツリー構造がインデントの深さで表現されます。

　今回扱った実行計画はとてもシンプルなものなので、逆にツリー構造をイメージしにくいかもしれませんが、ともあれ以下のことには注意してください。基本的には、インデントが最も深いOperationから実行されます。しかし同じ深さのインデントのOperationが存在する場合には、上から先に実行されます。

　TBL2表に対するSELECTでは、まず「PK_TBL2_COL1」索引に対する索引スキャン「INDEX UNIQUE SCAN」で「COL1=10000」にヒットするROWIDを把握しています。次にTBL2表に対する「TABLE ACCESS BY INDEX ROWID」でROWIDにヒットするレコードにのみアクセスしていることが読み取れます。Bツリー索引のリーフ・ブロックには「ROWID」と「列データ値」がセットで格納されているからこそ、Bツリー索引をたどってピンポイントに表側のレコードにアクセスできるのです。

　一方、TBL3表に対するSELECTでは、TBL3表に対する「TABLE ACCESS FULL」で全レコードにアクセス（フルスキャン）して、「COL1=10000」にヒットするレコードをフィルタリングしていることが読み取れます。

　つまりTBL2表に対するSELECTは「索引検索」であり、一方のTBL3表に対するSELECTは「全表検索」になっています。一般的に、数レコードにヒットするSELECTでは、必要最低限のブロックアクセスで完了する「索引検索」の方が高速です。統計に出力されている「db block gets + consistent gets」（メモリに保存されているバッファ・キャッシュから読み込んだブロック数）や「physical reads」（ストレージから読み込んだブロック数）を比較しても、「索引検索」の方が圧倒的に少ないブロック数を記録します。これが、演習4で「なぜ、実行時間に大きな差が出たのか」に対する理由となります。

　では、なぜTBL3表に対するSELECTが「全表検索になってしまった」のでしょう。それは、TBL3表の「COL1」列に索引が作成されていないからです。対するTBL2表のCol1列には、主キーによる「ユニーク索引」を作成していました。