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

» 2017年06月28日 05時00分 公開
[柴田長日本オラクル]

演習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」ファンクションを使うことです。

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。