トップ  > SQL*Plusの極意  > SQL*Plusで実行計画を知る
 



■ SQL*Plusで実行計画を知る  (● ̄□ ̄●)!!

■ autotrace
( ●・ω・●)y─┛

SQL*PlusからSQL文を実行した際に、そのSQL文の実行計画と統計情報を得ることができます。
開発者にとっては、非常に便利な機能だと言えます。

まずは、SQL*Plusから動的パフォーマンスビューを参照することができるようにユーザに権限を付与します。
その後PLAN_TABLEを作成すれば、「set autotrace on」でSQL文の実行計画と統計情報を得ることができます。
※実行計画を取る為のユーザを特別に作成する手順から説明しますが、既存のユーザを使用する場合は、その
部分を読み飛ばして下さい。

daruma> sqlplus 'system/manager as sysdba'


SQL*Plus: Release 9.2.0.1.0 - Production on 水 Apr 20 11:20:48 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.



Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
に接続されました。
SQL> CREATE USER TESTUSER IDENTIFIED BY TESTUSER; 

ユーザーが作成されました。

SQL> CREATE ROLE PLUSTRACE;

ロールが作成されました。

SQL> GRANT SELECT ON V_$SESSTAT TO PLUSTRACE;

権限付与が成功しました。

SQL> GRANT SELECT ON V_$STATNAME TO PLUSTRACE;

権限付与が成功しました。

SQL> GRANT SELECT ON V_$MYSTAT TO PLUSTRACE;

権限付与が成功しました。

SQL> GRANT SELECT ON V_$SESSION TO PLUSTRACE;

権限付与が成功しました。

SQL> GRANT PLUSTRACE TO DBA WITH ADMIN OPTION;

権限付与が成功しました。

SQL> GRANT DBA TO TESTUSER;

権限付与が成功しました。

SQL> exit
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Productionとの接続が切断されました。
daruma> sqlplus TESTUSER/TESTUSER


SQL*Plus: Release 9.2.0.1.0 - Production on 水 Apr 20 11:20:48 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.



Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
に接続されました。
SQL> @?/rdbms/admin/utlxplan.sql

表が作成されました。

SQL> DESC PLAN_TABLE
 名前                                      NULL?    型
 ----------------------------------------- -------- ----------------------------
 STATEMENT_ID                                       VARCHAR2(30)
 TIMESTAMP                                          DATE
 REMARKS                                            VARCHAR2(80)
 OPERATION                                          VARCHAR2(30)
 OPTIONS                                            VARCHAR2(255)
 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)
 CPU_COST                                           NUMBER(38)
 IO_COST                                            NUMBER(38)
 TEMP_SPACE                                         NUMBER(38)
 ACCESS_PREDICATES                                  VARCHAR2(4000)
 FILTER_PREDICATES                                  VARCHAR2(4000)

SQL>

/(●゚∀゚●)\

環境が整ったところで、「set autotrace on」として、SQL文の実行計画と統計情報を取得しましょう。

SQL> set autotrace on 
SQL> SELECT * FROM DUAL;

DUM
---
X

1行が選択されました。

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'DUAL'




統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        398  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

(●・д・●)y ☆パチッ よし!取得できました!

(●゚A゚●)┼─┤

「set autotrace on」以外にも、autotraceを中止する「set autotrace off」や、実行計画のみを表示する「set autotrace on explain」、
統計情報のみを表示する「set autotrace on statistics」、クエリ結果を表示しない「set autotrace traceonly」があります。

SQL> set autotrace off 
SQL> SELECT * FROM DUAL;

DUM
---
X

1行が選択されました。

SQL> set autotrace on explain 
SQL> SELECT * FROM DUAL;

DUM
---
X

1行が選択されました。

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'DUAL'

SQL> set autotrace on statistics 
SQL> SELECT * FROM DUAL;

DUM
---
X

1行が選択されました。

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        398  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace traceonly 
SQL> SELECT * FROM DUAL;

1行が選択されました。

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'DUAL'




統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        398  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>