OracleでSQLがどのように実行されるか︵実行計画︶を確認する方法。
●事前準備︵PLAN_TABLE作成︶ [2008-02-06]
●EXPLAIN文 [2008-02-06]
●autotrace
●統計情報の取得 [/2010-10-23]
→ヒントの使用 [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
参考
●Oracle10gのSQL*Plusのチューニング
●Oracle10gの問合せオプティマイザ
SQLメモへ戻る /
Oracle目次へ戻る /
新機能へ戻る /
技術メモへ戻る
メールの送信先‥ひしだま