OracleでSQLがどのように実行されるか（実行計画）を確認する方法。
→ヒントの使用 [2008-04-29]
実行計画を照会するには、PLAN_TABLEというテーブルが必要になる。[2008-02-06]
このテーブルは以下のコマンド（スクリプトファイルの実行）で作成する。
> cd C:\oracle\ora92\rdbms\admin > sqlplus scott/tiger @utlxplan.sql
> sqlplus scott/tiger SQL> @ ?\rdbms\admin\utlxplan.sql
なお、これは「create table PLAN_TABLE」を実行しているだけなので、PLAN_TABLEが不要になったら普通にdropで削除すればよい。
実行計画を照会するには、「EXPLAIN PLAN FOR SQL文;」というSQLを実行する。[2008-02-06]
SQL> explain plan for
2 select * from emp;
select * from emp
*
行2でエラーが発生しました。:
ORA-02402: PLAN_TABLE表が見つかりません。
ただし、実行するにはPLAN_TABLEというテーブルが必要なので、作っていないなら作成する必要がある。
→PLAN_TABLEの作成
SQL> explain plan for 2 select * from emp; 解析されました。
解析されるだけかよ!? PostgreSQLと違ってケチ（というか不便）だなぁ…。
解析された結果を参照するにはPLAN_TABLEの中を見ればいいのだが、加工してやらないと分かりづらい。
utlxplp.sqlというスクリプトファイルを実行すると、見やすく表示してくれる。
SQL> @ ?/rdbms/admin/utlxplp.sql PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | TABLE ACCESS FULL | EMP | | | | -------------------------------------------------------------------- Note: rule based optimization 9行が選択されました。
これは、実質的には以下のSQL文（PL/SQL？）を実行しているだけなので、スクリプトファイルが無いとき（インストールされていないとき）はこれを実行すればよい。
SQL> select * from table(dbms_xplan.display());
SQL*Plusのシステム変数autotraceをONまたはTRACEONLYにすると、SQLを実行した際に実行計画が表示されるようになる。
SQL> set autotrace on
SP2-0613: PLAN_TABLEの形式または存在を検証できません。
SP2-0611: EXPLAINレポートを使用可能にするときにエラーが発生しました。
SP2-0618: セッション識別子が見つかりません。PLUSTRACEロールが使用可能かを確認してください。
SP2-0611: STATISTICSレポートを使用可能にするときにエラーが発生しました。
ただし、使用するにはPLAN_TABLEというテーブルが必要なので、作っていないなら作成する必要がある。
→PLAN_TABLEの作成
PLUSTRACEというロールもエラーメッセージに含まれているが、explainを付ける場合は要らないようだ。
SQL> set autotrace on explain …ONは、SQLの実行結果の表示後に実行計画を表示する。 SQL> set autot on exp …短縮形 SQL> set autotrace traceonly explain …TRACEONLYは、実行計画のみを表示する。 SQL> set autot trace exp …短縮形
SQL> set autot trace exp SQL> select * from emp; 実行計画 ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'EMP'
統計情報を取得・設定するには、DBMS_STATSパッケージを使用する。[2008-10-04]
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP')
統計情報がいつ収集されたのかは、user_tablesやuser_indexesのLAST_ANALYZEDで確認できる。[2010-07-20]
SQL> select LAST_ANALYZED from user_tables where table_name='EMP';
Oracle10gでは、統計情報が定期的に自動収集されるようになったらしい。（設定により変更可能）[2010-10-23]
参考： archive-redo-blogさんのオプティマイザ統計情報の収集は基本的にOracle任せでOK