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

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

演習5：「dbms_xplan.display_cursor」ファンクションで原因を追及する

　SQL*Plusから「TRY」ユーザーでデータベースに接続し、対象のSQL文を実行した後で「dbms_xplan.display_cursor」ファンクションを使用します。

$ sqlplus TRY/TRY12345
SQL> select count(*) from TBL2;
　
  COUNT(*)
----------
   1000000
　
SQL> select * from table(dbms_xplan.display_cursor());
　
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION
　
　
SQL> connect / as sysdba
SQL> grant select on V$SESSION to TRY;
grant select on V$SESSION to TRY
                *
行1でエラーが発生しました。:
ORA-02030: 固定表/固定ビューでは選択のみ可能です。

　このファンクションを実行するには事前準備（権限の付与）が必要であり、権限が不足しているというエラーが発生します。そのために「V$SESSION」のSELECT権限を付与しようと試みると、今度はORAエラーが発生してしまいました。

　実行計画の比較の話から少し脱線しますが、ちょうど良いタイミングなので解説しておきます。「V$SESSION」などのデータ・ディクショナリ・ビューは、実体ではなく「シノニム（SYNONYM）」です。V$SESSIONという名前のVIEWは、実は存在しないのですね。例えば以下のように、データ・ディクショナリ・ビューの「DBA_SYNONYMS」に問い合わせることで、V$SESSIONの実態は「V_$SESSION」であることを確認できます。

$ sqlplus / as sysdba
SQL>
select SYNONYM_NAME, TABLE_NAME 
from DBA_SYNONYMS 
where SYNONYM_NAME = 'V$SESSION';
　
SYNONYM_NAME                   TABLE_NAME
------------------------------ ------------------------------
V$SESSION                      V_$SESSION
　

「DBA_SYNONYMS」に問い合わせる

　……というわけで、V$SESSIONにSELECT権限を付与したいならば、実体である「V_$SESSION」に対してSELECT権限を付与するのが正しい操作です。

　また、本題である「dbms_xplan.display_cursor」ファンクションを使用するには、その他のデータ・ディクショナリ・ビューのSELECT権限も必要となります。以下のGRANT文を参考に実行してください。

$ sqlplus / as sysdba
SQL>
grant select on V_$SESSION to TRY;
grant select on V_$SQL_PLAN to TRY;
grant select on V_$SQL to TRY;
grant select on V_$SQL_PLAN_STATISTICS_ALL to TRY;

その他のデータ・ディクショナリ・ビューのSELECT権限も付与する

　これで、ようやく「dbms_xplan.display_cursor」ファンクションを使える状態になりました。再度実行してみましょう。

　ちなみに、実行計画を確認したいSQL文を実行後、引数なしの状態で「dbms_xplan.display_cursor」ファンクションを実行すると、そのセッションで最後に実行された（直前の）SQL文の実行計画が出力されます。また、「V$SQL」や「V$SQLAREA」データ・ディクショナリ・ビューのSQL_IDカラムから取得したSQL_IDを第一引数に埋め込んで実行することによって、特定のSQL文の実行計画を取得することも可能です。詳細については、Oracle Databaseのマニュアル「PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス」も参照ください。

$ sqlplus TRY/TRY12345
SQL> select count(*) from TBL2;
　
  COUNT(*)
----------
   1000000
　
SQL> select * from table(dbms_xplan.display_cursor());
　
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID  61z00xm8cw770, child number 0
-------------------------------------
select count(*) from TBL2
　
Plan hash value: 3652068985
　
------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |       |   586 (100)|          |
|   1 |  SORT AGGREGATE       |              |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_TBL2_COL1 |   920K|   586   (2)| 00:00:01 |
------------------------------------------------------------------------------
　

TBL2の実行計画を「dbms_xplan.display_cursor」ファンクションで調べる

$ sqlplus TRY/TRY12345
SQL> select count(*) from TBL3;
　
  COUNT(*)
----------
   1000000
　
経過: 00:00:10.58
　
SQL> select * from table(dbms_xplan.display_cursor());
　
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID  9pytj15maxmjb, child number 0
-------------------------------------
select count(*) from TBL3
　
Plan hash value: 2630253756
　
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |  4225 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TBL3 |  1010K|  4225   (1)| 00:00:01 |
-------------------------------------------------------------------
　

TBL3の実行計画を「dbms_xplan.display_cursor」ファンクションで調べる

　この結果から実行計画の比較を行います。TBL3表に対するSELECTでは、予想通り「TABLE ACCESS FULL」が選択されていることを確認できます。一方のTBL2表に対するSELECTでは「INDEX FAST FULL SCAN」という“いかにも速そう”な名称のOperationが選択されていることを確認できました。

　この「INDEX FAST FULL SCANが選択されたこと」が、TBL2表に対するSELECTの方が高速だった理由です。

　参考として、「Bツリー索引のリーフ・ブロックには、ROWIDと索引キーの列データが格納されていることを理解している人」に対して、もう少し詳細を補足します。

　「INDEX FAST FULL SCAN」は、索引ブロックを全てスキャンし、その中からリーフ・ブロックに格納されている索引キーの列データを取り出すことで、全表検索で表ブロックにアクセスせずに、列データを取得できるようになっています。

　一般的には表セグメントよりも索引セグメントのサイズの方が小さいので、INDEX FAST FULL SCANでは、アクセスするブロック数が相対的に少なく、結果として高速化が期待できるのです。ちなみに、今回の演習環境では「PK_TBL2_COL1」索引とTBL3表のセグメントサイズは次のようになっていました。

$ sqlplus TRY/TRY12345
SQL> 
select SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS 
from USER_SEGMENTS
where SEGMENT_NAME in ('PK_TBL2_COL1','TBL3');
　
SEGMENT_NAME             SEGMENT_TYPE       TABLESPACE_NAME                    BLOCKS
------------------------ ------------------ ------------------------------ ----------
TBL3                     TABLE              TBS_BIG                             16384
PK_TBL2_COL1             INDEX              TBS_BIG                              2176
　

「PK_TBL2_COL1」索引とTBL3表のセグメント・サイズ

　圧倒的に索引セグメント「PK_TBL2_COL1」の方がサイズが小さいことを確認できますね。

---

　今回は、実行計画から処理時間を比較するための、比較的簡単な方法を3つ紹介しました。これ以外にも、「SQLトレース」や「Statspack/AWR」からの実行計画の取得、Oracle Database 11gから導入された「リアルタイムSQL監視」、さらにはツール化したものではない「V$SQL_PLANの直接参照」など、さまざまな手法があります。

　トラブルシューティングの現場では、状況によっては、実行計画を取得するコマンドを実行する「前段階」の準備作業でさえ許可されない場合や、問題のあるSQLは再実行・再測定不可という場面もよくあります。いつでも全ての手法、慣れた手法が使えるわけではありません。自身でさまざまな手法を経験しておくことは、この先、必ず役に立ちます。ぜひ、自分の手でコマンドを入力して試してください。

　次回は、「さらに高度なSQLの実行計画の取得方法」を紹介する予定です。それではまた次回お会いしましょう！

筆者紹介

柴田長（しばた つかさ）

日本オラクル データベーススペシャリスト。Oracle GRID Centerの設立当初からオラクルの持つ最新技術をパートナー各社と共同で検証し、これまでにリアルなパフォーマンスに裏付けられた数多くのホワイトペーパーを執筆。2017年現在は、大規模案件の現場を訪問し、お客さまのシステムに最適なソリューションデザインの提案やパフォーマンストラブルの問題解決に従事している