Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

SQL*Plus を使った実行計画の取得

SQL*Plus には実行計画と統計情報を取得するために以下の AUTOTRACE 設定がある

EXPLAIN PLAN 文による実行計画の取得とは異なり DML の処理とフェッチ、データ転送処理も行なわれる(※)ので大量の件数を取得する検索文の場合には注意する。

(※) TRACEONLY を使用することでデータ表示に伴う速度の低下は軽減することは可能

AUTOTRACE 準備作業

実行計画と統計情報は 動的パフォーマンス・ビュー を参照する必要があるため一般ユーザーには使用できない場合がある。

PLUSTRACE ロールの作成スクリプト
(sys の v_$sesstat,v_$statname,v_$mystat パフォーマンスビューへの参照権限をもつロールの作成)

SYSDBA 権限で接続 (Windows 系でも '/' によるディレクトリ表記で問題ない)
# sqlplus / as sysdba
SQL> @?/sqlplus/admin/plustrce.sql
...
-- RIVUS ユーザーに PLUSTRACE ロールを付与
SQL> GRANT PLUSTRACE TO RIVUS;

準備作業 その２

実行計画を一時的に保存しておくテーブルの作成。
現在のバージョンにおいて PLAN_TABLE の作成は必須ではない。現在の初期設定では

CREATE GLOBAL TEMPORARY TABLE SYS.PLAN_TABLE$ ...
CREATE PUBLIC SYNONYM PLAN_TABLE FOR SYS.PLAN_TABLE$

の一時表の定義が使用されている。
utlxplan.sql の PLAN_TABLE は永続表であるが、SYS.PLAN_TABLE$ は一時表のために実行の度に 内容を削除しなくてもよいなど都合がよい。 SQL*Plus で DESC PLAN_TABLE で表定義が表示されるようであれば作成しない。

ユーザー・スキーマに PLAN_TABLE を作成

SQL> CONN RIVUS/password
SQL> @?/rdbms/admin/utlxplan.sql
.. 表の作成

実行計画の取得

実行計画の取得例

SQL> set lines 200
SQL> col plan_plus_exp format a200
SQL> set pages 0
SQL> set autotrace on
SQL> select num from test2 where num = 1;
 
レコードが選択されませんでした。
 
 
実行計画
----------------------------------------------------------
Plan hash value: 2520579295
 
------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST2_IX1 |     1 |     4 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("NUM"=1)
 
 
統計
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        283  bytes sent via SQL*Net to client
        388  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Predicate Information

オペレーションID（実行計画の ID列）の従属情報
例の場合には TEST2_IX1 のインデックスに対して "NUM"=1 で INDEX RANGE SCAN アクセスして結果を抽出していることを表わす。

この操作には Access Predicates（アクセス述語）と Filter Predicates （フィルタ述語）がある。

Access Predicates は、行（主に複数の行）を特定する操作に使用する述語（条件）を表わす。（能動的な操作のイメージ）
例えば ROWID を戻すようなアクセス構造体（インデックス）に対してキーを指定してアクセスする。

Filter Predicates は、結果セット（中間結果セット）を生成する前に条件に合うものをフィルタする述語（条件）を表わす。
つまり filter にはアクセスするための個別のアクセス構造（インデックスやジョイン）でなくテーブルやビューなどから中間結果セット（リザルトセット）の生成する前段のデータストリームの中から余分なものを取り除く操作を行なう。（受動的な操作のイメージ）
この「ふるい」操作には単純な評価式だけではなくクエリーが含まれることもある。

実行計画の統計名と意味

データベース統計名	説明
recursive calls	再帰的コールの回数（ユーザーおよびシステムレベル）
db block gets	ブロックの要求回数
consistent gets	ブロックレベルの一貫性読込み回数
physical reads	物理読込みの合計数 (physical reads direct + physical reads cache)
redo size	生成された REDO の合計（バイト）
bytes sent via SQL*Net to client	Oracle Net 経由でクライアントに送信されたバイト数
bytes received via SQL*Net from clien	クライアントから受信したバイト数
SQL*Net roundtrips to/from client	Oracle Net の送受信のやり取りの合計数
sorts (memory)	完全にメモリー内で実行されたソートの数
sorts (disk)	ディスク書込みを伴ったソートの数
rows processed	処理を行なった行数

---

実行計画の関連事項

• EXPLAIN PLAN による実行計画の取得
• 実行計画の操作の意味