「なぜ、あのSQLは遅いのか」を正しく的確に調査する方法：しばちょう先生の試して納得！ DBAへの道（改）（2）（3/3 ページ）
データベース技術や知識は、座学だけでなく「実際に手を動かして、実際に試して」理解を深めよう──。日本オラクルのデータベーススペシャリストが「新人をDBAに育てる際に使用する課題」をベースに、DBAがすぐ実践できる即効テクニックを紹介。今回は「“SQLの実行計画と処理時間の関係”の後編」を解説します。
演習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
……というわけで、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;
これで、ようやく「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 | ------------------------------------------------------------------------------
$ 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表に対する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」の方がサイズが小さいことを確認できますね。
今回は、実行計画から処理時間を比較するための、比較的簡単な方法を3つ紹介しました。これ以外にも、「SQLトレース」や「Statspack/AWR」からの実行計画の取得、Oracle Database 11gから導入された「リアルタイムSQL監視」、さらにはツール化したものではない「V$SQL_PLANの直接参照」など、さまざまな手法があります。
トラブルシューティングの現場では、状況によっては、実行計画を取得するコマンドを実行する「前段階」の準備作業でさえ許可されない場合や、問題のあるSQLは再実行・再測定不可という場面もよくあります。いつでも全ての手法、慣れた手法が使えるわけではありません。自身でさまざまな手法を経験しておくことは、この先、必ず役に立ちます。ぜひ、自分の手でコマンドを入力して試してください。
次回は、「さらに高度なSQLの実行計画の取得方法」を紹介する予定です。それではまた次回お会いしましょう！
Copyright © ITmedia, Inc. All Rights Reserved.
関連記事
【Oracle Database】忘れていませんか？ 「アラートログ調査」に必要な、たった3つのキホン
データベース管理システムの運用でトラブルが発生したらどうするか。データベースサポートスペシャリストが現場目線の解決Tipsをお届けします。今回は基本編として「アラートログの調査で押さえるべき3つのポイント」を解説します。【Oracle Database 12c対応版】
障害発生！ 問題切り分けはスピード勝負
Oracleデータベースの運用管理者は、突発的に直面するパフォーマンス障害にどうやって対処したらよいか。本連載は、非常に複雑なOracleのアーキテクチャに頭を悩ます管理者に向け、短時間で問題を切り分け、対処法を見つけるノウハウを紹介する。対象とするバージョンはOracle8から9iまでを基本とし、10gの情報は随時加えていく。（編集局）
パフォーマンス向上の最短コースを知る
本連載では、Oracleデータベースのパフォーマンス・チューニングの中から、特にSQLのチューニングに注目して、実践レベルの手法を解説する。読者はOracleデータベースのアーキテクチャを理解し、運用管理の実務経験を積んでいることが望ましい。対象とするバージョンは現状広く使われているOracle9iの機能を基本とするが、Oracle 10gで有効な情報も随時紹介していく。（編集局）- SQL実践講座
SQLは、データを操作するために非常に簡単な構文で構成されているように見えます。ところが実際に使い込んでいくと、一見簡単に取得できるように見えるデータが取得できない場面にぶち当たることもあります。こういった場面のために、SQLの効率的な使い方をエッセンスにしてお伝えします。