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

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

演習3：SQLの実行計画の取得（2）　「explain plan for」での実行計画の取得方法も確認する

　続いて「set autotrace」と並んでよく使われる、「explain plan for」を使った実行計画の取得方法も確認しましょう。

$ sqlplus TRY/TRY12345
SQL> 
explain plan for
select * from TBL2 where col1 = 10000;
　
解析されました。
　
SQL> @?/rdbms/admin/utlxpls
　
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
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)
　

「explain plan for」を使って実行計画を取得する

　ここでは、実行計画を取得したいSQL文を「explain plan for」に続けて記述します。そのSQL文は、SELECT文でもDML文でも実際には実行されず、実行計画だけを取得できます。このコマンドは現場でも意外と重宝するので、ぜひ覚えておいてください。ただし注意点が2つあります。

　1つ目は「バインド変数を含んだSQL文では、実際と異なる実行計画を出力することがある」ことです。バインド変数を含むSQL文の実行計画を確認したい場合には、以降の演習で紹介予定の「dbms_xplan.display_cursor」ファンクションを使用することをお勧めします。

　2つ目は、パフォーマンスのトラブルが発生した“後”で「set autotrace」や「explain plan for」で実行計画を取得しても、それは「トラブルが発生した際の実行計画ではない」可能性があることです。set autotraceやexplain plan forは、実行したその時点での実行計画なので、データベースの状態、正確には統計情報が変化しているかもしれません。この場合には、「dbms_xplan.display_cursor」ファンクションで解決可能です。まさにトラブル発生時の実行計画を取得できます。

演習4：同じ「全レコードの総数集計」でも実行時間が違う理由を理解する

　では、「TBL2」表と「TBL3」表から、それぞれ「全レコードの総数を集計する実行時間」を測定してみましょう。

sqlplus /nolog
SQL>
connect / as sysdba
alter system FLUSH BUFFER_CACHE;
　
connect TRY/TRY
set timing on
select count(*) from TBL2;
　
  COUNT(*)
----------
   1000000
　
経過: 00:00:02.61
　
　
connect / as sysdba
alter system FLUSH BUFFER_CACHE;
　
connect TRY/TRY
set timing on
select count(*) from TBL3;
　
  COUNT(*)
----------
   1000000
　
経過: 00:00:10.58
　

「TBL2」表と「TBL3」表から、「全レコードの総数を集計する実行時間」を測定

　あれ？　なぜだ？　と思っていただきたい演習です。TBL2表とTBL3表は同じ100万行のレコードが格納されているので、2つのSELECT文の結果もキッチリと100万となっています。全レコードへアクセスしているのだから、全表検索（実行計画では「TABLE ACCESS FULL」）だと推測されますが、なぜここまで実行時間に差が出てしまうのでしょうか。

　この理由を追究するのが、現場のトラブルシューティングでも非常に重宝する実行計画の取得方法である、「dbms_xplan.display_cursor」ファンクションを使うことです。