EXPLAIN PLANを使ってみる
EXPLAIN PLANを使ってみる
０．更新履歴
１．はじめに
このドキュメントでは，Personal Oracle8i R8.1.7.0.0を使用して，Explain Planを使ってみる．
Explain Planとは，SELECT,INSERT,UPDATE,DELETE文について，オプティマイザが選択した実行計画を取得するコマンドで，パフォーマンス分析に用いる．
２．前準備
- Explain Planを実行するためには，表PLAN_TABLEをユーザ毎に作成しておく必要がある．
- ここでは，scottユーザを用いて，表PLAN_TABLEを作成するスクリプトutlxpaln.sqlを実行する．
C:\>sqlplus scott/tiger
SQL*Plus: Release 8.1.7.0.0 - Production on 土 Oct 27 21:14:55 2001
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Personal Oracle8i Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
に接続されました。
SQL> @d:\oracle\ora81\rdbms\admin\utlxplan.sql
表が作成されました。
SQL>
|
SQL> desc plan_table;
名前 NULL? 型
----------------------------------------- -------- -----------------------
STATEMENT_ID VARCHAR2(30)
TIMESTAMP DATE
REMARKS VARCHAR2(80)
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(30)
OBJECT_NODE VARCHAR2(128)
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
OBJECT_INSTANCE NUMBER(38)
OBJECT_TYPE VARCHAR2(30)
OPTIMIZER VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID NUMBER(38)
PARENT_ID NUMBER(38)
POSITION NUMBER(38)
COST NUMBER(38)
CARDINALITY NUMBER(38)
BYTES NUMBER(38)
OTHER_TAG VARCHAR2(255)
PARTITION_START VARCHAR2(255)
PARTITION_STOP VARCHAR2(255)
PARTITION_ID NUMBER(38)
OTHER LONG
DISTRIBUTION VARCHAR2(30)
SQL>
|
２．EXPLAIN PLANを実行してみる
- SQL*Plus上から，EXPLAIN PLANを実行してみる．
- EXPLAIN PLANでは，実行計画を取得したいSQL文を書くが，実際にはSQL文を実行しないため，稼動中Oracleにも負担が少ない．
SQL> EXPLAIN PLAN
2 SET statement_id = '初めてのEXPLAIN'
3 FOR
4 SELECT empno,ename FROM emp
5 WHERE sal = 5000
6 ;
Explainに成功しました.
SQL>
|
- 表PLAN_TABLEの中身を，ちょっとだけ見てみる．
SQL> SELECT count(*) FROM plan_table;
COUNT(*)
----------
2
SQL> SELECT statement_id,timestamp FROM plan_table;
STATEMENT_ID TIMESTAM
------------------------------ --------
初めてのEXPLAIN 01-10-27
初めてのEXPLAIN 01-10-27
SQL>
|
３．実行計画を確認してみる
- 前での通り，PLAN_TABLEは見辛い．
- よって，整形して表示してくれるスクリプトを使ってみる．
シリアル実行計画用 utlxpls.sqlの結果サンプル
SQL> set linesize 80
SQL> @d:\oracle\ora81\rdbms\admin\utlxpls.sql
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 2 | 28 | 1 | | |
| TABLE ACCESS FULL |EMP | 2 | 28 | 1 | | |
--------------------------------------------------------------------------------
SQL>
|
- パラレルクエリーを使っている場合の結果は，前出のスクリプトとは違う．
パラレル実行計画用 utlxplp.sqlの結果サンプル
SQL> set linesize 108
SQL> @d:\oracle\ora81\rdbms\admin\utlxplp.sql
Plan Table
------------------------------------------------------------------------------
| Operation |Name|Rows|Bytes|Cost|TQ|IN-OUT|PQ Distrib|Pstart|Pstop|
------------------------------------------------------------------------------
| SELECT STATEMENT | | 2| 28 | 1| | | | | |
| TABLE ACCESS FULL |EMP | 2| 28 | 1| | | | | |
------------------------------------------------------------------------------
SQL>
|
- 結果に"TABLE ACCESS FULL"となっているので，全表走査（フルスキャン）が行われている事がわかる．
- これらのスクリプトは，最後に行ったExplain Planを表示することに注意する．
４．empにインデックスをつけてみたりする
SQL> CREATE INDEX idx_emp_sale ON emp(sal);
索引が作成されました。
SQL>
|
SQL> EXPLAIN PLAN
2 SET statement_id = 'indexをつけてみた'
3 FOR
4 SELECT empno,ename FROM emp
5 WHERE sal = 5000
6 ;
Explainに成功しました.
SQL>
|
SQL> @d:\oracle\ora81\rdbms\admin\utlxpls.sql
Plan Table
---------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 2 | 28 | 1 | | |
| TABLE ACCESS FULL |EMP | 2 | 28 | 1 | | |
--------------------------------------------------------------------------------
SQL>
|
- フルスキャンされているので，インデックスは使われていない．
- たぶん，データ件数が少ないためだと推測する．
５．実験してみる
前出の結果では，インデックスをつけてもオプティマイザによって全表走査が選択されている理由は，データ件数が少ないと考えたので，次はデータ件数を多くしてテストしてみる．
SQL> CREATE TABLE TESTPLAN
2 (
3 AA char(10),
4 BB number(10,0)
5 )
6 ;
表が作成されました。
SQL>
|
SQL> begin
2 for i in 1..3000 loop
3 insert into testplan values('LOOP',i);
4 end loop;
5 end;
6 /
PL/SQLプロシージャが正常に完了しました。
SQL>
|
- この処理で，3000件のデータが登録された．
- この状態で，1行を選択するSELECT文を実行する場合の実行計画を表示してみる．
SQL> EXPLAIN PLAN
2 SET statement_id = 'NON index'
3 FOR
4 SELECT aa,bb FROM testplan
5 WHERE bb = 2000
6 ;
Explainに成功しました.
SQL> @d:\oracle\ora81\rdbms\admin\utlxpls.sql
Plan Table
---------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | | | | | |
| TABLE ACCESS FULL |TESTPLAN | | | | | |
--------------------------------------------------------------------------------
SQL>
|
- この時点では，全表走査が行われている．
- 次にインデックスをつける．
SQL> CREATE INDEX idx_testplan_bb ON testplan(bb);
索引が作成されました。
SQL>
|
- インデックスをつけた後に，Explain Planを実行してみる．
SQL> EXPLAIN PLAN
2 SET statement_id = 'INDEX'
3 FOR
4 SELECT aa,bb FROM testplan
5 WHERE bb = 2000
6 ;
Explainに成功しました.
SQL> @d:\oracle\ora81\rdbms\admin\utlxpls.sql
Plan Table
----------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | | | | | |
| TABLE ACCESS BY INDEX ROW|TESTPLAN | | | | | |
| INDEX RANGE SCAN |IDX_TESTP | | | | | |
--------------------------------------------------------------------------------
6行が選択されました。
SQL>
|
- 今度は，インデックスを使った検索をオプティマイザが選んだ事がわかる．
